test/odbc/Select/odbcselect6.c

   1 // create table t1 .
   2 // select statements with nonexisting field.it should failed
   3
   4 // AUTHOR : Jitendra Lenka
   5
   6 #include<stdio.h>
   7 #include<stdlib.h>
   8 #include<sql.h>
   9 #include<sqlext.h>
  10 #include<string.h>
  11
  12 //*************************************************************************
  13
  14 inline void checkrc(int rc,int line)
  15 {
  16    if(rc)
  17    {
  18        printf("ERROR %d at line %d\n",rc,line);
  19        exit(1);
  20    }
  21 }
  22
  23 //*************************************************************************
  24 int  InsertTest(SQLHANDLE env,SQLHANDLE dbc,SQLHANDLE stmt)
  25 {
  26
  27       int ret;
  28       int f1=90; // f1 field
  29       int f2=20;//f2 field
  30
  31
  32       ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(?,?)",SQL_NTS);
  33       checkrc(ret,__LINE__);
  34
  35       // BIND PARAMETER
  36
  37       ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f1,0,NULL);
  38       checkrc(ret,__LINE__);
  39
  40       ret = SQLBindParameter(stmt,2,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,0,&f2,0,NULL);
  41       checkrc(ret,__LINE__);
  42
  43
  44
  45       int i,count=0;
  46       for(i=0;i<10;i++)
  47       {
  48          f1++;
  49          f2++;
  50
  51          ret = SQLExecute(stmt);
  52          checkrc(ret,__LINE__);
  53
  54          ret = SQLTransact(env,dbc,SQL_COMMIT);
  55          checkrc(ret,__LINE__);
  56          count++;
  57       }
  58       printf("Total row inserted=%d\n",count);
  59       return 0;
  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       int rettype ;
  70
  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
  77
  78     //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,)
  79     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f1,0,NULL);
  80
  81
  82     ret = SQLBindCol(stmt,2,SQL_C_SLONG,&f2,0,NULL);
  83
  84
  85
  86
  87     int j, count=0;
  88     ret = SQLExecute(stmt);
  89     checkrc(ret,__LINE__);
  90     while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
  91     {
  92         count++;
  93
  94     }
  95
  96
  97
  98     ret = SQLTransact(env,dbc,SQL_COMMIT);
  99     checkrc(ret,__LINE__);
 100
 101     printf("Total row fetched=%d\n",count);
 102     return 0;
 103 }
 104
 105
 106 int main()
 107 {
 108   SQLHENV env;
 109   SQLHDBC dbc;
 110   SQLHSTMT stmt;
 111   SQLRETURN ret;
 112   SQLCHAR outstr[1024];
 113   SQLSMALLINT outstrlen;
 114
 115   // Aloocate an environment handle
 116   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
 117   checkrc(ret,__LINE__);
 118
 119    //we need odbc3 support
 120    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
 121
 122   //ALLOCATE A Connection handle
 123   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
 124   checkrc(ret,__LINE__);
 125
 126   // connect to the DSN mydsn
 127    ret = SQLConnect (dbc,
 128                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
 129                    (SQLCHAR *) "root",
 130                    (SQLSMALLINT) strlen ("root"),
 131                    (SQLCHAR *) "manager",
 132                    (SQLSMALLINT) strlen (""));
 133
 134   if(SQL_SUCCEEDED(ret))
 135   {
 136      printf("\nConnected to the Data Source..\n");
 137
 138
 139   }
 140    else
 141    {
 142         printf("error in connection\n");
 143
 144         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 145         checkrc(ret,__LINE__);
 146
 147         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 148         checkrc(ret,__LINE__);
 149         return 1;
 150     }
 151
 152
 153    //******************************************************************
 154    // TABLE CREATED
 155    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 156    checkrc(ret,__LINE__);
 157
 158    SQLCHAR table[200]=
 159      "CREATE TABLE T1(F1 INT,F2 INT)";
 160    ret = SQLPrepare(stmt,table,SQL_NTS);
 161    checkrc(ret,__LINE__);
 162    ret = SQLExecute(stmt);
 163    printf("\nTABLE CREATED\n");
 164    //****************************************************************
 165
 166    InsertTest(env,dbc,stmt);
 167    //*****************************************************************
 168     int ret1;
 169     ret1=FetchTest(env,dbc,stmt);
 170
 171    //****************************************************************
 172    ret = SQLPrepare(stmt,(unsigned char*)"drop table T1;",SQL_NTS);
 173    checkrc(ret,__LINE__);
 174
 175    ret = SQLExecute(stmt);
 176    checkrc(ret,__LINE__);
 177    printf("Table 'T1' Dropped\n");
 178
 179    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 180    checkrc(ret,__LINE__);
 181
 182    ret = SQLDisconnect(dbc);
 183    checkrc(ret,__LINE__);
 184
 185    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 186    checkrc(ret,__LINE__);
 187
 188    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 189    checkrc(ret,__LINE__);
 190
 191    if(ret1 == 0)return 1;
 192    return 0;
 193 }