test/sqlnetwork/Parameter/paratest4.c

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