bool OdbcConnection::Connect()
{
if (m_szDSN.length() == 0)
return false;
tstring szConn = _T("DSN=") + m_szDSN + _T(";");
// Reconnect if we need to.
if (isConnected())
Disconnect();
// Allocate enviroment handle
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_envHandle)))
{
ReportSQLError(SQL_HANDLE_ENV, m_envHandle, _T("SQLAllocHandle"), _T("Unable to allocate environment handle."));
goto error_handler;
}
// Request ODBC3 support
if (!SQL_SUCCEEDED(SQLSetEnvAttr(m_envHandle, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0)))
{
ReportSQLError(SQL_HANDLE_ENV, m_envHandle, _T("SQLSetEnvAttr"), _T("Unable to set environment attribute (SQL_ATTR_ODBC_VERSION)."));
goto error_handler;
}
// Allocate the connection handle
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, m_envHandle, &m_connHandle)))
{
ReportSQLError(SQL_HANDLE_ENV, m_envHandle, _T("SQLAllocHandle"), _T("Unable to allocate connection handle."));
goto error_handler;
}
if (m_szUser.length())
{
szConn += _T("UID=") + m_szUser + _T(";");
if (m_szPass.length())
szConn += _T("PWD=") + m_szPass + _T(";");
}
// Enable multiple active result sets
if (m_bMarsEnabled)
szConn += _T("MARS_Connection=yes;");
if (!SQL_SUCCEEDED(SQLDriverConnect(m_connHandle, NULL, (SQLTCHAR *)szConn.c_str(), SQL_NTS, NULL, NULL, NULL, NULL)))
{
ReportSQLError(SQL_HANDLE_DBC, m_connHandle, _T("SQLDriverConnect"), _T("Unable to establish connection."));
goto error_handler;
}
for (auto itr = m_commandSet.begin(); itr != m_commandSet.end(); itr++)
(*itr)->SetConnectionHandle(m_connHandle);
return true;
error_handler:
ResetHandles();
return false;
}
bool OdbcConnection::Connect()
{
if (m_szDSN.empty())
return false;
FastGuard lock(m_lock);
tstring szConn = _T("DSN=") + m_szDSN + _T(";");
// Reconnect if we need to.
if (isConnected())
Disconnect();
// Allocate enviroment handle
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_envHandle)))
{
ReportSQLError(SQL_HANDLE_ENV, m_envHandle, _T("SQLAllocHandle"), _T("Unable to allocate environment handle."));
goto error_handler;
}
// Request ODBC3 support
if (!SQL_SUCCEEDED(SQLSetEnvAttr(m_envHandle, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0)))
{
ReportSQLError(SQL_HANDLE_ENV, m_envHandle, _T("SQLSetEnvAttr"), _T("Unable to set environment attribute (SQL_ATTR_ODBC_VERSION)."));
goto error_handler;
}
// Allocate the connection handle
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, m_envHandle, &m_connHandle)))
{
ReportSQLError(SQL_HANDLE_ENV, m_envHandle, _T("SQLAllocHandle"), _T("Unable to allocate connection handle."));
goto error_handler;
}
if (m_szUser.length())
{
szConn += _T("UID=") + m_szUser + _T(";");
if (m_szPass.length())
szConn += _T("PWD=") + m_szPass + _T(";");
}
// Enable multiple active result sets
if (m_bMarsEnabled)
{
if (!SQL_SUCCEEDED(SQLSetConnectAttr(m_connHandle, SQL_COPT_SS_MARS_ENABLED, SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER)))
{
printf("** WARNING **\n\n");
printf("Attempted to used MARS (Multiple Active Result Sets), but this\n");
printf("feature is not supported by your ODBC driver or SQL Server version.\n\n");
printf("To benefit from MARS, you need to be using at least SQL Server 2005, and at\n");
printf("least the 'SQL Native Client' ODBC driver (as opposed to the vastly outdated\n'SQL Server' driver).\n\n");
printf("Continuing to connect without MARS.\n\n");
m_bMarsEnabled = false;
}
// NOTE: We can enable MARS via specifying the following, but we are unable to detect if it's supported this way.
// szConn += _T("MARS_Connection=yes;");
}
if (!SQL_SUCCEEDED(SQLDriverConnect(m_connHandle, SQL_NULL_HANDLE, (SQLTCHAR *)szConn.c_str(), SQL_NTS, 0, 0, 0, 0)))
{
ReportSQLError(SQL_HANDLE_DBC, m_connHandle, _T("SQLDriverConnect"), _T("Unable to establish connection."));
goto error_handler;
}
for (auto itr = m_commandSet.begin(); itr != m_commandSet.end(); itr++)
(*itr)->SetConnectionHandle(m_connHandle);
return true;
error_handler:
ResetHandles();
return false;
}
/***************************************************
CUGODBCDatasource::Browse : call this function to invoke the
ODBC driver to prompt for connection params.
This function gathers information about all of the
tables in the datasource selected, storing the information
in the array m_dsInfoArray for later retrieval.
This function also saves the resulting connect string for
later use.
****************************************************/
int CUGODBCDatasource::Browse(HWND hwnd){
HENV henv;
HDBC hdbc;
HSTMT hstmt;
RETCODE retcode;
SQLTCHAR buf[255];
SQLTCHAR sqlerror[255];
SQLTCHAR sqlstate[255];
SDWORD nerr, cbVal;
SWORD num;
// for table info....
SQLTCHAR szTableQualifier[128];
SQLTCHAR szTableOwner[128];
SQLTCHAR szTableName[128];
SQLTCHAR szTableType[128];
SQLTCHAR szRemarks[255];
// close open db
Close();
// clean up previous data
m_connectString = "";
m_tableCount = 0;
retcode = SQLAllocEnv(&henv); // Environment handle
if (retcode == SQL_ERROR) {
AfxMessageBox(_T("Probable memory allocation error in SQLAllocEnv"));
return (int) retcode;
}
retcode = SQLAllocConnect(henv,&hdbc);
if (retcode == SQL_ERROR) {
ReportSQLError(henv, SQL_NULL_HDBC, SQL_NULL_HSTMT, retcode, _T("SQLAllocConnect"));
SQLFreeEnv(henv);
return (int) retcode;
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
// Set login timeout to 5 seconds.
SQLSetConnectOption(hdbc, SQL_LOGIN_TIMEOUT, 5);
// Connect to data source
// this should cause the ODBC Driver to come up with the
// necessary prompts to form a connect string...
retcode = SQLDriverConnect(hdbc,hwnd,(SQLTCHAR*)_T(""),
0,buf,254,&num,SQL_DRIVER_PROMPT);
if (retcode != SQL_SUCCESS) {
#ifdef UG_REPORT_ERROR
ReportSQLError(henv, hdbc, SQL_NULL_HSTMT, retcode, _T("SQLDriverConnect"));
#endif
// user probably cancelled connection...
if (retcode != SQL_SUCCESS_WITH_INFO) {
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
return retcode;
}
}
if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
// retcode from SQLDriverConnect...
// should be cool to try to open a recordset with the info
// we have here in buf...
CDatabase db;
TCHAR buf2[255];
UGStr::stprintf(buf2,255,_T("ODBC;%s"),buf);
// now we have the connect string in buf2 - but wait to
// see if we can actually open the db before storing
// it in m_connectString...
try {
retcode = db.Open(NULL,FALSE,FALSE,buf2);
}
catch(CMemoryException* e){
#ifdef UG_ODBC_REPORTERROR
e->ReportError();
#endif
e->Delete();
AfxAbort();
}
catch(CDBException* e){
// standard procedure for ODBC - save the
// RETCODE code for the return value -
// Report on the error - optional -
// use Delete to delete the exception object
RETCODE er = e->m_nRetCode;
#ifdef UG_ODBC_REPORTERROR
e->ReportError();
#endif
e->Delete();
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
return (int)er;
}
// now, given an open connection, we should
// be able to set up a statement handle..
retcode = SQLAllocStmt(hdbc, &hstmt);
switch (retcode) {
case SQL_SUCCESS: {
// AfxMessageBox("SQLAllocStmt returned success ");
// AfxMessageBox((LPCSTR)buf);
break;
}
case SQL_SUCCESS_WITH_INFO:{
AfxMessageBox(_T("SQLAllocStmt returned success with info"));
// this indicates that there is a driver specific warning
// to be investigated...
SQLError(SQL_NULL_HENV,hdbc,SQL_NULL_HSTMT,sqlstate,
&nerr,sqlerror,254,&num);
AfxMessageBox((LPCTSTR)sqlstate);
AfxMessageBox((LPCTSTR)sqlerror);
break;
}
case SQL_NO_DATA_FOUND:{
AfxMessageBox(_T("SQLAllocStmt returned no data found"));
break;
}
case SQL_ERROR:{
AfxMessageBox(_T("SQLAllocStmt returned sql error"));
SQLError(SQL_NULL_HENV,hdbc,SQL_NULL_HSTMT,sqlstate,&nerr,sqlerror,254,&num);
AfxMessageBox((LPCTSTR)sqlstate);
AfxMessageBox((LPCTSTR)sqlerror);
break;
}
case SQL_INVALID_HANDLE:{
AfxMessageBox(_T("SQLDriverConnect returned invalid handle"));
break;
}
default:
break;
}
// ok - now lets see if we can enumerate the tables
// contained in the database...
retcode = SQLTables(hstmt, // statement handle for
// retrieved results
NULL, // szTableQualifier
SQL_NTS, // cbTableQualifier
NULL, // szTableOwner
SQL_NTS, // cbTableOwner
NULL, // szTableName
SQL_NTS, // cbTableName
(SQLTCHAR*)_T("'TABLE','VIEW',\
'SYSTEM TABLE','GLOBAL TEMPORARY','LOCAL TEMPORARY',\
'ALIAS','SYNONYM'"), // szTableType (list of
// table types to match)
SQL_NTS); // cbTableTypes
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
ReportSQLError(henv, hdbc, hstmt, retcode, _T("SQLTables"));
SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
return retcode; // unable to query tables
// User should see message box
}
// now - can we iterate through the tables found?
#ifndef _UNICODE
SQLBindCol(hstmt, 1, SQL_C_CHAR, szTableQualifier, 128, &cbVal);
SQLBindCol(hstmt, 2, SQL_C_CHAR, szTableOwner, 128, &cbVal);
SQLBindCol(hstmt, 3, SQL_C_CHAR, szTableName, 128, &cbVal);
SQLBindCol(hstmt, 4, SQL_C_CHAR, szTableType, 128, &cbVal);
SQLBindCol(hstmt, 5, SQL_C_CHAR, szRemarks, 255, &cbVal);
#else
SQLBindCol(hstmt, 1, SQL_UNICODE_CHAR, szTableQualifier, 128, &cbVal);
SQLBindCol(hstmt, 2, SQL_UNICODE_CHAR, szTableOwner, 128, &cbVal);
SQLBindCol(hstmt, 3, SQL_UNICODE_CHAR, szTableName, 128, &cbVal);
SQLBindCol(hstmt, 4, SQL_UNICODE_CHAR, szTableType, 128, &cbVal);
SQLBindCol(hstmt, 5, SQL_UNICODE_CHAR, szRemarks, 255, &cbVal);
#endif
retcode = SQLFetch(hstmt);
if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO)
ReportSQLError(henv, hdbc, hstmt, retcode, _T("SQLFetch"));
if (retcode != SQL_NO_DATA_FOUND)
m_connectString = (LPTSTR)buf2; // save connection string!
// Subsequent calls to GetTableInfo will be able to
// retrieve this information...
while (retcode != SQL_NO_DATA_FOUND) {
UGStr::tcscpy((LPTSTR)m_dsInfoArray[m_tableCount].tableQualifier,128,(LPTSTR)szTableQualifier);
UGStr::tcscpy((LPTSTR)m_dsInfoArray[m_tableCount].tableOwner,128, (LPTSTR)szTableOwner);
UGStr::tcscpy((LPTSTR)m_dsInfoArray[m_tableCount].tableName,128,(LPTSTR)szTableName);
UGStr::tcscpy((LPTSTR)m_dsInfoArray[m_tableCount].tableType,128,(LPTSTR)szTableType);
UGStr::tcscpy((LPTSTR)m_dsInfoArray[m_tableCount].remarks,255,(LPTSTR)szRemarks);
m_tableCount++;
if (m_tableCount >= MAX_TABLES)
break;
retcode = SQLFetch(hstmt);
}
}
