Getting Invalid Cursor State on mssql - complex queries - Urgent help needed

[kowsikbabu]

Hello team. I have built SOCI 4.0.3 locally using the recommended steps and facing this error during a complex (batch) query result fetch.

Some background:

• DLL built for: 32 bit
• Dependent Exe version: 32 bit
• MSSQL server version: MSSQL.15
• Driver used for connection: Tried with both ODBC Driver 17 for SQL Server & SQL Server (Driver version: 10.00.19041.4170)

The issue: Single statement works flawlessly without any issue, however if I try even a simple batch query, I get the following error: 0x000001c8fb187fb0 "Error fetching data: [Microsoft][ODBC Driver 17 for SQL Server]Invalid cursor state (SQL state 24000) while fetching data from \"DECLARE ...

The statement used here: DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND name NOT IN ('master','tempdb','model','msdb');DECLARE @DatabaseName NVARCHAR(128);DECLARE @outset TABLE([DATABASENAME] varchar(100),[TABLENAME] varchar(100));OPEN db_cursor;FETCH NEXT FROM db_cursor INTO @DatabaseName;WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @command nvarchar(1000) = 'USE '+ QUOTENAME(@DatabaseName) +'; SELECT DB_NAME() AS databasename,''[''+SCHEMA_NAME(schema_id)+''].[''+name+'']'' AS tablename FROM sys.tables' INSERT INTO @outset EXEC (@command) FETCH NEXT FROM db_cursor INTO @DatabaseName END CLOSE db_cursor DEALLOCATE db_cursor select databasename,tablename from @outset;

Running this query in SSMS (SQL Server management studio) or sqlcmd works flawlessly.

Sample output from sqlcmd

What I have done:

Firstly, I obviously doubted my implementation, so I revisited the documentation and used all the possibilities.

• I tried Implementing rowset directly:

rowset<int> rs = (sql.prepare << query);

• Then I tried the rowset_iterator example as well:

statement st(sql);
st.alloc();
st.prepare(query);
st.define_and_bind();
st.exchange_for_rowset(into(r));
st.execute(false);
rowset_iterator<row> it(st, r);
rowset_iterator<row> end;
for (; it != end; ++it) {
}

In this execution, the error occurs when rowset_iterator<row> it(st, r); is initialized.

• Then the third option, I used:

statement st = (sql.prepare << query , into(vectorA), into(vectorB));
st.execute();
while (st.fetch())
{
}

In this execution, while(st.fetch()) returns false. Hence while loop is never executed.

Further Debugging: (I might be wrong here) Upon debugging this further in one of many queries, I was able to see that the while(st.fetch()) false was being returned from odbc\Statement.cpp in the method statement_backend::exec_fetch_result odbc_statement_backend::do_fetch(int beginRow, int endRow)

In this method, the exact statement returning false was:

    if (is_odbc_error(rc))
    {
        throw odbc_soci_error(SQL_HANDLE_STMT, hstmt_, "fetching data");
    }

Surprisingly, when I used the statement api to execute the query and call the st.get_affected_rows() I'm getting the correct number of rows that was returned.

Note: I faced this same exact error even when I ran USE DATABASE; SELECT * FROM sample_table; as well. I re-verified to check if SOCI supported batch queries and it was supported as per the documentation. For this issue I did the workaround of running two queries, one to run USE DATABASE and then the actual query, this worked for that simple query, however it doesn't work for the complex batch query quoted above as we use DECLARE.

It would be very helpful if one of you guys (contributors) or the community helped me with this issue (if possible on priority) as one of our project delivery is dependent on this. I also apologize in advance if there was a silly mistake was made and that was the root cause of the issue.

I'm happy to add to this post if any other information is required to resolve this issue.

Kowsik

[vadz]

Sorry, I can't look at this in the details but this looks like something that could be fixed by enabling MARS ("Multiple Active Result Sets") for your connection. If you can't do this, you'll have to change your query to avoid having multiple active cursors simultaneously.

[kowsikbabu]

Hello @vadz

Thanks for your quick response. I tested it with MARS enabled MultipleActiveResultSets=true in the connection string and I'm still facing the issue.

I just ran a simple query: USE test; select * from sample_table;

and still I got the same error:

Error while executing query: Error fetching data: [Microsoft][ODBC Driver 17 for SQL Server]Invalid cursor state (SQL state 24000) while fetching data from "USE test; select * from sample_table;"

[kowsikbabu]

Hello @vadz

As a temporary workaround we have used SET NOCOUNT ON to bypass this error, but still this is a good design, because setting no count on will return no of affected rows as -1 by default.

Posting this in hope that this would help someone else in the future.