Getting MSSQL message return on DBI

shonorio has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks,

I need to get the result message of the query coded below to parse.

my $dbcon = DBI->connect ( 
   qq{dbi:ODBC:driver={SQL Server};Server=127.0.0.1;database=master;},
   qq{sa},
   qq{*********} ) 
   or die qq{Can't connect to database, erro : $DBI::errstr};

my $sth = $dbcon->prepare(q{
use [Northwind]
DECLARE @id  int, @indid int, @counter dec (15)
CREATE TABLE #Temp ( id int, indid int )
INSERT INTO #Temp
       SELECT  o.id, i.indid
       FROM      sysobjects AS o
       FULL JOIN sysindexes AS i
       ON        o.id      = i.id
       WHERE     o.xtype   = 'U'
SELECT @counter = COUNT(*) FROM #Temp
SET rowcount  1
WHILE ( @counter ) > 0
BEGIN
   SELECT @id = id, @indid = indid  FROM #Temp
   SET    @counter  = @counter -1
   DBCC SHOWCONTIG (@id, @indid)
   DELETE FROM #Temp WHERE id = @id and indid = @indid
END
SET rowcount 0
DROP TABLE #Temp}) || die "Can't prepare sql:\n$DBI::errstr\n";

$sth->execute();

print $DBI::errstr;
[download]

But even the SQL code running well on SQL Query Analyzer, I'm getting the error message DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid cursor state (SQL-24000)(DBD: dbd_describe/SQLNumResultCols err=-1).

I'd like your help on 2 way, first to help-me on this error message, and second in how can I get the result message of the query.

Thanks,

Solli Moreira Honorio
Sao Paulo - Brazil

Comment on Getting MSSQL message return on DBI Select or Download Code

Replies are listed 'Best First'.
Re: Getting MSSQL message return on DBI by JamesNC (Chaplain) on Oct 30, 2005 at 13:57 UTC
The reason you get this error is because of the statement "use Northwind" the solution: 1) execute that statement in a separate statement handle -- OR BETTER -- 2) use fully qualified statements ( which is the most correct and you should ALWAYS do ;-) Fully qualified means to do something like `select A.[Column Name] from [Database Name].[owner].[Table Name] A` [download] Most applications like SQL Query Analyzer parse the use statement out for you and that is why you might think it is ok to use. JamesNC	[reply] [d/l]
Re^2: Getting MSSQL message return on DBI by shonorio (Hermit) on Oct 30, 2005 at 14:37 UTC
You are correct, and I found at DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX or Linux to Microsoft SQL Server another way, using `$dbcon->{odbc_exec_direct} = 1;`. Now, I'm looking for a way to get the result of this code, that return by error message. Thank you Solli Moreira Honorio Sao Paulo - Brazil	[reply] [d/l]
Re^3: Getting MSSQL message return on DBI by JamesNC (Chaplain) on Oct 30, 2005 at 15:40 UTC
I am not sure I understand your question. But if you are wondering how to get the results of a statement handle with multiple result sets you should have code like: `my @rs; #result set do { while( my $hr = $sth->fetchrow_hashref ){ push @rs, $hr; } #process @rs (array of hashrefs in order the server returned them ) } while ( $sth->{odbc_more_results} );` [download] If your question is about the error still, then you need to read perldoc DBI and DBD::ODBC for more specifics on how errors are handled an some suggestions on methods to do that. There are some additional flags you can set in both the db handle such as { RaiseError=> 1 } and in the statement handle. In general a common error handle method is to wrap your calls in eval {}; and check $@.	[reply] [d/l]
Re^4: Getting MSSQL message return on DBI by shonorio (Hermit) on Oct 30, 2005 at 18:49 UTC

Back to Seekers of Perl Wisdom