FreeTDS Development Group

["Lowden, James K" <james.k.lowden AT alliancebernstein.com>]

> From: ZIGLIO, Frediano, VF-IT
> Sent: Wednesday, June 28, 2006 8:23 AM
> 
> > I'm getting an invalid cursor state error trying to fetch data
> > from a stored procedure using prepare/execute from DBD::ODBC.
> > It looks like DBD::ODBC thinks there is more than one result set
> > and is trying to get column descriptors when there are no metadata.
> ...
> >     # call the stored procedure my $sth = $dbh->prepare(
$procedure);
> >     $sth->execute;
> 
> Do die means core or just an error that calls cleanly die functions
> ??

My mistake.  It dies fetching the "second" resultset.  Not a coredump;
an error from fetchrow_array().  

> > error.c:349:odbc_errs_reset error.c:349:odbc_errs_reset
> >       col  1: 'LatestInputsmDate' sqltype=TIMESTAMP,
ctype=SQL_C_CHAR,
> > maxlen=25, (dp = 0, cp = 0) dbd_describe failed, dbd_st_execute
> > #2...!
> > 
> 
> Mmmm... and why does dbd_describe fail?? Strange, isn't it?

Perhaps that "#2" indicates the second resultset (the return status)?  

> > error.c:349:odbc_errs_reset error.c:349:odbc_errs_reset
> >     dbd_describe sql 1084704768: num_fields=0 dbd_describe skipped
> >     (no result cols) (sql f1084704768)
> > MORE Results dbd_describe success...!
> > 
> > [DBD discovers there's no resultset (0 columns). An odd kind of
> > "success"!]
> > 
> 
> No problems, ODBC 3 can return resultset without columns...

Eh?  I understand a resultset with no *rows*.  What kind of "result" has
no columns?  How does one discover the metadata?  

> >        SQLFetch rc -1
> > dbd_error: err_rc=-1 rc=0 s/d/e: 1084704768/1084694528/1084712448
> > dbd_error: SQL-24000 (native 0): [unixODBC][FreeTDS][SQL
> > Server]Invalid cursor state (SQL-24000) dbd_error: err_rc=-1 rc=0
> > s/d/e: 0/1084694528/1084712448 dbd_error: err_rc=-1 rc=0 s/d/e:
> > 0/0/1084712448 st_fetch/SQLFetch error -1 recorded:
> > [unixODBC][FreeTDS][SQL Server]Invalid cursor state (SQL-24000)(DBD:
> > st_fetch/SQLFetch err=-1)
> 
> Here it seems we have (correctly) no data...  James, can you post
> jkl script source ?

Attached, and appended in case the attachment is stripped.  :-(

It's not a thing of beauty; it's a hack of the production script.  If
you set the DSN yourself, you can delete the "require"s and
production_init().

use DBI;
use Getopt::Std;
use strict;

unshift(@INC, '/usr/users/production/perl');

require 'datadate.pl';
require 'dbi_dsn.pl';
require 'production.pl';
require 'update_status.pl';

&production_init();

# detect command-line switches
my %opts;
Getopt::Std::getopts('U:P:t', \%opts);

# analyze parameters
my( $server
  , $db
  , $ascertainReadiness
  , $jobToPost
  ) = @ARGV;


# connect to SQL Server
my ($user, $password) = ( $opts{U}, $opts{P} );
my $dsn = dbi_dsn( $server, $db);
my $dbh = DBI->connect( $dsn, $user, $password, { PrintError => 0
                                                , RaiseError => 1
                                                , AutoCommit => 0
                                                , syb_flush_finish => 1
# avoid hang
                                                }
                      )
     or die $DBI::errstr;
$dbh->do( "use $db" );  # not done by dsn for ODBC datasources


    # call the stored procedure
print "Executing $ascertainReadiness...\n";
    my $sth = $dbh->prepare( $ascertainReadiness);
    $sth->execute or die $DBI::errstr;
    statementSuccess( $sth );
print "done.\n";
exit;

sub statementSuccess {
#-----------------------------------------------------------------------
--------
#   did the just-executed SQL statement or stored procedure return
#   success (non-zero) ?

my( $sth           # statement handle
  ) = @_;
#-----------------------------------------------------------------------
--------

# loop through all result sets from the statement, ending with the
returned value
my ( $stmtOutput
   , $stmtResult
   );
do {
    # loop through all rows in the current result set
    while( ($stmtOutput) = $sth->fetchrow_array())
        { 
                print $stmtOutput, $/;
        }
    } while $sth->{Active};  # while there are more result sets

return( $stmtResult);

}  # of statementSuccess

Attachment: jkl.pl
Description: jkl.pl