test/odbc/Parameters/parameter5.c

   1 /*  CREATE TABLE "T1" WITH 10 FIELDS.
   2     INSERT SOME TUPLES IN IT.
   3     SQLNumParam() should return 10.
   4
   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 #include<CSql.h>
  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
  28 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  29 {
  30
  31       int ret;
  32       int f1=90; // f1 field
  33       short int f2=20;//f2 field
  34       char f3[50]= "jitendra";
  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_NTS;
  42       //***********************************
  43       // STRUCTURE FOR 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
  70
  71       ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?);",SQL_NTS);
  72       checkrc(ret,__LINE__);
  73
  74
  75       SQLSMALLINT nop;
  76       SQLNumParams(stmt,&nop);
  77
  78
  79
  80       if(nop!=1)return 1;
  81       printf("Number of parameter=%d\n",nop);
  82
  83       // BIND PARAMETER FOR ALL THE FIELD
  84
  85       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1,0,NULL);
  86       checkrc(ret,__LINE__);
  87
  88      /* ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,0,0,&f2,0,NULL);
  89       checkrc(ret,__LINE__);
  90
  91       ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)f3,0,&slen);
  92       checkrc(ret,__LINE__);
  93
  94       ret = SQLBindParameter(stmt,4,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f4,0,NULL);
  95       checkrc(ret,__LINE__);
  96
  97       ret = SQLBindParameter(stmt,5,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f5,0,NULL);
  98       checkrc(ret,__LINE__);
  99
 100       ret = SQLBindParameter(stmt,6,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_DATE,196,0,&date,sizeof(date),&slen);
 101       checkrc(ret,__LINE__);
 102
 103       ret = SQLBindParameter(stmt,7,SQL_PARAM_INPUT,SQL_C_TYPE_TIME,SQL_TYPE_TIME,196,0,&time,sizeof(time),&slen);
 104       checkrc(ret,__LINE__);
 105
 106       ret = SQLBindParameter(stmt,8,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,196,0,&timestamp,sizeof(timestamp),&slen);
 107       checkrc(ret,__LINE__);
 108
 109       ret = SQLBindParameter(stmt,9,SQL_PARAM_INPUT,SQL_C_TINYINT,SQL_TINYINT,0,0,&f9,0,NULL);
 110       checkrc(ret,__LINE__);
 111
 112       ret = SQLBindParameter(stmt,10,SQL_PARAM_INPUT,SQL_C_SBIGINT,SQL_BIGINT,0,0,&f10,0,NULL);
 113       checkrc(ret,__LINE__);*/
 114
 115       int i,count=0;
 116       // EXECUTE THE STATEMENT
 117       for(i=0;i<20;i++)
 118       {
 119          f1++;
 120          f2++;
 121          f4++;
 122          ret = SQLExecute(stmt);
 123          checkrc(ret,__LINE__);
 124
 125          ret = SQLTransact(env,dbc,SQL_COMMIT);
 126          checkrc(ret,__LINE__);
 127          count++;
 128       }
 129       printf("Total row inserted=%d\n",count);
 130   }
 131 //***********************************************************************
 132
 133
 134 int main()
 135 {
 136   SQLHENV env;
 137   SQLHDBC dbc;
 138   SQLHSTMT stmt;
 139   SQLRETURN ret;
 140   SQLCHAR outstr[1024];
 141   SQLSMALLINT outstrlen;
 142
 143   // Aloocate an environment handle
 144   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 145   checkrc(ret,__LINE__);
 146
 147    //we need odbc3 support
 148    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 149
 150   //ALLOCATE A Connection handle
 151   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 152   checkrc(ret,__LINE__);
 153
 154   // connect to the DSN mydsn
 155    ret = SQLConnect (dbc,
 156                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 157                    (SQLCHAR *) "root",
 158                    (SQLSMALLINT) strlen ("root"),
 159                    (SQLCHAR *) "manager",
 160                    (SQLSMALLINT) strlen (""));
 161
 162
 163   if(SQL_SUCCEEDED(ret))
 164   {
 165      printf("\nConnected to the Data Source..\n");
 166
 167
 168
 169   }
 170    else
 171    {
 172         printf("error in conenction\n");
 173
 174          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 175          checkrc(ret,__LINE__);
 176
 177          ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 178          checkrc(ret,__LINE__);
 179          return 1;
 180    }
 181
 182
 183    //*****************************************************
 184    // TABLE CREATED
 185    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 186    checkrc(ret,__LINE__);
 187
 188 //   SQLCHAR table[200]=
 189     //"CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT);";
 190
 191
 192     SQLCHAR table[200]=
 193       "CREATE TABLE T1(F1 INT);";
 194
 195
 196
 197
 198    ret = SQLPrepare(stmt,table,SQL_NTS);
 199    checkrc(ret,__LINE__);
 200
 201    ret = SQLExecute(stmt);
 202    checkrc(ret,__LINE__);
 203    printf("\nTABLE CREATED\n");
 204    //***************************
 205    InsertTest(env,dbc,stmt);
 206
 207  //  FetchTest(env,dbc,stmt);
 208
 209    //**************************************************
 210     SQLCHAR drop[100]="DROP TABLE T1";
 211     ret = SQLPrepare(stmt,drop,SQL_NTS);
 212     checkrc(ret,__LINE__);
 213     ret = SQLExecute(stmt);
 214     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 215     {
 216       printf("Statement failed\n");
 217        return 1;
 218     }
 219     else
 220        printf("Table 'T1' dropped successfully\n");
 221
 222     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 223     checkrc(ret,__LINE__);
 224
 225    ret = SQLDisconnect(dbc);
 226    checkrc(ret,__LINE__);
 227
 228    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 229    checkrc(ret,__LINE__);
 230
 231    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 232    checkrc(ret,__LINE__);
 233    return 0;
 234 }