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.

Answer 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.

Answer 2

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

Answer 3

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.

Answer 4

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;