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