test/sqlapi/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<AbsSqlStatement.h>
  11 #include<SqlFactory.h>
  12 #include<Info.h>
  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   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, F11 BINARY(4));");
  24
  25    int rows = 0;
  26    rv = stmt->prepare(statement);
  27    if(rv!=OK)
  28    {
  29      delete stmt;
  30      delete con;
  31      return 2;
  32    }
  33
  34    rv = stmt->execute(rows);
  35    if(rv!=OK)
  36    {
  37      delete stmt;
  38      delete con;
  39      return 3;
  40    }
  41
  42    stmt->free();
  43    printf("Table created\n");
  44
  45    // insert into table
  46
  47    strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
  48    int f1var = 100;
  49    short int f2var = 10;
  50    char f3var[32]="jitendra";
  51    float f4var = 5.5;
  52    float f5var = 10.50;
  53    Date f6var;
  54    f6var.set(2007,01,21);
  55
  56    Time f7var;
  57    f7var.set(12,29,30);
  58
  59    TimeStamp f8var;
  60    f8var.setDate(2007,01,21);
  61    f8var.setTime(12,29,30);
  62
  63    int f9var = 20;
  64    long long f10var = 12000;
  65    char f11var[8]="23fe";
  66    rv = stmt->prepare(statement);
  67    if(rv!=OK)
  68    {
  69      delete stmt;
  70      delete con;
  71      return 4;
  72    }
  73 //******************************************************************************
  74     int nop;
  75     nop = stmt->noOfParamFields();
  76     printf("noOfParamField Returns=%d\n",nop);
  77
  78
  79     //getParamFldInfo()
  80     FieldInfo *field = new FieldInfo();
  81
  82
  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
  91     for(int i=1;i<=11;i++)
  92     {
  93          printf("\n***********************************************\n");
  94          printf("%d FIELD INFORMATION ::\n",i);;
  95          rv = stmt->getParamFldInfo(i,field);
  96         if(rv!=OK)return 1;
  97          printf("Field Name :%s\n", field->fldName);
  98          printf("Data type of the field :%d\n",field->type);
  99          printf("length of the field :%d\n",field->length);
 100          printf("Offset :%d\n",field->offset);
 101          printf("Default value :%s\n",field->defaultValueBuf);
 102          printf("Null field :%d\n",field->isNull);
 103          printf("Default value :%d\n",field->isDefault);
 104          printf("***********************************************\n");
 105    }
 106    delete field;
 107     int count=0;
 108
 109    for(int i=0;i<10;i++)
 110    {
 111         rv  = con->beginTrans();
 112         if(rv!=OK) break;
 113         f1var=i;
 114         stmt->setIntParam(1,f1var);
 115         stmt->setShortParam(2,f2var);
 116         stmt->setStringParam(3,f3var);
 117         stmt->setFloatParam(4,f4var);
 118         stmt->setFloatParam(5,f5var);
 119         stmt->setDateParam(6,f6var);
 120         stmt->setTimeParam(7,f7var);
 121         stmt->setTimeStampParam(8,f8var);
 122         stmt->setIntParam(9,f9var);
 123         stmt->setLongLongParam(10,f10var);
 124         stmt->setBinaryParam(11,f11var);
 125         rv = stmt->execute(rows);
 126         if(rv!=OK)break;
 127         rv = con->commit();
 128         if(rv!=OK)break;
 129         count++;
 130     }
 131     printf("Total rows inserted %d\n",count);
 132
 133     stmt->free();
 134
 135     //**********************************************************
 136
 137     // SELECT * FROM T1;
 138
 139     strcpy(statement,"SELECT * FROM T1;");
 140     rv = stmt->prepare(statement);
 141     if(rv!=OK)
 142     {
 143       delete stmt;
 144       delete con;
 145       return 5;
 146     }
 147
 148     stmt->bindField(1,&f1var);
 149     stmt->bindField(2,&f2var);
 150     stmt->bindField(3,f3var);
 151     stmt->bindField(4,&f4var);
 152     stmt->bindField(5,&f5var);
 153     stmt->bindField(6,&f6var);
 154     stmt->bindField(7,&f7var);
 155     stmt->bindField(8,&f8var);
 156     stmt->bindField(9,&f9var);
 157     stmt->bindField(10,&f10var);
 158     stmt->bindField(11,f11var);
 159     count=0;
 160     rv = con->beginTrans();
 161     if(rv!=OK)return 6;
 162     stmt->execute(rows);
 163     while(stmt->fetch() !=NULL)
 164     {
 165         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);
 166         printf(" | ");
 167         AllDataType::printVal(f11var,typeBinary,4);
 168         printf("\n");
 169         count++;
 170     }
 171
 172     stmt->close();
 173     rv = con->commit();
 174     if(rv !=OK)
 175     {
 176       delete stmt;
 177       delete con;
 178       return 7;
 179     }
 180
 181     printf("Total row fetched=%d\n",count);
 182     stmt->free();
 183
 184
 185     strcpy(statement,"DROP TABLE T1;");
 186     rv = stmt->prepare(statement);
 187     if(rv!=OK)
 188     {
 189       delete stmt;
 190       delete con;
 191       return 8;
 192     }
 193     rv = stmt->execute(rows);
 194     if(rv!=OK)
 195      {
 196       delete stmt;
 197       delete con;
 198       return 9;
 199     }
 200     stmt->free();
 201     printf("Table dropped\n");
 202
 203     delete stmt;
 204     delete con;
 205     return 0;
 206 }