test/odbc/Select/odbcselect7nw.c

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