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