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
>
