Why does Perl DBI complain about "Fetch try on on unopen cursor"?
Here's 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 that is causing the Perl script error:
DBD :: Informix :: db selectrow_array failed: SQL: -400: The selection is performed on an unopened cursor.
If I write a simple script to connect to the database $dbstr
and display the contents of the table, it works fine, but the above code doesn't work.
a source to share
The method is selectrow_array
intended for use with operators that return result sets. The SET ISOLATION statement is not such a statement — it will fail.
If the -400 error is the best error is a bit controversial - someday I'll look and see if anything can be done. However, IIRC, the method is selectrow_array
provided by DBI, not DBD :: Informix, so DBI creates it from the lower level primitives. Therefore, these primitives cannot provide a check of what a higher-level function can provide, since they must also operate separately.
The correct way to write this code is:
$db_handle->do("set isolation mode to dirty read");
a source to share
No results to get from the statement you are executing:
set isolation to dirty read
therefore selectrow_array()
is the wrong calling method. Use $ dbh-> do (...) instead:
$db_handle->do('set isolation to dirty read');
Here's a longer explanation for the -400 error:
-400 Retrieve attempt at unopened cursor.
This FETCH statement names a cursor that has never been opened or has been closed. Review the program logic and make sure that it opens the cursor to this point and does not accidentally close it. If a cursor is declared WITH HOLD, it is automatically closed by COMMIT WORK or ROLLBACK WORK.
As Jonathan points out, this may not be the most glaring mistake, but it makes sense if you understand what's going on.
a source to share
Read the DBD :: Informix documentation , especially the CONNECTING_TO_A_DATABASE section. The minimum code required to connect to an Informix database is as follows:
$dbh = DBI->connect("dbi:Informix:$database");
Thus, you will need to provide more than just the name of the database.
a source to share
set isolation to dirty read
is not a request, it is a statement. Requests only 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;
a source to share