test/sqlgw/Parameter/paratest6.c

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