No data results hides subsequent result sets.
lexicalunit opened this issue · 4 comments
From @aquasync on December 30, 2014 0:48
I'm using nanodbc to run a chunk of sql containing multiple statements (dml/ddl along with selects), and iterating through the results with something like the following:
nanodbc::connection db("...");
nanodbc::result res = execute(db, sql);
do {
if (bool(res) && res.columns() > 0) {
// process resultset
}
} while (bool(res) && res.next_result());
I ran into the situation where queries which return no data return a default-constructed result object, as the SQL_NO_DATA path in execute/execute_direct is taken. This causes it to have no underlying result_impl or reference to the statement, so you are unable to advance to subsequent result sets. Adding a preceding dummy query seems to fix this.
For example, in "select top 0 1 as a into #temp; select 2 as b", data from the latter select is not reachable, but prepending "select 1 as a into #dummy; " returns a valid result object allowing you to iterate to the final result set.
Commenting out the above-mentioned SQL_NO_DATA path in both execute & execute_direct fixes this for me by returning a valid result object always. Could this be deleted upstream or is it needed in some other situtation?
Copied from original issue: lexicalunit/nanodbc#33
Thank you so much for the awesome issue and information. Fixed in 918d73c :)
From @mloskot on May 15, 2016 19:40
Since there is some related future work planned as per this point in the README: Refactor code to remove the need for the NANODBC_HANDLE_NODATA_BUG option, and for the sake of archive, it may be important to mention that this is likely a bug in ODBC driver, not nanodbc.
According ot the ODBC reference, SQLNumResultCols should return success and 0 columns here:
If the statement associated with StatementHandle does not return columns, SQLNumResultCols sets *ColumnCountPtr to 0.
I don't exactly see how the code could be refactored to catter for such buggy drivers without some performance overhead/complexity increase (extra ODBC API calls). If anything needs to be done, it deserves a dedicated pull request/issue.
Yeah, the workaround was provided to get nanodbc working with Vertica ODBC drivers. There probably isn't an easy way to automatically handle busted drivers like this without detecting them at runtime, and there's no reason to go down that road if the #define solution is working reasonable well for users.