test/odbc/Transaction/transact1nw.c

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