test/adapter/Parameter/paratest2.c

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