test/odbc/Parameters/parameter6nw.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.
   4 //  SELECT TUPLES WITH WHERE CLAUSE
   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 // ERROR CHECK FUNCTION
  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 //*************************************************************************
  26 // FUNCTION FOR INSERTING 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;
  34       f3 =NULL; //(char*)malloc(50);
  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_NULL_DATA;
  42       //***********************************
  43       // STRUCTURE FIOR DATE DATATYPE
  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       //  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 = 2008;
  61       timestamp.month = 03;
  62       timestamp.day = 18;
  63       timestamp.hour = 5;
  64       timestamp.minute = 22;
  65       timestamp.second = 10;
  66       timestamp.fraction = 764576;
  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),NULL);
  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          ret = SQLExecute(stmt);
 112          checkrc(ret,__LINE__);
 113
 114          ret = SQLTransact(env,dbc,SQL_COMMIT);
 115          checkrc(ret,__LINE__);
 116          count++;
 117       }
 118       printf("Total row inserted=%d\n",count);
 119       return 0;
 120   }
 121 //***********************************************************************
 122 // FETCH ROWS FROM THE TABLE "T1"......select * from T1;
 123 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
 124 {
 125
 126       int ret;
 127       int f1=10; // f1 field
 128       short int f2=20;//f2 field
 129       char f3[50]= "praba";
 130       float f4 = 2.5;
 131       float f5 = 10.50;
 132       int f9=5;
 133       long long f10=15000;
 134       SQLINTEGER slen = SQL_NTS;
 135       SQLINTEGER slen1[11];
 136       SQL_DATE_STRUCT  date,f6Date;
 137       SQL_TIME_STRUCT  time, f7Time;
 138       SQL_TIMESTAMP_STRUCT  timestamp, f8Timestamp;
 139       slen1[0]=0;
 140       date.year=2008;
 141       date.month=03;
 142       date.day=18;
 143
 144       time.hour=5;
 145       time.minute=22;
 146       time.second=10;
 147
 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        int f1temp,f2temp;
 157        char f3temp[20];
 158        float f4temp, f5temp;
 159
 160        int f9temp;
 161        long long f10temp;
 162
 163      ret = SQLPrepare(stmt,(unsigned char*)"SELECT *  FROM T1 ",SQL_NTS);
 164      checkrc(ret,__LINE__);
 165     //*******************************************************************************************************
 166
 167     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,&slen1[1]);
 168     checkrc(ret,__LINE__);
 169
 170     ret = SQLBindCol(stmt,2,SQL_C_SSHORT,&f2,0,&slen1[2]);
 171     checkrc(ret,__LINE__);
 172
 173
 174     ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),&slen1[3]);
 175     checkrc(ret,__LINE__);
 176
 177     ret = SQLBindCol(stmt,4,SQL_C_FLOAT,&f4,0,&slen1[4]);
 178     checkrc(ret,__LINE__);
 179
 180     ret = SQLBindCol(stmt,5,SQL_C_FLOAT,&f5,0,&slen1[5]);
 181     checkrc(ret,__LINE__);
 182
 183     ret = SQLBindCol(stmt,6,SQL_C_TYPE_DATE,&date,sizeof(date),&slen1[6]);
 184     checkrc(ret,__LINE__);
 185
 186     ret = SQLBindCol(stmt,7,SQL_C_TYPE_TIME,&time,sizeof(time),&slen1[7]);
 187     checkrc(ret,__LINE__);
 188
 189     ret = SQLBindCol(stmt,8,SQL_C_TYPE_TIMESTAMP,&timestamp,sizeof(timestamp),&slen1[8]);
 190     checkrc(ret,__LINE__);
 191
 192     ret = SQLBindCol(stmt,9,SQL_C_TINYINT,&f9,0,&slen1[9]);
 193     checkrc(ret,__LINE__);
 194
 195     ret = SQLBindCol(stmt,10,SQL_C_SBIGINT,&f10,0,&slen1[10]);
 196     checkrc(ret,__LINE__);
 197
 198     int j, count=0;
 199
 200     f6Date=date;
 201     f7Time=time;
 202     f8Timestamp=timestamp;
 203       ret = SQLExecute(stmt);
 204       checkrc(ret,__LINE__);
 205       while(SQL_SUCCEEDED(ret = SQLFetch(stmt))){
 206           count++;
 207           int k=1;
 208           if(slen1[k++] == SQL_NULL_DATA) printf("F1=NULL  ");
 209           else printf("F1=%d  ",f1);
 210           if(slen1[k++] == SQL_NULL_DATA) printf("F2=NULL  ");
 211           else printf("F2=%d  ",f2);
 212           if(slen1[k++] == SQL_NULL_DATA) printf("F3=NULL  ");
 213           else printf("F3=%s",f3);
 214           if(slen1[k++] == SQL_NULL_DATA) printf("F4=NULL  ");
 215           else printf("F4=%f  ",f4);
 216           if(slen1[k++] == SQL_NULL_DATA) printf("F5=NULL  ");
 217           else printf("F5=%f  ",f5);
 218           if(slen1[k++] == SQL_NULL_DATA) printf("F6=NULL  ");
 219           else printf("F6=%d/%d/%d  ",date.year,date.month,date.day);
 220           if(slen1[k++] == SQL_NULL_DATA) printf("F7=NULL  ");
 221           else printf("F7=%d-%d-%d  ",time.hour,time.minute,time.second);
 222           if(slen1[k++] == SQL_NULL_DATA) printf("F8=NULL  ");
 223           else printf("F8=%d/%d/%d %d-%d-%d  ",timestamp.year,timestamp.month,timestamp.day,timestamp.hour,timestamp.minute,timestamp.second);
 224           if(slen1[k++] == SQL_NULL_DATA) printf("F9=NULL  ");
 225           else printf("F9=%d  ",f9);
 226           if(slen1[k++] == SQL_NULL_DATA) printf("F10=NULL  \n");
 227           else printf("F10=%lld  \n",f10);
 228       }
 229       ret = SQLCloseCursor(stmt);
 230       checkrc(ret,__LINE__);
 231
 232     ret = SQLTransact(env,dbc,SQL_COMMIT);
 233     checkrc(ret,__LINE__);
 234
 235     printf("Total row fetched=%d\n",count);
 236     return 0;
 237 }
 238
 239
 240 int main()
 241 {
 242   SQLHENV env;
 243   SQLHDBC dbc;
 244   SQLHSTMT stmt;
 245   SQLRETURN ret;
 246   SQLCHAR outstr[1024];
 247   SQLSMALLINT outstrlen;
 248
 249   // Aloocate an environment handle
 250   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 251   checkrc(ret,__LINE__);
 252
 253    //we need odbc3 support
 254    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 255
 256   //ALLOCATE A Connection handle
 257   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 258   checkrc(ret,__LINE__);
 259
 260   // connect to the DSN mydsn
 261    ret = SQLConnect (dbc,
 262                    (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;"),
 263                    (SQLCHAR *) "root",
 264                    (SQLSMALLINT) strlen ("root"),
 265                    (SQLCHAR *) "manager",
 266                    (SQLSMALLINT) strlen (""));
 267
 268
 269   if(SQL_SUCCEEDED(ret))
 270   {
 271      printf("\nConnected to the Data Source..\n");
 272
 273
 274   }
 275    else
 276    {
 277         printf("error in connection\n");
 278         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 279         checkrc(ret,__LINE__);
 280
 281         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 282         checkrc(ret,__LINE__);
 283         return 1;
 284    }
 285
 286
 287    //******************************************************************
 288    // TABLE CREATED
 289    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 290    checkrc(ret,__LINE__);
 291
 292    SQLCHAR table[200]=
 293      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
 294    ret = SQLPrepare(stmt,table,SQL_NTS);
 295    checkrc(ret,__LINE__);
 296    ret = SQLExecute(stmt);
 297    checkrc(ret,__LINE__);
 298    printf("\nTABLE CREATED\n");
 299    //*****************************
 300    InsertTest(env,dbc,stmt);
 301    FetchTest(env,dbc,stmt);
 302
 303    //****************************************************************
 304     SQLCHAR drop[100]="DROP TABLE T1";
 305     ret = SQLPrepare(stmt,drop,SQL_NTS);
 306     checkrc(ret,__LINE__);
 307
 308     ret = SQLExecute(stmt);
 309     checkrc(ret,__LINE__);
 310     printf("Table  'T1' dropped successfully\n");
 311
 312     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 313    checkrc(ret,__LINE__);
 314
 315    ret = SQLDisconnect(dbc);
 316    checkrc(ret,__LINE__);
 317
 318    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 319    checkrc(ret,__LINE__);
 320
 321    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 322    checkrc(ret,__LINE__);
 323    return 0;
 324 }