We help IT Professionals succeed at work.

MFC App works with MS SQL Server; under Oracle get errors in SQL query strings

sniles
sniles asked
on
My MFC application functions perfectly when run against SQL Server (using ODBC through CRecordset-derived classes).  When I change my ODBC data source to point to Oracle, I have all sorts of problems with the SQL query strings being built wrong by MFC.  It seems to mostly be that with SQL Server, it wants to use square brackets around columnn and table names, but Oracle needs quotes there.  Example:

MFC generates queries like:
SELECT [account] FROM [Table1] WHERE [Balance] < 0

Oracle needs to have that query be:
SELECT "account" FROM "Table1" WHERE "Balance" < 0

Is there something I need to do to tell MFC what type of query string to produce?  I've looked on MS's site and KnowledgeBase but can't seem to find this.  
Comment
Watch Question

Commented:
Top Expert 2007
Commented:
I'm not sure about the exact answer to this question, but I'd suggest looking at these items:

DBCORE.CPP: void CDatabase::ReplaceBrackets(LPTSTR lpchSQL)

Appears to deal with the bracket issue and has a comment that reads "Replace brackets in SQL string with SQL_IDENTIFIER_QUOTE_CHAR"

DBCORE.CPP: void CDatabase::GetConnectInfo()

This function set's the member variable m_chIDQuoteChar that appears to be used as the quote character. It is set based on the SQL_IDENTIFIER_QUOTE_CHAR value returned by SQLGetInfo().

I'd debug through the code to see what's actually happening. Is MFC doing the correct job? Is your driver returning the quote character it wants? If not, you could create a new CDatabase class and fix the problem.

Good Luck,
Steve

Author

Commented:
That seemed to get me past the original problem.  Thanks!

For others that run into this same scenario:  I also had to go in and set the m_bUseUpdateSQL on the CRecordset to TRUE to get the whole thing completely working.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.