test/sqlnetwork/Parameter/paratest1.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it.
   3  *  noOfParamFields() should return 10.
   4  *  getParamFldInfo() for all fields and print the field info.
   5  *  select * from T1;
   6
   7  *  Author : Jitendra Lenka.
   8  */
   9
  10 #include<SqlNwConnection.h>
  11 #include<SqlNwStatement.h>
  12 #include<SqlFactory.h>
  13 #include<Info.h>
  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   char statement[200];
  28   strcpy(statement,"CREATE TABLE T1(F1 SMALLINT,F2 INT, F3 BIGINT, F4 FLOAT, F5 DOUBLE, F6 DATE,F7 TIME,F8 TIMESTAMP,F9 CHAR(30), F10 BINARY(4));");
  29
  30    int rows = 0;
  31    rv = stmt->prepare(statement);
  32    if(rv!=OK) { delete stmt; delete con; return 2; }
  33    rv = stmt->execute(rows);
  34    if(rv!=OK) { delete stmt; delete con; return 3; }
  35    printf("Table created\n");
  36
  37    // insert into table
  38
  39    strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  40    short f1var = 100;
  41    int f2var = 10;
  42    long long f3var = 12000;
  43    float f4var = 5.5;
  44    double f5var = 123.45;
  45    Date f6var;
  46    f6var.set(2007,01,21);
  47    Time f7var;
  48    f7var.set(12,29,30);
  49    TimeStamp f8var;
  50    f8var.setDate(2007,01,21);
  51    f8var.setTime(12,29,30);
  52    char f9var[31]="jitendra";
  53    char f10var[8]="abcdef";
  54    rv = stmt->prepare(statement);
  55    if(rv!=OK) { delete stmt; delete con; return 4; }
  56 //******************************************************************************
  57 /*    int nop;
  58     nop = stmt->noOfParamFields();
  59     printf("noOfParamField Returns=%d\n",nop);
  60
  61
  62     //getParamFldInfo()
  63     FieldInfo *field = new FieldInfo();
  64
  65
  66     char fldName[20];
  67     DataType type;
  68     size_t length;
  69     int offset;
  70     char defaultValueBuf[10];
  71     bool isNull;
  72     bool isDefault;
  73
  74     for(int i=1;i<=11;i++)
  75     {
  76          printf("\n***********************************************\n");
  77          printf("%d FIELD INFORMATION ::\n",i);;
  78          rv = stmt->getParamFldInfo(i,field);
  79         if(rv!=OK)return 1;
  80          printf("Field Name :%s\n", field->fldName);
  81          printf("Data type of the field :%d\n",field->type);
  82          printf("length of the field :%d\n",field->length);
  83          printf("Offset :%d\n",field->offset);
  84          printf("Default value :%s\n",field->defaultValueBuf);
  85          printf("Null field :%d\n",field->isNull);
  86          printf("Default value :%d\n",field->isDefault);
  87          printf("***********************************************\n");
  88    }
  89  */
  90     int count=0;
  91
  92    for(int i=0;i<10;i++) {
  93             rv  = con->beginTrans();
  94             if(rv!=OK) break;
  95             f1var=i;
  96         f2var= 1234+i;
  97         f3var= 12345678+i;
  98         f4var = 0.5 + i;
  99         f5var = 12345.5 + i;
 100         f6var.set(2000 + i, i + 1, i + 1);
 101         f7var.set(i+1, i+1, i+1);
 102         f8var.setDate(2000 + i, i + 1, i + 1);
 103         f8var.setTime(i+1, i+1, i+1);
 104         sprintf(f9var, "string%d", i);
 105         sprintf(f10var, "abcdef%d", i);
 106         stmt->setShortParam(1,f1var);
 107         stmt->setIntParam(2,f2var);
 108         stmt->setLongLongParam(3, f3var);
 109         stmt->setFloatParam(4,f4var);
 110         stmt->setDoubleParam(5,f5var);
 111         stmt->setDateParam(6,f6var);
 112         stmt->setTimeParam(7,f7var);
 113         stmt->setTimeStampParam(8,f8var);
 114         stmt->setStringParam(9,f9var);
 115         stmt->setBinaryParam(10,f10var);
 116         rv = stmt->execute(rows);
 117         if(rv!=OK)break;
 118         rv = con->commit();
 119             if(rv!=OK)break;
 120         count++;
 121     }
 122     printf("Total rows inserted %d\n",count);
 123
 124     //**********************************************************
 125
 126
 127     // SELECT * FROM T1;
 128
 129     strcpy(statement,"SELECT * FROM T1;");
 130     rv = stmt->prepare(statement);
 131     if(rv!=OK) { delete stmt; delete con; return 5; }
 132
 133     stmt->bindField(1,&f1var);
 134     stmt->bindField(2,&f2var);
 135     stmt->bindField(3,&f3var);
 136     stmt->bindField(4,&f4var);
 137     stmt->bindField(5,&f5var);
 138     stmt->bindField(6,&f6var);
 139     stmt->bindField(7,&f7var);
 140     stmt->bindField(8,&f8var);
 141     stmt->bindField(9,f9var);
 142     stmt->bindField(10,f10var);
 143     count=0;
 144     rv = con->beginTrans();
 145     if(rv!=OK)return 6;
 146     stmt->execute(rows);
 147     while(stmt->fetch(rv) !=NULL)
 148     {
 149         printf("F1=%hd | F2=%d | F3=%lld | F4=%f | F5=%lf | F6:DATE=%0d-%0d-%0d | F7:TIME=%2d:%2d:%2d | F8:TIMESTAMP=%4d-%2d-%2d %2d:%2d:%2d | F9=%s ",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);
 150         printf("| F10=");
 151         AllDataType::printVal(f10var, typeBinary, 4);
 152         printf("\n");
 153         count++;
 154     }
 155     stmt->close();
 156     rv = con->commit();
 157     if(rv !=OK) { delete stmt; delete con; return 7; }
 158     printf("Total row fetched=%d\n",count);
 159
 160     strcpy(statement,"DROP TABLE T1;");
 161     rv = stmt->prepare(statement);
 162     if(rv!=OK) { delete stmt; delete con; return 8; }
 163     rv = stmt->execute(rows);
 164     if(rv!=OK) { delete stmt; delete con; return 9; }
 165     printf("Table dropped\n");
 166
 167     delete stmt;
 168     delete con;
 169     return 0;
 170 }