test/sqlapi/Csql/ParamStmt/paratest1.c

   1 /* Test Case: Number of parameterised Field(noOfparamFields) and Getting Parameterised field info(getParamFldinfo)
   2  *  create table T1 with 10 fields.
   3  *  insert some records into it.
   4  *  noOfParamFields() should return 11.
   5  *  getParamFldInfo() for all fields and print the field info.
   6  *  select * from T1; It should display proper result
   7 */
   8
   9 #include"common.h"
  10
  11 int main()
  12 {
  13     DbRetVal rv = OK;
  14     AbsSqlConnection *con= createConnection();
  15     rv = con->connect("root","manager");
  16     if(rv!=OK)return 1;
  17     AbsSqlStatement *stmt = createStatement();
  18     stmt->setConnection(con);
  19     char statement[200];
  20     strcpy(statement,"CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT, F11 VARCHAR(20));");
  21     int rows = 0;
  22     rv = stmt->prepare(statement);
  23     if(rv!=OK) { delete stmt; delete con; return 2; }
  24     rv = stmt->execute(rows);
  25     if(rv!=OK) { delete stmt; delete con; return 3; }
  26     stmt->free();
  27     printf("Table created\n");
  28     // insert into table
  29     strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
  30     int f1var = 100;
  31     short int f2var = 10;
  32     char f3var[32]="jitendra";
  33     float f4var = 5.5;
  34     float f5var = 10.50;
  35     Date f6var;
  36     f6var.set(2007,01,21);
  37     Time f7var;
  38     f7var.set(12,29,30);
  39     TimeStamp f8var;
  40     f8var.setDate(2007,01,21);
  41     f8var.setTime(12,29,30);
  42     char f9var = 20;
  43     long long f10var = 12000;
  44     char f11var[20]="Aa1Bb2Cc3Dd4";
  45     rv = stmt->prepare(statement);
  46     if(rv!=OK) { delete stmt; delete con; return 4; }
  47 //****************************************************************************
  48     int nop;
  49     nop = stmt->noOfParamFields();
  50     printf("noOfParamField Returns=%d\n",nop);
  51     //getParamFldInfo()
  52     FieldInfo *field = new FieldInfo();
  53     char fldName[20];
  54     DataType type;
  55     size_t length;
  56     int offset;
  57     char defaultValueBuf[10];
  58     bool isNull;
  59     bool isDefault;
  60     for(int i=1;i<=11;i++) {
  61         printf("\n***********************************************\n");
  62         printf("%d FIELD INFORMATION ::\n",i);;
  63         rv = stmt->getParamFldInfo(i,field);
  64         if(rv!=OK) { delete stmt; delete con; return 1; }
  65         printf("Field Name :%s\n", field->fldName);
  66         printf("Data type of the field :%d\n",field->type);
  67         printf("length of the field :%d\n",field->length);
  68         printf("Offset :%d\n",field->offset);
  69         printf("Default value :%s\n",field->defaultValueBuf);
  70         printf("Null field :%d\n",field->isNull);
  71         printf("Default value :%d\n",field->isDefault);
  72         printf("***********************************************\n");
  73     }
  74     delete field;
  75     int count=0;
  76     for(int i=0;i<10;i++) {
  77         rv = con->beginTrans();
  78             if (rv!=OK) break;
  79             f1var=i;
  80         stmt->setIntParam(1,f1var);
  81         stmt->setShortParam(2,f2var);
  82         stmt->setStringParam(3,f3var);
  83         stmt->setFloatParam(4,f4var);
  84         stmt->setFloatParam(5,f5var);
  85         stmt->setDateParam(6,f6var);
  86         stmt->setTimeParam(7,f7var);
  87         stmt->setTimeStampParam(8,f8var);
  88         stmt->setByteIntParam(9,f9var);
  89         stmt->setLongLongParam(10,f10var);
  90         stmt->setStringParam(11,f11var);
  91         rv = stmt->execute(rows);
  92         if(rv!=OK)break;
  93         rv = con->commit();
  94             if(rv!=OK)break;
  95         count++;
  96     }
  97     printf("Total rows inserted %d\n",count);
  98     stmt->free();
  99     //**********************************************************
 100     // SELECT * FROM T1;
 101     strcpy(statement,"SELECT * FROM T1;");
 102     rv = stmt->prepare(statement);
 103     if(rv!=OK) { delete stmt; delete con; return 5; }
 104     stmt->bindField(1,&f1var);
 105     stmt->bindField(2,&f2var);
 106     stmt->bindField(3,f3var);
 107     stmt->bindField(4,&f4var);
 108     stmt->bindField(5,&f5var);
 109     stmt->bindField(6,&f6var);
 110     stmt->bindField(7,&f7var);
 111     stmt->bindField(8,&f8var);
 112     stmt->bindField(9,&f9var);
 113     stmt->bindField(10,&f10var);
 114     stmt->bindField(11,f11var);
 115     count=0;
 116     rv = con->beginTrans();
 117     if(rv!=OK)return 6;
 118     stmt->execute(rows);
 119     while(stmt->fetch() !=NULL) {
 120         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 |F11=%s \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,f11var);
 121         count++;
 122     }
 123     stmt->close();
 124     rv = con->commit();
 125     if(rv !=OK) { delete stmt; delete con; return 7; }
 126     printf("Total row fetched=%d\n",count);
 127     stmt->free();
 128     strcpy(statement,"DROP TABLE T1;");
 129     rv = stmt->prepare(statement);
 130     if(rv!=OK) { delete stmt; delete con; return 8; }
 131     rv = stmt->execute(rows);
 132     if(rv!=OK) { delete stmt; delete con; return 9; }
 133     printf("Table dropped\n");
 134     stmt->free();
 135     delete stmt; delete con;
 136     return 0;
 137 }