No active result set for multistatement query

[korobkov-mindbox]

Hi! I've been trying to migrate some legacy sql stored procedures into turbodbc-used queries and I've encountered a problem I do not have solution for. Multistatement queries (with exactly one result set) cannot be executed via turbodbc.

Minimal example:

from turbodbc import connect

def test(query: str):
    connection = connect(
        driver='{SQL Server}',
        server='staging-sql')
    cursor = connection.cursor()
    cursor.execute(sql=query)
    data = cursor.fetchallnumpy()
    print(data)

test('select 1 as some_data')
test('select 0 as fake_data into #tempdata; select 1 as some_data')

expected behaviour: same results actual behaviour: first query produces [[1]], second query fails at turbodbc\cursor.py", line 93, in _assert_valid_result_set with turbodbc.exceptions.InterfaceError: No active result set

only other point of reference I have is .Net SqlConnection behaviour:

void test(string text)
{
	var conn = new SqlConnection("server=staging-sql;Persist Security Info=True;Integrated Security=SSPI");
	conn.Open();
	var cmd = conn.CreateCommand();
	cmd.CommandText = text;
	var reader = cmd.ExecuteReader();
	reader.Read();
	Console.WriteLine(
		(reader.GetName(0), reader.GetInt32(0)));
}

test("select 1 as some_data");
test("select 0 as fake_data into #tempdata; select 1 as some_data");

in C# this correctly produces ("some_data", 1) values in both cases; executing both queries in Sql Server Management Studio yields identical results, too. Pyodbc, on the other hand, considers second query to be not a query, too ( pyodbc.ProgrammingError: No results. Previous SQL was not a query).

is there anything I can do to retrieve single result set of multistatement query using turbodbc?

[korobkov-mindbox]

I found a workaround of sorts: it seems that turbodbc's (or driver's, that i do not know) implementation uses rowcount messages as result set markers (which is, strictly speaking, incorrect; working with temp tables, removing and updating rows will produce rowcount messages without adding result set).

adding set nocount on to all my queries made them work.

[MathMagique]

Interesting! Turbodbc actually uses the number of columns in the result set to determine whether there is a result set or not (SQLNumResultCols() ODBC function). What is the correct way, in your opinion?

That said, turbodbc was not built with multiple statements in a single statement in mind, as this would imply handling multiple result sets. Doable, surely, but not done yet.

[korobkov-mindbox]

Sadly, i know too little of ODBC api to have an opinion on a correct way. Knowing Microsoft, i would not be surprised if they hacked their way around the same api or if they use something undocumented to provide such functionality in .NET & SSMS; however i'd say that overall behaviour of treating only select statements as result sets seems to be correct. In my own case we have a complex query that uses temporary table; it's select * into #someTempTable that messes up turbodbc's (and pyodbc's, too) behaviour but works alright with .Net adapter and SSMS.

[david-engelmann]

This should be closed out due to #369