test/odbc/Transaction/transact2nw.c

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