ID: 36910 User updated by: arnoldvmartinez at yahoo dot com Reported By: arnoldvmartinez at yahoo dot com -Status: No Feedback +Status: Open Bug Type: InterBase related Operating System: Windows 2003 PHP Version: 5.1.2 New Comment:
I am surprised to receive email regarding the bug I posted about a year ago!!! It's good to see too that another guy identified the same problem. Unfortunately, we never pushed Firebird in the project and switched to mySQL instead simply because it's hard to deal with the "unknowns". And if I would use Firebird DB, I probably would NOT use PHP. Is it a bug or not a bug??? Obviously that's debatable. Is it a Firebird issue or a PHP issue? I believe it's PHP's, because the issue does not happen in other applications (like FlameRobin, Workbench, etc.). When you commit an INSERT, it is sanely logical to see the record in your subsequent SELECTs... if not... then it contradicts the very principle of transaction processing. Database transactions should be an ACID (Atomicity, Consistency, Isolation, Durability) transaction ... otherwise it cannot ensure data integrity in a networked environment, especially in web applications. You may see this link: http://en.wikipedia.org/wiki/ACID Does Firebird support ACID transactions??? Absolutely!!! Otherwise no single company would use it! Is it a "bug" or NOT a "bug"??? In my opinion it is a bug. SELECTs are READ operations... they are suppose to, well..., READ what's IN the database, NOT what's in the "snapshot" or in the "cache" or any other form of temporary storage intended to optimize execution of queries. First and foremost, SELECTs should return accurate data at its latest and greatest state. Returning it fast is just a secondary thing... a nice-to-have. You don't need to initiate a TRANSACTION to do SELECTs.. be it a single SELECT statement or a thousand SELECT statements. Why? You're NOT CHANGING anything in the database.. you are just READING it. TRANSACTIONs generally involve INSERTS, UPDATE and/or DELETE operations. They are intended to CHANGE (NOT READ) the database, that's why, they have to support ACID requirements. Do we need SELECTs INSIDE transaction blocks??? I don't see any reason why you would want to do a SELECT while INSIDE the transaction block.... I ALWAYS try to put them OUTSIDE the transaction block. Embedding a SELECT statement is probably my LAST OPTION... that is, if I can't find any other feasible way to kick that SELECT out of the transaction block. Knowing the difference between SELECTs and TRANSACTIONs is very vital. With due respect to Lars... I believe you don't NEED to have transaction blocks if you're merely doing SELECTs because they are just READ operations. When you do a select, you do not intend to ALTER any piece of information in the database. Now if you need to ALTER the data, then you are compelled to use TRANSACTIONS....ACID transactions, to ensure and protect the integrity of data. Previous Comments: ------------------------------------------------------------------------ [2007-10-16 21:56:04] lars dot westermann at privat dot dk This has to do with the transaction handling of Interbase/Firebird. Taking the first example: The first SELECT uses the default transaction (taking a "snapshot" of the database). Then a NEW transaction is created (unseen by the default transaction) A record is inserted, and the transaction is closed. The next select STILL operates on the DEFAULT transaction, which was opened prior to the transaction, where the record was inserted - therefore the newly inserted record is not found. The solution is to ALWAYS use explicit transactions - and NEVER use the default transaction. You *might* want to fiddle with the transaction type (snapshot, read_commited etc.) but beware, that this can have a dramtic effect on performance on large databases. Personally I always start a transaction - even for a single select. This "bug" therefore isn't a bug, neither in PHP nor in Firebird. /Lars ------------------------------------------------------------------------ [2006-04-27 07:26:27] kogut at kogumelo dot com dot br I'm not the owner of this bug, but i´ve got the same problem. In my situation, as opposed to what the manual says, changing "$sth=ibase_query($th,$stmt)" to "$sth=ibase_query($dbh,$stmt)" just after the "insert ... " statement and "ibase_commit($th)" to "ibase_commit($dbh)" makes it work for me. And if i change to "ibase_rollback($dbh)", it works fine, too. I don´t know if it is a Php or a Firebird bug, but that´s weird. ------------------------------------------------------------------------ [2006-04-06 01:00:00] php-bugs at lists dot php dot net No feedback was provided for this bug for over a week, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open". ------------------------------------------------------------------------ [2006-03-29 13:41:32] [EMAIL PROTECTED] And why did you decide it's a problem of PHP? Are you able to get the expected result using some other tool? ------------------------------------------------------------------------ [2006-03-29 13:08:36] arnoldvmartinez at yahoo dot com $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 */ ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/36910 -- Edit this bug report at http://bugs.php.net/?id=36910&edit=1
