test/odbc/Select/odbcselect4nw.c

   1 //Use Connection String "DSN=mycsql;MODE=csql;SERVER=127.0.0.1;PORT=5678;" for Connect Data Source
   2 //create table T1 witjh 10 fileds and insert rows in it.
   3 // prepare statement with 5 fields.
   4 // use SQLNumResultCols() to get the nuber of fields in prepare statements.
   5
   6 // AUTHOR  :  Jitendra  Lenka
   7
   8 #include<stdio.h>
   9 #include<stdlib.h>
  10 #include<sql.h>
  11 #include<sqlext.h>
  12 #include<string.h>
  13
  14 //*************************************************************************
  15
  16 inline void checkrc(int rc,int line)
  17 {
  18    if(rc)
  19    {
  20        printf("ERROR %d at line %d\n",rc,line);
  21        exit(1);
  22    }
  23 }
  24
  25 //*************************************************************************
  26 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  27 {
  28
  29       int ret;
  30       int f1=100; // f1 field
  31       short int f2=20;//f2 field
  32       char f3[50]= "Praba";
  33       float f4 = 2.5;
  34       float f5 = 10.50;
  35       char f6date[15]= "2008-01-12";// f6 field.DATE Field. yyyy-mm-dd
  36       char f7time[20] = "11:30:55";// hh:mm:ss
  37       char f8timestamp[50]= "2008-01-12 11:30:55"; //YYYY-MM-DD .....HH:MM:SS
  38       int  f9 = 5;
  39       long long f10 = 15000;
  40       int result;
  41
  42       SQLINTEGER slen = SQL_NTS;
  43       //***********************************
  44       SQL_DATE_STRUCT date;
  45       char strDate[30];
  46
  47       date.year=2008;
  48       date.month=03;
  49       date.day=18;
  50      // strcpy(strDate,"{d '2008-03-18'}");
  51       //**********************************************
  52       SQL_TIME_STRUCT time;
  53       time.hour = 5;
  54       time.minute = 22;
  55       time.second = 10;
  56       //*********************************************
  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       ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?)",SQL_NTS);
  67       checkrc(ret,__LINE__);
  68
  69       // BIND PARAMETER
  70
  71       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1,0,NULL);
  72       checkrc(ret,__LINE__);
  73
  74       ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,0,0,&f2,0,NULL);
  75       checkrc(ret,__LINE__);
  76
  77       ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)f3,0,&slen);
  78       checkrc(ret,__LINE__);
  79
  80       ret = SQLBindParameter(stmt,4,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f4,0,NULL);
  81       checkrc(ret,__LINE__);
  82
  83       ret = SQLBindParameter(stmt,5,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_REAL,0,0,&f5,0,NULL);
  84       checkrc(ret,__LINE__);
  85
  86       ret = SQLBindParameter(stmt,6,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_DATE,196,0,&date,sizeof(date),&slen);
  87       checkrc(ret,__LINE__);
  88
  89       ret = SQLBindParameter(stmt,7,SQL_PARAM_INPUT,SQL_C_TYPE_TIME,SQL_TYPE_TIME,196,0,&time,sizeof(time),&slen);
  90       checkrc(ret,__LINE__);
  91
  92      ret = SQLBindParameter(stmt,8,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,196,0,&timestamp,sizeof(timestamp),&slen);
  93       checkrc(ret,__LINE__);
  94
  95       ret = SQLBindParameter(stmt,9,SQL_PARAM_INPUT,SQL_C_TINYINT,SQL_TINYINT,0,0,&f9,0,NULL);
  96       checkrc(ret,__LINE__);
  97
  98       ret = SQLBindParameter(stmt,10,SQL_PARAM_INPUT,SQL_C_SBIGINT,SQL_BIGINT,0,0,&f10,0,NULL);
  99       checkrc(ret,__LINE__);
 100
 101       int i,count=0;
 102       for(i=0;i<2;i++)
 103       {
 104        for(int j=0;j<5;j++)
 105        {
 106          f1= f1+i;
 107          f2++;
 108          f4++;
 109          ret = SQLExecute(stmt);
 110          checkrc(ret,__LINE__);
 111
 112          ret = SQLTransact(env,dbc,SQL_COMMIT);
 113          checkrc(ret,__LINE__);
 114          count++;
 115       }
 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=100; // f1 field
 127       short int f2=20;//f2 field
 128       char f3[50]= "jitendra";
 129       float f4 = 2.5;
 130       float f5 = 10.50;
 131       //char f6date[15]= "2008-01-12";// f6 field.DATE Field. yyyy-mm-dd
 132       //char f7time[20] = "11:30:55";// hh:mm:ss
 133       //char f8timestamp[50]= "2008-01-12 11:30:55"; //YYYY-MM-DD .....HH:MM:SS
 134       int  f9 = 5;
 135       long long f10 = 15000;
 136       int result;
 137
 138
 139       SQL_DATE_STRUCT date;
 140       char strDate[30];
 141
 142       date.year=2008;
 143       date.month=03;
 144       date.day=18;
 145      // strcpy(strDate,"{d '2008-03-18'}");
 146       //**********************************************
 147       SQL_TIME_STRUCT time;
 148       time.hour = 5;
 149       time.minute = 22;
 150       time.second = 10;
 151       //*********************************************
 152       SQL_TIMESTAMP_STRUCT timestamp;
 153       timestamp.year = 2008;
 154       timestamp.month = 03;
 155       timestamp.day = 18;
 156       timestamp.hour = 5;
 157       timestamp.minute = 22;
 158       timestamp.second = 10;
 159       timestamp.fraction = 764576;
 160
 161      ret = SQLPrepare(stmt,(unsigned char*)"SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100 ",SQL_NTS);
 162      checkrc(ret,__LINE__);
 163
 164      SQLSMALLINT noc;
 165     SQLNumResultCols(stmt,&noc);
 166     printf("SQLNumResultCols() returns=%d\n",noc);
 167
 168
 169
 170     ret = SQLBindCol(stmt,1,SQL_C_TYPE_TIMESTAMP,&timestamp,sizeof(timestamp),NULL);
 171     checkrc(ret,__LINE__);
 172
 173     ret = SQLBindCol(stmt,2,SQL_C_SBIGINT,&f10,0,NULL);
 174     checkrc(ret,__LINE__);
 175
 176     ret = SQLBindCol(stmt,3,SQL_C_SSHORT,&f2,0,NULL);
 177     checkrc(ret,__LINE__);
 178
 179      ret = SQLBindCol(stmt,4,SQL_C_TYPE_DATE,&date,sizeof(date),NULL);
 180      checkrc(ret,__LINE__);
 181
 182      ret = SQLBindCol(stmt,5,SQL_C_CHAR,f3,sizeof(f3),NULL);
 183      checkrc(ret,__LINE__);
 184
 185
 186     int j, count=0;
 187     ret = SQLExecute(stmt);
 188     checkrc(ret,__LINE__);
 189     printf("SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100 :\n");
 190
 191
 192     while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
 193     {
 194         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);
 195
 196
 197        count++;
 198
 199     }
 200
 201
 202     ret = SQLCloseCursor(stmt);
 203     checkrc(ret,__LINE__);
 204
 205     ret = SQLTransact(env,dbc,SQL_COMMIT);
 206     checkrc(ret,__LINE__);
 207
 208     printf("Total row fetched=%d\n",count);
 209     return 0;
 210 }
 211
 212
 213 int main()
 214 {
 215   SQLHENV env;
 216   SQLHDBC dbc;
 217   SQLHSTMT stmt;
 218   SQLRETURN ret;
 219   SQLCHAR outstr[1024];
 220   SQLSMALLINT outstrlen;
 221
 222   // Aloocate an environment handle
 223   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 224   checkrc(ret,__LINE__);
 225
 226    //we need odbc3 support
 227    ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 228    checkrc(ret,__LINE__);
 229   //ALLOCATE A Connection handle
 230   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 231   checkrc(ret,__LINE__);
 232
 233   // connect to the DSN mydsn
 234    ret = SQLConnect (dbc,
 235                    (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;"),
 236                    (SQLCHAR *) "root",
 237                    (SQLSMALLINT) strlen ("root"),
 238                    (SQLCHAR *) "manager",
 239                    (SQLSMALLINT) strlen (""));
 240
 241   if(SQL_SUCCEEDED(ret))
 242   {
 243      printf("\nConnected to the Data Source..\n");
 244
 245
 246   }
 247    else
 248    {
 249         printf("Error in conenction\n");
 250
 251         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 252         checkrc(ret,__LINE__);
 253
 254         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 255         checkrc(ret,__LINE__);
 256         return 1;
 257    }
 258
 259
 260    //******************************************************************
 261    // TABLE CREATED
 262    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 263    checkrc(ret,__LINE__);
 264
 265    SQLCHAR table[200]=
 266      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
 267    ret = SQLPrepare(stmt,table,SQL_NTS);
 268    checkrc(ret,__LINE__);
 269
 270    ret = SQLExecute(stmt);
 271    checkrc(ret,__LINE__);
 272    printf("\nTABLE CREATED\n");
 273    //****************************************************************
 274
 275    InsertTest(env,dbc,stmt);
 276    //*****************************************************************
 277
 278     FetchTest(env,dbc,stmt);
 279
 280    //****************************************************************
 281     SQLCHAR drop[100]="DROP TABLE T1";
 282     ret = SQLPrepare(stmt,drop,SQL_NTS);
 283     checkrc(ret,__LINE__);
 284
 285     ret = SQLExecute(stmt);
 286     if(ret!=0)
 287        printf("Statement failed\n");
 288     else
 289        printf("Table dropped successfully\n");
 290     checkrc(ret,__LINE__);
 291
 292     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 293     checkrc(ret,__LINE__);
 294
 295    ret = SQLDisconnect(dbc);
 296    checkrc(ret,__LINE__);
 297
 298    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 299    checkrc(ret,__LINE__);
 300
 301    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 302    checkrc(ret,__LINE__);
 303    return 0;
 304 }