Did you try perldoc DBD::ODBC and look for more_results?
$sth->{odbc_more_results}...
Jeff
> -----Original Message-----
> From: Mitchell, Louise M [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 30, 2005 11:03 AM
> To: Jeff Urlwin; dbi-users@perl.org
> Subject: RE: error string being lost in DBD::ODBC
>
>
> I couldn't find a reference to $sth->more_results in the
> documentation, but I did find a reference to DBI::dum_results($sth).
>
> If I add that to my code (snippet below):
>
> #this one does not
> $sql = "test_raiserror2";
> print "\n\ntest_raiserror2 - usually doesnt work\n";
> $sth = $dbh->prepare($sql);
> warn $DBI::errstr if $DBI::errstr;
>
> $rc = $sth->execute();
> warn $DBI::errstr if $DBI::errstr;
> print "** dumping results: \n";
> DBI::dump_results($sth);
>
> print "errstr: $DBI::errstr\n\n";
> print "dropped\n\n";
>
>
> then I get the following results (errstr is set, but it's a
> weird error)
>
> test_raiserror2 - usually doesnt work
> ** dumping results:
> '1'
> '1'
> 2 rows (-1: [Microsoft][ODBC SQL Server Driver]Function sequence error
> (SQL-HY010)(DBD: st_fetch/SQLFetch err=-1))
> errstr: [Microsoft][ODBC SQL Server Driver]Function sequence error
> (SQL-HY010)(DBD: st_fetch/SQLFetch err=-1)
>
> dropped
>
> The error I expected is a raiserror in the sproc (sproc code below).
>
> Is the telling me that I am executing something wrong via the
> DBI? Or that my sproc has an issue (it runs well in the SQL
> 2000 Query Analyzer).
>
> I also tried adding a block of code to test for more_results,
> guessing at how to do it. Code snippet below. The results
> were the same as listed below in my first post.
>
> #if ( $sth->more_results )
> #{
> # while ( @row_ary = $sth->fetchrow_array )
> # {
> # if ( $DBI::errstr )
> # {
> # print "err: $DBI::errstr \n";
> # }
> # else
> # {
> # print "**row: @row_ary \n";
> # }
> # }
> #}
>
> Thanks for any help,
> Louise Mitchell
>
> -----Original Message-----
> From: Jeff Urlwin [mailto:[EMAIL PROTECTED]
> Sent: Saturday, August 27, 2005 8:54 AM
> To: Mitchell, Louise M; dbi-users@perl.org
> Subject: RE: error string being lost in DBD::ODBC
>
> Try checking your second $sth for more_results. It may be
> affecting the outcome.
>
> Regards,
>
> Jeff
>
> > -----Original Message-----
> > From: Mitchell, Louise M [mailto:[EMAIL PROTECTED]
> > Sent: Friday, August 26, 2005 4:31 PM
> > To: dbi-users@perl.org
> > Subject: RE: error string being lost in DBD::ODBC
> >
> >
> >
> > Hi All,
> >
> > I'm following up on this problem since it's now my problem.
> >
> > Does anyone have any ideas? I'm butting my head against a wall on
> > this one.
> >
> > Thanks,
> > Louise Mitchell
> > **************************************
> >
> > The most recent code below:
> >
> > #!d:\apps\perl\bin\perl.exe
> >
> > use DBI;
> > use DBD::ODBC;
> >
> > select STDOUT; $|=1;
> > select STDERR; $|=1;
> >
> >
> > my $dbh =
> DBI->connect("dbi:ODBC:mysqlserver","myuser","mypassword");
> > $dbh->{odbc_async_exec} = 1;
> > $dbh->{LongTruncOk} = 0;
> > $dbh->{LongReadLen} = 2000000;
> > $dbh->{PrintError} = 0;
> >
> > $sql = "drop procedure test_raiserror1";
> > $dbh->do($sql) || warn $DBD::errstr;
> >
> > $sql = "drop procedure test_raiserror2";
> > $dbh->do($sql) || warn $DBD::errstr;
> >
> > $sql = "create procedure test_raiserror1
> > as begin
> > raiserror('my error',16,1) with nowait
> > end";
> > $dbh->do($sql) || die $DBD::errstr;
> >
> > $sql = "create procedure test_raiserror2
> > as begin
> > select (1)
> > raiserror('my error',16,1) with nowait
> > end";
> > $dbh->do($sql) || die $DBD::errstr;
> >
> > print "created\n\n";
> >
> >
> > #this bit correctly returns "my error" into dbi::errstr $sql =
> > "test_raiserror1"; print "\n\ntest_raiserror1 - usually works\n";
> > $rows = $dbh->do($sql); print "rows: $rows\n"; print "errstr:
> > $DBI::errstr\n\n";
> >
> > #this one does not
> > $sql = "test_raiserror2";
> > print "\n\ntest_raiserror2 - usually doesnt work\n"; $sth =
> > $dbh->prepare($sql);
> > warn $DBI::errstr if $DBI::errstr;
> >
> > $rc = $sth->execute();
> > warn $DBI::errstr if $DBI::errstr;
> > #$rows = $dbh->do($sql);
> > print "rows: $rows\n";
> > print "errstr: $DBI::errstr\n\n";
> > print "dropped\n\n";
> >
> > ***************************
> > RESULTS BELOW
> > ***************************
> > created
> >
> >
> >
> > test_raiserror1 - usually works
> > rows:
> > errstr: [Microsoft][ODBC SQL Server Driver][SQL Server]my error
> > (SQL-42000)(DBD: Execute immediate failed err=-1)
> >
> >
> >
> > test_raiserror2 - usually doesnt work
> > rows:
> > errstr:
> >
> > dropped
> >
> >
> >
> >
> > -----Original Message-----
> > From: Mitchell, Adam R
> > Sent: Monday, August 01, 2005 3:50 PM
> > To: 'dbi-users@perl.org'
> > Subject: error string being lost in DBD::ODBC
> >
> > I'm having a problem where the error string from a
> raiserror (inside a
>
> > stored procedure) is disappearing. I've reduced it to the fact that
> > it only occurs if there is some part of the stored procedure that
> > returns rows (0 or more).
> >
> > Here is a code snippet that illustrates the problem:
> >
> >
> >
> > use DBI;
> > use DBD::ODBC;
> >
> >
> > my $dbh = DBI->connect("my_sqlserver_database","myuser","mypass");
> >
> >
> > $sql = "create procedure test_raiserror1
> > as begin
> > raiserror('my error',16,1) with nowait
> > end";
> > $dbh->do($sql) || die $DBD::errstr;
> >
> > $sql = "create procedure test_raiserror2
> > as begin
> > select (1)
> > raiserror('my error',16,1) with nowait
> > end";
> > $dbh->do($sql) || die $DBD::errstr;
> >
> > print "created\n\n";
> >
> >
> > #this bit correctly returns "my error" into dbi::errstr $sql =
> > "test_raiserror1"; print "test_raiserror1:\n"; $rows =
> $dbh->do($sql);
>
> > print "rows: $rows\n"; print "errstr:
> > $DBI::errstr\n\n";
> >
> > #this one does not
> > $sql = "test_raiserror2";
> > print "test_raiserror2:\n";
> > $rows = $dbh->do($sql);
> > print "rows: $rows\n";
> > print "errstr: $DBI::errstr\n\n";
> >
> >
> > $sql = "drop procedure test_raiserror1";
> > $dbh->do($sql) || die $DBD::errstr;
> >
> > $sql = "drop procedure test_raiserror2";
> > $dbh->do($sql) || die $DBD::errstr;
> >
> >
> > print "dropped\n\n";
> >
> >
> >
> >
> >
> > Note how the first one returns the error, while the second
> one simply
> > returns -1.
> >
> > I think it may have to do with the string returned from sql server.
> > Here is the difference in the return string when they are
> executed in
> > ms sql query analyzer:
> >
> >
> > test1:
> > Server: Msg 50000, Level 16, State 1, Procedure
> test_raiserror1, Line
> > 4 my error
> >
> >
> > test2:
> > (1 row(s) affected)
> >
> > Server: Msg 50000, Level 16, State 1, Procedure
> test_raiserror1, Line
> > 4 my error
> >
> >
> >
> > Does anyone know how I can get at the error string?
> >
> > Thanks,
> > Adam Mitchell
> >
>
