test/odbc/Select/odbcselect3.c

   1 // create table t1 with 10 fileds an d insert some rows in it.
   2 // fetch for some selected field where f1=100
   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 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 // insert some rows in it.
  26 int InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  27 {
  28
  29       int ret;
  30       int f1=90; // f1 field
  31       short int f2=20;//f2 field
  32       char f3[50]= "jitendra";
  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<20;i++)
 103       {
 104          f1++;
 105          f2++;
 106          f4++;
 107          ret = SQLExecute(stmt);
 108          checkrc(ret,__LINE__);
 109
 110          ret = SQLTransact(env,dbc,SQL_COMMIT);
 111          checkrc(ret,__LINE__);
 112          count++;
 113       }
 114       printf("Total row inserted=%d\n",count);
 115      return 0;
 116   }
 117 //***********************************************************************
 118
 119 // FETCH ROWS FROM THE TABLE "T1"......select * from T1;
 120 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
 121 {
 122
 123       int ret;
 124       int f1=10; // f1 field
 125       short int f2=20;//f2 field
 126       char f3[50]= "jitendra";
 127       float f4 = 2.5;
 128       float f5 = 10.50;
 129       int  f9 = 5;
 130       long long f10 = 15000;
 131       int result;
 132
 133
 134       SQL_DATE_STRUCT date;
 135       char strDate[30];
 136
 137       date.year=2008;
 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 = 2008;
 149       timestamp.month = 03;
 150       timestamp.day = 18;
 151       timestamp.hour = 5;
 152       timestamp.minute = 22;
 153       timestamp.second = 10;
 154       timestamp.fraction = 764576;
 155
 156      ret = SQLPrepare(stmt,(unsigned char*)"SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100 ",SQL_NTS);
 157      checkrc(ret,__LINE__);
 158
 159     //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,)
 160
 161
 162     ret = SQLBindCol(stmt,1,SQL_C_TYPE_TIMESTAMP,&timestamp,sizeof(timestamp),NULL);
 163     checkrc(ret,__LINE__);
 164
 165     ret = SQLBindCol(stmt,2,SQL_C_SBIGINT,&f10,0,NULL);
 166     checkrc(ret,__LINE__);
 167
 168     ret = SQLBindCol(stmt,3,SQL_C_SSHORT,&f2,0,NULL);
 169     checkrc(ret,__LINE__);
 170
 171      ret = SQLBindCol(stmt,4,SQL_C_TYPE_DATE,&date,sizeof(date),NULL);
 172      checkrc(ret,__LINE__);
 173
 174      ret = SQLBindCol(stmt,5,SQL_C_CHAR,f3,sizeof(f3),NULL);
 175      checkrc(ret,__LINE__);
 176
 177
 178     int j, count=0;
 179     ret = SQLExecute(stmt);
 180     checkrc(ret,__LINE__);
 181     printf("SELECT F8,F10,F2,F6,F3 FROM T1 WHERE F1=100:\n ");
 182
 183     while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
 184     {
 185       printf("TIMESTAMP=%d-%d-%d %d:%d:%d  F10=%lld  F2=%d  DATE=%d-%d-%d  F3=%s\n ",timestamp.year,timestamp.month,timestamp.day,timestamp.hour,timestamp.minute,timestamp.second,f10,f2,date.year,date.month,date.day,f3);
 186       count++;
 187
 188
 189     }
 190
 191
 192     ret = SQLCloseCursor(stmt);
 193     checkrc(ret,__LINE__);
 194
 195     ret = SQLTransact(env,dbc,SQL_COMMIT);
 196     checkrc(ret,__LINE__);
 197
 198     printf("Total row fetched=%d\n",count);
 199     return 0;
 200 }
 201
 202
 203 int main()
 204 {
 205   SQLHENV env;
 206   SQLHDBC dbc;
 207   SQLHSTMT stmt;
 208   SQLRETURN ret;
 209   SQLCHAR outstr[1024];
 210   SQLSMALLINT outstrlen;
 211
 212   // Aloocate an environment handle
 213   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 214   checkrc(ret,__LINE__);
 215
 216    //we need odbc3 support
 217    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 218
 219   //ALLOCATE A Connection handle
 220   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 221   checkrc(ret,__LINE__);
 222
 223   // connect to the DSN mydsn
 224    ret = SQLConnect (dbc,
 225                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 226                    (SQLCHAR *) "root",
 227                    (SQLSMALLINT) strlen ("root"),
 228                    (SQLCHAR *) "manager",
 229                    (SQLSMALLINT) strlen (""));
 230
 231   if(SQL_SUCCEEDED(ret))
 232   {
 233      printf("\nConnected to the Data Source..\n");
 234
 235
 236   }
 237    else
 238    {
 239         printf("error in conenction\n");
 240
 241
 242         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 243         checkrc(ret,__LINE__);
 244
 245        ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 246        checkrc(ret,__LINE__);
 247        return 1;
 248    }
 249
 250
 251    //******************************************************************
 252    // TABLE CREATED
 253    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 254    checkrc(ret,__LINE__);
 255
 256    SQLCHAR table[200]=
 257      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
 258    ret = SQLPrepare(stmt,table,SQL_NTS);
 259    checkrc(ret,__LINE__);
 260
 261    ret = SQLExecute(stmt);
 262    checkrc(ret,__LINE__);
 263    printf("\nTABLE CREATED\n");
 264    //****************************************************************
 265
 266    InsertTest(env,dbc,stmt);
 267    //*****************************************************************
 268
 269     FetchTest(env,dbc,stmt);
 270
 271    //****************************************************************
 272     SQLCHAR drop[100]="DROP TABLE T1";
 273     ret = SQLPrepare(stmt,drop,SQL_NTS);
 274     checkrc(ret,__LINE__);
 275     ret = SQLExecute(stmt);
 276     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 277        printf("Statement failed\n");
 278     else
 279        printf("Table dropped successfully\n");
 280     checkrc(ret,__LINE__);
 281
 282     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 283     checkrc(ret,__LINE__);
 284
 285    ret = SQLDisconnect(dbc);
 286    checkrc(ret,__LINE__);
 287
 288    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 289    checkrc(ret,__LINE__);
 290
 291    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 292    checkrc(ret,__LINE__);
 293    return 0;
 294 }