test/adapter/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    if(strcmp(getenv("DSN"),"oracle")==0)
  22       strcpy(statement,"CREATE TABLE t1(f1 number(9),f2 number(4),f3 CHAR(30),f4 FLOAT,f5 FLOAT,f6 DATE,f8 TIMESTAMP,f9 number(9),f10 number(18));");
  23    else if(strcmp(getenv("DSN"),"psql")==0)
  24       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 FLOAT4,f5 FLOAT4,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
  25    else if(strcmp(getenv("DSN"),"sybase")==0)
  26       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 DATETIME,f9 INT,f10 BIGINT);");
  27    else if(strcmp(getenv("DSN"),"db2")==0)
  28       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT)");
  29    else
  30       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);");
  31
  32    int rows = 0;
  33    rv = stmt->prepare(statement);
  34    if(rv!=OK)
  35    {
  36      delete stmt;
  37      delete con;
  38      return 2;
  39    }
  40
  41    rv = stmt->execute(rows);
  42    if(rv!=OK)
  43    {
  44      delete stmt;
  45      delete con;
  46      return 3;
  47    }
  48    con->commit();
  49    stmt->free();
  50    printf("Table created\n");
  51
  52    // insert into table
  53    int para=0;
  54    if(strcmp(getenv("DSN"),"oracle")==0){
  55       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  56       para=8;
  57    }
  58    else if(strcmp(getenv("DSN"),"db2")==0){
  59       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  60       para=9;
  61    }
  62    else{
  63       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  64       para=9;
  65    }
  66    int f1var = 100;
  67    short int f2var = 10;
  68    char f3var[32]="jitendra";
  69    float f4var = 5.5;
  70    float f5var = 10.50;
  71    Date f6var;
  72    f6var.set(2007,01,21);
  73
  74    Time f7var;
  75    f7var.set(12,29,30);
  76
  77    TimeStamp f8var;
  78    f8var.setDate(2007,01,21);
  79    f8var.setTime(12,29,30);
  80
  81    int f9var = 20;
  82    long long f10var = 12000;
  83
  84    rv = stmt->prepare(statement);
  85    if(rv!=OK)
  86    {
  87      delete stmt;
  88      delete con;
  89      return 4;
  90    }
  91 //******************************************************************************
  92    if(strcmp(getenv("DSN"),"psql")==0){
  93    con->rollback();
  94    }
  95     int nop;
  96     nop = stmt->noOfParamFields();
  97     printf("noOfParamField Returns=%d\n",nop);
  98
  99
 100     //getParamFldInfo()
 101     FieldInfo *field = new FieldInfo();
 102
 103
 104     char fldName[20];
 105     DataType type;
 106     size_t length;
 107     int offset;
 108     char defaultValueBuf[10];
 109     bool isNull;
 110     bool isDefault;
 111
 112     for(int i=0;i<=para;i++)
 113     {
 114          printf("\n***********************************************\n");
 115          printf("%d FIELD INFORMATION ::\n",i);;
 116          rv = stmt->getParamFldInfo(i+1,field);
 117         if(rv!=OK)return 1;
 118          printf("Field Name :%s\n", field->fldName);
 119          printf("Data type of the field :%d\n",field->type);
 120          printf("length of the field :%d\n",field->length);
 121          printf("Offset :%d\n",field->offset);
 122          printf("Default value :%s\n",field->defaultValueBuf);
 123          printf("Null field :%d\n",field->isNull);
 124          printf("Default value :%d\n",field->isDefault);
 125          printf("***********************************************\n");
 126    }
 127
 128     int count=0;
 129
 130    for(int i=0;i<10;i++)
 131    {
 132         rv  = con->beginTrans();
 133         if(rv!=OK) break;
 134         f1var=i;
 135         stmt->setIntParam(1,f1var);
 136         stmt->setShortParam(2,f2var);
 137         stmt->setStringParam(3,f3var);
 138         stmt->setFloatParam(4,f4var);
 139         stmt->setFloatParam(5,f5var);
 140         stmt->setDateParam(6,f6var);
 141         if(strcmp(getenv("DSN"),"oracle")==0){
 142             stmt->setTimeStampParam(7,f8var);
 143             stmt->setIntParam(8,f9var);
 144             stmt->setLongLongParam(9,f10var);
 145         }else{
 146             stmt->setTimeParam(7,f7var);
 147             stmt->setTimeStampParam(8,f8var);
 148             stmt->setIntParam(9,f9var);
 149             stmt->setLongLongParam(10,f10var);
 150         }
 151         rv = stmt->execute(rows);
 152         if(rv!=OK)break;
 153         rv = con->commit();
 154         if(rv!=OK)break;
 155         count++;
 156     }
 157     printf("Total rows inserted %d\n",count);
 158
 159     stmt->free();
 160
 161     //**********************************************************
 162
 163     // SELECT * FROM t1;
 164
 165     strcpy(statement,"SELECT * FROM t1;");
 166     if(strcmp(getenv("DSN"),"db2")==0){
 167     strcpy(statement,"SELECT * FROM t1");
 168     }
 169     rv = stmt->prepare(statement);
 170     if(rv!=OK)
 171     {
 172       delete stmt;
 173       delete con;
 174       return 5;
 175     }
 176
 177     stmt->bindField(1,&f1var);
 178     stmt->bindField(2,&f2var);
 179     stmt->bindField(3,f3var);
 180     stmt->bindField(4,&f4var);
 181     stmt->bindField(5,&f5var);
 182     stmt->bindField(6,&f6var);
 183     if(strcmp(getenv("DSN"),"oracle")==0){
 184         stmt->bindField(7,&f8var);
 185         stmt->bindField(8,&f9var);
 186         stmt->bindField(9,&f10var);
 187
 188     }else{
 189         stmt->bindField(7,&f7var);
 190         stmt->bindField(8,&f8var);
 191         stmt->bindField(9,&f9var);
 192         stmt->bindField(10,&f10var);
 193    }
 194
 195     count=0;
 196     rv = con->beginTrans();
 197     if(rv!=OK)return 6;
 198     stmt->execute(rows);
 199     while(stmt->fetch() !=NULL)
 200     {
 201         if(strcmp(getenv("DSN"),"oracle")==0)
 202              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);
 203         else
 204              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);
 205         count++;
 206     }
 207
 208     stmt->close();
 209     rv = con->commit();
 210     if(rv !=OK)
 211     {
 212       delete stmt;
 213       delete con;
 214       return 7;
 215     }
 216
 217     printf("Total row fetched=%d\n",count);
 218     stmt->free();
 219
 220
 221     strcpy(statement,"DROP TABLE t1;");
 222     if(strcmp(getenv("DSN"),"db2")==0){
 223         strcpy(statement,"DROP TABLE t1");
 224     }
 225     rv = stmt->prepare(statement);
 226     if(rv!=OK)
 227     {
 228       delete stmt;
 229       delete con;
 230       return 8;
 231     }
 232     rv = stmt->execute(rows);
 233     if(rv!=OK)
 234      {
 235       delete stmt;
 236       delete con;
 237       return 9;
 238     }
 239     stmt->free();
 240     printf("Table dropped\n");
 241     con->commit();
 242     delete stmt;
 243     delete con;
 244     return 0;
 245 }