James
if you think it's a DBD::ODBC issue, why not use DBD::Sybase instead?
Dan
"Moosmann, James" <[EMAIL PROTECTED]>
19/10/2004 16:17
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, (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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Monday, October 18, 2004 9:08 PM
To: Moosmann, James
Cc: '[EMAIL PROTECTED]'
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
[EMAIL PROTECTED]
____________________________
"Moosmann, James" <[EMAIL PROTECTED]>
10/18/2004 06:46 PM
To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>
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.
********************************************************************************
