Bug #2858

Query cursor lost after insert record (firbird)

Details

Submitted	2004-11-30 07:19 UTC
From	almond at polydata dot biz
Assigned	danielc
Status	Bogus
Package	DB
PHP Version	4.3.4
OS	Linux redhat 7.3
Roadmaps	(Not assigned)

Comments

[2004-11-30 07:19 UTC] almond at polydata dot biz

Description:
------------
Hello. I'm using Firebird 1.0, PEAR 1.3.3.1 and DB-1.6.8.

I try to insert record insert a while loop of a query result. The query cursor always lost after a insert query is executed. I changes the query using prepare has no differences.

Error message: DB Error: unknown error
A more detailed error description:
insert into customer (customer) values ('customer'); [nativecode=Dynamic SQL Error SQL error code = -504 Cursor unknown]

Reproduce code:
---------------
<?php
require_once "DB.php" ;
$gdb = "/opt/interbase/examples/employee.gdb?dialect=3";
$DSN = "ibase://sysdba:masterkey@localhost/$gdb?dialect=3";
$DB_Connect = DB::connect($DSN);
$QUERY = "select emp_no, full_name, hire_date from employee";
$INSERT = "insert into customer (customer) values ('customer');";

$title = "Firebird PHP pear DB test page " . date("h:i:s ");
echo "<TITLE> $title </TITLE><BODY BGCOLOR= \"white\"><H1> $title </H1>";
$print_out = "<TABLE BGCOLOR=\"c0c0c0\" width=\"100%\">";

$DB_Query = $DB_Connect->Query($QUERY);
while ($ROW = $DB_Query->fetchRow()) {
$INSERT_Query = $DB_Connect->Query($INSERT);
$print_out .= "<TR><TD width=\"20%\"> $ROW[0] </TD>";
$print_out .= "<TD width=\"60%\"> $ROW[1] </TD>";
$print_out .= "<TD> $ROW[2] </TD></TR>";
}

$print_out .= "</TABLE>";
echo $print_out;
?>

Expected result:
----------------
I directly use ibase() function and the loop completed sucessfully.

<?php
$title = "Firebird/Interbase PHP test page " . date("h:i:s ");
$gdb = "localhost:/opt/interbase/examples/employee.gdb";
$C = ibase_pconnect( $gdb, 'sysdba', 'masterkey' );
if (!$C) echo die( ibase_errmsg() );
ibase_timefmt("%y.%m.%d");
$QUERY = "select emp_no, full_name, hire_date from employee";
$INSERT = "insert into customer (customer) values ('customer');";
echo "<TITLE> $title </TITLE><BODY BGCOLOR= \"white\"><H1> $title </H1>";
$Q = ibase_query($QUERY);
if (!$Q) echo die( ibase_errmsg() );
while ($R = ibase_fetch_row($Q)) {
$QQ = ibase_query($INSERT);
echo "<TABLE BGCOLOR=\"d0d0d0\" width=\"100%\">
<TR><TD width=\"20%\"><FONT color=\"red\"><STRONG>EmpNo
</STRONG></FONT></TD><TD>$R[0]</TD></TR>
<TR><TD width=\"20%\"><FONT color=\"red\"><STRONG>Name
</STRONG></FONT></TD><TD>$R[1]</TD></TR>
<TR><TD><FONT color=\"red\"><STRONG>Hire Date
</STRONG></FONT></TD><TD>$R[2]</TD></TR>
<BR>";
}
?>

[2004-12-15 04:00 UTC] almond at polydata dot biz

I did try this before the first post. It return the same error.

Please advise the version you use.
Linux 7.3
PHP 4.3.4
Firebird 1.0 super server
PEAR 1.3.3.1
DB-1.6.8.

[2005-02-16 05:00 UTC] almond at polydata dot biz

Yes, you are right. It works now :).

I found that the commit() cannot be executed inside the loop or resulting in the same error. Even using 2 handler cannot solve the problem. This would led to a performance problem on mass insertion/deletion.