RE: Invalid cursor state when using PRINT in MSSQL --- TRACE AND SCRIPT ENCLOSED

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

  • Next message: James Moosmann: "RE: Invalid cursor state when using PRINT in MSSQL --- TRACE AND SCRIPT ENCLOSED" 
    Date: Tue, 19 Oct 2004 17:47:59 +0100 (BST)
To: dbi-users@perl.org

    James,

    CC'ing dbi-user since this is where you posted first.

    Basically you are stuffed. I analysed your log and did the same thing in
    ODBCTest which used to come with MDAC and it fails in the same way:

    SQLPrepare(print 's' select count(*) from table print 'e')
    SQLNumResultCols()
      returns SQL_ERROR and invalid cursor state.

    so, I'm afraid, there is no bug in DBD::ODBC and the problem is in the MS SQL
    Server driver. Interestingly, our ODBC-ODBC Bridge makes you code almost work
    because it inserts a call to SQLNumParams between the SQLPrepare and the
    SQLNumResultCols and that puts the MS SQL Server Driver into a different
    state. I say "almost" because it does appear to hightlight another bug in the
    MS SQL Server driver later on where SQLMoreResults returns SQL_NO_DATA but a
    later call to SQLNumResultCols returns 1 column and this persuades DBD::ODBC
    there is a column and it fails on a call to SQLDescribeCol (because
    SQLNumResultCols lied and there is no result-set).

    Your only choice is to take the prints out.

    Martin 

    --
Martin J. Evans
Easysoft Ltd, UK
Development
On 19-Oct-2004 Moosmann, James wrote:
> Enclosed is the full trace, and my Perl script:
> 
> I downloaded the CPAN 1.45 DBI and 1.11 ODBC, built and installed them.  I
> still get the error, perhaps I am just not handling the exception it raises
> correctly?
> 
> Any help would be greatly appreciated!  
> 
> -----BEGIN TRACE-----
<snipped>
> ----END TACE----
> 
> --- BEGIN PERL SCRIPT ---
> 
>#!/perl/bin/perl -w
> use DBI;
> $dbh =  DBI->connect("dbi:ODBC:CMBS_TMP", '','', { PrintError => 0,
>               RaiseError => 1,
>               LongReadLen => 65536,
>               odbc_async_exec => 1,
>               odbc_err_handler => sub {
>                                         my ($state, $msg) = @_;
>                                         # Strip out all of the driver ID
> stuff
>                                         $msg =~ s/^(\[[\w\s]*\])+//;
>                                         my $err_text .= $msg."\n";
>                                       print "Err($state):",$err_text;
>                                         return 1;
>                                        }
>             }
> );
> 
> 
> $command = qq%
> PRINT 'START'
> select count(*) from APiwb_index_cusip
> PRINT 'END'
> 
> %;
> 
> 
> $sth = $dbh->prepare($command);
>     die $DBI::errstr unless $sth;
> 
> my $rc = $sth->execute();  #  This is the 'do it'.....
> 
> 
> do {
> 
> 
> 
>     my @row;
>     while (@row = $sth->fetchrow_array()) {
>           print join(",", @row), "\n";
>     }
> 
> 
>  
> } while ($sth->{odbc_more_results});
> 
> $dbh->disconnect;
  • Next message: James Moosmann: "RE: Invalid cursor state when using PRINT in MSSQL --- TRACE AND SCRIPT ENCLOSED"