RE: Invalid cursor state when using PRINT in MSSQL

• Previous message: Dan Horne: "RE: Invalid cursor state when using PRINT in MSSQL"
• Maybe in reply to: James Moosmann: "Invalid cursor state when using PRINT in MSSQL"
• Next in thread: Martin J. Evans: "RE: Invalid cursor state when using PRINT in MSSQL"
• Reply: Martin J. Evans: "RE: Invalid cursor state when using PRINT in MSSQL"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 19 Oct 2004 00:00:54 -0400
To: "'dan.horne@apn.co.nz'" <dan.horne@apn.co.nz>

Dan,
The server and all the SP's I am running this on is MSSQL and not Sybase. I
have another DSN to a Sybase server and tested this behavior on that one,
and reported the results here. I was hoping to find someone who knew the
ODBC driver guts well enough to know what the problem was or knew a
solution.
 
I personally don't use PRINT in my SQL and before I go ask these other
programmers to stop doing what works everywhere else but in DBI::ODBC I was
hoping to find the solution. Perhaps this is just broken here and it cannot
be fixed. So far everyone acknowledges the problem, but no one knows why it
doesn't work or how to fix it. If this is the wrong place to discuss this,
then I apologize and will try to find the correct forum.
 
Thanks.

 -----Original Message-----
From: dan.horne@apn.co.nz [mailto:dan.horne@apn.co.nz]
Sent: Monday, October 18, 2004 11:47 PM
To: Moosmann, James
Cc: 'dbi-users@perl.org'; 'Jeffrey.Seger@fairchildsemi.com'
Subject: RE: Invalid cursor state when using PRINT in MSSQL

James

if you think it's a DBD::ODBC issue, why not use DBD::Sybase instead?

Dan

        "Moosmann, James" <james.moosmann@bankofamerica.com>

19/10/2004 16:17

        
        To: "'Jeffrey.Seger@fairchildsemi.com'"
<Jeffrey.Seger@fairchildsemi.com>
        cc: "'dbi-users@perl.org'" <dbi-users@perl.org>, (bcc: Dan
Horne/IT/AKLWHG/WHNZ)
        Subject: RE: Invalid cursor state when using PRINT in MSSQL

Jeff,

It is VERY VALID syntax for both MSSQL ... AND.. Sybase. I have been coding
in Perl and DBI for several years now and I am NOT trying to use the perl
print function. Here is the skinny on PRINT direct from our DBA for MSSQL
Server PRINT :

The PRINT statement takes either one character or a Unicode string
expression as a parameter. It returns the string as a message to the
application. The message is returned as an informational error in ADO, OLE
DB, and ODBC applications. SQLSTATE is set to 01000, the native error is set
to 0, and the error message string is set to the character string specified
in the PRINT statement. The string is returned to the message handler
call-back function in DB-Library applications.

The example I gave is just minimal (...sigh) to display the error, and I am
not sure if you understand the question.

I asked this question in Perl Monks and got this informative reply:

PRINT '...begin' select * from foo.bar.mytable

SQLExecDirect:

In: hstmt = 0x00991FB8, szSqlStr = "", cbSqlStr = -3

Return: SQL_SUCCESS_WITH_INFO=1

stmt: szSqlState = "01000", *pfNativeError = 0, *pcbErrorMsg = 55,
*ColumnNumber = -1, *RowNumber

+ = 1

   MessageText = "[Microsoft][ODBC SQL Server Driver][SQL Server]...begin"

Get Data All:

   -1 rows affected by INSERT/UPDATE/DELETE or other statement.

"col1", "col2", "col3"

1, "row1", "row1"

2, "row2", "row2"

2 rows fetched from 3 columns.

MS SQL Server help also mentions that you have to call SQLError right after
statement is executed.

The timing of calling SQLError is critical when output from PRINT or
RAISERROR statements are included in a result set. The call to SQLError to
retrieve the PRINT or RAISERROR output must be made immediately after the
statement that receives SQL_ERROR or SQL_SUCCESS_WITH_INFO. This is
straightforward when only a single SQL statement is executed, as in the
examples above. In these cases, the call to SQLExecDirect or SQLExecute
returns SQL_ERROR or SQL_SUCCESS_WITH_INFO and SQLError can then be called.
It is less straightforward when coding loops to handle the output of a batch
of SQL statements or when executing SQL Server stored procedures.

***
I have researched this and this looks like a ODBC driver issue. You get the
first print and the select if you use Sybase, but you don't get the ending
PRINT. We have some VERY long running an complex queries and the PRINTS are
embedded in STORED PROCEDURES. I am trying to use the output of the PRINTS
to give feedback to the user when the queries will finish.

-----Original Message-----
From: Jeffrey.Seger@fairchildsemi.com
[mailto:Jeffrey.Seger@fairchildsemi.com]
Sent: Monday, October 18, 2004 9:08 PM
To: Moosmann, James
Cc: 'dbi-users@perl.org'
Subject: Re: Invalid cursor state when using PRINT in MSSQL

my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;

is not valid sql.

try this:

my $sql = qq#
select count(*) from anytable
#;

my $sth = $dbh->prepare( $sql );

print 'starting select';
my $rv = $sth->execute();

while ( my $hr = $sth->fetchrow_hashref ){

  print $$hr{$_}, "\n" for ( keys %$hr );
  print "Err: ", $dbh->errstr, "\n" if $dbh->errstr;

}
print 'finished';

_________________________
Jeff Seger
Fairchild Semiconductor
jeffrey.seger@fairchildsemi.com
____________________________

                "Moosmann, James" <james.moosmann@bankofamerica.com>

10/18/2004 06:46 PM

       
       To: "'dbi-users@perl.org'" <dbi-users@perl.org>
       cc:
       Subject: Invalid cursor state when using PRINT in MSSQL

I am using DBI and DBD-ODBC to connect to an MSSQL Server and this query
gets an invalid cursor state:

PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'

If I only run the PRINT command... it works.

If I run the full query against a sybase server... only the top select
prints.

I am using:

Win32 AS-5.8.0 Multithread build 806 DBI(1.43)DBD::ODBC(1.07) MSSQL Server I
get a invalid cursor state when I run any SQL with a PRINT statement at the
beginning of any SQL statement.

The error:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid
cursor state (SQL-24000)(DB
D: dbd_describe/SQLNumResultCols err=-1) at H:\progs\dbi_test.pl line 10.

Example code:
#c:/perl/bin/perl

use DBI;

my $dbh = DBI->connect( 'dbi:ODBC:TEST_DSN', '','', {RaiseError=> 1} );

my $sql = qq#
PRINT 'starting select'
select count(*) from anytable
PRINT 'finished'
#;

my $sth = $dbh->prepare( $sql );

my $rv = $sth->execute();

while ( my $hr = $sth->fetchrow_hashref ){

  print $$hr{$_}, "\n" for ( keys %$hr );
  print "Err: ", $dbh->errstr, "\n" if $dbh->errstr;

}

Does this have anything to do with how SQL_SUCCESS_WITH_INFO is being
handled by the driver?

How do I get all the results..

Thanks!

James

****************************************************************************
****
NOTICE
This email and any attachments are confidential. They may contain privileged

information or copyright material. If you are not an intended recipient, you

should not read, copy, use or disclose the contents without authorisation as

we request you contact us as once by return email. Please then delete the
email and any attachments from your system. We do not accept liability in
connection with computer viruses, data corruption, delay, interruption,
unauthorised access or unauthorised amendment. Any views expressed in this
email and any attachments do not necessarily reflect the views of the
company.
****************************************************************************
****

• Previous message: Dan Horne: "RE: Invalid cursor state when using PRINT in MSSQL"
• Maybe in reply to: James Moosmann: "Invalid cursor state when using PRINT in MSSQL"
• Next in thread: Martin J. Evans: "RE: Invalid cursor state when using PRINT in MSSQL"
• Reply: Martin J. Evans: "RE: Invalid cursor state when using PRINT in MSSQL"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]