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