test/odbc/Parameters/parameter3.c

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