test/sqlapi/Parameter/paratest3.c

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