php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #43012 Cannot retrieve a newly inserted record after calling ibase_commit
Submitted: 2007-10-18 02:52 UTC Modified: 2007-10-19 16:02 UTC
From: arnoldvmartinez at yahoo dot com Assigned:
Status: Not a bug Package: InterBase related
PHP Version: 5.2CVS-2007-10-18 (snap) OS: Windows 2003
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
4 + 33 = ?
Subscribe to this entry?

 
 [2007-10-18 02:52 UTC] arnoldvmartinez at yahoo dot com 
Description:
------------
Re-post Bug #36910 :Cannot retrieve a newly inserted record after calling ibase_commit

It was marked bogus and it now prevents me from adding more comments... I dunno if its intentional or not. I am posting here my response to the last post, readers could refer to Bug #36910 for details. If you can't access it you could email me and I am willing to provide the message tree.
---------------------------------------------

I stated in my previous that the issue does not happen to FlameRobin, Database Workbench... I tried it myself. I can write a simple VB, Delphi, Java, or any other program that can prove that Firebird returns a newly-inserted record. Applications connect to Firebird using the lower-level API provided by Firebird. Now if the bug is Firebird's, who in the world would use it? Would anyone use a database that cannot return a newly-inserted (committed) record? Would you want your customer to still see $1 million in his account that he's just withdrawn? That would be ridiculously phenominal!!! 

If you can recreate the bug using either of these applications then we will put this case to rest. If not, then the bug should be properly recognized, investigated and fixed. PHP should act responsibly to at least inform PHP-Firebird users about it.

Besides, please elaborate your claim and support your argument. Contradict and disprove point-by-point, any or all the items in my previous post. One-liner explanation or comment does no good. 

Only facts can argue against facts...

... and someone's opinion is just as good as anyone's opinion.



Reproduce code:
---------------
$dbh = ibase_connect($host, $user, $password);
$stmt = 'SELECT * FROM Employees';
$sth = ibase_query($dbh, $stmt);
while ($row = ibase_fetch_object($sth)) {
       echo '<br>'.$row->EMPNAME;
}
$th=ibase_trans(IBASE_DEFAULT,$dbh);
$stmt='INSERT statement here...';
$sth = ibase_query($th, $stmt);
ibase_commit($th);
$stmt = 'SELECT * FROM Employees';
$sth = ibase_query($dbh, $stmt);
while ($row = ibase_fetch_object($sth)) {
       echo '<br>'.$row->EMPNAME;
}
/* new record not listed */

Expected result:
----------------
The newly inserted (COMMITTED) record to be included in the new SELECT operation.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-10-19 15:35 UTC] Lars dot westermann at privat dot dk 
Still - this isn't a bug in PHP (and not even in Firebird).

One has to understand that EVERYTHING in Interbase/Firebird happens inside transaction(s). Even if you don't create one, a default transaction is started for you, when you issue the first statement, whether it is a select or an insert statement.

You can do anything inside a transaction, multiple selects, inserts etc. - and it's not illegal to make inserts in the default transaction either.

But it's vital to know how the server treats transactions. The default is "snapshot" mode, which takes a snapshot of the database, when the transactions start. Commits made by other transactions are invisible to this transaction. That's what happens in your example as explained in my previous comment to bug #36910.

You can, however, start a transaction in one of several other modes (i.e. read-committed), where read-committed will include the result of other transactions when they are committed. Bare in mind though, that if you have a system with many concurrent connections/transactions it can do very bad things to the performance (been there, done that ...).

To prove, that this is not a PHP bug, try this:

1) Start two terminal windows, and start isql in each window.
2) Type your select statement in the first window and hit ENTER.
3) In the second window type the insert statement and hit ENTER.
4) Type commit and hit ENTER (this ends the default transaction in the sedond window)
5) Type the select statement in the first window and hit ENTER (the new record won't show up)
6) Type the select statement in the second window (the new record will show up)
7) Now type COMMIT; and hit ENTER in the first window (which ends the default transaction)
8) Then type your select statement in the first window (which starts a NEW default transaction (with a new snapshot) - and now you will see the new record.

I have often used an isql session for debugging and used a webapplication (PHP based naturally) to update records, and often wondered why I couldn't see the new records (until realizing I forgot to do a COMMIT in isql to close the transaction and start a new one with the next select statement).

I don't know (or I haven't tried) the applications you mention, but I think they can be set up (maybe even default) to work in auto-commit mode, which means that every statement is handled in it's own transaction - meaning that a snapshot is taken for every statement, thus giving the result you expect. But this can also have an impact on performance when dealing with large databases and/or datasets.

Here's a couple of links which give a detailed explanation of transactions in Interbase (and in Firebird):

http://bdn1.borland.com/borcon2004/article/paper/0,1963,32280,00.html
http://en.wikipedia.org/wiki/Interbase

Hope this clarifies it a bit more.
 [2007-10-19 16:02 UTC] johannes@php.net 
Lars explanation is really good
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Fri Apr 19 20:01:29 2024 UTC