Query not working through freeTDS works in Microsoft ODBC Driver

[kowsikbabu]

Before posting here, I have verified this is not a driver side issue, because the query works in both FreeTDS & Microsoft ODBC Driver using native SQL code.

When we use Microsoft ODBC driver via SOCI, we get the results as expected. 3 rows with several column values
But when we use FreeTDS via SOCI, we get the same number of rows returned, but the rows are returned empty.

We have tried to debug the issue for more than a week, but we are stuck. Kindly help us resolve this issue.

We have tried with both 4.0.3 & master & both (64 & 32) versions as well. SQL server version is 2019.

Query for reference:

SET NOCOUNT ON;DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND name IN ('TestDB');DECLARE @DatabaseName NVARCHAR(128);DECLARE @outset TABLE(INSTANCENAME varchar(50),DATABASENAME varchar(100),TABLENAME varchar(100),NUMBEROFRECORDS_I bigint);OPEN db_cursor;FETCH NEXT FROM db_cursor INTO @DatabaseName;WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @command nvarchar(1000) = 'USE '+ QUOTENAME(@DatabaseName) +'; SELECT @@SERVERNAME,DB_NAME(),T.NAME,P.[ROWS] FROM sys.tables T INNER JOIN sys.indexes I ON T.OBJECT_ID = I.OBJECT_ID INNER JOIN sys.partitions P ON I.OBJECT_ID = P.OBJECT_ID AND I.INDEX_ID = P.INDEX_ID INNER JOIN sys.allocation_units A ON P.PARTITION_ID = A.CONTAINER_ID WHERE T.NAME NOT LIKE ''DT%'' AND I.OBJECT_ID > 255 AND I.INDEX_ID <= 1 GROUP BY T.NAME, I.OBJECT_ID, I.INDEX_ID, I.NAME, P.[ROWS] ORDER BY OBJECT_NAME(I.OBJECT_ID)' INSERT INTO @outset EXEC (@command) FETCH NEXT FROM db_cursor INTO @DatabaseName END CLOSE db_cursor DEALLOCATE db_cursor select INSTANCENAME,DATABASENAME,TABLENAME,NUMBEROFRECORDS_I from @outset;

Please rename TestDB with any database name present in your sql server to reproduce the issue

[vadz]

Sorry, I won't have time to debug this in the observable future. Please try doing it yourself and, of course, if possible please try to simplify the query as my eyes completely glazed over it as written now (especially formatted like this).

[kowsikbabu]

Basically any query using CURSOR has this and we have identified the issue SQLNumResultCols returning 0 during the describe() call.

As a workaround we are trying to move the describe() call after the execute() is done, as a proof of concept, this fixed the issue as SQLNumResultCols returns value if it is called after execute() is done

I would like to know what are the implications of this change.

We are not using soci::use api at all, but please confirm if we will face any issues if we try something like this.

As always, thanks for your time and input.

Cheers,
Kowsik

[vadz]

Unfortunately I'm almost sure that moving it as proposed (please, please use diffs instead of annotated screenshots...) will break something, please try running the unit test suite after making this change to check it.

I also think that if SQLNumResultCols() doesn't behave in the same way in freeTDS as in the official ODBC driver, it would be worth reporting it to freeTDS developers, even if it's unlikely to be immediately helpful.

[kowsikbabu]

Yes, I have already raised an issue in the FreeTDS repository

Thanks, I will try to run the tests.

Apologies for the bad screenshot, I'm not on my work laptop and just wanted to reply with a quick screen grab.

Cheers
Kowsik