bug in SQLMoreResults?

[gbromov]

I am trying to use freetds ODBC driver together with Microsoft ODBC driver (same application, different OS), but there seems to be inconsistency in SQLMoreResults behavior and I am not quite sure how to work around it.

I am using SQLExecDirect to execute a batch of statements, and I need to read the total rows affected. Since the batch contains multiple commands, I have to use SQLMoreResults to iterate over all results and sum those. The code is something like this:

SQLLEN CountRowsAffected( SQLHSTMT hStatement )
{
	SQLLEN result = 0;
	for ( ; ; )
	{
		SQLLEN rows_affected = 0;
		SQLRowCount( hStatement , &rows_affected );
		result += rows_affected;

		SQLRETURN more_results = SQLMoreResults( hStatement );
		if ( more_results != SQL_SUCCESS && more_results != SQL_SUCCESS_WITH_INFO )
			break;
	}
	return result;
}

Trying the code with multiple INSERT statements works fine in both Microsoft and FreeTDS driver - for each, minus one, INSERT the SQLMoreResults call returns SQL_SUCCESS, and the next SQLRowCount reads it.

The problem arise when instead of INSERT statements (which is just for testing purposes really, multiple inserts can be done in a single statement and SQLRowCount works fine there), I use multiple EXECUTE statements. Then Microsoft's driver works as expected - one successful SQLMoreResults for each, minus one, EXECUTE statement, and total sum matches the expected result. Unfortunately FreeTDS version of SQLMoreResults for some reason returns SQL_NO_DATA (100) immediately and SQLRowCount is read only for the first EXECUTE.

Is this a known issue? Can I work around it somehow?

[0x00000oak]

Hello, I know its old but have you found a workardound? I might be experiencing the same or similar although I do not have source code for the application I am trying to run to fully verify it.

[freddy77]

Do you have some example queries/code?

[gbromov]

I didn't find any workaround to handle the issue and I am fairly certain it is a bug, maybe in SQLMoreResults. I've written a quick and dirty test to show the issue:

SQL code:

CREATE TABLE trash( id UNIQUEIDENTIFIER );
CREATE PROCEDURE usp_insert_trash AS INSERT INTO trash (id) VALUES (NEWID());

C code:

static const wchar_t SQL_INSERTS_CALL[ ] = L""
L"INSERT INTO [trash] (id) VALUES (NEWID());"
L"INSERT INTO [trash] (id) VALUES (NEWID());"
L"INSERT INTO [trash] (id) VALUES (NEWID());";

static const wchar_t SQL_PROCEDURES_CALL[ ] = L""
L"EXEC usp_insert_trash;"
L"EXEC usp_insert_trash;"
L"EXEC usp_insert_trash;";

static SQLRETURN trace_call( const char * text , SQLRETURN result )
{
	printf( "%s returned %d\n" , text , result );
	return result;
}

#define TRACE( x )				trace_call( #x , x )

static SQLLEN count_affected_rows( HSTMT statement ) noexcept
{
	SQLLEN result = 0;
	for ( ; ; )
	{
		SQLLEN rows_affected = 0;
		TRACE( SQLRowCount( statement , &rows_affected ) );
		printf( "RowCount returned: %lld\n" , (long long)rows_affected );
		result += rows_affected;

		SQLRETURN more_results = TRACE( SQLMoreResults( statement ) );
		if ( more_results != SQL_SUCCESS && more_results != SQL_SUCCESS_WITH_INFO )
			break;
	}

	return result;
}

static void test( HDBC conn , const wchar_t * sql )
{
	HSTMT stmt = SQL_NULL_HANDLE;

	SQLAllocHandle( SQL_HANDLE_STMT , conn , &stmt );
	TRACE( SQLExecDirect( stmt , (wchar_t*)sql , SQL_NTS ) );

	SQLLEN result = count_affected_rows( stmt );
	printf( "===> Total Affected Rows: %d\n" , (int)result );

	SQLFreeStmt( stmt , SQL_CLOSE );
	SQLFreeHandle( SQL_HANDLE_STMT , stmt );
}

int main( int argc , char **argv )
{
	HENV env = SQL_NULL_HANDLE;
	SQLAllocHandle( SQL_HANDLE_ENV , SQL_NULL_HANDLE , &env );
	SQLSetEnvAttr( env , SQL_ATTR_ODBC_VERSION , (SQLPOINTER)SQL_OV_ODBC3 , 0 );

	HDBC conn = SQL_NULL_HANDLE;
	SQLAllocHandle( SQL_HANDLE_DBC , env , &conn );
	SQLSetConnectAttr( conn , SQL_LOGIN_TIMEOUT , (SQLPOINTER)15 , SQL_IS_UINTEGER );
	SQLDriverConnect( conn , SQL_NULL_HANDLE , (wchar_t *)CONNECTION_STRING , SQL_NTS , nullptr , 0 , nullptr , SQL_DRIVER_NOPROMPT );

	printf( "\nCalling three inserts...\n" );
	printf( "==================================\n" );
	test( conn , SQL_INSERTS_CALL );
	printf( "==================================\n" );

	printf( "\nCalling three procedures...\n" );
	printf( "==================================\n" );
	test( conn , SQL_PROCEDURES_CALL );
	printf( "==================================\n" );

	SQLDisconnect( conn );
	SQLFreeHandle( SQL_HANDLE_DBC , conn );
	SQLFreeHandle( SQL_HANDLE_ENV , env );

	return 0;
}

Output when running with Microsoft SQL Server ODBC driver:

Calling three inserts...
==================================
SQLExecDirect( stmt , (wchar_t*)sql , SQL_NTS ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 100
===> Total Affected Rows: 3
==================================

Calling three procedures...
==================================
SQLExecDirect( stmt , (wchar_t*)sql , SQL_NTS ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 100
===> Total Affected Rows: 3
==================================

Output when running FreeTDS driver:

Calling three inserts...
==================================
SQLExecDirect( stmt , (wchar_t*)sql , SQL_NTS ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 100
===> Total Affected Rows: 3
==================================

Calling three procedures...
==================================
SQLExecDirect( stmt , (wchar_t*)sql , SQL_NTS ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 100
===> Total Affected Rows: 1
==================================

You can see that Microsoft's driver correctly identify the total affected rows in both cases, where FreeTDS driver fails in the second one.

[fziglio]

Thanks for the test. Yes, there are some minor differences on FreeTDS and Microsoft... and also, believe it or not from Microsoft and Microsoft! A bit surprised this difference with SQLExecDirect. I would bet that the behaviour with Microsoft driver is different if you use SQLPrepare/SQLExecute instead.

[gbromov]

I just tried using
TRACE( SQLPrepare( stmt , (wchar_t*)sql , SQL_NTS ) );
TRACE( SQLExecute( stmt ) );
instead of
//TRACE( SQLExecDirect( stmt , (wchar_t*)sql , SQL_NTS ) );

With the Microsoft driver the results are exactly the same, it is however different with the FreeTDS driver. Unfortunatelly the difference is not in a good way:

Calling three inserts...
==================================
SQLPrepare( stmt , (wchar_t*)sql , SQL_NTS ) returned 0
SQLExecute( stmt ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 100
===> Total Affected Rows: 1
==================================

Calling three procedures...
==================================
SQLPrepare( stmt , (wchar_t*)sql , SQL_NTS ) returned 0
SQLExecute( stmt ) returned 0
SQLRowCount( statement , &rows_affected ) returned 0
RowCount returned: 1
SQLMoreResults( statement ) returned 100
===> Total Affected Rows: 1
==================================

As you can see, when using Prepare/Execute instead of ExecDirect both queries fail in calculating the total affected rows.

I am aware that there are differences between the drivers, and it is fine, but my issue is that I cannot figure out any way of calculating the total affected rows using FreeTDS.

To add to the test, by creating a new procedure:

CREATE PROCEDURE usp_add_trash_three
AS
BEGIN
  INSERT INTO trash (id) VALUES (NEWID());
  INSERT INTO trash (id) VALUES (NEWID());
  INSERT INTO trash (id) VALUES (NEWID());
END

Again - Microsoft's driver correctly calculates 3 rows affected, FreeTDS says 1 row is affected, regardless of whether it is called via SQLExecDirect or SQLPrepare/SQLExecute.

Any hint what I can try to work around that?

[0x00000oak]

Since I do not have source code for the application I have to either relicate the same behavior at the driver level or rewrite all existing stores procedures. Any possibility of FreeTDS matching the Microsoft driver regarding this?

[freddy77]

Try to reproduce. A workaround could be something like

CREATE PROCEDURE usp_add_trash_three
AS
BEGIN
  INSERT INTO trash (id) VALUES (NEWID()), (NEWID()), (NEWID());
END

(if possible)

[gbromov]

This is a valid workaround indeed, unfortunately it is very limited - only applies to INSERTS, these INSERTS must be for the same table with the same id list and you cannot have any statements between the inserts.

[freddy77]

Sure, I didn't say I'm not going to fix it.
Meantime I looked at the tests and, yes, there's a test for something like this, but it's only testing ODBC version 2, which, in this respect, is pretty different.