RE: Invalid cursor state when using PRINT in MSSQL

From: Martin J. Evans (martin.evans_at_easysoft.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 08:54:18 +0100 (BST)
To: dbi-users@perl.org

James,

Interesting article but I seriously doubt you are using the ODBC 2.0 MS SQL
Server driver.

Also, DBD::ODBC does call SQLError immediately after SQLExecute returns
SQL_SUCCESS_WITH_INFO so it is complying with this article.

As I said, I can reproduce this issue easily without DBD::ODBC.

SQLPrepare(print 'a' select count(*) from table print 'b')
SQLExecute()
SQLError picks up 'a'
SQLNumResultCols returns SQL_ERROR and invalid cursor state.

The only bit I saw which was new to me was:

"If the ODBC application then processes through the result set using
SQLFetch(), the application can get the second print statement by calling
SQLError() when SQLFetch() returns SQL_NO_DATA_FOUND."

i.e. if SQLFetch returns SQL_NO_DATA_FOUND there may be diagnostics to pick up.
However, I was unable to reproduce this with the ODBC 3 MS SQL Server driver
which everyone will be using.

I wait to be proved wrong but I still don't think DBD::ODBC is doing anything
wrong here.

Martin 

--
Martin J. Evans
Easysoft Ltd, UK
Development
On 19-Oct-2004 Moosmann, James wrote:
> Ooops.. here is the link:
> 
> http://support.microsoft.com/default.aspx?scid=kb;en-us;140696
> 
> Tite: INF: RAISERROR, PRINT, and the MS SQL Server ODBC Driver
> 
> -----Original Message-----
> From: Moosmann, James 
> Sent: Tuesday, October 19, 2004 4:54 PM
> To: 'dan.horne@apn.co.nz'
> Cc: dbi-users@perl.org
> Subject: RE: Invalid cursor state when using PRINT in MSSQL
> 
> 
> I tried that... didn't work.
> 
> I found this MS article on the problem:  I kind of suspect it is in how
> DBD-ODBC implements its calls SQLExecDirect() and the subsequent call to
> SQLError().  The article seems to imply that the timing and order of the
> call to SQLError is VERY important in statements that return multiple result
> sets.  It would explain why things work for a single PRINT working as it
> should ( which is to raise error 01000 ).  DBD-ODBC apparently doesn't
> follow the necessary logic inside when these EXCEPTIONS occur in batch
> results.  Perhaps the author of DBD-ODBC can decode this article and
> implement it.  I also have a hunch that the fix for this would probably fix
> the same error you get when you try to do something like
> 
> use Database
> select * from ...
> 
> as you get the correct message back 'Changed database context to 'Database'
> and then you get 'Invalid cursor state'.
> 
> I looked through the ODBC.c file briefly... I just don't know enough about
> the code yet to incorporate this information.
> 
> 
> James
> 
> -----Original Message-----
> From: dan.horne@apn.co.nz [mailto:dan.horne@apn.co.nz]
> Sent: Tuesday, October 19, 2004 4:34 PM
> To: Moosmann, James
> Cc: dbi-users@perl.org
> Subject: RE: Invalid cursor state when using PRINT in MSSQL
> 
> 
> 
> James 
> 
> I'm an Oracle not a Sybase or SQL Server person, but I believe that you can
> connect to SQL Server using Free TDS and DBD::Sybase
Loading