December 28, 2015 at 7:27 am
Hi all,
I want to execute ad-hoc queries with SQL Server Native Client ODBC, using different ODBC calls and cursor options, according to the type of the SQL query.
For example, when the SQL returns a result set (typical SELECT), I want to use a server-side Fast-Forward Only cursor (SQL_CO_FFO) or scrollable cursor (SQL_SCROLLABLE) by using SQLPrepare / SQLExecute. But if the SQL is a stored procedure call, or if it creates a temporary table, I just want to perform a SQLExecDirect() without using the sp_cursor* stored procedures...
So far, I need to parse the SQL text, to identify what sort of SQL statement needs to be executed... But this is tricky and does not cover all possible Transact-SQL statements that could be performed... For example, an INSERT INTO with OUTPUT clauses will return a result set, but since it's identified as a simple INSERT, it will not be treated as a statement returning a result set:
INSERT INTO #mytmptab (id, name) OUTPUT INSERTED.id, INSERTED.name SELECT * FROM srctab ...
Looking at the sp_cursor* calls produced by the SNAC ODBC driver, it appears that cursor options such as SQL_CO_FFO have to be set before calling SQLPrepare(), because it's sp_cursorprepare which is handling such options as FAST_FORWARD (0x10): sp_cursorprepare doc)...
So how can I know if an ad-hoc query will produce a result set, or must not be executed indirectly as a server cursor in sp_cursor* calls, for SQL statements that require to stay in the same scope (temp table creations for ex)?
Is there a kind of pre-prepare, to identify the SQL statement, without losing performance?
I cannot rewrite a complete Transact-SQL parser to identify the SQL text...
Any clue / advice is welcome...
Thanks for reading!
December 28, 2015 at 8:57 am
You should use the ODBC API to request a cursor type - don't go down the "formerly-undocumented sp_cursor% call" rabbit hole:-P. But, if you are curious as to what the ODBC API does (under the covers) see the sp_cursoropen syntax at https://msdn.microsoft.com/en-us/library/ff848779.aspx. Keep in mind that a cursor type is just a request, not a directive. Implicit cursor conversions can and do happen. To address implicit cursor conversions , the API offers https://msdn.microsoft.com/en-us/library/ms131660(v=sql.120).aspx.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply