SQLBindParameter/SQLExecute returns SQL_NO_DATA on second iteration

Question

Hi, ALL,

I'm trying to run the following scenario:

std::wstring query = L"INSERT INTO abcattbl SELECT ?, ?, (SELECT object_id FROM sys.objects o, sys.schemas s WHERE s.schema_id = o.schema_id AND o.name = ? AND s.name = ?), '', 8, 400, 'N', 0, 0, 34, 0, 'MS Sans Serif', 8, 400, 'N', 0, 0, 34, 0, 'MS Sans Serif', 8, 400, 'N', 0, 0, 34, 0, 'MS Sans Serif', '' WHERE NOT EXISTS(SELECT * FROM dbo.abcattbl WHERE abt_tnam=? AND abt_ownr=?);";

SQLTables( stmt1 );

SQLPrepare( stmt2, query );

for( SQLFetch() )

{

// SQLBindParameter() calls

SQLExecute( stmt2 );

}

Problem is everything runs fine only first time. The second time I'm querting SQL_NO_DATA.

I even tried to add call to SQLFreeStmt( stmt2, SQL_RESET_PARAMS ); but without success.

Could someone explain what needs to be done in order to successfully rebind and re-run the query?

Thank you,

Answer 1

ODBC is not an API I have worked with, so I'm not really in position to give advice. It does not help that your code sample is not coherent. You first declare and assign a variable query and then there is a query2. So even if I would know ODBC better, I might still be confused over what you are doing.

However, what you could try is to issue SET NOCOUNT ON before you running anything else. These "rows affected" messages often causes problems, if you don't cater for them. But this is just a shot in the dark.

Permit me also to point out on thing about the SQL as such. You have an INSERT statement without a column list. This is very bad practice in my opinion. If you or someone else innocently adds a nullable column to the table, thinking nothing can go wrong, your code will stop working. That is, the code should read:

INSERT tbl(col1, col2, ...) 
    SELECT ...