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