test/odbc/Select/odbcselect10.c

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