test/sqlapi/Csql/ParamStmt/paratest2.c

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