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
