test/adapter/Select/selecttest1.c

   1 /*   create table T1 with 10 fields,
   2  *   insert 10 rows into the table ,
   3  *   noofprojFields shoul dreturn 10.
   4  *   call fetch and count the record.
   5 */
   6
   7 #include "common.h"
   8 int main()
   9 {
  10   DbRetVal rv = OK;
  11   AbsSqlConnection *con = createConnection();
  12   rv  = con->connect("root","manager");
  13   if(rv!=OK)return 1;
  14
  15   AbsSqlStatement *stmt = createStatement();
  16   stmt->setConnection(con);
  17   char statement[200];
  18   if(strcmp(getenv("DSN"),"oracle")==0)
  19       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));");
  20   else if(strcmp(getenv("DSN"),"psql")==0)
  21       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);");
  22    else if(strcmp(getenv("DSN"),"sybase")==0)
  23       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);");
  24    else if(strcmp(getenv("DSN"),"db2")==0)
  25       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)");
  26    else
  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)
  32   {
  33     delete stmt;
  34     delete con;
  35     return 2;
  36  }
  37
  38  rv  = stmt->execute(rows);
  39   if(rv!=OK)
  40   {
  41     delete stmt;
  42     delete con;
  43     return 3;
  44  }
  45  stmt->free();
  46  con->commit();
  47  printf("'T1'  table created\n");
  48
  49  //  insert records
  50
  51  if(strcmp(getenv("DSN"),"oracle")==0){
  52       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
  53    }
  54    else if(strcmp(getenv("DSN"),"db2")==0)
  55       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
  56    else{
  57       strcpy(statement,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  58    }
  59
  60
  61  int f1var = 99;
  62  short int f2var = 1;
  63  char f3var[32] = "lakshya";
  64  float f4var = 5.5;
  65  float f5var = 10.50;
  66  Date f6var;
  67  f6var.set(2008,1,21);
  68
  69  Time f7var;
  70  f7var.set(12,29,30);
  71
  72  TimeStamp f8var;
  73  f8var.setDate(2008,1,21);
  74  f8var.setTime(12,29,30);
  75
  76  int f9var = 20;
  77  long long f10var = 12000;
  78
  79  rv = stmt->prepare(statement);
  80  if(rv!=OK)
  81  {
  82    delete stmt;
  83    delete con;
  84    return 4;
  85  }
  86
  87
  88  int count=0;
  89
  90  for(int i=0;i<2;i++)
  91  {
  92    f1var = f1var+i;
  93    for(int j=0;j<5;j++)
  94     {
  95         rv = con->beginTrans();
  96         if(rv!=OK)break;
  97
  98
  99         stmt->setIntParam(1,f1var);
 100         stmt->setShortParam(2,f2var);
 101         stmt->setStringParam(3,f3var);
 102         stmt->setFloatParam(4,f4var);
 103         stmt->setFloatParam(5,f5var);
 104         stmt->setDateParam(6,f6var);
 105         if(strcmp(getenv("DSN"),"oracle")==0){
 106             stmt->setTimeStampParam(7,f8var);
 107             stmt->setIntParam(8,f9var);
 108             stmt->setLongLongParam(9,f10var);
 109         }else{
 110             stmt->setTimeParam(7,f7var);
 111             stmt->setTimeStampParam(8,f8var);
 112             stmt->setIntParam(9,f9var);
 113             stmt->setLongLongParam(10,f10var);
 114         }
 115         rv = stmt->execute(rows);
 116         if(rv!=OK)break;
 117         rv = con->commit();
 118         if(rv!=OK)break;
 119         count++;
 120     }
 121
 122  }
 123   printf("%d Rows Inserted\n",count);
 124   stmt->free();
 125
 126 //*******************************************
 127
 128   strcpy(statement,"SELECT * FROM t1 WHERE f1=100;");
 129   if(strcmp(getenv("DSN"),"db2")==0)
 130         strcpy(statement,"SELECT * FROM t1 WHERE f1=100");
 131   rv = stmt->prepare(statement);
 132
 133    int nop;
 134    nop = stmt->noOfProjFields();
 135    printf("noOfProjFields returns=%d\n",nop);
 136
 137    if(rv !=OK)
 138   {
 139      delete stmt;
 140      delete con;
 141      return 5;
 142   }
 143
 144   stmt->bindField(1,&f1var);
 145   stmt->bindField(2,&f2var);
 146   stmt->bindField(3,f3var);
 147   stmt->bindField(4,&f4var);
 148   stmt->bindField(5,&f5var);
 149   stmt->bindField(6,&f6var);
 150   if(strcmp(getenv("DSN"),"oracle")==0){
 151         stmt->bindField(7,&f8var);
 152         stmt->bindField(8,&f9var);
 153         stmt->bindField(9,&f10var);
 154
 155     }else{
 156         stmt->bindField(7,&f7var);
 157         stmt->bindField(8,&f8var);
 158         stmt->bindField(9,&f9var);
 159         stmt->bindField(10,&f10var);
 160    }
 161
 162
 163
 164   count = 0;
 165   rv = con->beginTrans();
 166   if(rv!=OK)return 6;
 167   stmt->execute(rows);
 168   printf("Fetching starts on table 'T1' :\n");
 169   while(stmt->fetch()!=NULL)
 170   {
 171      if(strcmp(getenv("DSN"),"oracle")==0)
 172              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);
 173      else
 174              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);
 175
 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("%d Rows fetched\n",count);
 189
 190   stmt->free();
 191
 192  strcpy(statement,"DROP TABLE t1;");
 193  if(strcmp(getenv("DSN"),"db2")==0)
 194         strcpy(statement,"DROP TABLE t1");
 195  rv = stmt->prepare(statement);
 196  if(rv!=OK)
 197  {
 198    delete stmt;
 199    delete con;
 200    return 8;
 201  }
 202  rv = stmt->execute(rows);
 203  if(rv!=OK)
 204  {
 205    delete stmt;
 206    delete con;
 207    return 9;
 208  }
 209  stmt->free();
 210  con->commit();
 211  printf("'T1' table dropped\n");
 212
 213  delete stmt;
 214  delete con;
 215  return 0;
 216 }