sqldatabase.cpp

应用VC++编的简单的ODBC访问接口。(我已经是会员了! 挖哈哈!!!)

#include "SQLRecordset.h"

#include "SQLDatabase.h"
#include <sqlext.h>

#include <string>
using namespace std;

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
CSQLDatabase::CSQLDatabase()
:
   _isOpen( false ),
   _rc( SQL_SUCCESS ),
   _henv( 0 ),
   _hdbc( 0 ),
   _autoCommit( true )
{
   // Allocate environment handle
   _rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &_henv );
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      return;
   _rc = SQLSetEnvAttr( _henv, SQL_ATTR_ODBC_VERSION,( void* )SQL_OV_ODBC3, 0 );
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      return;
   _rc = SQLAllocHandle( SQL_HANDLE_DBC, _henv, &_hdbc ); 
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      return;
   _rc = SQLSetConnectAttr( _hdbc, SQL_LOGIN_TIMEOUT,( void* )7, 0 );
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      return;
}

CSQLDatabase::~CSQLDatabase()
{
   Close();
}

bool CSQLDatabase::Open( const char* dsn, const char* user, const char* password ) 
{ 
   return this->SQLConnect( dsn, user, password ); 
}

bool CSQLDatabase::OpenEx( const char* dsn, const char* user, const char* password ) 
{ 
   return this->SQLConnect( dsn, user, password ); 
}

bool CSQLDatabase::Close()
{
   if ( _isOpen )
      return false;

   if ( _hdbc )
   {
      _rc = ::SQLDisconnect( _hdbc );
      if ( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) 
         _rc = ::SQLFreeHandle( SQL_HANDLE_DBC, _hdbc );
   }
   if ( _henv )
      _rc = ::SQLFreeHandle( SQL_HANDLE_ENV, _henv );

   _isOpen = false;
   return true;
}

bool CSQLDatabase::SQLConnect( const char * a_szDSN, 
   const char* a_szUserName, const char* a_szPassword )
{
   _rc = ::SQLConnect( _hdbc,( SQLCHAR* )a_szDSN, SQL_NTS, 
     ( SQLCHAR* )a_szUserName, SQL_NTS,( SQLCHAR* )a_szPassword, SQL_NTS );
   _isOpen = ( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO );
   return _isOpen;
}

bool CSQLDatabase::ExecuteSQL( const char* a_szStmt )
{
   _rc = ::SQLAllocHandle( SQL_HANDLE_STMT, _hdbc, &_hstmt );
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      ThrowError( SQL_HANDLE_DBC );

   // returns: SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, 
   // SQL_STILL_EXECUTING, SQL_ERROR, SQL_NO_DATA, or SQL_INVALID_HANDLE   
   _rc = ::SQLExecDirect( _hstmt,( unsigned char* )a_szStmt, SQL_NTS );
   if ( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO )
   {
      // COMMIT the transaction
      if ( _autoCommit )
      {
         _rc = ::SQLEndTran( SQL_HANDLE_DBC, _hdbc, SQL_COMMIT );
         if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
            ThrowError( SQL_HANDLE_DBC );
      }
   }
   else
   {
      if ( _rc != SQL_NO_DATA )
         ThrowError();
   }

   if ( _hstmt != NULL )
   {
      SQLRETURN rc = ::SQLFreeHandle( SQL_HANDLE_STMT, _hstmt );
   _hstmt = NULL;
   }

   bool retVal = _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO;
   return retVal;
}

int CSQLDatabase::GetSQLInt( const char * a_szStmt, int a_nDefault )
{
   try 
   {
      CSQLRecordset rs( _hdbc );
      if ( rs.SQLExecDirect( a_szStmt ) )
      {
         if ( rs.SQLFetch() ) 
         {
            _sResult = rs.SQLGetData( 1 );
            return atoi( _sResult.c_str() );
         }
      }
      return a_nDefault;
   }
   catch ( CSQLException* e )
   {
      e->Delete(); // must do if not using MFC catch () macro
      return a_nDefault;
   }
}

const char* CSQLDatabase::GetSQLString( const char * a_szStmt, const char* a_szDefault )
{
   try 
   {
      CSQLRecordset rs( _hdbc );
      if ( rs.SQLExecDirect( a_szStmt ) )
      {
         if ( rs.SQLFetch() ) 
         {
            _sResult = rs.SQLGetData( 1 );
            return _sResult.c_str();
         }
      }
      return a_szDefault;
   }
   catch ( CSQLException* e )
   {
      e->Delete(); // must do if not using MFC catch () macro
      return a_szDefault;
   }
}

// see CSQLRecordset::ThrowError(); code is same.  Make a base class?
void CSQLDatabase::ThrowError( SQLSMALLINT handleType )
{
   SQLCHAR     szSQLState[6];
   SQLINTEGER  nNativeError = 0;
   SQLCHAR     szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
   SQLSMALLINT cbErrorMsgMax = sizeof( szErrorMsg ) - 1;
   SQLSMALLINT cbErrorMsg = 0;

   SQLRETURN   rc;
   SQLHANDLE   theHandle;
   switch ( handleType ) {
   case SQL_HANDLE_ENV:  theHandle = _henv;  break;
   case SQL_HANDLE_DBC:  theHandle = _hdbc;  break;
   case SQL_HANDLE_STMT: theHandle = _hstmt; break;
   //case SQL_HANDLE_DESC: theHandle =       break;
   default:              theHandle = _hstmt; break;
   }

   rc = SQLGetDiagRec( handleType, theHandle, 1 /*recnumber?*/, szSQLState, 
      &nNativeError, szErrorMsg, cbErrorMsgMax, &cbErrorMsg );
   szSQLState[sizeof( szSQLState )-1]=0; // insurance
   szErrorMsg[cbErrorMsg] = 0;         // more insurance

	CSQLException* pException = new CSQLException();
   pException->m_nRetCode = nNativeError;
   pException->m_strError = ( const char* )szErrorMsg;
   pException->m_strStateNativeOrigin = ( const char* )szSQLState;

   if ( _hstmt != NULL )
   {
      SQLRETURN rc = ::SQLFreeHandle( SQL_HANDLE_STMT, _hstmt );
      _hstmt = NULL;
   }

	throw pException;
}

bool CSQLDatabase::SupportsTransactions()    // supports ROLLBACK and COMMIT
{
   SQLUINTEGER result;
   SQLRETURN rc = ::SQLGetInfo( _hdbc, SQL_TXN_CAPABLE,( SQLPOINTER )&result, 0, 0 );
   return result != SQL_TC_NONE;
}

void CSQLDatabase::SetAutoCommit( bool tf ) // makes all transactions COMMIT immediately
{
   _autoCommit = tf;

   if ( _autoCommit == true )
      return;

   if ( ! SupportsTransactions() )
      _autoCommit = true;
}

bool CSQLDatabase::GetAutoCommit()
{
   return _autoCommit;
}

bool CSQLDatabase::Commit()
{
   _rc = ::SQLEndTran( SQL_HANDLE_DBC, _hdbc, SQL_COMMIT );
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      ThrowError( SQL_HANDLE_DBC );
   return true;
}

bool CSQLDatabase::Rollback()
{
   _rc = ::SQLEndTran( SQL_HANDLE_DBC, _hdbc, SQL_ROLLBACK );
   if ( !( _rc == SQL_SUCCESS || _rc == SQL_SUCCESS_WITH_INFO ) )
      ThrowError( SQL_HANDLE_DBC );
   return true;
}