test/sqlgw/Parameter/paratest1.c

   1 /*  create table t1 with 10 fields.
   2  *  insert some records into it.
   3  *  noOfParamFields() should return 10.
   4  *  getParamFldInfo() for all fields and print the field info.
   5  *  select * from t1;
   6
   7  *  Author : Jitendra Lenka.
   8  */
   9
  10 #include "common.h"
  11 int main()
  12 {
  13   DbRetVal rv = OK;
  14   AbsSqlConnection *con= createConnection();
  15   rv = con->connect("root","manager");
  16   if(rv!=OK)return 1;
  17
  18   AbsSqlStatement *stmt = createStatement();
  19   stmt->setConnection(con);
  20   char statement[200];
  21    int rows = 0;
  22    // insert into table
  23    int para=0;
  24    if(strcmp(getenv("DSN"),"oracle")==0){
  25       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  26       para=8;
  27    }
  28    else if(strcmp(getenv("DSN"),"db2")==0){
  29       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  30       para=9;
  31    }
  32    else{
  33       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  34       para=9;
  35    }
  36    int f1var = 100;
  37    short int f2var = 10;
  38    char f3var[32]="jitendra";
  39    float f4var = 5.5;
  40    float f5var = 10.50;
  41    Date f6var;
  42    f6var.set(2007,01,21);
  43
  44    Time f7var;
  45    f7var.set(12,29,30);
  46
  47    TimeStamp f8var;
  48    f8var.setDate(2007,01,21);
  49    f8var.setTime(12,29,30);
  50
  51    int f9var = 20;
  52    long long f10var = 12000;
  53
  54    rv = stmt->prepare(statement);
  55    if(rv!=OK)
  56    {
  57      delete stmt;
  58      delete con;
  59      return 4;
  60    }
  61 //******************************************************************************
  62    if(strcmp(getenv("DSN"),"psql")==0){
  63    con->rollback();
  64    }
  65     int nop;
  66     nop = stmt->noOfParamFields();
  67     printf("noOfParamField Returns=%d\n",nop);
  68
  69
  70     //getParamFldInfo()
  71     FieldInfo *field = new FieldInfo();
  72
  73
  74     char fldName[20];
  75     DataType type;
  76     size_t length;
  77     int offset;
  78     char defaultValueBuf[10];
  79     bool isNull;
  80     bool isDefault;
  81
  82     for(int i=0;i<nop;i++)
  83     {
  84          printf("\n***********************************************\n");
  85          printf("%d FIELD INFORMATION ::\n",i);;
  86          rv = stmt->getParamFldInfo(i+1,field);
  87          if(rv!=OK)return 1;
  88          printf("Field Name :%s\n", field->fldName);
  89          printf("Data type of the field :%d\n",field->type);
  90          printf("length of the field :%d\n",field->length);
  91          printf("Offset :%d\n",field->offset);
  92          printf("Default value :%s\n",field->defaultValueBuf);
  93          printf("Null field :%d\n",field->isNull);
  94          printf("Default value :%d\n",field->isDefault);
  95          printf("***********************************************\n");
  96    }
  97    delete field;
  98     int count=0;
  99
 100    for(int i=0;i<10;i++)
 101    {
 102         rv  = con->beginTrans();
 103         if(rv!=OK) break;
 104         f1var=i;
 105         stmt->setIntParam(1,f1var);
 106         stmt->setShortParam(2,f2var);
 107         stmt->setStringParam(3,f3var);
 108         stmt->setFloatParam(4,f4var);
 109         stmt->setFloatParam(5,f5var);
 110         stmt->setDateParam(6,f6var);
 111         if(strcmp(getenv("DSN"),"oracle")==0){
 112             stmt->setTimeStampParam(7,f8var);
 113             stmt->setIntParam(8,f9var);
 114             stmt->setLongLongParam(9,f10var);
 115         }else{
 116             stmt->setTimeParam(7,f7var);
 117             stmt->setTimeStampParam(8,f8var);
 118             stmt->setIntParam(9,f9var);
 119             stmt->setLongLongParam(10,f10var);
 120         }
 121         rv = stmt->execute(rows);
 122         if(rv!=OK)break;
 123         rv = con->commit();
 124         if(rv!=OK)break;
 125         count++;
 126     }
 127     printf("Total rows inserted %d\n",count);
 128
 129     stmt->free();
 130
 131     //**********************************************************
 132
 133     // SELECT * FROM t1;
 134
 135     strcpy(statement,"SELECT * FROM t1;");
 136     if(strcmp(getenv("DSN"),"db2")==0){
 137         strcpy(statement,"SELECT * FROM t1");
 138     }
 139
 140     rv = stmt->prepare(statement);
 141     if(rv!=OK)
 142     {
 143       delete stmt;
 144       delete con;
 145       return 5;
 146     }
 147
 148     stmt->bindField(1,&f1var);
 149     stmt->bindField(2,&f2var);
 150     stmt->bindField(3,f3var);
 151     stmt->bindField(4,&f4var);
 152     stmt->bindField(5,&f5var);
 153     stmt->bindField(6,&f6var);
 154     if(strcmp(getenv("DSN"),"oracle")==0){
 155         stmt->bindField(7,&f8var);
 156         stmt->bindField(8,&f9var);
 157         stmt->bindField(9,&f10var);
 158
 159     }else{
 160         stmt->bindField(7,&f7var);
 161         stmt->bindField(8,&f8var);
 162         stmt->bindField(9,&f9var);
 163         stmt->bindField(10,&f10var);
 164    }
 165
 166     count=0;
 167     rv = con->beginTrans();
 168     if(rv!=OK)return 6;
 169     stmt->execute(rows);
 170     while(stmt->fetch() !=NULL)
 171     {
 172         if(strcmp(getenv("DSN"),"oracle")==0)
 173              printf("f1=%d | f2=%hd | f3=%s | f4=%f | f5=%f | DATE=%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(),f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds(),f9var,f10var);
 174         else
 175              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);
 176         count++;
 177     }
 178
 179     stmt->close();
 180     rv = con->commit();
 181     if(rv !=OK)
 182     {
 183       delete stmt;
 184       delete con;
 185       return 7;
 186     }
 187
 188     printf("Total row fetched=%d\n",count);
 189
 190     stmt->free();
 191     delete stmt;
 192     delete con;
 193     return 0;
 194 }