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 >
