test/adapter/Select/selecttest10.c

   1 /*   create table t1 with 10 fields,( f2 NOT NULL , f3 CHAR(20) DEFAULT 'LAKSHYA')
   2  *   insert 10 rows for 9 Fields except f3 Field into the table
   3  *   noofprojFields should return 5.
   4  *   getProjFldInfo() for all fields.
   5  *   select with 5 parameters withIwhere clause.(WHERE f1=100)
   6 */
   7
   8 #include "common.h"
   9 int main()
  10 {
  11   DbRetVal rv = OK;
  12   AbsSqlConnection *con = createConnection();
  13   rv  = con->connect("root","manager");
  14   if(rv!=OK)return 1;
  15
  16   AbsSqlStatement *stmt = createStatement();
  17   stmt->setConnection(con);
  18   char statement[200];
  19   if(strcmp(getenv("DSN"),"oracle")==0)
  20       strcpy(statement,"CREATE TABLE t1(f1 number(9),f2 number(4),f3 CHAR(30) DEFAULT 'LAKSHYA',f4 FLOAT,f5 FLOAT,f6 DATE,f8 TIMESTAMP,f9 number(9),f10 number(18));");
  21   else if(strcmp(getenv("DSN"),"psql")==0)
  22       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 FLOAT4,f5 FLOAT4,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
  23    else if(strcmp(getenv("DSN"),"sybase")==0)
  24       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 DATETIME,f9 INT,f10 BIGINT);");
  25    else if(strcmp(getenv("DSN"),"db2")==0)
  26       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 REAL,f5 REAL,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT)");
  27    else
  28       strcpy(statement,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30) DEFAULT 'LAKSHYA',f4 FLOAT,f5 FLOAT,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
  29
  30   int rows=0;
  31   rv = stmt->prepare(statement);
  32   if(rv!=OK)
  33   {
  34     delete stmt;
  35     delete con;
  36     return 2;
  37  }
  38
  39  rv  = stmt->execute(rows);
  40   if(rv!=OK)
  41   {
  42     delete stmt;
  43     delete con;
  44     return 3;
  45  }
  46  stmt->free();
  47  con->commit();
  48  printf("'t1' Table created\n");
  49
  50  //  insert records
  51  if(strcmp(getenv("DSN"),"oracle")==0)
  52      strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?);");
  53  else if(strcmp(getenv("DSN"),"db2")==0)
  54      strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f7,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?,?)");
  55  else
  56      strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f7,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?,?);");
  57
  58  int f1var = 99;
  59  short int f2var = 1;
  60 // char f3var[20] = "prabakaran";
  61  float f4var = 5.5;
  62  float f5var = 10.50;
  63  Date f6var;
  64  f6var.set(2008,1,21);
  65
  66  Time f7var;
  67  f7var.set(12,29,30);
  68
  69  TimeStamp f8var;
  70  f8var.setDate(2008,1,21);
  71  f8var.setTime(12,29,30);
  72
  73  int f9var = 20;
  74  long long f10var = 12000;
  75
  76  rv = stmt->prepare(statement);
  77  if(rv!=OK)
  78  {
  79    delete stmt;
  80    delete con;
  81    return 4;
  82  }
  83
  84  int count=0;
  85
  86   for(int i=0;i<2;i++)
  87   {
  88     f1var = f1var+i;
  89     for(int j=0;j<5;j++)
  90     {
  91         rv = con->beginTrans();
  92         if(rv!=OK)break;
  93
  94
  95     stmt->setIntParam(1,f1var);
  96         stmt->setShortParam(2,f2var);
  97         //stmt->setStringParam(3,f3var);
  98         stmt->setFloatParam(3,f4var);
  99         stmt->setFloatParam(4,f5var);
 100         stmt->setDateParam(5,f6var);
 101         if(strcmp(getenv("DSN"),"oracle")==0){
 102             stmt->setTimeStampParam(6,f8var);
 103             stmt->setIntParam(7,f9var);
 104             stmt->setLongLongParam(8,f10var);
 105         }else{
 106             stmt->setTimeParam(6,f7var);
 107             stmt->setTimeStampParam(7,f8var);
 108             stmt->setIntParam(8,f9var);
 109             stmt->setLongLongParam(9,f10var);
 110         }
 111
 112         rv = stmt->execute(rows);
 113         if(rv!=OK)break;
 114         rv = con->commit();
 115         if(rv!=OK)break;
 116         count++;
 117      }
 118
 119   }
 120   printf("%d Rows Inserted\n",count);
 121   stmt->free();
 122
 123 //*******************************************
 124
 125   strcpy(statement,"SELECT f8,f10,f2,f6,f3  FROM t1 WHERE f1=100;");
 126   if(strcmp(getenv("DSN"),"db2")==0)
 127         strcpy(statement,"SELECT f8,f10,f2,f6,f3  FROM t1 WHERE f1=100");
 128   rv = stmt->prepare(statement);
 129
 130    int nop;
 131    nop = stmt->noOfProjFields();
 132    if(nop!=5)return 1;
 133    printf("Total number of projection fields in the select statement=%d\n",nop);
 134
 135    // getParamFldInfo()
 136     FieldInfo *field = new FieldInfo();
 137
 138     char fldName[20];
 139     DataType type;
 140     size_t length;
 141     int offset;
 142     char defaultValueBuf[10];
 143     bool isNull;
 144     bool isDefault;
 145
 146   if(rv !=OK)
 147   {
 148      delete stmt;
 149      delete con;
 150      return 5;
 151   }
 152
 153   char f3var[32]="jitu";
 154
 155   stmt->bindField(1,&f8var);
 156   stmt->bindField(2,&f10var);
 157   stmt->bindField(3,&f2var);
 158   stmt->bindField(4,&f6var);
 159   stmt->bindField(5,f3var);
 160
 161
 162   count = 0;
 163   rv = con->beginTrans();
 164   if(rv!=OK)return 6;
 165   stmt->execute(rows);
 166   while(stmt->fetch()!=NULL)
 167   {
 168
 169         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);
 170
 171        count++;
 172   }
 173
 174   stmt->close();
 175   rv = con->commit();
 176   if(rv!=OK)
 177   {
 178     delete stmt;
 179     delete con;
 180     return 6;
 181   }
 182
 183   printf("Total row fetched=%d\n",count);
 184
 185   stmt->free();
 186
 187  strcpy(statement,"DROP TABLE t1;");
 188  if(strcmp(getenv("DSN"),"db2")==0)
 189      strcpy(statement,"DROP TABLE t1");
 190  rv = stmt->prepare(statement);
 191  if(rv!=OK)
 192  {
 193    delete stmt;
 194    delete con;
 195    return 7;
 196  }
 197  rv = stmt->execute(rows);
 198  if(rv!=OK)
 199  {
 200    delete stmt;
 201    delete con;
 202    return 8;
 203  }
 204  stmt->free();
 205  con->commit();
 206  printf("Table dropped\n");
 207
 208  delete stmt;
 209  delete con;
 210  return 0;
 211 }
 212
 213
 214