easy_odbc.cpp

VC++中

// easy_odbc.cpp
// declares C++ wrappers for win32 ODBC function calls
// Author: Vijay Mathew Pandyalakal
// 27-MAY-2003

#include "easy_odbc.h"
using namespace easyodbc;

int Database::db_dynamic = SQL_CURSOR_DYNAMIC;
int Database::db_keysetdriven = SQL_CURSOR_KEYSET_DRIVEN;
int Database::db_static = SQL_CURSOR_STATIC;
int Database::db_forwardonly = SQL_CURSOR_FORWARD_ONLY;

// @class Database implementations

// $bool Open(const char* dsn,const char* user = NULL,const char* pw = NULL)
// opens a database connection
// throws EasyODBCException
bool Database::Open(const char* dsn,char* user,char* pw) {
	SQLRETURN ret = 0;
	try {
		if(user == NULL) strcpy(user,"");
		if(pw == NULL) strcpy(pw,"");
		ret = SQLAllocEnv(&this->m_hEnv);
		if(ret == SQL_ERROR) {
			HandleError("ENV");
		}else if(ret == SQL_INVALID_HANDLE || ret < 0) {
			HandleError("ENV");
		}
	}catch(...) { HandleError("ENV"); }

	try {
		ret = SQLAllocConnect(this->m_hEnv, &this->m_hDbc);// allocates memory for ODBC
		if(ret == SQL_ERROR) {
			HandleError("ENV");
		}else if(ret == SQL_INVALID_HANDLE || ret < 0) {
			HandleError("ENV");
		}
	}catch(...) { HandleError("ENV"); }

		/*SQLSetConnectAttr(this->m_hDbc, SQL_LOGIN_TIMEOUT, 
			(void*)&this->m_nConnTimeOut, 0);// sets timeout*/

	try {
		ret = SQLConnect(this->m_hDbc,(SQLCHAR*)dsn,strlen(dsn),
			(SQLCHAR*)user,strlen(user),(SQLCHAR*)pw,strlen(pw));// connects to the ODBC driver
		if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE || ret < 0) {
			HandleError("DBC");
		}

		ret = SQLAllocStmt(this->m_hDbc, &this->m_hStmt);
		if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE || ret < 0) {
			HandleError("DBC");
		}
		this->m_bOpened = true;
		return true;
	}catch(...) { HandleError("DBC"); }
	return false;
}

// $long Execute(const char* sql) 
// execute any INSERT,DELETE,UPDATE statements
long Database::Execute(const char* sql) {
	try {
		if(!this->m_bOpened) {
			throw EasyODBCException("Call Open() before Execute()",-1);
		}
		SQLRETURN ret = SQLPrepare(this->m_hStmt,(unsigned char*)sql,
			SQL_NTS);//strlen(sql));
		if(ret == SQL_ERROR) {
			HandleError("STMT");
		}
		ret = SQLExecute(this->m_hStmt);//,(SQLCHAR*)sql,strlen(sql));

		if(ret == SQL_ERROR || ret < 0) {
			HandleError("STMT");
		}

		long count = -1;
		ret = SQLRowCount(this->m_hStmt,&count);
		return count;
	}catch(...) {
		HandleError("STMT");
	}
	return -2;
}

// $ResultSet ExecuteQuery(const char* sql) 
// execute SELECT statements
ResultSet Database::ExecuteQuery(const char* sql) {	
	try {
		if(!this->m_bOpened) {
			throw EasyODBCException("Connection not opened",-1);
		}				
		SQLFreeStmt(this->m_hStmt,SQL_CLOSE);
		SQLRETURN ret = SQLAllocStmt(this->m_hDbc, &this->m_hStmt);
		if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE || ret < 0) {
			HandleError("DBC");
		}
		ret = SQLSetStmtAttr(this->m_hStmt, SQL_ATTR_CURSOR_TYPE,
			(SQLPOINTER)SQL_CURSOR_DYNAMIC,SQL_IS_INTEGER);
		if(ret < 0) {
			HandleError("STMT");
		}
		/*ret = SQLSetStmtAttr(this->m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
                        (SQLPOINTER)SQL_SCROLLABLE,
                        SQL_IS_INTEGER);
		if(ret < 0) {
			HandleError("STMT");
		}*/
		ret = SQLExecDirect(this->m_hStmt,(SQLTCHAR*)sql,strlen(sql));
		if(ret == SQL_ERROR) {
			HandleError("STMT");
		}
	}catch(...) { HandleError("STMT"); }
	ResultSet rslt(&this->m_hStmt);
	return rslt;
}

// $ResultSet ExecuteQuery(const char* sql) 
// execute SELECT statements
ResultSet Database::ExecuteQuery(const char* sql,int cursor_type,bool scrollable) {	
	try {
		if(!this->m_bOpened) {
			throw EasyODBCException("Connection not opened",-1);
		}					
		if(cursor_type != Database::db_dynamic &&
			cursor_type != Database::db_forwardonly &&
			cursor_type != Database::db_keysetdriven &&
			cursor_type != Database::db_static) {
			throw EasyODBCException("Invalid cursor type",-1);
		}
		SQLFreeStmt(this->m_hStmt,SQL_CLOSE);
		SQLRETURN ret = SQLAllocStmt(this->m_hDbc, &this->m_hStmt);
		if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE || ret < 0) {
			HandleError("DBC");
		}
		ret = SQLSetStmtAttr(this->m_hStmt, SQL_ATTR_CURSOR_TYPE,
			(SQLPOINTER)cursor_type,SQL_IS_INTEGER);
		if(ret < 0) {
			HandleError("STMT");
		}
		if(scrollable) {
			ret = SQLSetStmtAttr(this->m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
                        (SQLPOINTER)SQL_SCROLLABLE,
                        SQL_IS_INTEGER);
			if(ret < 0) {
				HandleError("STMT");
			}
		}
		ret = SQLExecDirect(this->m_hStmt,(SQLTCHAR*)sql,strlen(sql));
		if(ret == SQL_ERROR) {
			HandleError("STMT");
		}
	}catch(...) { HandleError("STMT"); }
	ResultSet rslt(&this->m_hStmt);
	return rslt;
}


bool Database::Commit() {
	SQLRETURN ret = SQLEndTran(SQL_HANDLE_DBC,this->m_hDbc,SQL_COMMIT);
	if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE) {
		throw EasyODBCException("Commit failed",ret);
	}
	return true;
}

bool Database::Rollback() {
	SQLRETURN ret = SQLEndTran(SQL_HANDLE_DBC,this->m_hDbc,SQL_ROLLBACK);
	if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE) {
		throw EasyODBCException("Rollback failed",ret);
	}
	return true;
}

void Database::HandleError(const char* type) {
	try {
		if(strcmp(type,"STMT") == 0) {
			SQLGetDiagRec(SQL_HANDLE_STMT,this->m_hStmt,this->m_iRec,
			this->m_cState,&this->m_nErr,this->m_cMsg,
			sizeof(m_cMsg),&this->m_nMsg);		
		}else if(strcmp(type,"ENV") == 0) {
			SQLGetDiagRec(SQL_HANDLE_ENV,this->m_hEnv,this->m_iRec,
			this->m_cState,&this->m_nErr,this->m_cMsg,
			sizeof(m_cMsg),&this->m_nMsg);		
		}else if(strcmp(type,"DBC") == 0) {
			SQLGetDiagRec(SQL_HANDLE_DBC,this->m_hDbc,this->m_iRec,
			this->m_cState,&this->m_nErr,this->m_cMsg,
			sizeof(m_cMsg),&this->m_nMsg);		
		}
	}catch(...) {
		throw EasyODBCException("Database error",0);
	}

	throw EasyODBCException((char*)this->m_cMsg,this->m_nErr);
}

// @class ResultSet implementations

// $ResultSetMetaData GetMetaData()
// returns data about the selected data
ResultSetMetaData ResultSet::GetMetaData() {
	ResultSetMetaData mtdt;
	try {
		SQLSMALLINT column_count = 0;
		SQLRETURN ret = SQLNumResultCols(*this->m_hStmt,&column_count);
		if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE) {
			HandleError();
		}			
		mtdt.SetNumCols(column_count);
		for(int i=1;i<=column_count;i++) {
			Column col;
			int name_length = 0;
			strcpy(col.title,"");
			col.decim_size = 0;
			col.nullable = 0;
			col.size = 0;
			col.type = 0;
			ret =  SQLDescribeCol(*this->m_hStmt,(SQLSMALLINT)i,
						(SQLCHAR*)col.title,
						(SQLSMALLINT)sizeof(col.title),
						(SQLSMALLINT*)&name_length,
						(SQLSMALLINT*)&col.type,
						(SQLUINTEGER*)&col.size,
						(SQLSMALLINT*)&col.decim_size,
						(SQLSMALLINT*)&col.nullable);			
			if(ret == SQL_ERROR || ret == SQL_INVALID_HANDLE) {
				HandleError();
			}		
			mtdt.AddColumn(col);
		}		
	}catch(...) { HandleError(); }
	return mtdt;
}

bool ResultSet::MoveNext() {
	try {
		SQLRETURN rc = SQLFetchScroll(*this->m_hStmt,SQL_FETCH_NEXT,0);
		if(rc == SQL_NO_DATA) return false;
		if(rc == SQL_ERROR) {
			HandleError(); 
		}
	}catch(...) { HandleError(); }
	return true;
}

bool ResultSet::MovePrevious() {
	try {
		SQLRETURN rc = SQLFetchScroll(*this->m_hStmt,SQL_FETCH_PRIOR,0);
		if(rc == SQL_NO_DATA || rc < 0) return false;
		if(rc == SQL_ERROR) {
			HandleError();
		}
	}catch(...) { HandleError(); }
	return true;
}

bool ResultSet::MoveFirst() {
	try {		
		SQLRETURN rc = SQLFetchScroll(*this->m_hStmt,SQL_FETCH_FIRST,0);
		if(rc == SQL_NO_DATA) return false;
		if(rc < 0) {
			return false;
		}
		if(rc == SQL_ERROR) {
			HandleError();
			/*this->MoveNext();
			rc = SQLFetchScroll(this->m_hStmt,SQL_FETCH_FIRST,0);
			if(rc == SQL_NO_DATA) return false;*/
		}
	}catch(...) { HandleError(); }
	return true;
}

bool ResultSet::MoveLast() {
	try {
		SQLRETURN rc = SQLFetchScroll(*this->m_hStmt,SQL_FETCH_LAST,0);
		if(rc == SQL_NO_DATA || rc < 0) return false;
		if(rc == SQL_ERROR) {
			HandleError();
		}
	}catch(...) { HandleError(); }
	return true;
}

bool ResultSet::Bind(int col_no,char* buff,int len) {
	try {
		SQLINTEGER sz = 0;			
		SQLRETURN rc = SQLBindCol(*this->m_hStmt, col_no, SQL_C_CHAR,
			buff, len, &sz);
		if(rc == SQL_ERROR) {
			HandleError();
		}
	}catch(...) { HandleError(); }
	return true;
}

void ResultSet::HandleError() {
	SQLGetDiagRec(SQL_HANDLE_STMT,*this->m_hStmt,this->m_iRec,
	this->m_cState,&this->m_nErr,this->m_cMsg,
	sizeof(m_cMsg),&this->m_nMsg);
	throw EasyODBCException((char*)this->m_cMsg,this->m_nErr);
}