[C++] Basic MSSQL Connection and queries

[PenguinGuy]

Forward note :
1) RDBField is just a class that has a DWORD m_dwType for the variable type and a boolean for m_bIsNull
Also has a anonymous union declared in it. That union is for all the data types.
2) _SQL_CHECK_RETURN and alike are custom prep. definitions, not apart of the standard MSSQL headers, provided in the Microsoft Platform SDK.
3) Error and bug prone, and can't be fully used. Serves as a basis and an idea so others will stop running to closed-source, money-hungry "libraries".
This was written while I was still going through MSDN.. I think I'll start reading it on IBM's website, MSDN is just a bit too funky for me.

Connection part;

bool RDatabase::Connect( const TCHAR* pszDSNName, const TCHAR* pszUsername, const TCHAR* pszPassword )
{
    //SQLRETURN is a typedef of SQLSMALLINT, which is a typedef of 'short'.
    //Used as a method return value for SQL related functions.
    SQLRETURN hRet = SQL_SUCCESS;


    //Allocate the SQL environment handle.
    hRet = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_hEnvironmentHandle );
    _SQL_CHECK_RETURN( hRet, SQL_NULL_HANDLE, m_hEnvironmentHandle );


    //Set the SQL environment attributes.
    //We are setting the ODBC version to 3.0.
    hRet = SQLSetEnvAttr( m_hEnvironmentHandle, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, NULL );
    _SQL_CHECK_RETURN( hRet );


    //Allocate the SQL connection handle.
    hRet = SQLAllocHandle( SQL_HANDLE_DBC, m_hEnvironmentHandle, &m_hConnectionHandle );
    _SQL_CHECK_RETURN( hRet, SQL_HANDLE_DBC, m_hConnectionHandle );


    //Attempt to connect to the database.
    //Important parameters : 
    //m_hConnectionHandle - The (just) allocated connection handle.
    //pszDSNName - The name you used when configuring ODBCAD32
    //pszUsername - The login username for your database
    //pszPassword - The login password for your database
    //SQL_NTS = (SQL)_NULL-TERMINATING-STRING (N.T.S... Get it?)
#ifdef UNICODE
    hRet = SQLConnect( m_hConnectionHandle, (SQLWCHAR*)pszDSNName, SQL_NTS,
        (SQLWCHAR*)pszUsername, SQL_NTS, (SQLWCHAR*)pszPassword, SQL_NTS );
#else
    hRet = SQLConnect( m_hConnectionHandle, (SQLCHAR*)pszDSNName, SQL_NTS,
        (SQLCHAR*)pszUsername, SQL_NTS, (SQLCHAR*)pszPassword, SQL_NTS );
#endif
    if( hRet != SQL_SUCCESS && hRet != SQL_SUCCESS_WITH_INFO )
    {
        OutputDebugString( L"Error connecting to database. Check credentials.\n" );
        _ASSERT( false );
        return false;
    }


    return true;
}

void RDatabase::Disconnect()
{
    //Free the statement handle.
    SQLFreeHandle( SQL_HANDLE_STMT, m_hStatementHandle );


    //Disconnect from the database.
    SQLDisconnect( m_hConnectionHandle );


    //Free the other handles we allocated.
    //Remember, C & C++ requires to free what you use.
    SQLFreeHandle( SQL_HANDLE_DBC, m_hConnectionHandle );
    SQLFreeHandle( SQL_HANDLE_ENV, m_hEnvironmentHandle );
}

And... Simplistic queries.

bool RDatabase::Execute( const TCHAR* pszQuery )
{
    _SQL_CHECK_HANDLE( SQL_HANDLE_STMT, m_hStatementHandle );
    _SQL_CHECK_HANDLE( SQL_HANDLE_DBC, m_hConnectionHandle );
    if( m_hStatementHandle != NULL )
    {
        SQLFreeHandle( SQL_HANDLE_STMT, m_hStatementHandle );
        m_hStatementHandle = SQL_SUCCESS;
    }


    //Create the statement handle.
    //We use this handle for all the queries.
    SQLRETURN hRet = SQLAllocHandle( SQL_HANDLE_STMT, m_hConnectionHandle, &m_hStatementHandle );
    _SQL_CHECK_RETURN( hRet, SQL_HANDLE_STMT, m_hStatementHandle );
    
    //Execute the query directly.
#ifdef UNICODE
    hRet = SQLExecDirect( m_hStatementHandle, (SQLWCHAR*)pszQuery, SQL_NTS );
#else
    hRet = SQLExecDirect( m_hStatementHandle, (SQLCHAR*)pszQuery, SQL_NTS );
#endif
    if( hRet == SQL_SUCCESS || hRet == SQL_SUCCESS_WITH_INFO )
    {
        if( m_pFields != NULL )
        {
            delete[] m_pFields;
            m_pFields = NULL;
        }
        
        //Retreive the number of columns from the query.
        hRet = SQLNumResultCols( m_hStatementHandle, (SQLSMALLINT*)&m_nFieldsCount );


        m_pFields = new RDBField[m_nFieldsCount];


        for( int i = 0; i < m_nFieldsCount; i++ )
        {
            SQLSMALLINT nColumnNameLength = 0;
            SQLSMALLINT nColumnType = 0;
            SQLUINTEGER nColumnSize = 0;
            SQLSMALLINT nNumOfDecimals = 0;
            SQLSMALLINT nAllowsNulls = 0;


            //Ask about the current column type.
            //(NOTE - 0 for the ColumnNumber is meant for booking ONLY (and something else....)
            //    So increment it by 1.
            //SQLDescribeCol tells us the type, column name, length, size and value.
#ifdef UNICODE
            if( SQLDescribeCol( m_hStatementHandle, (SQLUSMALLINT)i, (SQLWCHAR*)m_szColumnName,
                (SQLSMALLINT)32, &nColumnNameLength, &nColumnType, &nColumnSize, &nNumOfDecimals,
                &nAllowsNulls ) == SQL_SUCCESS )
#else
            hRet = SQLDescribeCol( m_hStatementHandle, (SQLUSMALLINT)i + 1, (SQLCHAR*)m_szColumnName,
                (SQLSMALLINT)32, &nColumnNameLength, &nColumnType, &nColumnSize, &nNumOfDecimals,
                &nAllowsNulls );


            if( hRet == SQL_SUCCESS || hRet == SQL_SUCCESS_WITH_INFO )
#endif
            {
                //Once get a clear description of the column, switch through
                //and assign the Field's information.


                m_pFields[i].m_bIsNull = false;
                switch( nColumnType )
                {
                    case SQL_INTEGER:
                    {
                        m_pFields[i].m_longValue = 0;
                        m_pFields[i].m_dwType = DBVT_LONG;
                        strcpy_s( m_pFields[i].m_szName, m_szColumnName );
                        hRet = SQLFetch( m_hStatementHandle );
                        if( hRet == SQL_SUCCESS || hRet == SQL_SUCCESS_WITH_INFO )
                        {
                            hRet = SQLGetData( m_hStatementHandle, (SQLUSMALLINT)i + 1,
                                SQL_INTEGER, (SQLPOINTER)&m_pFields[i].m_longValue, sizeof(long),
                                NULL );
                        }
                        break;
                    }
                    case SQL_SMALLINT:
                        m_pFields[i].m_shortValue = 0;
                        m_pFields[i].m_dwType = DBVT_SHORT;
                        strcpy_s( m_pFields[i].m_szName, m_szColumnName );
                        hRet = SQLFetch( m_hStatementHandle );
                        if( hRet == SQL_SUCCESS || hRet == SQL_SUCCESS_WITH_INFO )
                        {
                            hRet = SQLGetData( m_hStatementHandle, (SQLUSMALLINT)i + 1,
                                SQL_INTEGER, (SQLPOINTER)&m_pFields[i].m_shortValue, sizeof(long),
                                NULL );
                        }
                        break;
                    case SQL_VARCHAR:
                    {
                        m_pFields[i].m_pString = new CString();
                        m_pFields[i].m_dwType = DBVT_STRING;
                        strcpy_s( m_pFields[i].m_szName, m_szColumnName );
                        hRet = SQLFetch( m_hStatementHandle );
                        char szBuff[56] = { 0 };
    
                        hRet = SQLGetData( m_hStatementHandle, (SQLUSMALLINT)i + 1,
                        SQL_C_CHAR, (SQLPOINTER)szBuff, 56,
                        NULL );


                        break;
                    }
                    default:
                        break;
                }


                


            }
        }
    }


    return true;
}

Need any more help?

You must be registered to see links

That link, on the left-hand side, will list all the SQL functions. Go nuts.

I'll edit this in a few to a link for all my projects.
Currently fixing and finishing this along with MySQL.
Also got a handy Win32 GUI library and expandable networking library coming up soon.

Added link to library.
Needs fixes and more testing.. Haven't gotten to MySQL yet, but supports ANSI and Unicode.

 

[PenguinGuy]

Updated post, added headers and source files. Haven't fully complete the Doxygen documentation, but will get to finishing that Monday.

Edit : Oh yeah. duck TCHAR's, I haven't gotten to finishing MString yet.