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