test/sqlgw/Parameter/paratest5.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it with null values
   3  *  delete records from T1 with where clause
   4  *
   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     char statement[200];
  19     int rows = 0;
  20
  21     // insert into table
  22     if(strcmp(getenv("DSN"),"oracle")==0){
  23      strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  24     }
  25     else if(strcmp(getenv("DSN"),"db2")==0){
  26         strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  27     } else {
  28         strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  29     }
  30
  31     int f1var = 0;
  32     short int f2var = 1;
  33     char f3var[32]="jitendra";
  34     float f4var = 5.5;
  35     float f5var = 10.50;
  36     Date f6var;
  37     f6var.set(2007,01,21);
  38     Time f7var;
  39     f7var.set(12,29,30);
  40     TimeStamp f8var;
  41     f8var.setDate(2007,01,21);
  42     f8var.setTime(12,29,30);
  43     int f9var = 20;
  44     long long f10var = 12000;
  45     char f11var[12]="23fe";
  46     rv = stmt->prepare(statement);
  47     if(rv!=OK) { delete stmt; delete con; return 4; }
  48    if(strcmp(getenv("DSN"),"psql")==0){
  49    con->rollback();
  50    }
  51
  52     int count=0;
  53     for(int i=0;i<10;i++) {
  54             rv  = con->beginTrans();
  55             if(rv!=OK) break;
  56             f1var=i;
  57         stmt->setIntParam(1,f1var);
  58         stmt->setShortParam(2,f2var);
  59         stmt->setStringParam(3,f3var);
  60         stmt->setFloatParam(4,f4var);
  61         stmt->setNull(5);
  62         stmt->setDateParam(6,f6var);
  63         if(strcmp(getenv("DSN"),"oracle")==0){
  64             stmt->setTimeStampParam(7,f8var);
  65             stmt->setIntParam(8,f9var);
  66             stmt->setLongLongParam(9,f10var);
  67         }else{
  68             stmt->setNull(7);
  69             stmt->setTimeStampParam(8,f8var);
  70             stmt->setIntParam(9,f9var);
  71             stmt->setLongLongParam(10,f10var);
  72         //stmt->setBinaryParam(11,f11var, 12);
  73         }
  74         rv = stmt->execute(rows);
  75         if(rv!=OK)break;
  76         rv = con->commit();
  77         if(rv!=OK)break;
  78         count++;
  79     }
  80     stmt->free();
  81     //*********************************************************
  82     //  Insert with value list records from the T1
  83     if(strcmp(getenv("DSN"),"oracle")==0)
  84         strcpy(statement,"INSERT INTO t1(f1,f2,f3,f4,f5,f6,f8,f9,f10) VALUES(?,?,?,?,?,?,?,?,?);");
  85     else if(strcmp(getenv("DSN"),"db2")==0)
  86         strcpy(statement,"INSERT INTO t1(f1,f2,f3,f4,f5,f6,f7,f8,f9,f10) VALUES(?,?,?,?,?,?,?,?,?,?)");
  87     else
  88         strcpy(statement,"INSERT INTO t1(f1,f2,f3,f4,f5,f6,f7,f8,f9,f10) VALUES(?,?,?,?,?,?,?,?,?,?);");
  89
  90     rv = stmt->prepare(statement);
  91     if(rv!=OK) { delete stmt; delete con; return 4; }
  92    if(strcmp(getenv("DSN"),"psql")==0){
  93    con->rollback();
  94    }
  95
  96     for(int i=0;i<10;i++) {
  97             rv  = con->beginTrans();
  98             if(rv!=OK) break;
  99             f1var=i;
 100         stmt->setIntParam(1,f1var);
 101         stmt->setShortParam(2,f2var);
 102         stmt->setStringParam(3,f3var);
 103         stmt->setFloatParam(4,f4var);
 104         stmt->setNull(5);
 105         stmt->setDateParam(6,f6var);
 106         if(strcmp(getenv("DSN"),"oracle")==0){
 107             stmt->setTimeStampParam(7,f8var);
 108             stmt->setIntParam(8,f9var);
 109             stmt->setLongLongParam(9,f10var);
 110         }else{
 111             stmt->setNull(7);
 112             stmt->setTimeStampParam(8,f8var);
 113             stmt->setIntParam(9,f9var);
 114             stmt->setLongLongParam(10,f10var);
 115         }
 116         rv = stmt->execute(rows);
 117         if(rv!=OK)break;
 118         rv = con->commit();
 119         if(rv!=OK)break;
 120         count++;
 121     }
 122     printf("Total rows inserted %d\n",count);
 123     stmt->free();
 124
 125     //**********************************************************
 126     // SELECT * FROM T1;
 127     strcpy(statement,"SELECT * FROM t1;");
 128     if(strcmp(getenv("DSN"),"db2")==0){
 129         strcpy(statement,"SELECT * FROM t1");
 130     }
 131     rv = stmt->prepare(statement);
 132     if(rv!=OK) { delete stmt; delete con; return 5; }
 133     stmt->bindField(1,&f1var);
 134     stmt->bindField(2,&f2var);
 135     stmt->bindField(3,f3var);
 136     stmt->bindField(4,&f4var);
 137     stmt->bindField(5,&f5var);
 138     stmt->bindField(6,&f6var);
 139     if(strcmp(getenv("DSN"),"oracle")==0){
 140         stmt->bindField(7,&f8var);
 141         stmt->bindField(8,&f9var);
 142         stmt->bindField(9,&f10var);
 143
 144     }else{
 145         stmt->bindField(7,&f7var);
 146         stmt->bindField(8,&f8var);
 147         stmt->bindField(9,&f9var);
 148         stmt->bindField(10,&f10var);
 149    }
 150     //stmt->bindField(11,f11var);
 151     count=0;
 152     rv = con->beginTrans();
 153     if(rv!=OK)return 6;
 154     stmt->execute(rows);
 155     while(stmt->fetch() !=NULL) {
 156         if(stmt->isFldNull(1)) printf("F1=NULL | ");
 157         else printf("F1=%d | ", f1var);
 158         if(stmt->isFldNull(2)) printf("F2=NULL | ");
 159         else printf("F2=%hd | ", f2var);
 160         if(stmt->isFldNull(3)) printf("F3=NULL | ");
 161         else printf("F3=%s | ", f3var);
 162         if(stmt->isFldNull(4)) printf("F4=NULL | ");
 163         else printf("F4=%f | ", f4var);
 164         if(stmt->isFldNull(5)) printf("F5=NULL | ");
 165         else printf("F5=%f | ", f5var);
 166         if(stmt->isFldNull(6)) printf("F6=NULL | ");
 167         else printf("F6=%02d-%02d-%02d | ",f6var.year(), f6var.month(), f6var.dayOfMonth());
 168         if(strcmp(getenv("DSN"),"oracle") != 0){
 169         if(stmt->isFldNull(7)) printf("F7=NULL | ");
 170         else printf("F7=%02d:%02d:%02d | ", f7var.hours(),f7var.minutes(),f7var.seconds());
 171         }
 172         if(stmt->isFldNull(8)) printf("F8=NULL | ");
 173         else printf("F8=%d-%d-%d %d:%d:%d | ", f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds());
 174         if(stmt->isFldNull(9)) printf("F9=NULL | ");
 175         else printf("F9=%d | ", f9var);
 176         if(stmt->isFldNull(10)) printf("F10=NULL | ");
 177         else printf("F10=%lld | ", f10var);
 178         printf("\n");
 179         count++;
 180     }
 181     stmt->close();
 182     rv = con->commit();
 183     if(rv !=OK) { delete stmt; delete con; return 7; }
 184     printf("Total row fetched=%d\n",count);
 185     stmt->free(); delete stmt; delete con;
 186     return 0;
 187 }