test/odbc/Select/odbcselect4.c

   1 // create table T1 witjh 10 fileds and insert rows in it.
   2 // prepare statement with 5 fields.
   3 // use SQLNumResultCols() to get the nuber 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 inline void checkrc(int rc,int line)
  16 {
  17    if(rc)
  18    {
  19        printf("ERROR %d at line %d\n",rc,line);
  20        exit(1);
  21    }
  22 }
  23
  24 //*************************************************************************
  25 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  26 {
  27
  28       int ret;
  29       int f1=100; // f1 field
  30       short int f2=20;//f2 field
  31       char f3[50]= "Praba";
  32       float f4 = 2.5;
  33       float f5 = 10.50;
  34       char f6date[15]= "2008-01-12";// f6 field.DATE Field. yyyy-mm-dd
  35       char f7time[20] = "11:30:55";// hh:mm:ss
  36       char f8timestamp[50]= "2008-01-12 11:30:55"; //YYYY-MM-DD .....HH:MM:SS
  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       char strDate[30];
  45
  46       date.year=2008;
  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 = 2008;
  58       timestamp.month = 03;
  59       timestamp.day = 18;
  60       timestamp.hour = 5;
  61       timestamp.minute = 22;
  62       timestamp.second = 10;
  63       timestamp.fraction = 764576;
  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<2;i++)
 102       {
 103        for(int j=0;j<5;j++)
 104        {
 105          f1= f1+i;
 106          f2++;
 107          f4++;
 108          ret = SQLExecute(stmt);
 109          checkrc(ret,__LINE__);
 110
 111          ret = SQLTransact(env,dbc,SQL_COMMIT);
 112          checkrc(ret,__LINE__);
 113          count++;
 114       }
 115      }
 116     printf("Total row inserted=%d\n",count);
 117     return 0;
 118   }
 119 //***********************************************************************
 120 // FETCH ROWS FROM THE TABLE "T1"......select * from T1;
 121 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
 122 {
 123
 124       int ret;
 125       int f1=100; // f1 field
 126       short int f2=20;//f2 field
 127       char f3[50]= "jitendra";
 128       float f4 = 2.5;
 129       float f5 = 10.50;
 130       //char f6date[15]= "2008-01-12";// f6 field.DATE Field. yyyy-mm-dd
 131       //char f7time[20] = "11:30:55";// hh:mm:ss
 132       //char f8timestamp[50]= "2008-01-12 11:30:55"; //YYYY-MM-DD .....HH:MM:SS
 133       int  f9 = 5;
 134       long long f10 = 15000;
 135       int result;
 136
 137
 138       SQL_DATE_STRUCT date;
 139       char strDate[30];
 140
 141       date.year=2008;
 142       date.month=03;
 143       date.day=18;
 144      // strcpy(strDate,"{d '2008-03-18'}");
 145       //**********************************************
 146       SQL_TIME_STRUCT time;
 147       time.hour = 5;
 148       time.minute = 22;
 149       time.second = 10;
 150       //*********************************************
 151       SQL_TIMESTAMP_STRUCT timestamp;
 152       timestamp.year = 2008;
 153       timestamp.month = 03;
 154       timestamp.day = 18;
 155       timestamp.hour = 5;
 156       timestamp.minute = 22;
 157       timestamp.second = 10;
 158       timestamp.fraction = 764576;
 159
 160      ret = SQLPrepare(stmt,(unsigned char*)"SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100 ",SQL_NTS);
 161      checkrc(ret,__LINE__);
 162
 163      SQLSMALLINT noc;
 164     SQLNumResultCols(stmt,&noc);
 165     printf("SQLNumResultCols() returns=%d\n",noc);
 166
 167
 168
 169     ret = SQLBindCol(stmt,1,SQL_C_TYPE_TIMESTAMP,&timestamp,sizeof(timestamp),NULL);
 170     checkrc(ret,__LINE__);
 171
 172     ret = SQLBindCol(stmt,2,SQL_C_SBIGINT,&f10,0,NULL);
 173     checkrc(ret,__LINE__);
 174
 175     ret = SQLBindCol(stmt,3,SQL_C_SSHORT,&f2,0,NULL);
 176     checkrc(ret,__LINE__);
 177
 178      ret = SQLBindCol(stmt,4,SQL_C_TYPE_DATE,&date,sizeof(date),NULL);
 179      checkrc(ret,__LINE__);
 180
 181      ret = SQLBindCol(stmt,5,SQL_C_CHAR,f3,sizeof(f3),NULL);
 182      checkrc(ret,__LINE__);
 183
 184
 185     int j, count=0;
 186     ret = SQLExecute(stmt);
 187     checkrc(ret,__LINE__);
 188     printf("SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100 :\n");
 189
 190
 191     while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
 192     {
 193         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);
 194
 195
 196        count++;
 197
 198     }
 199
 200
 201     ret = SQLCloseCursor(stmt);
 202     checkrc(ret,__LINE__);
 203
 204     ret = SQLTransact(env,dbc,SQL_COMMIT);
 205     checkrc(ret,__LINE__);
 206
 207     printf("Total row fetched=%d\n",count);
 208     return 0;
 209 }
 210
 211
 212 int main()
 213 {
 214   SQLHENV env;
 215   SQLHDBC dbc;
 216   SQLHSTMT stmt;
 217   SQLRETURN ret;
 218   SQLCHAR outstr[1024];
 219   SQLSMALLINT outstrlen;
 220
 221   // Aloocate an environment handle
 222   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 223   checkrc(ret,__LINE__);
 224
 225    //we need odbc3 support
 226    ret = SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 227    checkrc(ret,__LINE__);
 228   //ALLOCATE A Connection handle
 229   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 230   checkrc(ret,__LINE__);
 231
 232   // connect to the DSN mydsn
 233    ret = SQLConnect (dbc,
 234                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 235                    (SQLCHAR *) "root",
 236                    (SQLSMALLINT) strlen ("root"),
 237                    (SQLCHAR *) "manager",
 238                    (SQLSMALLINT) strlen (""));
 239
 240   if(SQL_SUCCEEDED(ret))
 241   {
 242      printf("\nConnected to the Data Source..\n");
 243
 244
 245   }
 246    else
 247    {
 248         printf("Error in conenction\n");
 249
 250         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 251         checkrc(ret,__LINE__);
 252
 253         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 254         checkrc(ret,__LINE__);
 255         return 1;
 256    }
 257
 258
 259    //******************************************************************
 260    // TABLE CREATED
 261    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 262    checkrc(ret,__LINE__);
 263
 264    SQLCHAR table[200]=
 265      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
 266    ret = SQLPrepare(stmt,table,SQL_NTS);
 267    checkrc(ret,__LINE__);
 268
 269    ret = SQLExecute(stmt);
 270    checkrc(ret,__LINE__);
 271    printf("\nTABLE CREATED\n");
 272    //****************************************************************
 273
 274    InsertTest(env,dbc,stmt);
 275    //*****************************************************************
 276
 277     FetchTest(env,dbc,stmt);
 278
 279    //****************************************************************
 280     SQLCHAR drop[100]="DROP TABLE T1";
 281     ret = SQLPrepare(stmt,drop,SQL_NTS);
 282     checkrc(ret,__LINE__);
 283
 284     ret = SQLExecute(stmt);
 285     if(ret!=0)
 286        printf("Statement failed\n");
 287     else
 288        printf("Table dropped successfully\n");
 289     checkrc(ret,__LINE__);
 290
 291     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 292     checkrc(ret,__LINE__);
 293
 294    ret = SQLDisconnect(dbc);
 295    checkrc(ret,__LINE__);
 296
 297    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 298    checkrc(ret,__LINE__);
 299
 300    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 301    checkrc(ret,__LINE__);
 302    return 0;
 303 }