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