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