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_LONG,&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     return 0;
  61 }
  62
  63
  64
  65 int main()
  66 {
  67   SQLHENV env;
  68   SQLHDBC dbc;
  69   SQLHSTMT stmt;
  70   SQLRETURN ret;
  71   SQLCHAR outstr[1024];
  72   SQLSMALLINT outstrlen;
  73
  74   // Aloocate an environment handle
  75   ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&env);
  76   checkrc(ret,__LINE__);
  77
  78    //we need odbc3 support
  79    ret =SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
  80    checkrc(ret,__LINE__);
  81   //ALLOCATE A Connection handle
  82   ret = SQLAllocHandle(SQL_HANDLE_DBC,env,&dbc);
  83   checkrc(ret,__LINE__);
  84
  85   // connect to the DSN mydsn
  86    ret = SQLConnect (dbc,
  87                    (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
  88                    (SQLCHAR *) "root",
  89                    (SQLSMALLINT) strlen ("root"),
  90                    (SQLCHAR *) "manager",
  91                    (SQLSMALLINT) strlen (""));
  92
  93   if(SQL_SUCCEEDED(ret))
  94   {
  95      printf("\nConnected to the Data Source..\n");
  96
  97
  98   }
  99    else
 100    {
 101         printf("error in connection\n");
 102         ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 103         checkrc(ret,__LINE__);
 104
 105         ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 106         checkrc(ret,__LINE__);
 107         return 1;
 108    }
 109
 110    ret = SQLSetConnectAttr(dbc,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_UINTEGER);
 111    checkrc(ret,__LINE__);
 112    //******************************************************************
 113    // TABLE CREATED
 114    ret = SQLAllocHandle(SQL_HANDLE_STMT,dbc,&stmt);
 115    checkrc(ret,__LINE__);
 116
 117    SQLCHAR table[200]=
 118      "CREATE TABLE T1(F1 INT,F2 INT);";
 119    ret = SQLPrepare(stmt,table,SQL_NTS);
 120    checkrc(ret,__LINE__);
 121    ret = SQLExecute(stmt);
 122    checkrc(ret,__LINE__);
 123    SQLFreeStmt(stmt, SQL_CLOSE);
 124
 125    printf("\nTABLE CREATED\n");
 126    //****************************************************************
 127     ret = SQLPrepare(stmt,(unsigned char*)"INSERT INTO T1 VALUES(1,1);",SQL_NTS);
 128     checkrc(ret,__LINE__);
 129     ret = SQLExecute(stmt);
 130     checkrc(ret,__LINE__);
 131
 132     SQLFreeStmt(stmt, SQL_CLOSE);
 133     ret = SQLEndTran(SQL_HANDLE_DBC,dbc,SQL_COMMIT);
 134     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 135     printf("error in commit\n");
 136    //InsertTest(env,dbc,stmt);
 137    //*****************************************************************
 138
 139     //update
 140     ret = SQLPrepare(stmt,(unsigned char*)"UPDATE T1 SET F2=100;",SQL_NTS);
 141     checkrc(ret,__LINE__);
 142     ret = SQLExecute(stmt);
 143     checkrc(ret,__LINE__);
 144     SQLFreeStmt(stmt, SQL_CLOSE);
 145     ret = SQLEndTran(SQL_HANDLE_DBC,dbc,SQL_COMMIT);
 146     if(ret!=SQL_SUCCESS && ret !=SQL_SUCCESS_WITH_INFO)
 147     printf("error in commit\n");
 148     checkrc(ret,__LINE__);
 149    //***********************************************************
 150      //FETCH
 151     FetchTest(env,dbc,stmt);
 152
 153    //****************************************************************
 154    ret = SQLExecDirect(stmt,(unsigned char*)"DROP TABLE T1",SQL_NTS);
 155    checkrc(ret,__LINE__);
 156    SQLFreeStmt(stmt, SQL_CLOSE);
 157    printf("Table T1 dropped\n");
 158
 159    ret = SQLFreeHandle(SQL_HANDLE_STMT,stmt);
 160    checkrc(ret,__LINE__);
 161
 162    ret = SQLDisconnect(dbc);
 163    checkrc(ret,__LINE__);
 164
 165    ret = SQLFreeHandle(SQL_HANDLE_DBC,dbc);
 166    checkrc(ret,__LINE__);
 167
 168    ret = SQLFreeHandle(SQL_HANDLE_ENV,env);
 169    checkrc(ret,__LINE__);
 170    return 0;
 171 }