test/odbc/Select/odbcselect6nw.c

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