test/sqlnetwork/Parameter/paratest100.c

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