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