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_LONG,&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     SQLFreeStmt(stmt, SQL_CLOSE);
  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    SQLFreeStmt(stmt, SQL_CLOSE);
 128    printf("\nTABLE CREATED\n");
 129    //****************************************************************
 130
 131      ret = SQLSetConnectAttr(dbc,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_UINTEGER);
 132      checkrc(ret,__LINE__);
 133
 134     ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(1,1)",SQL_NTS);
 135     checkrc(ret,__LINE__);
 136     ret = SQLExecute(stmt);
 137     checkrc(ret,__LINE__);
 138     SQLFreeStmt(stmt, SQL_CLOSE);
 139
 140    //*****************************************************************
 141
 142    //update
 143    ret = SQLPrepare(stmt,(unsigned char*)"UPDATE T1 SET F2=100",SQL_NTS);
 144    checkrc(ret,__LINE__);
 145    ret = SQLExecute(stmt);
 146    checkrc(ret,__LINE__);
 147    SQLFreeStmt(stmt, SQL_CLOSE);
 148
 149
 150     ret = SQLEndTran(SQL_HANDLE_DBC,dbc,SQL_ROLLBACK);
 151     checkrc(ret,__LINE__);
 152    //***********************************************************
 153      //FETCH
 154     int ret1;
 155     ret1 = FetchTest(env,dbc,stmt);
 156
 157    //****************************************************************
 158    ret = SQLPrepare(stmt,(unsigned char*)"DROP TABLE T1",SQL_NTS);
 159    checkrc(ret,__LINE__);
 160    ret = SQLExecute(stmt);
 161    checkrc(ret,__LINE__);
 162    SQLFreeStmt(stmt, SQL_CLOSE);
 163
 164    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 165    checkrc(ret,__LINE__);
 166
 167    ret = SQLDisconnect(dbc);
 168    checkrc(ret,__LINE__);
 169
 170    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 171    checkrc(ret,__LINE__);
 172
 173    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 174    checkrc(ret,__LINE__);
 175    if(ret1 ==1)
 176    {
 177
 178       printf("Test script failed\n");
 179       return 1;
 180    }
 181 else
 182   printf("Test script passed\n");
 183   return 0;
 184 }
 185