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 =3D DBI->connect("my_sqlserver_database","myuser","mypass");


$sql =3D "create procedure test_raiserror1
as begin
raiserror('my error',16,1) with nowait
end";
$dbh->do($sql) || die $DBD::errstr;

$sql =3D "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 =3D "test_raiserror1";
print "test_raiserror1:\n";
$rows =3D $dbh->do($sql);
print "rows: $rows\n";
print "errstr: $DBI::errstr\n\n";

#this one does not
$sql =3D "test_raiserror2";
print "test_raiserror2:\n";
$rows =3D $dbh->do($sql);
print "rows: $rows\n";
print "errstr: $DBI::errstr\n\n";


$sql =3D "drop procedure test_raiserror1";
$dbh->do($sql) || die $DBD::errstr;

$sql =3D "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

RE: [dbi] error string being lost in DBD::ODBC

Adam,

Have you tried:

odbc_async_exec
Allow asynchronous execution of queries. Right now,
this causes a spin-loop (with a small "sleep") until
the sql is complete. This is useful, however, if you
want the error handling and asynchronous messages (see
the err_handler) below. See t/20SQLServer.t for an
example of this.

setting this (from DBD::ODBC pod).

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development


On 01-Aug-2005 Mitchell, Adam R wrote:
> 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

RE: error string being lost in DBD::ODBC

=20
Hi All,

I'm following up on this problem since it's now my problem.=20

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; $|=3D1;
select STDERR; $|=3D1;


my $dbh =3D DBI->connect("dbi:ODBC:mysqlserver","myuser","mypassword");
$dbh->{odbc_async_exec} =3D 1;
$dbh->{LongTruncOk} =3D 0;
$dbh->{LongReadLen} =3D 2000000;
$dbh->{PrintError} =3D 0;

$sql =3D "drop procedure test_raiserror1";
$dbh->do($sql) || warn $DBD::errstr;

$sql =3D "drop procedure test_raiserror2";
$dbh->do($sql) || warn $DBD::errstr;

$sql =3D "create procedure test_raiserror1
as begin
raiserror('my error',16,1) with nowait
end";
$dbh->do($sql) || die $DBD::errstr;

$sql =3D "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=20
$sql =3D "test_raiserror1";=20
print "\n\ntest_raiserror1 - usually works\n";=20
$rows =3D $dbh->do($sql);=20
print "rows: $rows\n";=20
print "errstr: $DBI::errstr\n\n";

#this one does not
$sql =3D "test_raiserror2";
print "\n\ntest_raiserror2 - usually doesnt work\n";
$sth =3D $dbh->prepare($sql);
warn $DBI::errstr if $DBI::errstr;
=20
$rc =3D $sth->execute();
warn $DBI::errstr if $DBI::errstr;
#$rows =3D $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:=20
errstr: [Microsoft][ODBC SQL Server Driver][SQL Server]my error
(SQL-42000)(DBD: Execute immediate failed err=3D-1)



test_raiserror2 - usually doesnt work
rows:=20
errstr:=20

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 =3D DBI->connect("my_sqlserver_database","myuser","mypass");


$sql =3D "create procedure test_raiserror1
as begin
raiserror('my error',16,1) with nowait
end";
$dbh->do($sql) || die $DBD::errstr;

$sql =3D "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 =3D
"test_raiserror1"; print "test_raiserror1:\n"; $rows =3D $dbh->do($sql);
print "rows: $rows\n"; print "errstr: $DBI::errstr\n\n";

#this one does not
$sql =3D "test_raiserror2";
print "test_raiserror2:\n";
$rows =3D $dbh->do($sql);
print "rows: $rows\n";
print "errstr: $DBI::errstr\n\n";


$sql =3D "drop procedure test_raiserror1";
$dbh->do($sql) || die $DBD::errstr;

$sql =3D "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

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:Louise.Mitchell@pnl.gov]=20
> Sent: Friday, August 26, 2005 4:31 PM
> To: dbi-users@perl.org
> Subject: RE: error string being lost in DBD::ODBC
>=20
>=20
> =20
> Hi All,
>=20
> I'm following up on this problem since it's now my problem.=20
>=20
> Does anyone have any ideas? I'm butting my head against a=20
> wall on this one.
>=20
> Thanks,
> Louise Mitchell
> **************************************
>=20
> The most recent code below:
>=20
> #!d:\apps\perl\bin\perl.exe
>=20
> use DBI;
> use DBD::ODBC;
>=20
> select STDOUT; $|=3D1;
> select STDERR; $|=3D1;
>=20
>=20
> my $dbh =3D =
DBI->connect("dbi:ODBC:mysqlserver","myuser","mypassword");
> $dbh->{odbc_async_exec} =3D 1;
> $dbh->{LongTruncOk} =3D 0;
> $dbh->{LongReadLen} =3D 2000000;
> $dbh->{PrintError} =3D 0;
>=20
> $sql =3D "drop procedure test_raiserror1";
> $dbh->do($sql) || warn $DBD::errstr;
>=20
> $sql =3D "drop procedure test_raiserror2";
> $dbh->do($sql) || warn $DBD::errstr;
>=20
> $sql =3D "create procedure test_raiserror1
> as begin
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> $sql =3D "create procedure test_raiserror2
> as begin
> select (1)
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> print "created\n\n";
>=20
>=20
> #this bit correctly returns "my error" into dbi::errstr=20
> $sql =3D "test_raiserror1";=20
> print "\n\ntest_raiserror1 - usually works\n";=20
> $rows =3D $dbh->do($sql);=20
> print "rows: $rows\n";=20
> print "errstr: $DBI::errstr\n\n";
>=20
> #this one does not
> $sql =3D "test_raiserror2";
> print "\n\ntest_raiserror2 - usually doesnt work\n";
> $sth =3D $dbh->prepare($sql);
> warn $DBI::errstr if $DBI::errstr;
> =20
> $rc =3D $sth->execute();
> warn $DBI::errstr if $DBI::errstr;
> #$rows =3D $dbh->do($sql);
> print "rows: $rows\n";
> print "errstr: $DBI::errstr\n\n";
> print "dropped\n\n";
>=20
> ***************************
> RESULTS BELOW
> ***************************
> created
>=20
>=20
>=20
> test_raiserror1 - usually works
> rows:=20
> errstr: [Microsoft][ODBC SQL Server Driver][SQL Server]my error
> (SQL-42000)(DBD: Execute immediate failed err=3D-1)
>=20
>=20
>=20
> test_raiserror2 - usually doesnt work
> rows:=20
> errstr:=20
>=20
> dropped
>=20
>=20
>=20
>=20
> -----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
>=20
> I'm having a problem where the error string from a raiserror=20
> (inside a stored procedure) is disappearing. I've reduced it=20
> to the fact that it only occurs if there is some part of the=20
> stored procedure that returns rows (0 or more).
>=20
> Here is a code snippet that illustrates the problem:
>=20
>=20
>=20
> use DBI;
> use DBD::ODBC;
>=20
>=20
> my $dbh =3D DBI->connect("my_sqlserver_database","myuser","mypass");
>=20
>=20
> $sql =3D "create procedure test_raiserror1
> as begin
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> $sql =3D "create procedure test_raiserror2
> as begin
> select (1)
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> print "created\n\n";
>=20
>=20
> #this bit correctly returns "my error" into dbi::errstr $sql=20
> =3D "test_raiserror1"; print "test_raiserror1:\n"; $rows =
> $dbh->do($sql); print "rows: $rows\n"; print "errstr:=20
> $DBI::errstr\n\n";
>=20
> #this one does not
> $sql =3D "test_raiserror2";
> print "test_raiserror2:\n";
> $rows =3D $dbh->do($sql);
> print "rows: $rows\n";
> print "errstr: $DBI::errstr\n\n";
>=20
>=20
> $sql =3D "drop procedure test_raiserror1";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> $sql =3D "drop procedure test_raiserror2";
> $dbh->do($sql) || die $DBD::errstr;
>=20
>=20
> print "dropped\n\n";
>=20
>=20
>=20
>=20
>=20
> Note how the first one returns the error, while the second=20
> one simply returns -1.
>=20
> I think it may have to do with the string returned from sql=20
> server. Here is the difference in the return string when they=20
> are executed in ms sql query analyzer:
>=20
>=20
> test1:
> Server: Msg 50000, Level 16, State 1, Procedure=20
> test_raiserror1, Line 4 my error
>=20
>=20
> test2:
> (1 row(s) affected)
>=20
> Server: Msg 50000, Level 16, State 1, Procedure=20
> test_raiserror1, Line 4 my error
>=20
>=20
>=20
> Does anyone know how I can get at the error string?
>=20
> Thanks,
> Adam Mitchell
>=20

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 =3D "test_raiserror2";
print "\n\ntest_raiserror2 - usually doesnt work\n";
$sth =3D $dbh->prepare($sql);
warn $DBI::errstr if $DBI::errstr;
=20
$rc =3D $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:=20
'1'
'1'
2 rows (-1: [Microsoft][ODBC SQL Server Driver]Function sequence error
(SQL-HY010)(DBD: st_fetch/SQLFetch err=3D-1))
errstr: [Microsoft][ODBC SQL Server Driver]Function sequence error
(SQL-HY010)(DBD: st_fetch/SQLFetch err=3D-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 =3D $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:jurlwin@urlwincc.com]=20
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:Louise.Mitchell@pnl.gov]
> Sent: Friday, August 26, 2005 4:31 PM
> To: dbi-users@perl.org
> Subject: RE: error string being lost in DBD::ODBC
>=20
>=20
> =20
> Hi All,
>=20
> I'm following up on this problem since it's now my problem.=20
>=20
> Does anyone have any ideas? I'm butting my head against a wall on=20
> this one.
>=20
> Thanks,
> Louise Mitchell
> **************************************
>=20
> The most recent code below:
>=20
> #!d:\apps\perl\bin\perl.exe
>=20
> use DBI;
> use DBD::ODBC;
>=20
> select STDOUT; $|=3D1;
> select STDERR; $|=3D1;
>=20
>=20
> my $dbh =3D =
DBI->connect("dbi:ODBC:mysqlserver","myuser","mypassword");
> $dbh->{odbc_async_exec} =3D 1;
> $dbh->{LongTruncOk} =3D 0;
> $dbh->{LongReadLen} =3D 2000000;
> $dbh->{PrintError} =3D 0;
>=20
> $sql =3D "drop procedure test_raiserror1";
> $dbh->do($sql) || warn $DBD::errstr;
>=20
> $sql =3D "drop procedure test_raiserror2";
> $dbh->do($sql) || warn $DBD::errstr;
>=20
> $sql =3D "create procedure test_raiserror1
> as begin
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> $sql =3D "create procedure test_raiserror2
> as begin
> select (1)
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> print "created\n\n";
>=20
>=20
> #this bit correctly returns "my error" into dbi::errstr $sql =
> "test_raiserror1"; print "\n\ntest_raiserror1 - usually works\n";=20
> $rows =3D $dbh->do($sql); print "rows: $rows\n"; print "errstr:=20
> $DBI::errstr\n\n";
>=20
> #this one does not
> $sql =3D "test_raiserror2";
> print "\n\ntest_raiserror2 - usually doesnt work\n"; $sth =
> $dbh->prepare($sql);
> warn $DBI::errstr if $DBI::errstr;
> =20
> $rc =3D $sth->execute();
> warn $DBI::errstr if $DBI::errstr;
> #$rows =3D $dbh->do($sql);
> print "rows: $rows\n";
> print "errstr: $DBI::errstr\n\n";
> print "dropped\n\n";
>=20
> ***************************
> RESULTS BELOW
> ***************************
> created
>=20
>=20
>=20
> test_raiserror1 - usually works
> rows:=20
> errstr: [Microsoft][ODBC SQL Server Driver][SQL Server]my error
> (SQL-42000)(DBD: Execute immediate failed err=3D-1)
>=20
>=20
>=20
> test_raiserror2 - usually doesnt work
> rows:=20
> errstr:=20
>=20
> dropped
>=20
>=20
>=20
>=20
> -----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
>=20
> 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=20
> it only occurs if there is some part of the stored procedure that=20
> returns rows (0 or more).
>=20
> Here is a code snippet that illustrates the problem:
>=20
>=20
>=20
> use DBI;
> use DBD::ODBC;
>=20
>=20
> my $dbh =3D DBI->connect("my_sqlserver_database","myuser","mypass");
>=20
>=20
> $sql =3D "create procedure test_raiserror1
> as begin
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> $sql =3D "create procedure test_raiserror2
> as begin
> select (1)
> raiserror('my error',16,1) with nowait
> end";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> print "created\n\n";
>=20
>=20
> #this bit correctly returns "my error" into dbi::errstr $sql =
> "test_raiserror1"; print "test_raiserror1:\n"; $rows =3D =
$dbh->do($sql);

> print "rows: $rows\n"; print "errstr:
> $DBI::errstr\n\n";
>=20
> #this one does not
> $sql =3D "test_raiserror2";
> print "test_raiserror2:\n";
> $rows =3D $dbh->do($sql);
> print "rows: $rows\n";
> print "errstr: $DBI::errstr\n\n";
>=20
>=20
> $sql =3D "drop procedure test_raiserror1";
> $dbh->do($sql) || die $DBD::errstr;
>=20
> $sql =3D "drop procedure test_raiserror2";
> $dbh->do($sql) || die $DBD::errstr;
>=20
>=20
> print "dropped\n\n";
>=20
>=20
>=20
>=20
>=20
> Note how the first one returns the error, while the second one simply=20
> returns -1.
>=20
> I think it may have to do with the string returned from sql server.=20
> Here is the difference in the return string when they are executed in=20
> ms sql query analyzer:
>=20
>=20
> test1:
> Server: Msg 50000, Level 16, State 1, Procedure test_raiserror1, Line=20
> 4 my error
>=20
>=20
> test2:
> (1 row(s) affected)
>=20
> Server: Msg 50000, Level 16, State 1, Procedure test_raiserror1, Line=20
> 4 my error
>=20
>=20
>=20
> Does anyone know how I can get at the error string?
>=20
> Thanks,
> Adam Mitchell
>=20

RE: error string being lost in DBD::ODBC

Did you try perldoc DBD::ODBC and look for more_results? =20

$sth->{odbc_more_results}...

Jeff

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