test/odbc/Select/odbcselect2.c

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