test/odbc/Metadata/metadata1nw.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     SQLDescribecol()  Should return all column information in the resultset
   4     SQLNumResultCols() Should return no of column in result set
   5
   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       // BIND PARAMETER FOR ALL THE FIELD
  75
  76       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1,0,NULL);
  77       checkrc(ret,__LINE__);
  78
  79       ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_SMALLINT,0,0,&f2,0,NULL);
  80       checkrc(ret,__LINE__);
  81
  82       ret = SQLBindParameter(stmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,196,0,(void*)f3,0,&slen);
  83       checkrc(ret,__LINE__);
  84
  85       ret = SQLBindParameter(stmt,4,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f4,0,NULL);
  86       checkrc(ret,__LINE__);
  87
  88       ret = SQLBindParameter(stmt,5,SQL_PARAM_INPUT,SQL_C_FLOAT,SQL_FLOAT,0,0,&f5,0,NULL);
  89       checkrc(ret,__LINE__);
  90
  91       ret = SQLBindParameter(stmt,6,SQL_PARAM_INPUT,SQL_C_TYPE_DATE,SQL_TYPE_DATE,196,0,&date,sizeof(date),&slen);
  92       checkrc(ret,__LINE__);
  93
  94       ret = SQLBindParameter(stmt,7,SQL_PARAM_INPUT,SQL_C_TYPE_TIME,SQL_TYPE_TIME,196,0,&time,sizeof(time),&slen);
  95       checkrc(ret,__LINE__);
  96
  97       ret = SQLBindParameter(stmt,8,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,196,0,&timestamp,sizeof(timestamp),&slen);
  98       checkrc(ret,__LINE__);
  99
 100       ret = SQLBindParameter(stmt,9,SQL_PARAM_INPUT,SQL_C_TINYINT,SQL_TINYINT,0,0,&f9,0,NULL);
 101       checkrc(ret,__LINE__);
 102
 103       ret = SQLBindParameter(stmt,10,SQL_PARAM_INPUT,SQL_C_SBIGINT,SQL_BIGINT,0,0,&f10,0,NULL);
 104       checkrc(ret,__LINE__);
 105
 106       int i,count=0;
 107       // EXECUTE THE STATEMENT
 108       for(i=0;i<20;i++)
 109       {
 110          f1++;
 111          f2++;
 112          f4++;
 113          ret = SQLExecute(stmt);
 114          checkrc(ret,__LINE__);
 115
 116          ret = SQLTransact(env,dbc,SQL_COMMIT);
 117          checkrc(ret,__LINE__);
 118          count++;
 119       }
 120       printf("Total row inserted=%d\n",count);
 121       return 0;
 122   }
 123 //***********************************************************************
 124 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
 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       SQL_DATE_STRUCT  date,f6Date;
 136       SQL_TIME_STRUCT  time, f7Time;
 137       SQL_TIMESTAMP_STRUCT  timestamp, f8Timestamp;
 138
 139       date.year=2008;
 140       date.month=03;
 141       date.day=18;
 142
 143       time.hour=5;
 144       time.minute=22;
 145       time.second=10;
 146
 147       timestamp.year=2008;
 148       timestamp.month=03;
 149       timestamp.day=18;
 150       timestamp.hour=5;
 151       timestamp.minute = 22;
 152       timestamp.second = 10;
 153       timestamp.fraction = 764576;
 154
 155        int f1temp,f2temp;
 156        char f3temp[20];
 157        float f4temp, f5temp;
 158
 159        int f9temp;
 160        long long f10temp;
 161
 162      ret = SQLPrepare(stmt,(unsigned char*)"SELECT *  FROM T1 ",SQL_NTS);
 163      checkrc(ret,__LINE__);
 164     short totalFields=0;
 165     ret = SQLNumResultCols (stmt, &totalFields);
 166     checkrc(ret,__LINE__);
 167     printf( "No of columns in resultset = %d\n",totalFields);
 168     UWORD                   icol;
 169     UCHAR                   colName[IDENTIFIER_LENGTH];
 170     SWORD                   colNameMax;
 171     SWORD                   nameLength;
 172     SWORD                   colType;
 173     SQLULEN                 colLength;
 174     SWORD                   scale;
 175     SWORD                   nullable;
 176     icol = 1; colNameMax = IDENTIFIER_LENGTH;
 177      while(icol <=10){
 178      ret = SQLDescribeCol(stmt, icol, colName, colNameMax, &nameLength, &colType, &colLength, &scale, &nullable);
 179      checkrc(ret,__LINE__);
 180
 181       printf("Name %s len %d type %d collen %d scale %d nullable %d\n",colName,nameLength,colType,colLength,scale,nullable );
 182       icol ++;
 183      }
 184
 185
 186     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL);
 187     checkrc(ret,__LINE__);
 188
 189     ret = SQLBindCol(stmt,2,SQL_C_SSHORT,&f2,0,NULL);
 190     checkrc(ret,__LINE__);
 191
 192
 193     ret = SQLBindCol(stmt,3,SQL_C_CHAR,f3,sizeof(f3),NULL);
 194     checkrc(ret,__LINE__);
 195
 196     ret = SQLBindCol(stmt,4,SQL_C_FLOAT,&f4,0,NULL);
 197     checkrc(ret,__LINE__);
 198
 199     ret = SQLBindCol(stmt,5,SQL_C_FLOAT,&f5,0,NULL);
 200     checkrc(ret,__LINE__);
 201
 202     ret = SQLBindCol(stmt,6,SQL_C_TYPE_DATE,&date,sizeof(date),NULL);
 203     checkrc(ret,__LINE__);
 204
 205     ret = SQLBindCol(stmt,7,SQL_C_TYPE_TIME,&time,sizeof(time),NULL);
 206     checkrc(ret,__LINE__);
 207
 208     ret = SQLBindCol(stmt,8,SQL_C_TYPE_TIMESTAMP,&timestamp,sizeof(timestamp),NULL);
 209     checkrc(ret,__LINE__);
 210
 211     ret = SQLBindCol(stmt,9,SQL_C_TINYINT,&f9,0,NULL);
 212     checkrc(ret,__LINE__);
 213
 214     ret = SQLBindCol(stmt,10,SQL_C_SBIGINT,&f10,0,NULL);
 215     checkrc(ret,__LINE__);
 216
 217     int j, count=0;
 218
 219
 220
 221     f6Date=date;
 222     f7Time=time;
 223     f8Timestamp=timestamp;
 224     for(j=0;j<10;j++)
 225     {
 226       f1temp=f1++;
 227       f2temp=f2++;
 228       strcpy(f3temp,"praba");
 229       f4temp=f4++;
 230       f5temp=f5;
 231       f9temp=f9;
 232       f10temp=f10;
 233
 234       ret = SQLExecute(stmt);
 235       checkrc(ret,__LINE__);
 236       ret = SQLFetch(stmt);
 237       ret = SQLCloseCursor(stmt);
 238       checkrc(ret,__LINE__);
 239       count++;
 240
 241        printf("F1=%d\tF2=%d\tF3=%s\tF4=%f\tF5=%f\tDATE=%d/%d/%d\tTIME=%d-%d-%d\tTIMESTAMP=%d/%d/%d %d-%d-%d\tF9=%d\tF10=%lld\n ",f1,f2,f3,f4,f5,date.year,date.month,date.day,time.hour,time.minute,time.second,timestamp.year,timestamp.month,timestamp.day,timestamp.hour,timestamp.minute,timestamp.second,f9,f10);
 242
 243
 244   }
 245
 246     ret = SQLTransact(env,dbc,SQL_COMMIT);
 247        checkrc(ret,__LINE__);
 248
 249     printf("Total row fetched=%d\n",count);
 250     return 0;
 251 }
 252
 253
 254
 255 int main()
 256 {
 257   SQLHENV env;
 258   SQLHDBC dbc;
 259   SQLHSTMT stmt;
 260   SQLRETURN ret;
 261   SQLCHAR outstr[1024];
 262   SQLSMALLINT outstrlen;
 263
 264   // Aloocate an environment handle
 265   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 266   checkrc(ret,__LINE__);
 267
 268    //we need odbc3 support
 269    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 270
 271   //ALLOCATE A Connection handle
 272   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 273   checkrc(ret,__LINE__);
 274
 275   // connect to the DSN mydsn
 276    ret = SQLConnect (dbc,
 277                    (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;"),
 278                    (SQLCHAR *) "root",
 279                    (SQLSMALLINT) strlen ("root"),
 280                    (SQLCHAR *) "manager",
 281                    (SQLSMALLINT) strlen (""));
 282
 283
 284   if(SQL_SUCCEEDED(ret))
 285   {
 286      printf("\nConnected to the Data Source..\n");
 287
 288
 289
 290   }
 291    else
 292    {
 293         printf("error in conenction\n");
 294
 295          ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 296          checkrc(ret,__LINE__);
 297
 298          ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 299          checkrc(ret,__LINE__);
 300          return 1;
 301    }
 302
 303
 304    //*****************************************************
 305    // TABLE CREATED
 306    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 307    checkrc(ret,__LINE__);
 308
 309    SQLCHAR table[200]=
 310      "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT);";
 311
 312    ret = SQLPrepare(stmt,table,SQL_NTS);
 313    checkrc(ret,__LINE__);
 314
 315    ret = SQLExecute(stmt);
 316    checkrc(ret,__LINE__);
 317    printf("\nTABLE CREATED\n");
 318    //***************************
 319    InsertTest(env,dbc,stmt);
 320    FetchTest(env,dbc,stmt);
 321    //**************************************************
 322     SQLCHAR drop[100]="DROP TABLE T1";
 323     ret = SQLPrepare(stmt,drop,SQL_NTS);
 324     checkrc(ret,__LINE__);
 325     ret = SQLExecute(stmt);
 326     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 327     {
 328       printf("Statement failed\n");
 329        return 1;
 330     }
 331     else
 332        printf("Table 'T1' dropped successfully\n");
 333
 334     ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 335     checkrc(ret,__LINE__);
 336
 337    ret = SQLDisconnect(dbc);
 338    checkrc(ret,__LINE__);
 339
 340    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 341    checkrc(ret,__LINE__);
 342
 343    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 344    checkrc(ret,__LINE__);
 345    return 0;
 346 }