test/odbc/Parameters/parameter4nw.c

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