test/sqlnetwork/Parameter/paratest2.c

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