test/adapter/Parameter/paratest6.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it.
   3  *  update T1 with some Null values in the parameter.
   4     noOfParamFields() should return 8.
   5  *  select * from T1;
   6 */
   7
   8 #include "common.h"
   9 int main()
  10 {
  11     DbRetVal rv = OK;
  12     AbsSqlConnection *con = createConnection();
  13     rv = con->connect("root","manager");
  14     if(rv!=OK)return 1;
  15
  16     AbsSqlStatement *stmt = createStatement();
  17     stmt->setConnection(con);
  18
  19     char statement[200];
  20     if(strcmp(getenv("DSN"),"oracle")==0)
  21       strcpy(statement,"CREATE TABLE t1(f1 number(9),f2 number(4),f3 CHAR(30),f4 FLOAT,f5 FLOAT,f6 DATE,f8 TIMESTAMP,f9 number(9),f10 number(18));");
  22    else if(strcmp(getenv("DSN"),"psql")==0)
  23       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 FLOAT4,f5 FLOAT4,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
  24    else if(strcmp(getenv("DSN"),"sybase")==0)
  25       strcpy(statement,"CREATE TABLE t1(f1 INT NULL,f2 SMALLINT NULL,f3 CHAR(30) NULL,f4 REAL NULL,f5 REAL NULL,f6 DATE NULL,f7 TIME NULL,f8 DATETIME NULL,f9 INT NULL,f10 BIGINT NULL);");
  26    else if(strcmp(getenv("DSN"),"db2")==0)
  27       strcpy(statement,"CREATE TABLE t1(f1 INT ,f2 SMALLINT ,f3 CHAR(30) ,f4 REAL ,f5 REAL ,f6 DATE ,f7 TIME ,f8 TIMESTAMP ,f9 INT ,f10 BIGINT )");
  28    else
  29       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 FLOAT,f5 FLOAT,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
  30     int rows = 0;
  31     rv = stmt->prepare(statement);
  32     if(rv!=OK) { delete stmt; delete con; return 2; }
  33     rv = stmt->execute(rows);
  34     if(rv!=OK) { delete stmt; delete con; return 3; }
  35     con->commit();
  36     stmt->free();
  37     printf("Table created\n");
  38     // insert into table
  39     if(strcmp(getenv("DSN"),"oracle")==0){
  40       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  41    }
  42    else if(strcmp(getenv("DSN"),"db2")==0)
  43       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  44    else{
  45       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  46    }
  47
  48     int f1var = 0;
  49     short int f2var = 10;
  50     char f3var[32]="jitendra";
  51     float f4var = 5.5;
  52     float f5var = 10.50;
  53     Date f6var;
  54     f6var.set(2008,01,21);
  55     Time f7var;
  56     f7var.set(12,29,30);
  57     TimeStamp f8var;
  58     f8var.setDate(2008,01,21);
  59     f8var.setTime(12,29,30);
  60     int f9var = 20;
  61     long long f10var = 12000;
  62     char f11var[8]="23fe";
  63     rv = stmt->prepare(statement);
  64     if(rv!=OK) { delete stmt; delete con; return 4; }
  65    if(strcmp(getenv("DSN"),"psql")==0){
  66    con->rollback();
  67    }
  68
  69     int count=0;
  70     for(int i=0;i<10;i++) {
  71         rv  = con->beginTrans();
  72             if(rv!=OK) break;
  73             f1var=i;
  74         f2var++;
  75         stmt->setIntParam(1,f1var);
  76         stmt->setShortParam(2,f2var);
  77         stmt->setStringParam(3,f3var);
  78         stmt->setFloatParam(4,f4var);
  79         stmt->setFloatParam(5,f5var);
  80         stmt->setDateParam(6,f6var);
  81         if(strcmp(getenv("DSN"),"oracle")==0){
  82             stmt->setTimeStampParam(7,f8var);
  83             stmt->setIntParam(8,f9var);
  84             stmt->setLongLongParam(9,f10var);
  85         }else{
  86             stmt->setTimeParam(7,f7var);
  87             stmt->setTimeStampParam(8,f8var);
  88             stmt->setIntParam(9,f9var);
  89             stmt->setLongLongParam(10,f10var);
  90         }
  91     //    stmt->setBinaryParam(11,f11var,8);
  92         rv = stmt->execute(rows);
  93         if(rv!=OK)break;
  94         rv = con->commit();
  95             if(rv!=OK)break;
  96         count++;
  97     }
  98     printf("%d Rows inserted \n",count);
  99     stmt->free();
 100     //**********************************************************
 101     // UPDATE TABLE
 102     if(strcmp(getenv("DSN"),"sybase")==0)
 103         strcpy(statement,"UPDATE t1 SET f10=?,f8=?,f6=?,f5=?,f4=?,f3=? WHERE f2=?;");
 104     else if(strcmp(getenv("DSN"),"db2")==0)
 105         strcpy(statement,"UPDATE t1 SET f10=?,f8=?,f6=?,f5=?,f4=?,f3=? WHERE f2=?");
 106     else
 107         strcpy(statement,"UPDATE t1 SET F10=?,F8=?,F6=?,F5=?,F4=?,F3=? WHERE F2=?;");
 108     rv = stmt->prepare(statement);
 109    if(strcmp(getenv("DSN"),"psql")==0){
 110    con->rollback();
 111    }
 112
 113     int nop;
 114     nop = stmt->noOfParamFields();
 115     printf("noOfParamFields return=%d\n",nop);
 116     if(nop!=7)return 5;
 117     long long f10var1=15000;
 118     TimeStamp f8var1;
 119     f8var1.setDate(2007,3,30);
 120     f8var1.setTime(10,25,55);
 121     Date f6var1;
 122     f6var1.set(2007,3,30);
 123     float f5var1=20.25;
 124     float f4var1=11.22;
 125     char f3var1[10]="lakshya";
 126     char f11var1[8]="ab12";
 127     short int f2var1=10;
 128     count=0;
 129     for(int i=0;i<5;i++) {
 130         rv = con->beginTrans();
 131         if(rv!=OK)break;
 132         f2var1++;
 133         stmt->setShortParam(7,f2var1);
 134         stmt->setLongLongParam(1,f10var1);
 135         stmt->setTimeStampParam(2,f8var1);
 136         stmt->setNull(3);
 137         stmt->setFloatParam(4,f5var1);
 138         stmt->setFloatParam(5,f4var1);
 139         stmt->setNull(6);
 140         //stmt->setBinaryParam(7,f11var1, 8);
 141         rv = stmt->execute(rows);
 142         if(rv!=OK)break;
 143         rv  = con->commit();
 144         if(rv!=OK)break;
 145         count++;
 146     }
 147     stmt->free();
 148     printf("%d Rows updated\n",count);
 149     //**********************************************************
 150     // SELECT * FROM T1;
 151     strcpy(statement,"SELECT * FROM t1;");
 152     if(strcmp(getenv("DSN"),"db2")==0)
 153         strcpy(statement,"SELECT * FROM t1");
 154     rv = stmt->prepare(statement);
 155     if(rv!=OK) { delete stmt; delete con; return 6; }
 156     stmt->bindField(1,&f1var);
 157     stmt->bindField(2,&f2var);
 158     stmt->bindField(3,f3var);
 159     stmt->bindField(4,&f4var);
 160     stmt->bindField(5,&f5var);
 161     stmt->bindField(6,&f6var);
 162     if(strcmp(getenv("DSN"),"oracle")==0){
 163         stmt->bindField(7,&f8var);
 164         stmt->bindField(8,&f9var);
 165         stmt->bindField(9,&f10var);
 166
 167     }else{
 168         stmt->bindField(7,&f7var);
 169         stmt->bindField(8,&f8var);
 170         stmt->bindField(9,&f9var);
 171         stmt->bindField(10,&f10var);
 172    }
 173     //stmt->bindField(11,f11var);
 174     count=0;
 175     rv = con->beginTrans();
 176     if(rv!=OK)return 7;
 177     stmt->execute(rows);
 178     while(stmt->fetch() !=NULL) {
 179         if(stmt->isFldNull(1)) printf("F1=NULL | ");
 180         else printf("F1=%d | ", f1var);
 181         if(stmt->isFldNull(2)) printf("F2=NULL | ");
 182         else printf("F2=%hd | ", f2var);
 183         if(stmt->isFldNull(3)) printf("F3=NULL | ");
 184         else printf("F3=%s | ", f3var);
 185         if(stmt->isFldNull(4)) printf("F4=NULL | ");
 186         else printf("F4=%f | ", f4var);
 187         if(stmt->isFldNull(5)) printf("F5=NULL | ");
 188         else printf("F5=%f | ", f5var);
 189         if(stmt->isFldNull(6)) printf("F6=NULL | ");
 190         else printf("F6=%02d-%02d-%02d | ",f6var.year(), f6var.month(), f6var.dayOfMonth());
 191         if(strcmp(getenv("DSN"),"oracle")!=0){
 192            if(stmt->isFldNull(7)) printf("F7=NULL | ");
 193            else printf("F7=%02d:%02d:%02d | ", f7var.hours(),f7var.minutes(),f7var.seconds());
 194         }
 195         if(stmt->isFldNull(8)) printf("F8=NULL | ");
 196         else printf("F8=%d-%d-%d %d:%d:%d | ", f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds());
 197         if(stmt->isFldNull(9)) printf("F9=NULL | ");
 198         else printf("F9=%d | ", f9var);
 199         if(stmt->isFldNull(10)) printf("F10=NULL | ");
 200         else printf("F10=%lld | ", f10var);
 201         printf("\n");
 202         count++;
 203     }
 204     stmt->close();
 205     rv = con->commit();
 206     if(rv !=OK) { delete stmt; delete con; return 8; }
 207     printf("Rows fetched=%d\n",count);
 208     stmt->free();
 209     strcpy(statement,"DROP TABLE t1;");
 210     if(strcmp(getenv("DSN"),"db2")==0)
 211         strcpy(statement,"DROP TABLE t1");
 212     rv = stmt->prepare(statement);
 213     if(rv!=OK) { delete stmt; delete con; return 9; }
 214     rv = stmt->execute(rows);
 215     if(rv!=OK) { delete stmt; delete con; return 10; }
 216     con->commit();
 217     printf("Table dropped\n");
 218     stmt->free(); delete stmt; delete con;
 219     return 0;
 220 }