Running this through unixODBC's isql program using our ODBC-ODBC Bridge to MS
SQL Server I get:
SQL> print 'start' select 1
[01000][unixODBC][NetConn: 0147c638][Microsoft][ODBC SQL Server Driver][SQL
Server]start
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
+------------+
| |
+------------+
| 1 |
+------------+
1 rows affected
1 rows returned
SQL>
You don't see the second print as a diag because isql does not use
SQLMoreResults.
So it would appear the SQLExecute returns SQL_SUCCESS_WITH_INFO and the
diagnostic is the string in the print statement. This sounds fine to me.
It works.
Running your Perl code to my SQL Server with my DSN etc I get:
[EMAIL PROTECTED] /tmp]$ ./invalid_cursor.pl
1000
In ODBC terms what happens for me is (some stuff omitted):
SQLConnect
SQLSetConnectOption(SQL_ATTR_AUTOCOMMIT)
SQLPrepare(PRINT 'starting select'
select count(*) from bench_char
PRINT 'finished'
= SQL_SUCCESS
SQLExecute returns SQL_SUCCCESS_WITH_INFO
Driver Manager calls SQLError to get diagnostic which is the print
SQLRowCount returns -1 rows
SQLMoreResults = SQL_SUCCESS
SQLNumResultCols returns 1 column
Various SQLDescribeCol and SQLColAttrobutes calls
SQLBindCol(as SQL_CHAR buffer length = 12)
* 2 (interesting!!!!)
SQLFetch = SQL_SUCCESS
SQLFetch = SQL_NO_DATA
SQLMoreResults = SQL_SUCCESS_WITH_INFO
and diag = "finished"
SQLMoreResults = SQL_NO_DATA
Just as I'd expect.
I am using (the latest I believe):
[EMAIL PROTECTED] /tmp]$ perl -e 'use DBD::ODBC; print $DBD::ODBC::VERSION;'
1.11
[EMAIL PROTECTED] /tmp]$ perl -e 'use DBI;print $DBI::VERSION;'
1.45
although I'm running on UNIX but still accessing MS SQL Server.
You go try turning on ODBC tracing in the driver manager, repeat and post the
trace (although it may be quite large in which case I'll take a look at it if
you send it to me). To turn on ODBC tracing, go to the ODBC administrator and
look on the trace tab.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
On 19-Oct-2004 Moosmann, James wrote:
> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 18, 2004 11:47 PM
> To: Moosmann, James
> Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
> 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" <[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
