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