test/odbc/Metadata/metadata1.c

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