test/adapter/Parameter/paratest3.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it.
   3  *  delete records from T1 with where clause
   4  *  select * from T1;
   5 */
   6
   7 #include "common.h"
   8 int main()
   9 {
  10   DbRetVal rv = OK;
  11   AbsSqlConnection *con = createConnection();
  12   rv = con->connect("root","manager");
  13   if(rv!=OK)return 1;
  14
  15   AbsSqlStatement *stmt = createStatement();
  16   stmt->setConnection(con);
  17   char statement[200];
  18    if(strcmp(getenv("DSN"),"oracle")==0)
  19       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));");
  20    else if(strcmp(getenv("DSN"),"psql")==0)
  21       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);");
  22    else if(strcmp(getenv("DSN"),"sybase")==0)
  23       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 DATETIME,f9 INT,f10 BIGINT);");
  24    else if(strcmp(getenv("DSN"),"db2")==0)
  25       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)");
  26    else
  27       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);");
  28
  29    int rows = 0;
  30    rv = stmt->prepare(statement);
  31    if(rv!=OK)
  32    {
  33      delete stmt;
  34      delete con;
  35      return 2;
  36    }
  37
  38    rv = stmt->execute(rows);
  39    if(rv!=OK)
  40    {
  41      delete stmt;
  42      delete con;
  43      return 3;
  44    }
  45    con->commit();
  46
  47    stmt->free();
  48    printf("Table T1 created\n");
  49
  50    // insert into table
  51
  52    if(strcmp(getenv("DSN"),"oracle")==0){
  53       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  54    }
  55    else if(strcmp(getenv("DSN"),"db2")==0){
  56       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  57    }
  58    else{
  59       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  60    }
  61
  62    int f1var = 0;
  63    short int f2var = 1;
  64    char f3var[35]="jitendra";
  65    float f4var = 5.5;
  66    float f5var = 10.50;
  67    Date f6var;
  68    f6var.set(2007,01,21);
  69
  70    Time f7var;
  71    f7var.set(12,29,30);
  72
  73    TimeStamp f8var;
  74    f8var.setDate(2007,01,21);
  75    f8var.setTime(12,29,30);
  76
  77    int f9var = 20;
  78    long long f10var = 12000;
  79
  80    rv = stmt->prepare(statement);
  81    if(rv!=OK)
  82    {
  83      delete stmt;
  84      delete con;
  85      return 4;
  86    }
  87    if(strcmp(getenv("DSN"),"psql")==0){
  88    con->rollback();
  89    }
  90
  91
  92    int count=0;
  93
  94    for(int i=0;i<10;i++)
  95    {
  96         rv  = con->beginTrans();
  97         if(rv!=OK) break;
  98         f1var=i;
  99         stmt->setIntParam(1,f1var);
 100         stmt->setShortParam(2,f2var);
 101         stmt->setStringParam(3,f3var);
 102         stmt->setFloatParam(4,f4var);
 103         stmt->setFloatParam(5,f5var);
 104         stmt->setDateParam(6,f6var);
 105         if(strcmp(getenv("DSN"),"oracle")==0){
 106             stmt->setTimeStampParam(7,f8var);
 107             stmt->setIntParam(8,f9var);
 108             stmt->setLongLongParam(9,f10var);
 109         }else{
 110             stmt->setTimeParam(7,f7var);
 111             stmt->setTimeStampParam(8,f8var);
 112             stmt->setIntParam(9,f9var);
 113             stmt->setLongLongParam(10,f10var);
 114         }
 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
 124
 125     stmt->free();
 126    //*********************************************************
 127
 128    //  delete records from the T1
 129    if(strcmp(getenv("DSN"),"oracle")==0)
 130         strcpy(statement,"DELETE FROM t1 WHERE F10=? AND F9=? AND F8=? AND F6=? AND F5=? AND F4=? AND F3=? AND F2=? AND F1=?;");
 131    else if(strcmp(getenv("DSN"),"sybase")==0)
 132         strcpy(statement,"DELETE FROM t1 WHERE f10=? AND f9=? AND f8=? AND f7=? AND f6=? AND f5=? AND f4=? AND f3=? AND f2=? AND f1=?;");
 133    else if(strcmp(getenv("DSN"),"db2")==0)
 134         strcpy(statement,"DELETE FROM t1 WHERE f10=? AND f9=? AND f8=? AND f7=? AND f6=? AND f5=? AND f4=? AND f3=? AND f2=? AND f1=?");
 135    else
 136         strcpy(statement,"DELETE FROM t1 WHERE F10=? AND F9=? AND F8=? AND F7=? AND F6=? AND F5=? AND F4=? AND F3=? AND F2=? AND F1=?;");
 137
 138    rv = stmt->prepare(statement);
 139    if(rv!=OK){delete stmt;delete con;return -1;}
 140    if(strcmp(getenv("DSN"),"psql")==0){
 141    con->rollback();
 142    }
 143
 144
 145   /* stmt->bindField(1,&f10var);
 146    stmt->bindField(2,&f9var);
 147    stmt->bindField(3,&f8var);
 148    stmt->bindField(4,&f7var);
 149    stmt->bindField(5,&f6var);
 150    stmt->bindField(6,&f5var);
 151    stmt->bindField(7,&f4var);
 152    stmt->bindField(8,f3var);
 153    stmt->bindField(9,&f2var);
 154    stmt->bindField(10,&f1var);*/
 155
 156    short int f2var1=1;
 157    char f3var1[20] ="jitendra";
 158    float f4var1 = 5.5;
 159    float f5var1 = 10.50;
 160    Date f6var1;
 161    f6var1.set(2007,1,21);
 162
 163    Time f7var1;
 164    f7var1.set(12,29,30);
 165
 166    TimeStamp f8var1;
 167    f8var1.setDate(2007,1,21);
 168    f8var1.setTime(12,29,30);
 169
 170    int f9var1=20;
 171    long long f10var1=12000;
 172    count=0;
 173
 174    for(int i=5;i<10;i++)
 175    {
 176       rv = con->beginTrans();
 177       if(rv!=OK)break;
 178
 179       stmt->setLongLongParam(1,f10var1);
 180       stmt->setIntParam(2,f9var1);
 181       stmt->setTimeStampParam(3,f8var1);
 182       if(strcmp(getenv("DSN"),"oracle")==0){
 183           stmt->setDateParam(4,f6var1);
 184           stmt->setFloatParam(5,f5var1);
 185           stmt->setFloatParam(6,f4var1);
 186           stmt->setStringParam(7,f3var1);
 187           stmt->setShortParam(8,f2var1);
 188           stmt->setIntParam(9,i);
 189       }else{
 190           stmt->setTimeParam(4,f7var1);
 191           stmt->setDateParam(5,f6var1);
 192           stmt->setFloatParam(6,f5var1);
 193           stmt->setFloatParam(7,f4var1);
 194           stmt->setStringParam(8,f3var1);
 195           stmt->setShortParam(9,f2var1);
 196           stmt->setIntParam(10,i);
 197
 198       }
 199
 200       rv = stmt->execute(rows);
 201       if(rv!=OK)break;
 202       rv = con->commit();
 203       if(rv!=OK)break;
 204       count++;
 205    }
 206    stmt->free();
 207    printf("Total row deleted=%d\n",count);
 208
 209
 210
 211
 212    //**********************************************************
 213
 214     // SELECT * FROM T1;
 215     strcpy(statement,"SELECT * FROM t1;");
 216     if(strcmp(getenv("DSN"),"db2")==0){
 217        strcpy(statement,"SELECT * FROM t1");
 218     }
 219     rv = stmt->prepare(statement);
 220     if(rv!=OK)
 221     {
 222       delete stmt;
 223       delete con;
 224       return 5;
 225     }
 226
 227     stmt->bindField(1,&f1var);
 228     stmt->bindField(2,&f2var);
 229     stmt->bindField(3,f3var);
 230     stmt->bindField(4,&f4var);
 231     stmt->bindField(5,&f5var);
 232     stmt->bindField(6,&f6var);
 233     if(strcmp(getenv("DSN"),"oracle")==0){
 234         stmt->bindField(7,&f8var);
 235         stmt->bindField(8,&f9var);
 236         stmt->bindField(9,&f10var);
 237
 238     }else{
 239         stmt->bindField(7,&f7var);
 240         stmt->bindField(8,&f8var);
 241         stmt->bindField(9,&f9var);
 242         stmt->bindField(10,&f10var);
 243    }
 244     count=0;
 245     rv = con->beginTrans();
 246     if(rv!=OK)return 6;
 247     stmt->execute(rows);
 248     while(stmt->fetch() !=NULL)
 249     {
 250         if(strcmp(getenv("DSN"),"oracle")==0)
 251             printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIMESTAMP=%d-%d-%d %d:%d:%d | F9=%d | F10=%lld  \n",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
 252         else
 253             printf("F1=%d | F2=%hd | F3=%s | F4=%f | F5=%f | DATE=%d-%d-%d | TIME=%d:%d:%d | TIMESTAMP=%d-%d-%d %d:%d:%d | F9=%d | F10=%lld  \n",f1var,f2var,f3var,f4var,f5var,f6var.year(),f6var.month(),f6var.dayOfMonth(),f7var.hours(),f7var.minutes(),f7var.seconds(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
 254
 255        count++;
 256     }
 257
 258     stmt->close();
 259     rv = con->commit();
 260     if(rv !=OK)
 261     {
 262       delete stmt;
 263       delete con;
 264       return 7;
 265     }
 266
 267     printf("Total row fetched=%d\n",count);
 268     stmt->free();
 269
 270
 271     strcpy(statement,"DROP TABLE t1;");
 272     if(strcmp(getenv("DSN"),"db2")==0){
 273         strcpy(statement,"DROP TABLE t1");
 274     }
 275     rv = stmt->prepare(statement);
 276     if(rv!=OK)
 277     {
 278       delete stmt;
 279       delete con;
 280       return 8;
 281     }
 282     rv = stmt->execute(rows);
 283     if(rv!=OK)
 284      {
 285       delete stmt;
 286       delete con;
 287       return 9;
 288     }
 289     stmt->free();
 290     printf("Table dropped\n");
 291     con->commit();
 292
 293     delete stmt;
 294     delete con;
 295     return 0;
 296 }