test/sqlapi/Select/selecttest12.c

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