test/odbc/Transaction/transact1.c

   1 // create table T1 with two integer field. perform insert,update,select.
   2
   3 // AUTHOR : Jitendra Lenka
   4
   5 #include<stdio.h>
   6 #include<stdlib.h>
   7 #include<sql.h>
   8 #include<sqlext.h>
   9 #include<string.h>
  10
  11 //*************************************************************************
  12
  13 inline void checkrc(int rc,int line)
  14 {
  15    if(rc)
  16    {
  17        printf("ERROR %d at line %d\n",rc,line);
  18        exit(1);
  19    }
  20 }
  21
  22
  23 // FETCH ROWS FROM THE TABLE "T1"......select * from T1;
  24 int FetchTest(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt)
  25 {
  26
  27       SQLRETURN  ret;
  28       int f2=20;//f2 field
  29
  30
  31
  32     ret = SQLPrepare(stmt,(unsigned char*)"SELECT F2  FROM T1 ",SQL_NTS);
  33     checkrc(ret,__LINE__);
  34
  35     //ret = SQLBindParameter(stmt,1,SQL_PARAM_INPUT,SQL_C_SMALL,SQL_SMALL,0,0,)
  36     ret = SQLBindCol(stmt,1,SQL_C_SLONG,&f2,0,NULL);
  37     checkrc(ret,__LINE__);
  38
  39     int j, count=0;
  40     ret = SQLExecute(stmt);
  41     checkrc(ret,__LINE__);
  42     while(SQL_SUCCEEDED(ret = SQLFetch(stmt)))
  43     {
  44         printf("F2=%d\n",f2);
  45         count++;
  46
  47     }
  48
  49     /*SQLSMALLINT noc;
  50     SQLNumResultCols(stmt,&noc);
  51     printf("SQLNumResultCols() returns=%d\n",noc);*/
  52
  53     ret = SQLCloseCursor(stmt);
  54     checkrc(ret,__LINE__);
  55
  56     ret = SQLTransact(env,dbc,SQL_COMMIT);
  57     checkrc(ret,__LINE__);
  58
  59     printf("Total row fetched=%d\n",count);
  60 }
  61
  62
  63
  64 int main()
  65 {
  66   SQLHENV env;
  67   SQLHDBC dbc;
  68   SQLHSTMT stmt;
  69   SQLRETURN ret;
  70   SQLCHAR outstr[1024];
  71   SQLSMALLINT outstrlen;
  72
  73   // Aloocate an environment handle
  74   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  75   checkrc(ret,__LINE__);
  76
  77    //we need odbc3 support
  78    ret =SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  79    checkrc(ret,__LINE__);
  80   //ALLOCATE A Connection handle
  81   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  82   checkrc(ret,__LINE__);
  83
  84   // connect to the DSN mydsn
  85    ret = SQLConnect (dbc,
  86                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  87                    (SQLCHAR *) "root",
  88                    (SQLSMALLINT) strlen ("root"),
  89                    (SQLCHAR *) "manager",
  90                    (SQLSMALLINT) strlen (""));
  91
  92   if(SQL_SUCCEEDED(ret))
  93   {
  94      printf("\nConnected to the Data Source..\n");
  95
  96
  97   }
  98    else
  99    {
 100         printf("error in connection\n");
 101         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 102         checkrc(ret,__LINE__);
 103
 104         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 105         checkrc(ret,__LINE__);
 106         return 1;
 107    }
 108
 109    ret = SQLSetConnectAttr(dbc,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_UINTEGER);
 110    checkrc(ret,__LINE__);
 111    //******************************************************************
 112    // TABLE CREATED
 113    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 114    checkrc(ret,__LINE__);
 115
 116    SQLCHAR table[200]=
 117      "CREATE TABLE T1(F1 INT,F2 INT)";
 118    ret = SQLPrepare(stmt,table,SQL_NTS);
 119    checkrc(ret,__LINE__);
 120    ret = SQLExecute(stmt);
 121    checkrc(ret,__LINE__);
 122
 123    printf("\nTABLE CREATED\n");
 124    //****************************************************************
 125     ret = SQLExecDirect(stmt,(unsigned char*)"INSERT INTO T1 VALUES(1,1)",SQL_NTS);
 126    //InsertTest(env,dbc,stmt);
 127    //*****************************************************************
 128
 129    //update
 130     ret = SQLExecDirect(stmt,(unsigned char*)"UPDATE T1 SET F2=100",SQL_NTS);
 131     checkrc(ret,__LINE__);
 132     ret = SQLEndTran(SQL_HANDLE_DBC,dbc,SQL_COMMIT);
 133     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 134     printf("error in commit\n");
 135     checkrc(ret,__LINE__);
 136    //***********************************************************
 137      //FETCH
 138     FetchTest(env,dbc,stmt);
 139
 140    //****************************************************************
 141    ret = SQLExecDirect(stmt,(unsigned char*)"DROP TABLE T1",SQL_NTS);
 142    checkrc(ret,__LINE__);
 143    printf("Table T1 dropped\n");
 144
 145    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 146    checkrc(ret,__LINE__);
 147
 148    ret = SQLDisconnect(dbc);
 149    checkrc(ret,__LINE__);
 150
 151    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 152    checkrc(ret,__LINE__);
 153
 154    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 155    checkrc(ret,__LINE__);
 156    return 0;
 157 }