test/adapter/Select/selecttest10.c

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