test/sqlapi/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"common.h"
  12
  13 int main()
  14 {
  15     DbRetVal rv = OK;
  16     AbsSqlConnection *con = createConnection();
  17     rv  = con->connect("root","manager");
  18     if(rv!=OK)return 1;
  19     AbsSqlStatement *stmt = createStatement();
  20     stmt->setConnection(con);
  21     char statement[200];
  22     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);");
  23     int rows=0;
  24     rv = stmt->prepare(statement);
  25     if(rv!=OK) { delete stmt; delete con; return 2; }
  26     rv  = stmt->execute(rows);
  27     if(rv!=OK) { delete stmt; delete con; return 3; }
  28     stmt->free();
  29     printf("'T1' Table created\n");
  30 //  insert records
  31     strcpy(statement,"INSERT INTO T1(F1,F2,F4,F5,F6,F7,F8,F9,F10)VALUES(?,?,?,?,?,?,?,?,?);");
  32     int f1var = 99;
  33     short int f2var = 1;
  34 // char f3var[20] = "prabakaran";
  35     float f4var = 5.5;
  36     float f5var = 10.50;
  37     Date f6var;
  38     f6var.set(2008,1,21);
  39     Time f7var;
  40     f7var.set(12,29,30);
  41     TimeStamp f8var;
  42     f8var.setDate(2008,1,21);
  43     f8var.setTime(12,29,30);
  44     int f9var = 20;
  45     long long f10var = 12000;
  46     rv = stmt->prepare(statement);
  47     if(rv!=OK) { delete stmt; delete con; return 4; }
  48     int count=0;
  49     for(int i=0;i<2;i++) {
  50         f1var = f1var+i;
  51         for(int j=0;j<5;j++) {
  52             rv = con->beginTrans();
  53                 if(rv!=OK)break;
  54             stmt->setIntParam(1,f1var);
  55                 stmt->setShortParam(2,f2var);
  56                 //stmt->setStringParam(3,f3var);
  57                 stmt->setFloatParam(3,f4var);
  58                 stmt->setFloatParam(4,f5var);
  59                 stmt->setDateParam(5,f6var);
  60                 stmt->setTimeParam(6,f7var);
  61                 stmt->setTimeStampParam(7,f8var);
  62                 stmt->setIntParam(8,f9var);
  63                 stmt->setLongLongParam(9,f10var);
  64                 rv = stmt->execute(rows);
  65                 if(rv!=OK)break;
  66                 rv = con->commit();
  67                 if(rv!=OK)break;
  68                 count++;
  69         }
  70     }
  71     printf("%d Rows Inserted\n",count);
  72     stmt->free();
  73 //*******************************************
  74     strcpy(statement,"SELECT F8,F10,F2,F6,F3  FROM T1 WHERE F1=100;");
  75     rv = stmt->prepare(statement);
  76     int nop;
  77     nop = stmt->noOfProjFields();
  78     if(nop!=5)return 1;
  79     printf("Total number of projection fields in the select statement=%d\n",nop);
  80     // getParamFldInfo()
  81     FieldInfo *field = new FieldInfo();
  82     printf("\nFIELD  INFORMATION :\n");
  83     char fldName[20];
  84     DataType type;
  85     size_t length;
  86     int offset;
  87     char defaultValueBuf[10];
  88     bool isNull;
  89     bool isDefault;
  90     for(int i=0;i<=4;i++) {
  91         printf("\n***********************************************\n");
  92         rv = stmt->getProjFldInfo(i+1,field);
  93         if(rv!=OK)return 1;
  94         printf("Field Name :%s\n", field->fldName);
  95         printf("Data type of the field :%d\n",field->type);
  96         printf("length of the field :%d\n",field->length);
  97         printf("Offset :%d\n",field->offset);
  98         printf("Default value Buffer :%s\n",field->defaultValueBuf);
  99         printf("Null field :%d\n",field->isNull);
 100         printf("Default value :%d\n",field->isDefault);
 101         printf("***********************************************\n");
 102     }
 103     char f3var[32]="jitu";
 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     stmt->close();
 118     rv = con->commit();
 119     if(rv!=OK) { delete stmt; delete con; return 6; }
 120     printf("Total row fetched=%d\n",count);
 121     stmt->free();
 122     strcpy(statement,"DROP TABLE T1;");
 123     rv = stmt->prepare(statement);
 124     if(rv!=OK) { delete stmt; delete con; return 7; }
 125     rv = stmt->execute(rows);
 126     if(rv!=OK) { delete stmt; delete con; return 8; }
 127     printf("Table dropped\n");
 128     stmt->free(); delete stmt; delete con;
 129     return 0;
 130 }