Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Getting MSSQL message return on DBI

by shonorio (Hermit)
on Oct 30, 2005 at 10:11 UTC ( #503976=perlquestion: print w/replies, xml ) Need Help??

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;
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

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
    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
        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} );
        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 $@.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://503976]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (6)
As of 2020-11-24 09:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?