test/odbc/Metadata/metadata2.c

   1 /*  CREATE TABLE "T1" WITH 10 FIELDS.
   2     SQLDescribeParam() should return Should return all field defination
   3     SQLNumParams() should return no of parameter in the prepare statement
   4 */
   5
   6 #include<stdio.h>
   7 #include<stdlib.h>
   8 #include<sql.h>
   9 #include<sqlext.h>
  10 #include<string.h>
  11 #include<CSql.h>
  12 //*************************************************************************
  13 // ERROR CHECK FUNCTION
  14 inline void checkrc(int rc,int line)
  15 {
  16    if(rc)
  17    {
  18        printf("ERROR %d at line %d\n",rc,line);
  19        exit(1);
  20    }
  21 }
  22
  23 //*************************************************************************
  24 // FUNCTION FOR INSERTING ROWS IN IT.
  25
  26 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  27 {
  28
  29       int ret;
  30       int f1=90; // f1 field
  31       short int f2=20;//f2 field
  32       char f3[50]= "jitendra";
  33       float f4 = 2.5;
  34       float f5 = 10.50;
  35       int  f9 = 5;
  36       long long f10 = 15000;
  37       int result;
  38
  39       SQLINTEGER slen = SQL_NTS;
  40       //***********************************
  41       // STRUCTURE FOR DATE DATATYPE
  42       SQL_DATE_STRUCT date;
  43       char strDate[30];
  44
  45       date.year=2008;
  46       date.month=03;
  47       date.day=18;
  48      // strcpy(strDate,"{d '2008-03-18'}");
  49       //*******************************
  50       //  STRUCTURE FOR TIME DATATYPE.
  51       SQL_TIME_STRUCT time;
  52       time.hour = 5;
  53       time.minute = 22;
  54       time.second = 10;
  55       //*****************************
  56       // STRUCTURE FOR TIMESTAMP DATATYPE
  57       SQL_TIMESTAMP_STRUCT timestamp;
  58       timestamp.year = 2008;
  59       timestamp.month = 03;
  60       timestamp.day = 18;
  61       timestamp.hour = 5;
  62       timestamp.minute = 22;
  63       timestamp.second = 10;
  64       timestamp.fraction = 764576;
  65      //******************************
  66       // PREPARE THE STATEMENT.
  67
  68
  69       ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?);",SQL_NTS);
  70       checkrc(ret,__LINE__);
  71
  72       // BIND PARAMETER FOR ALL THE FIELD
  73        short totalFields=0;
  74       ret = SQLNumParams (stmt, &totalFields);
  75       checkrc(ret,__LINE__);
  76       printf("No of parameter %d\n", totalFields);
  77       UWORD                   icol;
  78     UCHAR                   colName[IDENTIFIER_LENGTH];
  79     SWORD                   colNameMax;
  80     SWORD                   nameLength;
  81     SWORD                   colType;
  82     SQLULEN                 colLength;
  83     SWORD                   scale;
  84     SWORD                   nullable;
  85     icol = 1; colNameMax = IDENTIFIER_LENGTH;
  86   while (icol <= totalFields)
  87     {
  88         ret = SQLDescribeParam(stmt, icol, &colType, &colLength,&scale, &nullable);
  89         checkrc(ret,__LINE__);
  90         printf("Param Type %d , length %d,Scale %d Nullinfo %d\n",colType,colLength,scale,nullable);
  91         icol++;
  92     }
  93
  94
  95       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1,0,NULL);
  96       checkrc(ret,__LINE__);
  97
  98       ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,0,0,&f2,0,NULL);
  99       checkrc(ret,__LINE__);
 100
 101       ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)f3,0,&slen);
 102       checkrc(ret,__LINE__);
 103
 104       ret = SQLBindParameter(stmt,4,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f4,0,NULL);
 105       checkrc(ret,__LINE__);
 106
 107       ret = SQLBindParameter(stmt,5,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f5,0,NULL);
 108       checkrc(ret,__LINE__);
 109
 110       ret = SQLBindParameter(stmt,6,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_DATE,196,0,&date,sizeof(date),&slen);
 111       checkrc(ret,__LINE__);
 112
 113       ret = SQLBindParameter(stmt,7,SQL_PARAM_INPUT,SQL_C_TYPE_TIME,SQL_TYPE_TIME,196,0,&time,sizeof(time),&slen);
 114       checkrc(ret,__LINE__);
 115
 116       ret = SQLBindParameter(stmt,8,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,196,0,&timestamp,sizeof(timestamp),&slen);
 117       checkrc(ret,__LINE__);
 118
 119       ret = SQLBindParameter(stmt,9,SQL_PARAM_INPUT,SQL_C_TINYINT,SQL_TINYINT,0,0,&f9,0,NULL);
 120       checkrc(ret,__LINE__);
 121
 122       ret = SQLBindParameter(stmt,10,SQL_PARAM_INPUT,SQL_C_SBIGINT,SQL_BIGINT,0,0,&f10,0,NULL);
 123       checkrc(ret,__LINE__);
 124
 125       int i,count=0;
 126       // EXECUTE THE STATEMENT
 127       for(i=0;i<20;i++)
 128       {
 129          f1++;
 130          f2++;
 131          f4++;
 132          ret = SQLExecute(stmt);
 133          checkrc(ret,__LINE__);
 134
 135          ret = SQLTransact(env,dbc,SQL_COMMIT);
 136          checkrc(ret,__LINE__);
 137          count++;
 138       }
 139       printf("Total row inserted=%d\n",count);
 140       return 0;
 141   }
 142 //***********************************************************************
 143 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
 144 {
 145       int ret;
 146       int f1=10; // f1 field
 147       short int f2=20;//f2 field
 148       char f3[50]= "praba";
 149       float f4 = 2.5;
 150       float f5 = 10.50;
 151       int f9=5;
 152       long long f10=15000;
 153       SQLINTEGER slen = SQL_NTS;
 154       SQL_DATE_STRUCT  date,f6Date;
 155       SQL_TIME_STRUCT  time, f7Time;
 156       SQL_TIMESTAMP_STRUCT  timestamp, f8Timestamp;
 157
 158       date.year=2008;
 159       date.month=03;
 160       date.day=18;
 161
 162       time.hour=5;
 163       time.minute=22;
 164       time.second=10;
 165
 166       timestamp.year=2008;
 167       timestamp.month=03;
 168       timestamp.day=18;
 169       timestamp.hour=5;
 170       timestamp.minute = 22;
 171       timestamp.second = 10;
 172       timestamp.fraction = 764576;
 173
 174        int f1temp,f2temp;
 175        char f3temp[20];
 176        float f4temp, f5temp;
 177
 178        int f9temp;
 179        long long f10temp;
 180
 181      ret = SQLPrepare(stmt,(unsigned char*)"SELECT *  FROM T1 ",SQL_NTS);
 182      checkrc(ret,__LINE__);
 183
 184     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL);
 185     checkrc(ret,__LINE__);
 186
 187     ret = SQLBindCol(stmt,2,SQL_C_SSHORT,&f2,0,NULL);
 188     checkrc(ret,__LINE__);
 189
 190
 191     ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL);
 192     checkrc(ret,__LINE__);
 193
 194     ret = SQLBindCol(stmt,4,SQL_C_FLOAT,&f4,0,NULL);
 195     checkrc(ret,__LINE__);
 196
 197     ret = SQLBindCol(stmt,5,SQL_C_FLOAT,&f5,0,NULL);
 198     checkrc(ret,__LINE__);
 199
 200     ret = SQLBindCol(stmt,6,SQL_C_TYPE_DATE,&date,sizeof(date),NULL);
 201     checkrc(ret,__LINE__);
 202
 203     ret = SQLBindCol(stmt,7,SQL_C_TYPE_TIME,&time,sizeof(time),NULL);
 204     checkrc(ret,__LINE__);
 205
 206     ret = SQLBindCol(stmt,8,SQL_C_TYPE_TIMESTAMP,&timestamp,sizeof(timestamp),NULL);
 207     checkrc(ret,__LINE__);
 208
 209     ret = SQLBindCol(stmt,9,SQL_C_TINYINT,&f9,0,NULL);
 210     checkrc(ret,__LINE__);
 211
 212     ret = SQLBindCol(stmt,10,SQL_C_SBIGINT,&f10,0,NULL);
 213     checkrc(ret,__LINE__);
 214
 215     int j, count=0;
 216
 217
 218
 219     f6Date=date;
 220     f7Time=time;
 221     f8Timestamp=timestamp;
 222     for(j=0;j<10;j++)
 223     {
 224       f1temp=f1++;
 225       f2temp=f2++;
 226       strcpy(f3temp,"praba");
 227       f4temp=f4++;
 228       f5temp=f5;
 229       f9temp=f9;
 230       f10temp=f10;
 231
 232       ret = SQLExecute(stmt);
 233       checkrc(ret,__LINE__);
 234       ret = SQLFetch(stmt);
 235       ret = SQLCloseCursor(stmt);
 236       checkrc(ret,__LINE__);
 237       count++;
 238
 239        printf("F1=%d\tF2=%d\tF3=%s\tF4=%f\tF5=%f\tDATE=%d/%d/%d\tTIME=%d-%d-%d\tTIMESTAMP=%d/%d/%d %d-%d-%d\tF9=%d\tF10=%lld\n ",f1,f2,f3,f4,f5,date.year,date.month,date.day,time.hour,time.minute,time.second,timestamp.year,timestamp.month,timestamp.day,timestamp.hour,timestamp.minute,timestamp.second,f9,f10);
 240
 241
 242   }
 243
 244     ret = SQLTransact(env,dbc,SQL_COMMIT);
 245        checkrc(ret,__LINE__);
 246
 247     printf("Total row fetched=%d\n",count);
 248     return 0;
 249 }
 250
 251
 252
 253 int main()
 254 {
 255   SQLHENV env;
 256   SQLHDBC dbc;
 257   SQLHSTMT stmt;
 258   SQLRETURN ret;
 259   SQLCHAR outstr[1024];
 260   SQLSMALLINT outstrlen;
 261
 262   // Aloocate an environment handle
 263   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 264   checkrc(ret,__LINE__);
 265
 266    //we need odbc3 support
 267    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 268
 269   //ALLOCATE A Connection handle
 270   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 271   checkrc(ret,__LINE__);
 272
 273   // connect to the DSN mydsn
 274    ret = SQLConnect (dbc,
 275                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 276                    (SQLCHAR *) "root",
 277                    (SQLSMALLINT) strlen ("root"),
 278                    (SQLCHAR *) "manager",
 279                    (SQLSMALLINT) strlen (""));
 280
 281
 282   if(SQL_SUCCEEDED(ret))
 283   {
 284      printf("\nConnected to the Data Source..\n");
 285
 286
 287
 288   }
 289    else
 290    {
 291         printf("error in conenction\n");
 292
 293          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 294          checkrc(ret,__LINE__);
 295
 296          ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 297          checkrc(ret,__LINE__);
 298          return 1;
 299    }
 300
 301
 302    //*****************************************************
 303    // TABLE CREATED
 304    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 305    checkrc(ret,__LINE__);
 306
 307    SQLCHAR table[200]=
 308      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT);";
 309
 310    ret = SQLPrepare(stmt,table,SQL_NTS);
 311    checkrc(ret,__LINE__);
 312
 313    ret = SQLExecute(stmt);
 314    checkrc(ret,__LINE__);
 315    printf("\nTABLE CREATED\n");
 316    //***************************
 317    InsertTest(env,dbc,stmt);
 318    FetchTest(env,dbc,stmt);
 319    //**************************************************
 320     SQLCHAR drop[100]="DROP TABLE T1";
 321     ret = SQLPrepare(stmt,drop,SQL_NTS);
 322     checkrc(ret,__LINE__);
 323     ret = SQLExecute(stmt);
 324     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 325     {
 326       printf("Statement failed\n");
 327        return 1;
 328     }
 329     else
 330        printf("Table 'T1' dropped successfully\n");
 331
 332     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 333     checkrc(ret,__LINE__);
 334
 335    ret = SQLDisconnect(dbc);
 336    checkrc(ret,__LINE__);
 337
 338    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 339    checkrc(ret,__LINE__);
 340
 341    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 342    checkrc(ret,__LINE__);
 343    return 0;
 344 }