test/sqlapi/Select/paratest1.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it.
   3  *  noOfParamFields() should return zero.
   4  *  getParamFldInfo() for all fields and print the field info.
   5  *  select * from T1;
   6
   7  *  Author : Jitendra Lenka.
   8  */
   9
  10 #include<SqlStatement.h>
  11 #include<CSql.h>
  12 #include<Info.h>
  13 int main()
  14 {
  15   DbRetVal rv = OK;
  16   SqlConnection *con= new SqlConnection();
  17   rv = con->connect("root","manager");
  18   if(rv!=OK)return 1;
  19
  20   SqlStatement *stmt = new SqlStatement();
  21   stmt->setSqlConnection(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);");
  24
  25    int rows = 0;
  26    rv = stmt->prepare(statement);
  27    if(rv!=OK)
  28    {
  29      delete stmt;
  30      delete con;
  31      return 1;
  32    }
  33
  34    rv = stmt->execute(rows);
  35    if(rv!=OK)
  36    {
  37      delete stmt;
  38      delete con;
  39      return 1;
  40    }
  41
  42    stmt->free();
  43    printf("Table created\n");
  44
  45    // insert into table
  46
  47    strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?);");
  48
  49    int f1var = 0;
  50    short int f2var = 1;
  51    char f3var[20]="jitendra";
  52    float f4var = 5.5;
  53    float f5var = 10.50;
  54    Date f6var;
  55    f6var.set(2008,01,21);
  56
  57    Time f7var;
  58    f7var.set(12,29,30);
  59
  60    TimeStamp f8var;
  61    f8var.setDate(2008,01,21);
  62    f8var.setTime(12,29,30);
  63
  64    int f9var = 20;
  65    long long f10var = 12000;
  66
  67    rv = stmt->prepare(statement);
  68    if(rv!=OK)
  69    {
  70      delete stmt;
  71      delete con;
  72      return 1;
  73    }
  74
  75    int count=0;
  76
  77    for(int i=0;i<10;i++)
  78    {
  79         rv  = con->beginTrans();
  80         if(rv!=OK) break;
  81         f1var=i;
  82         stmt->setIntParam(1,f1var);
  83         stmt->setShortParam(2,f2var);
  84         stmt->setStringParam(3,f3var);
  85         stmt->setFloatParam(4,f4var);
  86         stmt->setFloatParam(5,f5var);
  87         stmt->setDateParam(6,f6var);
  88         stmt->setTimeParam(7,f7var);
  89         stmt->setTimeStampParam(8,f8var);
  90         stmt->setIntParam(9,f9var);
  91         stmt->setLongLongParam(10,f10var);
  92
  93         rv = stmt->execute(rows);
  94         if(rv!=OK)break;
  95         rv = con->commit();
  96         if(rv!=OK)break;
  97         count++;
  98     }
  99     printf("Total rows inserted %d\n",count);
 100
 101
 102     int nop;
 103     nop = stmt->noOfParamFields();
 104     printf("Total parameter in the statement=%d\n",nop);
 105
 106     FieldInfo * field = new FieldInfo();
 107
 108
 109     char fldName[20];
 110     DataType type;
 111     size_t length;
 112     int offset;
 113     char defaultValueBuf[10];
 114     bool isNull;
 115     bool isDefault;
 116
 117     rv = stmt->getParamFldInfo(1,field);
 118     if(rv !=OK)return -1;
 119     printf("fldname of field is %s\n", field->fldName);
 120     printf("data type of the field=%d\n",field->type);
 121     printf("length of the field=%d\n",field->length);
 122     printf("offset=%d\n",field->offset);
 123     printf("default value=%s\n",field->defaultValueBuf);
 124     printf("null field=%d\n",field->isNull);
 125     printf("default value is set for the field=%d\n",field->isDefault);
 126
 127    stmt->free();
 128
 129     //**********************************************************
 130
 131     // SELECT * FROM T1;
 132
 133     strcpy(statement,"SELECT * FROM T1;");
 134     rv = stmt->prepare(statement);
 135     if(rv!=OK)
 136     {
 137       delete stmt;
 138       delete con;
 139       return 5;
 140     }
 141
 142     stmt->bindField(1,&f1var);
 143     stmt->bindField(2,&f2var);
 144     stmt->bindField(3,f3var);
 145     stmt->bindField(4,&f4var);
 146     stmt->bindField(5,&f5var);
 147     stmt->bindField(6,&f6var);
 148     stmt->bindField(7,&f7var);
 149     stmt->bindField(8,&f8var);
 150     stmt->bindField(9,&f9var);
 151     stmt->bindField(10,&f10var);
 152
 153     count=0;
 154     rv = con->beginTrans();
 155     if(rv!=OK)return 6;
 156     stmt->execute(rows);
 157     while(stmt->fetch() !=NULL)
 158     {
 159       count++;
 160     }
 161
 162     stmt->close();
 163     rv = con->commit();
 164     if(rv !=OK)
 165     {
 166       delete stmt;
 167       delete con;
 168       return 7;
 169     }
 170
 171     printf("Total row fetched=%d\n",count);
 172     stmt->free();
 173
 174
 175     strcpy(statement,"DROP TABLE T1;");
 176     rv = stmt->prepare(statement);
 177     if(rv!=OK)
 178     {
 179       delete stmt;
 180       delete con;
 181       return 8;
 182     }
 183     rv = stmt->execute(rows);
 184     if(rv!=OK)
 185      {
 186       delete stmt;
 187       delete con;
 188       return 9;
 189     }
 190     stmt->free();
 191     printf("Table dropped\n");
 192
 193     delete stmt;
 194     delete con;
 195     return 0;
 196 }