test/odbc/Parameters/parameter4.c

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