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