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