ODBC - Coding Prepared Statements in C++

Question

Hi all,

Plugging away at learning how to develop ODBC SQL driver and data source stuff, but I seem to have run into a bit of a snag. I'm currently working with prepared statements on the a database with the following statement:

select * from TEST1 where NAME = ? and LOCATION__LATITUDE__S = ?

In English, find all records from TEST1 with to-be-specified name and latitudinal coordinate. I'm able to do the above with the ODBCTest app, so I know I can connect to the data source and query it with parameterized queries. Here's what I have for code for my problematic function:

void ExecPreparedStatement(const char* stmt) {
    HSTMT hstmt;
    SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

    RETCODE rc = SQLPrepare(hstmt, (WCHAR*)stmt, SQL_NTS);

    SQLSMALLINT numParams;
    rc = SQLNumParams(hstmt, &numParams);

    WCHAR* param1 = (WCHAR*)L"Jacob";
    SQLFLOAT param2 = 40.0;

    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 80, 0, (SQLPOINTER)param1, 300, NULL);
    rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_FLOAT, 0, 0, &param2, 300, NULL);

    rc = SQLExecute(hstmt); /* <fails here> */

    SQLSMALLINT numCols;
    SQLNumResultCols(hstmt, &numCols);

    DisplayRecords(hstmt, numCols);

    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

This should give me the same results from the test app: 3 records of 13 attributes (3 rows, 13 columns). Instead, it fails on execution. For ease of reading, I've removed all of my RetCode handling from the code, but I do have it in there to check that the statements are completed and to handle it gracefully if they fail. There must be something I'm misunderstanding here - it also shows me that the number of parameters from the statement is 0 (with the numParams variable); I theorize that this is because it should be placed after the Execute call, but I can't test that right now because I never get to that point in execution.

Any ideas? Banging my head on a brick wall here; MSDN and other online sources are proving less than informative on this.

Clarification on the main question: Does anybody have any idea why the Execute function is failing?

ANSWER FOUND

The issue was malformed SQL through misused casting. Instead of passing in a const char* string to the function, I pass in a WCHAR*-casted string instead, and inside the function I use WCHAR*. The functional code now looks like this:

HSTMT hstmt;

SQLAllocStmt(hdbc, &hstmt);

TryODBC(hstmt, SQL_HANDLE_STMT, SQLPrepare(hstmt, stmt, SQL_NTS));
// Prepare passes test - we return 0.

WCHAR* param1 = (WCHAR*)L"Jacob";

TryODBC(hstmt, SQL_HANDLE_STMT, 
    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_WCHAR, 
        SQL_WVARCHAR, 80, 0, (SQLPOINTER)param1, 300, NULL)
);

TryODBC(hstmt, SQL_HANDLE_STMT, SQLExecute(hstmt));

SQLSMALLINT numCols;
TryODBC(hstmt, SQL_HANDLE_STMT, SQLNumResultCols(hstmt, &numCols));
DisplayRecords(hstmt, numCols);

SQLFreeStmt(hstmt, SQL_CLOSE);

Where TryODBC() is a function as follows:

bool disconnectOnError = false;
if (rc == SQL_SUCCESS_WITH_INFO || rc == SQL_ERROR) {
    if (!Success(rc)) {
        disconnectOnError = true;
    }
    SQLWCHAR       state[6], errorMsg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    nativeError;
    SQLSMALLINT   i = 1, msgLen;

    while ((rc = SQLGetDiagRec(handleType, handle, i, state, 
        &nativeError, errorMsg, sizeof(errorMsg), &msgLen)) != SQL_NO_DATA)
    {
        ShowMessage(nativeError, errorMsg);
        i++;
    }
}
if (disconnectOnError) {
    Disconnect(-1);
}

Massive thanks to @erg for directing me towards the SQLGetDiagRec() function.

What error do you get on the line where it fails? SQLGetDiagRec should give you some good hints whats going wrong. Or even better, provide a stackoverflow.com/help/mcve — erg
– erg, Commented Dec 11, 2017 at 12:55
@erg Got it, thanks! I was unaware of SQLGetDiagRec and its use, so a bit of googling and experimentation made everything clear. The problem was that the SQL was somehow being malformed through misuse of casting and bad SQLWCHAR. — Jonathan McDevitt
– Jonathan McDevitt, Commented Dec 11, 2017 at 17:19

SoronelHaetir · Accepted Answer · 2017-12-08 23:14:28Z

1

You are passing a wide string but specifying SQL_C_CHAR as the parameter type, this should be SQL_C_WCHAR.

300 as your BufferLength parameter makes no sense, for the string argument pass the correct number of bytes, for param2 just pass 0 (BufferLength is ignored for non-character or binary-string data).

And you really need to check the error code after every ODBC call, and if it's an error dump the diagnostics. Either that or turn on the trace connection attribute and look at the results.

answered Dec 8, 2017 at 23:14

SoronelHaetir

15.6k1 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan McDevitt Over a year ago

This doesn't answer the question. With the exception of the CHAR vs WCHAR, it's either nit-picking or ignoring what was said in the text of the post.

Jonathan McDevitt · Accepted Answer · 2017-12-11 17:26:06Z

0

Answer noted in original question.

answered Dec 11, 2017 at 17:26

Jonathan McDevitt

1632 silver badges12 bronze badges

Collectives™ on Stack Overflow

ODBC - Coding Prepared Statements in C++

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related