Re: [PHP-DB] Transactions with PDO_INFORMIX
If any query failed inside a transaction then PDO will not issue a rollback, It will left the decision on you that you want to ignore the failed query it or want a rollback. You can find the examples of handing of query failure inside a transaction at http://www.php.net/manual/en/pdo.transactions.php You can trust on transaction handling built-in function of PDO, it is well implemented inside PDO_INFORMIX. Thanks, Rahul Priyadarshi - Forwarded by Daniel Krook/White Plains/IBM on 01/14/2014 10:33 AM - From: Neomi TR ne...@isoc.org.il To: php-db@lists.php.net Date: 01/14/2014 10:30 AM Subject:[PHP-DB] Transactions with PDO_INFORMIX Sent by:Neomi TR ne...@post.isoc.org.il Hi, I use the PDO functions PDO::beginTransaction, PDO::commit and PDO::rollBack to begin, commit and rollback transactions. I also check that we are in active or no active transaction with 'PDO::inTransaction'. System info: PHP 5.3.28 INFORMIX 11.70 PDO_INFORMIX-1.3.0 Lately we have encountered few failures when using transactions. Queries have failed and there was no rollback, although the failed queries were part of transaction. Even after one of the queries has failed, there was no rollback although it was declared, and part of the queries in the transaction were executed. My question is - can I trust the build-in functions of the PDO? Does this problem is known? Thank you, Neomi -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Transactions with PDO_INFORMIX
Hi, I use the PDO functions PDO::beginTransaction, PDO::commit and PDO::rollBack to begin, commit and rollback transactions. I also check that we are in active or no active transaction with 'PDO::inTransaction'. System info: PHP 5.3.28 INFORMIX 11.70 PDO_INFORMIX-1.3.0 Lately we have encountered few failures when using transactions. Queries have failed and there was no rollback, although the failed queries were part of transaction. Even after one of the queries has failed, there was no rollback although it was declared, and part of the queries in the transaction were executed. My question is - can I trust the build-in functions of the PDO? Does this problem is known? Thank you, Neomi -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions
Hello MySQL isn't a transactional DB it doesn't know rollback and commit. You can solve the problem probably by locking the tables (LOCK / UNLOCK) Regards Koen On 07/11/05, Bastien Koert [EMAIL PROTECTED] wrote: I don't think you can. Once the script finishes the connections are closed and the transactions completed. What are you trying to do where you need to have the transaction across multiple pages? Perhaps, if you are gathering the data across the pages, you could use a session to store the data until you have all the elements, then start the transaction, so your inserts and commit... Bastien From: martin lutsch [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] transactions Date: Mon, 07 Nov 2005 11:18:53 +0100 hi, i have a problem with mysql transactions and php: i want to do a transaction through more than one php scripts. i think, if one script ends and links to another, the database connection ends and the transaction rolles back. how can i continue one transaction through more than one script? any ideas? please help, cheers, martin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions
On Tuesday 08 November 2005 09:37, Koen Wagemans wrote: MySQL isn't a transactional DB it doesn't know rollback and commit. MySQL does support transactions so long as you use a reasonably recent version and InnoDB tables. However as Bastien suggested a transaction can't persist beyond the execution of a script. cheers Simon -- ~~ Simon Rees | [EMAIL PROTECTED] | ORA-03113: end-of-file on communication channel ~~ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions
Incorrect, MySQLs MyISAM engine is non transactional, the InnoDB engine / BDB egines both support transactions Bastien From: Koen Wagemans [EMAIL PROTECTED] To: Bastien Koert [EMAIL PROTECTED] CC: [EMAIL PROTECTED], php-db@lists.php.net Subject: Re: [PHP-DB] transactions Date: Tue, 8 Nov 2005 10:37:15 +0100 Hello MySQL isn't a transactional DB it doesn't know rollback and commit. You can solve the problem probably by locking the tables (LOCK / UNLOCK) Regards Koen On 07/11/05, Bastien Koert [EMAIL PROTECTED] wrote: I don't think you can. Once the script finishes the connections are closed and the transactions completed. What are you trying to do where you need to have the transaction across multiple pages? Perhaps, if you are gathering the data across the pages, you could use a session to store the data until you have all the elements, then start the transaction, so your inserts and commit... Bastien From: martin lutsch [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] transactions Date: Mon, 07 Nov 2005 11:18:53 +0100 hi, i have a problem with mysql transactions and php: i want to do a transaction through more than one php scripts. i think, if one script ends and links to another, the database connection ends and the transaction rolles back. how can i continue one transaction through more than one script? any ideas? please help, cheers, martin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] transactions
hi, i have a problem with mysql transactions and php: i want to do a transaction through more than one php scripts. i think, if one script ends and links to another, the database connection ends and the transaction rolles back. how can i continue one transaction through more than one script? any ideas? please help, cheers, martin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] transactions
I don't think you can. Once the script finishes the connections are closed and the transactions completed. What are you trying to do where you need to have the transaction across multiple pages? Perhaps, if you are gathering the data across the pages, you could use a session to store the data until you have all the elements, then start the transaction, so your inserts and commit... Bastien From: martin lutsch [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] transactions Date: Mon, 07 Nov 2005 11:18:53 +0100 hi, i have a problem with mysql transactions and php: i want to do a transaction through more than one php scripts. i think, if one script ends and links to another, the database connection ends and the transaction rolles back. how can i continue one transaction through more than one script? any ideas? please help, cheers, martin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Transactions - working but unsure about steps
My statements are all working but I'm not sure if things are set up correctly. I say this because at one point the first $query failed, yet the rest of inserts wre committed. Now I believe I need to set autocommit to 0 , yet the query failed due to a syntax error. Hence 0 records effected wouldn't be the case here. Here is what I have: //Create these functions - function begin() { mysql_query(BEGIN); } function commit() { mysql_query(COMMIT); } function rollback() { mysql_query(ROLLBACK); } connection statement with error checking... begin(); // transaction begins $query = INSERT INTO firsttable.//first query $result = mysql_query($query); // process first query $query = INSERT INTO secondtable...//second query $result = mysql_query($query); // process second query $query = INSERT INTO thirdtable//third query $result = mysql_query($query); // process third query then: if(!$result) { echo mysql_errno($link) . : . mysql_error($link). \n; rollback(); // transaction rolls back exit; } else { commit(); // transaction is committed echo your insertion was successful; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transactions - working but unsure about steps
El Sáb 16 Oct 2004 09:52, Stuart Felenstein escribió: My statements are all working but I'm not sure if things are set up correctly. I say this because at one point the first $query failed, yet the rest of inserts wre committed. Now I believe I need to set autocommit to 0 , yet the query failed due to a syntax error. Hence 0 records effected wouldn't be the case here. [snip] connection statement with error checking... I am not a MySQL user, but checking the sentences in a transactional way I see that you would have to check for ever query (at least in the way you are processing the transaction). begin(); // transaction begins $query = INSERT INTO firsttable.//first query $result = mysql_query($query); // process first query $query = INSERT INTO secondtable...//second query $result = mysql_query($query); // process second query $query = INSERT INTO thirdtable//third query $result = mysql_query($query); // process third query then: if(!$result) { echo mysql_errno($link) . : . mysql_error($link). \n; rollback(); // transaction rolls back exit; } else { commit(); // transaction is committed echo your insertion was successful; OK, lets see how I would execute the queries in PostgreSQL: prueba= create table tab1 ( prueba( cod SERIAL PRIMARY KEY, prueba( first INT CHECK (first 0), prueba( names varchar(25) prueba( ); CREATE TABLE prueba= begin; BEGIN prueba= insert into tab1 values (default,10,'Martín'); ERROR: new row for relation tab1 violates check constraint tab1_first prueba= insert into tab1 values (default,-8,'José'); ERROR: current transaction is aborted, commands ignored until end of transaction block prueba= end; COMMIT prueba= select * from tab1; cod | first | names -+---+--- (0 filas) Now, as you see, because the CHECK constraint was violated in the first INSERT, ever other comand that would modify the table would be ignored until the transaction is over. This is the way a transactional database works (Oracle, Informix, Sybase, etc.) As I already stated, I don't work with MySQL so I don't know how it works with transactions. Maybe you should try to execute the same sentences I did in the mysql shell (does MySQL have CHECK constraints? If not just try putting a string larger then 25 chars in the names field) to see what happens. Good luck. -- 10:55:02 up 34 days, 1:13, 2 users, load average: 0.36, 0.26, 0.19 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transactions - working but unsure about steps
I think you are adding a conditonal /validaton statement as the constraint ? More then x characters will generate an error. My understaning is an error in mysql transaction will rollback should rollback the entire set of transactions. error handling for each statement- values will be coming from user input into form, my validations will be in the form. I've also thought about checking for effected rows and then if == 0 , stopping the transaction, but that seems redundant to what I believe is the way mysql transactions should work. Stuart --- Martín Marqués [EMAIL PROTECTED] wrote: prueba= create table tab1 ( prueba( cod SERIAL PRIMARY KEY, prueba( first INT CHECK (first 0), prueba( names varchar(25) prueba( ); CREATE TABLE prueba= begin; BEGIN prueba= insert into tab1 values (default,10,'Martín'); ERROR: new row for relation tab1 violates check constraint tab1_first prueba= insert into tab1 values (default,-8,'José'); ERROR: current transaction is aborted, commands ignored until end of transaction block prueba= end; COMMIT prueba= select * from tab1; cod | first | names -+---+--- (0 filas) Now, as you see, because the CHECK constraint was violated in the first INSERT, ever other comand that would modify the table would be ignored until the transaction is over. This is the way a transactional database works (Oracle, Informix, Sybase, etc.) As I already stated, I don't work with MySQL so I don't know how it works with transactions. Maybe you should try to execute the same sentences I did in the mysql shell (does MySQL have CHECK constraints? If not just try putting a string larger then 25 chars in the names field) to see what happens. Good luck. -- 10:55:02 up 34 days, 1:13, 2 users, load average: 0.36, 0.26, 0.19 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transactions - working but unsure about steps
El Sáb 16 Oct 2004 11:36, Stuart Felenstein escribió: I think you are adding a conditonal /validaton statement as the constraint ? More then x characters will generate an error. I was tryiong to generate a validation that would fail with certain inserts (or modification of a register). Using more then 25 characters in the second field would yield the same result. My understaning is an error in mysql transaction will rollback should rollback the entire set of transactions. Thats how transactional databases work (in a theorical way, but also practical in most cases). error handling for each statement- values will be coming from user input into form, my validations will be in the form. I've also thought about checking for effected rows and then if == 0 , stopping the transaction, but that seems redundant to what I believe is the way mysql transactions should work. Why don't you try using PEAR::DB, set autocommit to false and work like this: // supose $db is a database objet which already has a conection made $db-autocommit(false); $db-query(INSERT INTO ...); $db-query(INSERT INTO ...); $db-query(INSERT INTO ...); $db-commit(); That should work, if the database is transactional. -- 11:40:02 up 34 days, 1:58, 2 users, load average: 0.24, 0.33, 0.35 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transactions - working but unsure about steps
See Interspersed: --- Martín Marqués [EMAIL PROTECTED] wrote: I was tryiong to generate a validation that would fail with certain inserts (or modification of a register). Using more then 25 characters in the second field would yield the same result. Got that and yes, for my pages , they won't even arrive at the transaction page until all validations have been passed. Why don't you try using PEAR::DB, set autocommit to false and work like this: // supose $db is a database objet which already has a conection made $db-autocommit(false); $db-query(INSERT INTO ...); $db-query(INSERT INTO ...); $db-query(INSERT INTO ...); $db-commit(); Isn't that what I'm doing with PHP ? I'm not ready to jump into PEAR until I have a better understanding of PHP plain but I think it's worth looking into . I've also considered Adodb with smart transactions. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Transactions - working but unsure about steps
I had that suspicion that I was only calling it for the last query. Thank you for confirming and for the code! Much appreciated. Stuart --- Graham Cossey [EMAIL PROTECTED] wrote: Stuart, you are calling your rollback function only if $result is false. This check you are performing at the end of performing your 3 queries, each of which update $result. The net result here is that you will only call rollback() if the 3rd query results in false. I would create another function called maybe run_query() which is something like: function run_query($sql) { $result = mysql_query($query); if(!$result) { return false; }else{ return true; } } then: begin(); $query = INSERT INTO firsttable.//first query $res1 = run_query($query); $query = INSERT INTO secondtable...//second query $res2 = run_query($query); $query = INSERT INTO thirdtable//third query $res3 = run_query($query); if($res1 $res2 $res3) // If all results are true { commit(); echo your insertions were successful; }else{ echo mysql_errno($link). : .mysql_error($link).\n; rollback(); exit; } Graham -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: 16 October 2004 13:53 To: [EMAIL PROTECTED] Subject: [PHP-DB] Transactions - working but unsure about steps My statements are all working but I'm not sure if things are set up correctly. I say this because at one point the first $query failed, yet the rest of inserts wre committed. Now I believe I need to set autocommit to 0 , yet the query failed due to a syntax error. Hence 0 records effected wouldn't be the case here. Here is what I have: //Create these functions - function begin() { mysql_query(BEGIN); } function commit() { mysql_query(COMMIT); } function rollback() { mysql_query(ROLLBACK); } connection statement with error checking... begin(); // transaction begins $query = INSERT INTO firsttable.//first query $result = mysql_query($query); // process first query $query = INSERT INTO secondtable...//second query $result = mysql_query($query); // process second query $query = INSERT INTO thirdtable//third query $result = mysql_query($query); // process third query then: if(!$result) { echo mysql_errno($link) . : . mysql_error($link). \n; rollback(); // transaction rolls back exit; } else { commit(); // transaction is committed echo your insertion was successful; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions and persitent connections
At 12:52 +0900 9/1/02, Jean-Christian Imbeault wrote: Paul Dubois wrote: I am worried that if I use persistent connections it might be possible for more than one PHP script to be inside the same transaction at the same time. Not at the *same* time, because although a persistent connect might be used by more than one script, this will be serially rather than simultaneously. That does mean it's possible for a transaction to be started by one script and then either committed or rolled back by the next if they share a connection. That's exactly what I meant and feared. In a sense User 2 who is re-using User 1's connection would be in User 1's un-completed transaction. And the result of User 2's transaction (commit or rollback) would affect the first transaction. Yuck ... 2- If user 1's hits the stop button on his browser, what happens to his transaction? I assume it is stopped. But what about the connection? If user 2 No, the script won't have any idea the stop button has been pressed. It should have executed and completed its transaction regardless of what the user does. Really? I don't exactly know how PHP script execute but I though that is that the user goes to a page that starts a script and then closes the browser that causes the script to end prematurely. The script doesn't know anything about what the browser does. Am I wrong? Yes. Does the script go from start to finish even though the connection to the browser has been severed. I.e. The script will run complete even if there is output to the browser as the script is running? Not necessarily. It won't base its actions on what the browser might happen to be doing while it's running, but it might exit early because of a bug, for example. But normally it will run to completion. If the script goes from start to finish regardless of what the user does that would be nice. That way I am sure a transaction will go from start to finish, and if I make sure to either commit or rollback the transaction at the end of the script then I don't have to worry. You should write your script so that is *does* go from start to finish. Forget about what the user might be doing. Maybe the safest thing to do is not use persistent connections at all? Or in your opinion is there a safe way to use persistent connections and transactions? No. Don't use persistent connections. If some unforeseen problem does occur with your script, the connection may be left open and you'll have the problems you're concerned about. If you use a non-persistent connection and a problem occurs, the connection will be closed (which presumably will make your transaction roll back). Jc PS Great book! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions and persitent connections
Paul Dubois wrote: Does the script go from start to finish even though the connection to the browser has been severed. I.e. The script will run complete even if there is output to the browser as the script is running? Not necessarily. It won't base its actions on what the browser might happen to be doing while it's running, but it might exit early because of a bug, for example. But normally it will run to completion. Thanks for clearing that up for me. You should write your script so that is *does* go from start to finish. Forget about what the user might be doing. Trying *very* hard. No. Don't use persistent connections. If some unforeseen problem does occur with your script, the connection may be left open and you'll have the problems you're concerned about. If you use a non-persistent connection and a problem occurs, the connection will be closed (which presumably will make your transaction roll back). Thanks. With what you've explained I understand the pitfalls persistent connections might and will not be using them. Such a shame ... Thanks again for all the information! Jc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] transactions and persitent connections
I'm a little confused/worried about database transactions, persistent connections and PHP. I am worried that if I use persistent connections it might be possible for more than one PHP script to be inside the same transaction at the same time. For example: 1- page1.php is a script that opens a persistent connection to a DB and starts a transaction. 2- User 1 goes to page1.php 3- User 2 goes to page1.php My questions: 1- is it possible that user 2 will be using the same connection as user 1? So if user 1's transaction fails so will user 2's? 2- If user 1's hits the stop button on his browser, what happens to his transaction? I assume it is stopped. But what about the connection? If user 2 gets the same connection, will his transaction fail? Thanks, Jc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions and persitent connections
At 17:46 +0900 8/31/02, Jean-Christian Imbeault wrote: I'm a little confused/worried about database transactions, persistent connections and PHP. I am worried that if I use persistent connections it might be possible for more than one PHP script to be inside the same transaction at the same time. Not at the *same* time, because although a persistent connect might be used by more than one script, this will be serially rather than simultaneously. That does mean it's possible for a transaction to be started by one script and then either committed or rolled back by the next if they share a connection. This can happen if the first script fails to issue a COMMIT or ROLLBACK, perhaps by exiting early due to an error. If the second script comes along and commits or rolls back, that affects the queries issued by the first script. For example: 1- page1.php is a script that opens a persistent connection to a DB and starts a transaction. 2- User 1 goes to page1.php 3- User 2 goes to page1.php My questions: 1- is it possible that user 2 will be using the same connection as user 1? So if user 1's transaction fails so will user 2's? 2- If user 1's hits the stop button on his browser, what happens to his transaction? I assume it is stopped. But what about the connection? If user 2 No, the script won't have any idea the stop button has been pressed. It should have executed and completed its transaction regardless of what the user does. Perhaps you are thinking that user 1 visits the page and begins a transaction, and then visits it again to complete the transaction? That doesn't work. You have no guarantee that user 1 will get the same connection both times. (That is, don't try to spread a transaction out over multiple visits by a user.) gets the same connection, will his transaction fail? Thanks, Jc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] transactions and persitent connections
Paul Dubois wrote: I am worried that if I use persistent connections it might be possible for more than one PHP script to be inside the same transaction at the same time. Not at the *same* time, because although a persistent connect might be used by more than one script, this will be serially rather than simultaneously. That does mean it's possible for a transaction to be started by one script and then either committed or rolled back by the next if they share a connection. That's exactly what I meant and feared. In a sense User 2 who is re-using User 1's connection would be in User 1's un-completed transaction. And the result of User 2's transaction (commit or rollback) would affect the first transaction. Yuck ... 2- If user 1's hits the stop button on his browser, what happens to his transaction? I assume it is stopped. But what about the connection? If user 2 No, the script won't have any idea the stop button has been pressed. It should have executed and completed its transaction regardless of what the user does. Really? I don't exactly know how PHP script execute but I though that is that the user goes to a page that starts a script and then closes the browser that causes the script to end prematurely. Am I wrong? Does the script go from start to finish even though the connection to the browser has been severed. I.e. The script will run complete even if there is output to the browser as the script is running? If the script goes from start to finish regardless of what the user does that would be nice. That way I am sure a transaction will go from start to finish, and if I make sure to either commit or rollback the transaction at the end of the script then I don't have to worry. Maybe the safest thing to do is not use persistent connections at all? Or in your opinion is there a safe way to use persistent connections and transactions? Jc PS Great book! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transactions. How?
Hello, "Marko Perich"! You wrote: I need to make a transaction over several pages and commit it after validating the last one. Insert line on the first page, update on next, mark as commited on the last. Each hour drop uncommited lines. You may also provide current time of transaction start when inserting on 1st page to prevent uncommited transactions that are in progress (started in last 10 minutes). -- Piloteers do it on the road -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]