DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this?

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

Om!

Problem:

my $dbh = DBI->connect("dbi:Sybase:server=$percdb", $user, $passwd,
                            {'RaiseError' => 1}
                        );
if ($DBI::errstr) {
    FATAL($DBI::errstr);
    exit 1;
}
else {
    DEBUG('Successfully connected to database!');
}

my $sth = $dbh->prepare($QUERY);
$sth->execute;
handle_db_error();
$result_hash = $sth->fetchall_hashref('ent_id');
handle_db_error();

sub handle_db_error { 
    if ($dbh->err) { 
        FATAL("Error: $DBI::errstr"); 
        exit 9;
    }
}
[download]

You see that I connect to the database and try to fetch all rows into a hash. It works most of the time and always from the shell (though not always when called from cron - strange!) Now my problem is that sometimes there is nothing in $sth and I get the following error: DBD::Sybase::st fetchall_hashref failed: Field 'ent_id' does not exist (not one of COL(1)) at /.../.../myscript line 75. As I do error checking, it looks like it just doesn't create an error. As is is a Select statement, I also cannot use the $sth->rows to check if there are any rows.

Any wisdom greatly appreciated!

Thanks, Sven

Comment on DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? Select or Download Code

Replies are listed 'Best First'.
Re: DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? by runrig (Abbot) on Jan 11, 2005 at 17:24 UTC
Perhaps you need to set `$dbh->{FetchHashKeyName} = 'NAME_lc';` (You should be able to tell by printing `@{$sth->{NAME}}` after the execute) (update: then again, you say it works most of the time, so I'm not sure this is the problem). Other comments: you set RaiseError on the connect (good for you), but check for errors on the next line. If there is an error on connect, the program will not reach the next line (for DBI 0.91 and later). The handle_db_error() function will never be called either (RaiseError causes the program to die with an error message). If you want special processing of errors, either use HandleError, or wrap all your DBI code in an eval block and check $@ afterward.	[reply] [d/l] [select]
Re^2: DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? by svenXY (Deacon) on Jan 12, 2005 at 07:46 UTC
Hi and thanks for the quick reply! Actually, I had not set RaiseError before to be able to handle errors myself via the handle_db_error() function (as I use log4perl). I was hoping setting RaiseError would eventually solve my problem, but it doesn't look like it does. Just dieing is not what I'm looking for ;-) I have the strange feeling that this driver sometimes doesn't completely work. I connect to a MSSQL database in the States and I'm in Germany. I have seen these errors where no error is reported a couple of times now. Actually, the starnghe thing is that the resultset is empty although it shouldn't be, but no error is generated. What I want to achieve at least is to find out if the resultset is empty before calling fetchall_hashref and assignning it to a variable. Will try with eval, but have so far no idea how to prevent this error from showing up on stdout/stderr. Cheers, Sven	[reply]
Re: DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? by jfroebe (Parson) on Jan 11, 2005 at 19:05 UTC
Hi, Keep in mind that stored procedures or queries using the print TSQL statement will send the output as informational error messages rather than in the result set. I'm suspecting this is what you are encountering. The error you are recieving indicates that there isn't a column with the name ent_id Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re^2: DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? by svenXY (Deacon) on Jan 12, 2005 at 08:43 UTC
Hi All, I might have found a solution to this (it's hard to test as it never fails when called from a shell). However, after having called $sth->execute(), I now use the following: `if ($sth->{NAME}->[0] ne 'ent_id') { FATAL(" No valid entities! Need to exit"); exit 99; }` [download] I'll see if this solves my problem, but logically seen, it "should" Cheers, Sven	[reply] [d/l]
Re^3: DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? by mpeppler (Vicar) on Jan 12, 2005 at 20:33 UTC
YOu can also run your query with DBI->trace(3) to see what DBD::Sybase thinks it is doing (what it is sending to the server, and what it is getting back.) This might help you get some better information about what is going on. Michael	[reply]
Re^4: DBI::Sybase sometimes returns nothing, but does not generate an error -> how can I catch this? by svenXY (Deacon) on Jan 13, 2005 at 08:59 UTC