test/sqlapi/Parameter/paratest5.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it with null values
   3  *  delete records from T1 with where clause
   4  *
   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 2; }
  25     rv = stmt->execute(rows);
  26     if(rv!=OK) { delete stmt; delete con; return 3; }
  27     stmt->free();
  28     printf("Table T1 created\n");
  29     // insert into table
  30     strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
  31     int f1var = 0;
  32     short int f2var = 1;
  33     char f3var[32]="jitendra";
  34     float f4var = 5.5;
  35     float f5var = 10.50;
  36     Date f6var;
  37     f6var.set(2007,01,21);
  38     Time f7var;
  39     f7var.set(12,29,30);
  40     TimeStamp f8var;
  41     f8var.setDate(2007,01,21);
  42     f8var.setTime(12,29,30);
  43     int f9var = 20;
  44     long long f10var = 12000;
  45     char f11var[12]="23fe";
  46     rv = stmt->prepare(statement);
  47     if(rv!=OK) { delete stmt; delete con; return 4; }
  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         stmt->setIntParam(1,f1var);
  54         stmt->setShortParam(2,f2var);
  55         stmt->setStringParam(3,f3var);
  56         stmt->setFloatParam(4,f4var);
  57         stmt->setNull(5);
  58         stmt->setDateParam(6,f6var);
  59         stmt->setNull(7);
  60         stmt->setTimeStampParam(8,f8var);
  61         stmt->setIntParam(9,f9var);
  62         stmt->setLongLongParam(10,f10var);
  63         stmt->setBinaryParam(11,f11var, 12);
  64         rv = stmt->execute(rows);
  65         if(rv!=OK)break;
  66         rv = con->commit();
  67         if(rv!=OK)break;
  68         count++;
  69     }
  70     stmt->free();
  71     //*********************************************************
  72     //  Insert with value list records from the T1
  73     strcpy(statement,"INSERT INTO T1(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) VALUES(?,?,?,?,?,?,?,?,?,?);");
  74     rv = stmt->prepare(statement);
  75     if(rv!=OK) { delete stmt; delete con; return 4; }
  76     for(int i=0;i<10;i++) {
  77             rv  = con->beginTrans();
  78             if(rv!=OK) break;
  79             f1var=i;
  80         stmt->setIntParam(1,f1var);
  81         stmt->setShortParam(2,f2var);
  82         stmt->setStringParam(3,f3var);
  83         stmt->setFloatParam(4,f4var);
  84         stmt->setNull(5);
  85         stmt->setDateParam(6,f6var);
  86         stmt->setNull(7);
  87         stmt->setTimeStampParam(8,f8var);
  88         stmt->setIntParam(9,f9var);
  89         stmt->setLongLongParam(10,f10var);
  90         rv = stmt->execute(rows);
  91         if(rv!=OK)break;
  92         rv = con->commit();
  93         if(rv!=OK)break;
  94         count++;
  95     }
  96     printf("Total rows inserted %d\n",count);
  97     stmt->free();
  98
  99     //**********************************************************
 100     // SELECT * FROM T1;
 101     strcpy(statement,"SELECT * FROM T1;");
 102     rv = stmt->prepare(statement);
 103     if(rv!=OK) { delete stmt; delete con; return 5; }
 104     stmt->bindField(1,&f1var);
 105     stmt->bindField(2,&f2var);
 106     stmt->bindField(3,f3var);
 107     stmt->bindField(4,&f4var);
 108     stmt->bindField(5,&f5var);
 109     stmt->bindField(6,&f6var);
 110     stmt->bindField(7,&f7var);
 111     stmt->bindField(8,&f8var);
 112     stmt->bindField(9,&f9var);
 113     stmt->bindField(10,&f10var);
 114     stmt->bindField(11,f11var);
 115     count=0;
 116     rv = con->beginTrans();
 117     if(rv!=OK)return 6;
 118     stmt->execute(rows);
 119     while(stmt->fetch() !=NULL) {
 120         if(stmt->isFldNull(1)) printf("F1=NULL | ");
 121         else printf("F1=%d | ", f1var);
 122         if(stmt->isFldNull(2)) printf("F2=NULL | ");
 123         else printf("F2=%hd | ", f2var);
 124         if(stmt->isFldNull(3)) printf("F3=NULL | ");
 125         else printf("F3=%s | ", f3var);
 126         if(stmt->isFldNull(4)) printf("F4=NULL | ");
 127         else printf("F4=%f | ", f4var);
 128         if(stmt->isFldNull(5)) printf("F5=NULL | ");
 129         else printf("F5=%f | ", f5var);
 130         if(stmt->isFldNull(6)) printf("F6=NULL | ");
 131         else printf("F6=%02d-%02d-%02d | ",f6var.year(), f6var.month(), f6var.dayOfMonth());
 132         if(stmt->isFldNull(7)) printf("F7=NULL | ");
 133         else printf("F7=%02d:%02d:%02d | ", f7var.hours(),f7var.minutes(),f7var.seconds());
 134         if(stmt->isFldNull(8)) printf("F8=NULL | ");
 135         else printf("F8=%d-%d-%d %d:%d:%d | ", f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds());
 136         if(stmt->isFldNull(9)) printf("F9=NULL | ");
 137         else printf("F9=%d | ", f9var);
 138         if(stmt->isFldNull(10)) printf("F10=NULL | ");
 139         else printf("F10=%lld | ", f10var);
 140         if(stmt->isFldNull(11)) printf("F11=NULL | ");
 141         else { AllDataType::printVal(f11var,typeBinary,4);
 142         }
 143         printf("\n");
 144         count++;
 145     }
 146     stmt->close();
 147     rv = con->commit();
 148     if(rv !=OK) { delete stmt; delete con; return 7; }
 149     printf("Total row fetched=%d\n",count);
 150     stmt->free();
 151     strcpy(statement,"DROP TABLE T1;");
 152     rv = stmt->prepare(statement);
 153     if(rv!=OK) { delete stmt; delete con; return 8; }
 154     rv = stmt->execute(rows);
 155     if(rv!=OK) { delete stmt; delete con; return 9; }
 156     printf("Table dropped\n");
 157     stmt->free(); delete stmt; delete con;
 158     return 0;
 159 }