CODEWITHSUNDEEP
visual-c++odbc
Mike Fulton
Mike Fulton

Reputation: 918

Problems getting SQLBindParameter to work in C++

I downloaded a small sample program in C++ that opens an ODBC connection to SQL Server, issues a query, and prints the result.

When the query string includes a parameter and I call SQLBindParameter, the query execution returns SQL_SUCCESS but subsequentally fails to get any records.

If I do the equivalent query directly in SQL Management studio it works.

Here's the code. Note that to keep things short I have removed error checking in places where I've not been having problems. The real connection string is obfuscated.

SQLHANDLE sqlenvhandle = 0;    
SQLHANDLE sqlconnectionhandle = 0;
SQLHANDLE sqlstatementhandle = 0;
SQLHANDLE sqlstatementhandle2 = 0;
SQLRETURN retcode = 0;
SQLWCHAR retconstring[1024];

retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle);
retcode = SQLSetEnvAttr(sqlenvhandle,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle);
retcode = SQLDriverConnect(sqlconnectionhandle, NULL, (SQLWCHAR*)_T("--connectionstring--"),
                            SQL_NTS, retconstring, 1024, NULL, SQL_DRIVER_NOPROMPT);

retcode = SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle2);

SQLWCHAR *query = _T("SELECT * FROM gbm_models WHERE stagename like ?");
SQLWCHAR *searchname = _T("Yuk%\0");

retcode = SQLPrepare (sqlstatementhandle2, query, SQL_NTS);
SQLINTEGER xxx = SQL_NTS;
retcode = SQLBindParameter( sqlstatementhandle2, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                            SQL_VARCHAR, 5, 0, searchname, 0, &xxx );

SQLWCHAR sqlbuffer[400];
SQLINTEGER buflen = 0;

retcode = SQLExecute (sqlstatementhandle2);

char name[512];
int id;

while(SQLFetch(sqlstatementhandle2) == SQL_SUCCESS)
{
    SQLGetData(sqlstatementhandle2, 1, SQL_C_ULONG, &id, 0, NULL);
    SQLGetData(sqlstatementhandle2, 4, SQL_C_CHAR, name, 500, NULL);
    cout << id << " " << name << endl;
}

SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle2 );
SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
SQLDisconnect(sqlconnectionhandle);
SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);

None of these calls return an error. What happens is that the call to SQLFetch returns SQL_NO_DATA. If I replace the "?" in the initial query string with the actual name string, and comment out the SQLBindParameter call, it works fine and retrieves the expected data. So obviously, the search string is not making its way into the query correctly.

Any ideas what's wrong? Does anybody know a way to get the processed query string with the parameter substitution done? I thought the SQLNativeSql function did that, but when I call it, I just get back the original query, so I'm not sure if it's working right or what. Could it be a Unicode thing?

Upvotes: 0

Views: 4657

Answers (1)

YePhIcK
YePhIcK

Reputation: 5856

See the datatypes at http://msdn.microsoft.com/en-us/library/windows/desktop/ms714556(v=vs.85).aspx

SQL_C_CHAR is not compatible with the SQLWCHAR so your binding fails to do a proper job - it is using a pointer to double-byte wide characters and a resulting "C-string" is not what you'd expect it to be.

Use compatible datatypes, in your case just go with:

SQLCHAR *searchname = "Yuk%";

Upvotes: 1

Related Questions