test/odbc/Select/odbcselect9nw.c

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