test/odbc/Select/odbcselect7.c

   1 //  create table t1 with two fields.
   2 // prepare with nonexisting field.
   3 // SQLNumResultCols()  should return zero.
   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
  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 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  26 {
  27
  28       int ret;
  29       int f1=90; // f1 field
  30       int f2=20;//f2 field
  31
  32
  33       ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?)",SQL_NTS);
  34       checkrc(ret,__LINE__);
  35
  36       // BIND PARAMETER
  37
  38       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1,0,NULL);
  39       checkrc(ret,__LINE__);
  40
  41       ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f2,0,NULL);
  42       checkrc(ret,__LINE__);
  43
  44
  45
  46       int i,count=0;
  47       for(i=0;i<10;i++)
  48       {
  49          f1++;
  50          f2++;
  51
  52          ret = SQLExecute(stmt);
  53          checkrc(ret,__LINE__);
  54
  55          ret = SQLTransact(env,dbc,SQL_COMMIT);
  56          checkrc(ret,__LINE__);
  57          count++;
  58       }
  59       printf("Total row inserted=%d\n",count);
  60   }
  61 //***********************************************************************
  62 // FETCH ROWS FROM THE TABLE "T1"......select * from T1;
  63 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
  64 {
  65
  66       int ret;
  67       int f1=10; // f1 field
  68       int f2=20;//f2 field
  69
  70        int rettype ;
  71
  72     ret = SQLPrepare(stmt,(unsigned char*)"SELECT F3,F2  FROM T1 ",SQL_NTS);
  73     rettype = ret;
  74     if(rettype!=0)return 1;
  75
  76     //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,)
  77     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL);
  78
  79
  80
  81     ret = SQLBindCol(stmt,2,SQL_C_SLONG,&f2,0,NULL);
  82
  83
  84
  85
  86     int j, count=0;
  87
  88     ret = SQLExecute(stmt);
  89
  90     SQLSMALLINT  noc;
  91     ret = SQLNumResultCols(stmt,&noc);
  92     rettype=ret;
  93
  94     if(rettype != 0 )
  95     {
  96       printf("SQLNumResultCol() returns = %d\n",noc);
  97       return 1;
  98     }
  99
 100    while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
 101     {
 102         count++;
 103
 104     }
 105
 106     /*SQLSMALLINT noc;
 107     ret =SQLNumResultCols(stmt,&noc);
 108
 109     printf("SQLNumResultCols() returns=%d\n",noc);*/
 110
 111     ret = SQLCloseCursor(stmt);
 112     checkrc(ret,__LINE__);
 113
 114     ret = SQLTransact(env,dbc,SQL_COMMIT);
 115     checkrc(ret,__LINE__);
 116
 117     printf("Total row fetched=%d\n",count);
 118 }
 119
 120
 121 int main()
 122 {
 123   SQLHENV env;
 124   SQLHDBC dbc;
 125   SQLHSTMT stmt;
 126   SQLRETURN ret;
 127   SQLCHAR outstr[1024];
 128   SQLSMALLINT outstrlen;
 129
 130   // Aloocate an environment handle
 131   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 132   checkrc(ret,__LINE__);
 133
 134    //we need odbc3 support
 135    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 136
 137   //ALLOCATE A Connection handle
 138   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 139   checkrc(ret,__LINE__);
 140
 141   // connect to the DSN mydsn
 142    ret = SQLConnect (dbc,
 143                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 144                    (SQLCHAR *) "root",
 145                    (SQLSMALLINT) strlen ("root"),
 146                    (SQLCHAR *) "manager",
 147                    (SQLSMALLINT) strlen (""));
 148
 149   if(SQL_SUCCEEDED(ret))
 150   {
 151      printf("\nConnected to the Data Source..\n");
 152
 153
 154   }
 155    else
 156    {
 157         printf("error in connection\n");
 158         return 1;
 159
 160         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 161         checkrc(ret,__LINE__);
 162
 163         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 164         checkrc(ret,__LINE__);
 165         return 1;
 166    }
 167
 168
 169    //******************************************************************
 170    // TABLE CREATED
 171    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 172    checkrc(ret,__LINE__);
 173
 174    SQLCHAR table[100]=
 175      "CREATE TABLE T1(F1 INT,F2 INT)";
 176    ret = SQLPrepare(stmt,table,SQL_NTS);
 177    checkrc(ret,__LINE__);
 178    ret = SQLExecute(stmt);
 179    printf("\nTABLE CREATED\n");
 180    //****************************************************************
 181
 182    InsertTest(env,dbc,stmt);
 183    //*****************************************************************
 184     int ret1;
 185     ret1=FetchTest(env,dbc,stmt);
 186
 187    //****************************************************************
 188    ret = SQLExecDirect(stmt,(unsigned char*)"DROP TABLE T1",SQL_NTS);
 189    checkrc(ret,__LINE__);
 190    printf("Table 'T1' dropped\n");
 191    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 192    checkrc(ret,__LINE__);
 193
 194    ret = SQLDisconnect(dbc);
 195    checkrc(ret,__LINE__);
 196
 197    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 198    checkrc(ret,__LINE__);
 199
 200    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 201    checkrc(ret,__LINE__);
 202
 203    if(ret1 == 0)return 1;
 204
 205    return 0;
 206 }