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