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

Brothers,
I can't see why if I prepare a query outside of a loop and
then execute the statement handle within the loop I get the following
error 
DBD::ODBC::st execute failed: [iODBC][Driver Manager]Invalid cursor st
+ate (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at ./addstuname.pl
+ line 21, <INPUT> chunk 2014.
[iODBC][Driver Manager]Invalid cursor state (SQL-24000)(DBD: st_execut
+e/SQLExecute err=-1) at ./addstuname.pl line 21, <INPUT> chunk 2014.

[download]
If I prepare the statement inside the loop all is fine
Here is the code. 
#!/usr/bin/perl

#use strict;
use DBI;

my $dbh = DBI->connect('dbi:ODBC:MY_DB')
                    or die (DBI->errstr());
open INPUT, '/tmp/records.asc';
my $query = qq(select b from c where a = ?);
#
# Preparing here causes an error!
#
#my $sth = $dbh->prepare($query)
#                    or die ($dbh->errstr());
while (my $rec = <INPUT>)
{
    chomp($rec);
    my @items = split(',', $rec);
    my $a = $items[6];
    $a =~ s/#//;
    if ($items[7] eq '')
    {
# If I prepare each time it works!
#
        my $sth = $dbh->prepare($query)
                    or die ($dbh->errstr());
        $sth->execute($a)
                    or die ($dbh->errstr());
        my $b = $sth->fetchrow_array();
        print qq(Name:$b missing\n);
    }
}

[download]
Any ideas would be most welcome.

Replies are listed 'Best First'.
Re: DBI (Prepare once execute many fails)
by gmax (Abbot) on Jun 06, 2003 at 07:46 UTC

    I can't tell you for sure the cause of this strange behavior, but there are a couple of things that are worth saying about your problem.

    • You are using fetchrow_array only once, without a finish statement. This fact might be the reason for the DB engine being out of synch with its cursor.
      The usual way of calling fetchrow_array is within a loop. This way, when it returns undef, the DBI calls finish for you. If you are sure that there will be only one record, then issue a $sth->finish call.
    • (Unrelated to the specific prolem) You are using fetchrow_array in scalar context. In this particular case, it should be no problem, but be aware that it may have unpleasant side effects if you select more than one field. (See DBI specification change)

    HTH

    Update

    Just to explain better my story. The call to finish is always necessary. There are times when the DBI calls finish implicitly, for example, when you use a call to fetchall_* or selectall_* functions. The finish is also implicit when the fetch function is inside a loop. In all the other cases, you should call it. And, just to be paranoid, if you call it even after a loop, you don't do any damage. :)

    A driver can be implemented in such a way that a call to finish is not necessary. For example, if the driver uses some sort of "fetch-at-once" mechanism, then the finish is not needed, because the driver itself, internally, has already retrieved ALL the records.

    Update (2)

    The DBI docs say that the driver should call finish "when all the data has been fetched." The driver has two ways of know that *ALL* the data is fetched: either you call a "fetch-all-at-once" routine or you call a "fetchrow_*" in a loop until it returns undef. When one of these cases happen, the driver calls finish.

    If you call a fetchrow_* function only once, the driver does not know if there is more data to fetch or not.

     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
      I'm betting with gmax on this one. Some RDBMSs require that you explicitly finish a partially-read result set (cursor). In DBI-speak, this means calling finish() unless you've read the entire result set.

[reply]
[d/l]
Re: DBI (Prepare once execute many fails)
by Grygonos (Chaplain) on Jun 06, 2003 at 13:36 UTC
    I'll echo their statements again....in all databases I've done perl dbi work in.. .Oracle, MySQL, and Access....they all seemed to have side effects if I didn't explicitly call finish. Remember however that if you call finish before your fetchrow_array call.. that it will fail due to the cursor not being valid. usual flow 
    $sth = $dbh->prepare($query);
$sth->execute();

while(my @row = $sth->fetchrow_array)
{
   #do stuff
}

$sth->finish();

    [download]
[reply]
[d/l]
Re: DBI (Prepare once execute many fails)
by Itatsumaki (Friar) on Jun 06, 2003 at 15:00 UTC

    Unfortunately I have to disagree a bit with the other posters. It is not necessary to call $sth->finish() and if you find it so I suspect it is due to an old version of the DBD::ODBC module. I had the same problem earlier this year and reported it on the DBI-users mailing list. The author of DBI had this reply. The problem was fixed in subsequent releases of DBD::ODBC (1.02 and onwards).

    So, if you see this problem you should upgrade DBD::ODBC. If you are running ActivePerl you can do so by going to a command line and typing:
    ppm install ftp://ftp.esoftmatic.com/outgoing/DBI
    (My memory might be off, but I think you might need a .ppd on the end of that -- not sure). Hope this helps!
    -Tats

[reply]
[d/l]

      From the DBI documentation: 

      When all the data has been fetched from a SELECT statement, the driver
+ should automatically call finish for you. So you should not normally
+ need to call it explicitly except when you know that you've not fetc
+hed all the data from a statement handle. The most common example is 
+when you only want to fetch one row, but in that case the selectrow_*
+ methods may be better anyway. Adding calls to finish after each fetc
+h loop is a common mistake, don't do it, it can mask genuine problems
+ like uncaught fetch errors.

      [download]
      And also says:
      Calling finish resets the /Active attribute for the statement. It may 
+also make some statement handle attributes (such as NAME and TYPE) un
+available if they have not already been accessed (and thus cached). 

The finish method does not affect the transaction status of the databa
+se connection. It has nothing to do with transactions. It's mostly an
+ internal "housekeeping" method that is rarely needed. See also /disc
+onnect and the /Active attribute.

      [download]

      Update: The DBI docs (same link) also says for the execute method:

      If execute() is called on a statement handle that's still active ($sth
+->{Active} is true) then it should effectively call finish() to tidy 
+up the previous execution results before starting this new execution.

      [download]

[reply]
[d/l]
[select]
        to clarify... it is neccesary when you don' want to retrieve all the data provided by a select statement, which should rarely be the case since you would filter the rows via your query. but in most cases of $sth->execute() it is not neccesary because most ppl do while(@row=$sth->fetchrow_array){} and if it doesn' work update drivers correct?
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom