test/sqlgw/Select/selecttest8.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     int rows=0;
  20
  21     //  insert records
  22     if(strcmp(getenv("DSN"),"oracle")==0)
  23         strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?);");
  24     else if(strcmp(getenv("DSN"),"db2")==0)
  25         strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f7,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?,?)");
  26     else
  27         strcpy(statement,"INSERT INTO t1(f1,f2,f4,f5,f6,f7,f8,f9,f10)VALUES(?,?,?,?,?,?,?,?,?);");
  28
  29     int f1var = 99;
  30     short int f2var = 1;
  31     // char f3var[20] = "prabakaran";
  32     float f4var = 5.5;
  33     float f5var = 10.50;
  34     Date f6var;
  35     f6var.set(2008,1,21);
  36
  37     Time f7var;
  38     f7var.set(12,29,30);
  39
  40     TimeStamp f8var;
  41     f8var.setDate(2008,1,21);
  42     f8var.setTime(12,29,30);
  43
  44     int f9var = 20;
  45     long long f10var = 12000;
  46
  47     rv = stmt->prepare(statement);
  48     if(rv!=OK) { delete stmt; delete con; return 4; }
  49
  50     int count=0;
  51
  52     for(int i=0;i<2;i++) {
  53         f1var = f1var+i;
  54         for(int j=0;j<5;j++) {
  55             rv = con->beginTrans();
  56                 if(rv!=OK)break;
  57                 stmt->setIntParam(1,f1var);
  58             stmt->setShortParam(2,f2var);
  59             //stmt->setStringParam(3,f3var);
  60             stmt->setFloatParam(3,f4var);
  61             stmt->setFloatParam(4,f5var);
  62             stmt->setDateParam(5,f6var);
  63             if(strcmp(getenv("DSN"),"oracle")==0){
  64                 stmt->setTimeStampParam(6,f8var);
  65                 stmt->setIntParam(7,f9var);
  66                 stmt->setLongLongParam(8,f10var);
  67             } else {
  68                 stmt->setTimeParam(6,f7var);
  69                 stmt->setTimeStampParam(7,f8var);
  70                 stmt->setIntParam(8,f9var);
  71                 stmt->setLongLongParam(9,f10var);
  72             }
  73                 rv = stmt->execute(rows);
  74             if(rv!=OK)break;
  75             rv = con->commit();
  76             if(rv!=OK)break;
  77             count++;
  78         }
  79     }
  80     printf("%d Rows Inserted\n",count);
  81     stmt->free();
  82
  83 //*******************************************
  84
  85     strcpy(statement,"SELECT f8,f10,f2,f6,f3  FROM t1 WHERE f1=100;");
  86     if(strcmp(getenv("DSN"),"db2")==0)
  87         strcpy(statement,"SELECT f8,f10,f2,f6,f3  FROM t1 WHERE f1=100");
  88     rv = stmt->prepare(statement);
  89     if(rv !=OK) { delete stmt; delete con; return 5; }
  90
  91     int nop;
  92     nop = stmt->noOfProjFields();
  93     if(nop!=5)return 1;
  94     printf("Total number of projection fields in the select statement=%d\n",nop);
  95     char fldName[20];
  96     DataType type;
  97     size_t length;
  98     int offset;
  99     char defaultValueBuf[10];
 100     bool isNull;
 101     bool isDefault;
 102     char f3var[32]="jitu";
 103
 104     stmt->bindField(1,&f8var);
 105     stmt->bindField(2,&f10var);
 106     stmt->bindField(3,&f2var);
 107     stmt->bindField(4,&f6var);
 108     stmt->bindField(5,f3var);
 109     count = 0;
 110     rv = con->beginTrans();
 111     if(rv!=OK)return 6;
 112     stmt->execute(rows);
 113     while(stmt->fetch()!=NULL) {
 114         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);
 115         count++;
 116     }
 117
 118     stmt->close();
 119     rv = con->commit();
 120     if(rv!=OK) { delete stmt; delete con; return 6; }
 121
 122     printf("Total row fetched=%d\n",count);
 123     stmt->free();
 124     delete stmt;
 125     delete con;
 126     return 0;
 127 }