test/odbc/Parameters/parameter2.c

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