test/adapter/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   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,f2 SMALLINT,f3 CHAR(30),f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 DATETIME,f9 INT,f10 BIGINT);");
  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)
  32    {
  33      delete stmt;
  34      delete con;
  35      return 1;
  36    }
  37
  38    rv = stmt->execute(rows);
  39    if(rv!=OK)
  40    {
  41      delete stmt;
  42      delete con;
  43      return 2;
  44    }
  45    con->commit();
  46    stmt->free();
  47    printf("Table created\n");
  48
  49    // insert into table
  50    if(strcmp(getenv("DSN"),"oracle")==0){
  51       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  52    }
  53    else if(strcmp(getenv("DSN"),"db2")==0)
  54       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  55    else{
  56       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  57    }
  58
  59    int f1var = 0;
  60    short int f2var = 10;
  61    char f3var[35]="jitendra";
  62    float f4var = 5.5;
  63    float f5var = 10.50;
  64    Date f6var;
  65    f6var.set(2008,01,21);
  66
  67    Time f7var;
  68    f7var.set(12,29,30);
  69
  70    TimeStamp f8var;
  71    f8var.setDate(2008,01,21);
  72    f8var.setTime(12,29,30);
  73
  74    int f9var = 20;
  75    long long f10var = 12000;
  76
  77    rv = stmt->prepare(statement);
  78    if(rv!=OK)
  79    {
  80      delete stmt;
  81      delete con;
  82      return 3;
  83    }
  84    if(strcmp(getenv("DSN"),"psql")==0){
  85    con->rollback();
  86    }
  87
  88
  89    int count=0;
  90
  91    for(int i=0;i<10;i++)
  92    {
  93         rv  = con->beginTrans();
  94         if(rv!=OK) break;
  95         f1var=i;
  96         f2var++;
  97         stmt->setIntParam(1,f1var);
  98         stmt->setShortParam(2,f2var);
  99         stmt->setStringParam(3,f3var);
 100         stmt->setFloatParam(4,f4var);
 101         stmt->setFloatParam(5,f5var);
 102         stmt->setDateParam(6,f6var);
 103         if(strcmp(getenv("DSN"),"oracle")==0){
 104             stmt->setTimeStampParam(7,f8var);
 105             stmt->setIntParam(8,f9var);
 106             stmt->setLongLongParam(9,f10var);
 107         }else{
 108             stmt->setTimeParam(7,f7var);
 109             stmt->setTimeStampParam(8,f8var);
 110             stmt->setIntParam(9,f9var);
 111             stmt->setLongLongParam(10,f10var);
 112         }
 113         rv = stmt->execute(rows);
 114         if(rv!=OK)break;
 115         rv = con->commit();
 116         if(rv!=OK)break;
 117         count++;
 118     }
 119     printf("%d Rows inserted \n",count);
 120
 121
 122     stmt->free();
 123
 124     //**********************************************************
 125     // UPDATE TABLE
 126     if(strcmp(getenv("DSN"),"sybase")==0)
 127         strcpy(statement,"UPDATE t1 SET f10=?,f8=?,f6=?,f5=?,f4=?,f3=? WHERE f1=?;");
 128     else if(strcmp(getenv("DSN"),"db2")==0)
 129         strcpy(statement,"UPDATE t1 SET f10=?,f8=?,f6=?,f5=?,f4=?,f3=? WHERE f1=?");
 130     else
 131         strcpy(statement,"UPDATE t1 SET F10=?,F8=?,F6=?,F5=?,F4=?,F3=? WHERE F1=?;");
 132     rv = stmt->prepare(statement);
 133    if(strcmp(getenv("DSN"),"psql")==0){
 134    con->rollback();
 135    }
 136
 137
 138      int nop;
 139     nop = stmt->noOfParamFields();
 140     printf("noOfParamFields return=%d\n",nop);
 141     if(nop!=7)return 4;
 142
 143     long long f10var1=15000;
 144     TimeStamp f8var1;
 145     f8var1.setDate(2007,3,30);
 146     f8var1.setTime(10,25,55);
 147
 148     Date f6var1;
 149     f6var1.set(2007,3,30);
 150
 151     float f5var1=20.25;
 152     float f4var1=11.22;
 153     char f3var1[10]="lakshya";
 154
 155      int f1var1;
 156     count=0;
 157
 158     for(int i=0;i<5;i++)
 159     {
 160
 161          rv = con->beginTrans();
 162          if(rv!=OK)break;
 163
 164          f1var1=i;
 165          stmt->setIntParam(7,f1var1);
 166          stmt->setLongLongParam(1,f10var1);
 167          stmt->setTimeStampParam(2,f8var1);
 168          stmt->setDateParam(3,f6var1);
 169          stmt->setFloatParam(4,f5var1);
 170          stmt->setFloatParam(5,f4var1);
 171          stmt->setStringParam(6,f3var1);
 172
 173          //stmt->setShortParam(7,f2var1);
 174
 175          rv = stmt->execute(rows);
 176          if(rv!=OK)break;
 177          rv  = con->commit();
 178          if(rv!=OK)break;
 179          count++;
 180      }
 181
 182      stmt->free();
 183
 184      printf("%d Rows updated\n",count);
 185
 186     //**********************************************************
 187     // SELECT * FROM T1;
 188
 189     strcpy(statement,"SELECT * FROM t1;");
 190     if(strcmp(getenv("DSN"),"db2")==0)
 191         strcpy(statement,"SELECT * FROM t1");
 192     rv = stmt->prepare(statement);
 193     if(rv!=OK)
 194     {
 195       delete stmt;
 196       delete con;
 197       return 5;
 198     }
 199
 200     stmt->bindField(1,&f1var);
 201     stmt->bindField(2,&f2var);
 202     stmt->bindField(3,f3var);
 203     stmt->bindField(4,&f4var);
 204     stmt->bindField(5,&f5var);
 205     stmt->bindField(6,&f6var);
 206      if(strcmp(getenv("DSN"),"oracle")==0){
 207         stmt->bindField(7,&f8var);
 208         stmt->bindField(8,&f9var);
 209         stmt->bindField(9,&f10var);
 210
 211     }else{
 212         stmt->bindField(7,&f7var);
 213         stmt->bindField(8,&f8var);
 214         stmt->bindField(9,&f9var);
 215         stmt->bindField(10,&f10var);
 216    }
 217
 218     count=0;
 219     rv = con->beginTrans();
 220     if(rv!=OK)return 6;
 221     stmt->execute(rows);
 222     while(stmt->fetch() !=NULL)
 223     {
 224        if(f1var==5)printf("\n");
 225        if(strcmp(getenv("DSN"),"oracle")==0)
 226              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);
 227        else
 228             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);
 229
 230
 231        count++;
 232     }
 233
 234     stmt->close();
 235     rv = con->commit();
 236     if(rv !=OK)
 237     {
 238       delete stmt;
 239       delete con;
 240       return 7;
 241     }
 242
 243     printf("%d Rows fetched\n",count);
 244     stmt->free();
 245
 246
 247     strcpy(statement,"DROP TABLE t1;");
 248     if(strcmp(getenv("DSN"),"db2")==0)
 249         strcpy(statement,"DROP TABLE t1");
 250     rv = stmt->prepare(statement);
 251     if(rv!=OK)
 252     {
 253       delete stmt;
 254       delete con;
 255       return 8;
 256     }
 257     rv = stmt->execute(rows);
 258     if(rv!=OK)
 259      {
 260       delete stmt;
 261       delete con;
 262       return 9;
 263     }
 264     stmt->free();
 265     printf("Table dropped\n");
 266     con->commit();
 267     delete stmt;
 268     delete con;
 269     return 0;
 270 }