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