test/sqlapi/Select/selecttest2.c

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