test/sqlgw/Parameter/paratest4.c

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