RE: error string being lost in DBD::ODBC

[Mitchell, Louise M]

 
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