test/odbc/Transaction/transact2.c

   1 //  create table with two integer field.
   2 //  update and rollbacked ,in sqlfetch counter value should be zero.
   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
  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
  26 //***********************************************************************
  27 // FETCH ROWS FROM THE TABLE "T1"......select * from T1;
  28 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
  29 {
  30
  31       SQLRETURN  ret;
  32       int f2=20;//f2 field
  33
  34
  35
  36     ret = SQLPrepare(stmt,(unsigned char*)"SELECT F2  FROM T1 ",SQL_NTS);
  37     checkrc(ret,__LINE__);
  38
  39     //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,)
  40     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f2,0,NULL);
  41     checkrc(ret,__LINE__);
  42
  43     int j, count=0;
  44     ret = SQLExecute(stmt);
  45     checkrc(ret,__LINE__);
  46     while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
  47     {
  48         count++;
  49
  50     }
  51
  52     SQLSMALLINT noc;
  53     SQLNumResultCols(stmt,&noc);
  54     printf("SQLNumResultCols() returns=%d\n",noc);
  55
  56     ret = SQLCloseCursor(stmt);
  57     checkrc(ret,__LINE__);
  58
  59
  60     printf("Total row fetched=%d\n",count);
  61     if(count != 0)return 1;
  62     return 0;
  63 }
  64
  65 //******************************************************************
  66
  67
  68 int main()
  69 {
  70   SQLHENV env;
  71   SQLHDBC dbc;
  72   SQLHSTMT stmt;
  73   SQLRETURN ret;
  74   SQLCHAR outstr[1024];
  75   SQLSMALLINT outstrlen;
  76
  77   // Aloocate an environment handle
  78   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  79   checkrc(ret,__LINE__);
  80
  81    //we need odbc3 support
  82    SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  83
  84   //ALLOCATE A Connection handle
  85   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  86   checkrc(ret,__LINE__);
  87
  88   // connect to the DSN mydsn
  89    ret= SQLConnect (dbc,
  90                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  91                    (SQLCHAR *) "root",
  92                    (SQLSMALLINT) strlen ("root"),
  93                    (SQLCHAR *) "manager",
  94                    (SQLSMALLINT) strlen (""));
  95
  96   if(SQL_SUCCEEDED(ret))
  97   {
  98      printf("\nConnected to the Data Source..\n");
  99
 100   }
 101    else
 102    {
 103         printf("Error in connection\n");
 104         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 105         checkrc(ret,__LINE__);
 106
 107         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 108         checkrc(ret,__LINE__);
 109         return 1;
 110    }
 111
 112    //ret = SQLSetConnectAttr(dbc,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_UINTEGER);
 113    //checkrc(ret,__LINE__);
 114
 115    //******************************************************************
 116    // TABLE CREATED
 117    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 118    checkrc(ret,__LINE__);
 119
 120    SQLCHAR table[200]=
 121      "CREATE TABLE T1(F1 INT,F2 INT)";
 122    ret = SQLPrepare(stmt,table,SQL_NTS);
 123    checkrc(ret,__LINE__);
 124
 125    ret = SQLExecute(stmt);
 126    checkrc(ret,__LINE__);
 127    printf("\nTABLE CREATED\n");
 128    //****************************************************************
 129
 130      ret = SQLSetConnectAttr(dbc,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_UINTEGER);
 131      checkrc(ret,__LINE__);
 132
 133     ret = SQLExecDirect(stmt,(unsigned char*)"INSERT INTO T1 VALUES(1,1)",SQL_NTS);
 134     checkrc(ret,__LINE__);
 135
 136    //*****************************************************************
 137
 138    //update
 139     ret = SQLExecDirect(stmt,(unsigned char*)"UPDATE T1 SET F2=100",SQL_NTS);
 140     checkrc(ret,__LINE__);
 141
 142     ret = SQLEndTran(SQL_HANDLE_DBC,dbc,SQL_ROLLBACK);
 143     checkrc(ret,__LINE__);
 144    //***********************************************************
 145      //FETCH
 146     int ret1;
 147     ret1 = FetchTest(env,dbc,stmt);
 148
 149    //****************************************************************
 150    ret = SQLExecDirect(stmt,(unsigned char*)"DROP TABLE T1",SQL_NTS);
 151    checkrc(ret,__LINE__);
 152
 153    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 154    checkrc(ret,__LINE__);
 155
 156    ret = SQLDisconnect(dbc);
 157    checkrc(ret,__LINE__);
 158
 159    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 160    checkrc(ret,__LINE__);
 161
 162    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 163    checkrc(ret,__LINE__);
 164    if(ret1 ==1)
 165    {
 166
 167       printf("Test script failed\n");
 168       return 1;
 169    }
 170 else
 171   printf("Test script passed\n");
 172   return 0;
 173 }
 174