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