Re: Possibly Silly q

Bruce,

With my version of Oracle and its DBD, both work (i.e., just re-opening the
cursor, or first issuing a "finish"):

$query = 'SELECT * FROM my_table';
$csr = $dbh->prepare($query);
$csr->execute();

# Just print two of the 24 rows:
@row = $csr->fetchrow_array();
print (join ',',@row) . "\n"; # prints the first row
@row = $csr->fetchrow_array();
print (join ',',@row) . "\n"; # prints the second row

# Now re-execute the query and fetch again:
$csr->execute();
@row = $csr->fetchrow_array();
print (join ',',@row) . "\n"; # prints the first row

# Now close the query and fetch again:
$csr->finish();
$csr->execute();
@row = $csr->fetchrow_array();
print (join ',',@row) . "\n"; # prints the first row
8< -------------------

So the latter is the "correct" way to do it.
No need to recreate the handle, though: the "prepare" is still valid, just
re-execute it.
But as I said, not closing the cursor might or might not work, depending on
the (version of the) server.

--      Peter Vanroose.


On Jul 01, 2012, at 05:13 AM, johnson@pharmacy.arizona.edu wrote:

> On Jun 30, 2012, at 3:57 PM, peter@vanroose.be wrote:
>
>> I guess it depends on the RDBMS server.
>> I would expect an implicit close, but I can imagine that the documentation
>> states that it's unpredictable behaviour.
>
>
> Looking at the DBD oracle docs I find:
>
> finish
>
>   $rv = $sth->finish;
> Indicates to DBI that you are finished with the statement handle and are not
> going to use it again. Only needed when you have not fetched all the possible
> rows.
>
>
> Does this mean I have to recreate the handle $sth to reuse it?
>
> --
> Bruce Johnson

• Possibly Silly q by Bruce Johnson
• Re: Possibly Silly q by peter
• Re: Possibly Silly q by peter