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"common.h"
  10
  11 int main()
  12 {
  13     DbRetVal rv = OK;
  14     AbsSqlConnection *con =  createConnection();
  15     rv = con->connect("root","manager");
  16     if(rv!=OK)return 1;
  17     AbsSqlStatement *stmt = createStatement();
  18     stmt->setConnection(con);
  19     char statement[200];
  20     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));");
  21     int rows = 0;
  22     rv = stmt->prepare(statement);
  23     if(rv!=OK) { delete stmt; delete con; return 2; }
  24     rv = stmt->execute(rows);
  25     if(rv!=OK) { delete stmt; delete con; return 3; }
  26     stmt->free();
  27     printf("Table T1 created\n");
  28     // insert into table
  29     strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
  30     int f1var = 0;
  31     short int f2var = 1;
  32     char f3var[32]="jitendra";
  33     float f4var = 5.5;
  34     float f5var = 10.50;
  35     Date f6var;
  36     f6var.set(2007,01,21);
  37     Time f7var;
  38     f7var.set(12,29,30);
  39     TimeStamp f8var;
  40     f8var.setDate(2007,01,21);
  41     f8var.setTime(12,29,30);
  42     int f9var = 20;
  43     long long f10var = 12000;
  44     char f11var[12]="23fe";
  45     rv = stmt->prepare(statement);
  46     if(rv!=OK) { delete stmt; delete con; return 4; }
  47     int count=0;
  48     for(int i=0;i<10;i++) {
  49             rv  = con->beginTrans();
  50             if(rv!=OK) break;
  51             f1var=i;
  52         stmt->setIntParam(1,f1var);
  53         stmt->setShortParam(2,f2var);
  54         stmt->setStringParam(3,f3var);
  55         stmt->setFloatParam(4,f4var);
  56         stmt->setFloatParam(5,f5var);
  57         stmt->setDateParam(6,f6var);
  58         stmt->setTimeParam(7,f7var);
  59         stmt->setTimeStampParam(8,f8var);
  60         stmt->setIntParam(9,f9var);
  61         stmt->setLongLongParam(10,f10var);
  62         stmt->setBinaryParam(11,f11var, 12);
  63         rv = stmt->execute(rows);
  64         if(rv!=OK)break;
  65         rv = con->commit();
  66         if(rv!=OK)break;
  67         count++;
  68     }
  69     printf("Total rows inserted %d\n",count);
  70     stmt->free();
  71     //*********************************************************
  72     //  delete records from the T1
  73     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=?;");
  74     rv = stmt->prepare(statement);
  75     if(rv!=OK){delete stmt;delete con;return -1;}
  76     short int f2var1=1;
  77     char f3var1[32] ="jitendra";
  78     float f4var1 = 5.5;
  79     float f5var1 = 10.50;
  80     Date f6var1;
  81     f6var1.set(2007,1,21);
  82     Time f7var1;
  83     f7var1.set(12,29,30);
  84     TimeStamp f8var1;
  85     f8var1.setDate(2007,1,21);
  86     f8var1.setTime(12,29,30);
  87     int f9var1=20;
  88     long long f10var1=12000;
  89     char f11var1[12]="23fe";
  90     count=0;
  91     for(int i=5;i<10;i++) {
  92         rv = con->beginTrans();
  93         if(rv!=OK)break;
  94         stmt->setLongLongParam(1,f10var1);
  95         stmt->setIntParam(2,f9var1);
  96         stmt->setTimeStampParam(3,f8var1);
  97         stmt->setTimeParam(4,f7var1);
  98         stmt->setDateParam(5,f6var1);
  99         stmt->setFloatParam(6,f5var1);
 100         stmt->setFloatParam(7,f4var1);
 101         stmt->setStringParam(8,f3var1);
 102         stmt->setShortParam(9,f2var1);
 103         stmt->setIntParam(10,i);
 104         stmt->setBinaryParam(11,f11var, 12);
 105         rv = stmt->execute(rows);
 106         if(rv!=OK)break;
 107         rv = con->commit();
 108         if(rv!=OK)break;
 109         count++;
 110     }
 111     stmt->free();
 112     printf("Total row deleted=%d\n",count);
 113     //**********************************************************
 114     // SELECT * FROM T1;
 115     strcpy(statement,"SELECT * FROM T1;");
 116     rv = stmt->prepare(statement);
 117     if(rv!=OK) { delete stmt; delete con; return 5; }
 118     stmt->bindField(1,&f1var);
 119     stmt->bindField(2,&f2var);
 120     stmt->bindField(3,f3var);
 121     stmt->bindField(4,&f4var);
 122     stmt->bindField(5,&f5var);
 123     stmt->bindField(6,&f6var);
 124     stmt->bindField(7,&f7var);
 125     stmt->bindField(8,&f8var);
 126     stmt->bindField(9,&f9var);
 127     stmt->bindField(10,&f10var);
 128     stmt->bindField(11,f11var);
 129     count=0;
 130     rv = con->beginTrans();
 131     if(rv!=OK)return 6;
 132     stmt->execute(rows);
 133     while(stmt->fetch() !=NULL) {
 134         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);
 135         printf(" | ");
 136         AllDataType::printVal(f11var,typeBinary,4);
 137         printf("\n");
 138         count++;
 139     }
 140     stmt->close();
 141     rv = con->commit();
 142     if(rv !=OK) { delete stmt; delete con; return 7; }
 143     printf("Total row fetched=%d\n",count);
 144     stmt->free();
 145     strcpy(statement,"DROP TABLE T1;");
 146     rv = stmt->prepare(statement);
 147     if(rv!=OK) { delete stmt; delete con; return 8; }
 148     rv = stmt->execute(rows);
 149     if(rv!=OK) { delete stmt; delete con; return 9; }
 150     printf("Table dropped\n");
 151     stmt->free(); delete stmt; delete con;
 152     return 0;
 153 }