Why does Perl's DBI complain about "Fetch attempted on unopen cursor"?

Question

Here is my script:

$db_handle=DBI->connect("$dbstr", "", "",
{RaiseError => 0, AutoCommit => 0, PrintError => 1}) 
|| die "Connect error: $DBI::errstr" ;
$result=$db_handle->selectrow_array("set isolation to dirty read");

Note: $dbstr is a valid database name.

I am not a database programmer. What am I doing wrong which is causing the Perl script fail saying:

DBD::Informix::db selectrow_array failed: SQL: -400: Fetch attempted on unopen cursor.

If I write a simple script to connect to database $dbstr and display the table contents it works fine but the above code does not work.

Please note that there are documented places to seek support for DBD::Informix (this isn't one of them, but it didn't exist when the last release of DBD::Informix was created). Please note, even more importantly, that there is a *LOT* of version information that is needed to answer more complex questions, including the version of: Perl, DBI, DBD::Informix, ESQL/C (or CSDK), IDS (or other Informix DBMS), platform, and sometimes even the compiler. These requirements are laid out in the documentation with the source. There's even a script provided to gather most of it - InformixTechSupport. — Jonathan Leffler, May 07 '09 at 14:17

score 3 · Answer 1 · answered May 07 '09 at 14:09

The selectrow_array method is intended for use with statements that return result sets. The SET ISOLATION statement is not such a statement - it will fail.

Whether error -400 is the best possible error is slightly debatable - sometime I'll take a look and see whether anything can be done. However, IIRC, the selectrow_array method is provided by DBI rather than DBD::Informix, so DBI builds it from lower-level primitives. Consequently, those primitives cannot provide the validation that the higher-level function could provide because they have to work separately too.

The correct way to write that code is:

$db_handle->do("set isolation mode to dirty read");

John Siracusa · Answer 2 · 2009-05-07T14:26:28.293

There are no results for you to fetch from the statement you are executing:

set isolation to dirty read

so selectrow_array() is the wrong method to call. Use $dbh->do(...) instead:

$db_handle->do('set isolation to dirty read');

Here's a longer explanation of the -400 error:

-400 Fetch attempted on unopen cursor.
This FETCH statement names a cursor that has never been opened or has been closed. Review the program logic, and check that it will open the cursor before this point and not accidentally close it. Unless a cursor is declared WITH HOLD, it is automatically closed by a COMMIT WORK or ROLLBACK WORK statement.

As Jonathan points out, this may not be the clearest error, but it does make some sense once you understand what's going on.

score 1 · Answer 3 · answered May 07 '09 at 13:54

1

Please read the documentation for DBD::Informix, especially the "CONNECTING_TO_A_DATABASE" section. The minimum required code to connect to an Informix database seems to be:

$dbh = DBI->connect("dbi:Informix:$database");

Thus, you will have to provide more than just the name of the database.

answered May 07 '09 at 13:54

innaM

47,505
4
67
87

The question said the connection was working - and therefore they did provide more than just the database name. The x-ref to the DBD::Informix documentation is still useful, though. – Jonathan Leffler May 07 '09 at 14:20

score 1 · Answer 4 · answered May 07 '09 at 15:10

set isolation to dirty read is not a query, it is a statement. Only queries go in selectrow_array. You need do:

#!usr/bin/perl

use strict;
use warnings;

use DBI;

my $dbi = "dbi:Informix:dbname";

my $dbh = DBI->connect(
    $dbi,
    "",
    "",
    {
        RaiseError => 1,
        AutoCommit => 0,
        PrintError => 1,
        ChopBlanks => 1,
    }
) or die "Connect error: $DBI::errstr";

my $result = $dbh->do("set isolation to dirty read");

$dbh->disconnect;

Why does Perl's DBI complain about "Fetch attempted on unopen cursor"?

4 Answers4