[PHP] transactions
Hi, Is there an elegant way to recover from DB errors in MySQL using PHP, i.e. transactions and rolling back - basically I have an insert statement then an update statement. if the insert succeeds the update is run but if the update fails I want to undo the insert! Any suggestions, I guess I could get the last inserted row id and perform an sql delete but is there a more elegant way? Cheers Matt
Re: [PHP] transactions
I believe you can use transactions with InnoDB tables on MySQL, if this is the case, a simple BEGIN; to begin your transaction, and a COMMIT; to save all the changes, plus a ROLLBACK; to undo your changes, should be sufficient. On Mon, 2004-03-29 at 13:12, Matthew Oatham wrote: Hi, Is there an elegant way to recover from DB errors in MySQL using PHP, i.e. transactions and rolling back - basically I have an insert statement then an update statement. if the insert succeeds the update is run but if the update fails I want to undo the insert! Any suggestions, I guess I could get the last inserted row id and perform an sql delete but is there a more elegant way? Cheers Matt -- Adam Voigt [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] transactions
Matthew Oatham wrote: Hi, Is there an elegant way to recover from DB errors in MySQL using PHP, i.e. transactions and rolling back - basically I have an insert statement then an update statement. if the insert succeeds the update is run but if the update fails I want to undo the insert! Any suggestions, I guess I could get the last inserted row id and perform an sql delete but is there a more elegant way? Cheers Matt Though I know there are many people out there who cringe at the thought of using DB abstraction layers, I really like ADOdb and it has very nice transaction support built in (as long as the underlying database supports it, obviously). $conn-BeginTrans(); $commit = false; $query = 'select col1, col2 from table 1'; $rs = $conn-Execute($query) ? true : false; $commit = $rs ? true : false; $query = 'insert into table1 (col1, col2) values (col1, col2)'; $commit = $commit == true $conn-Execute($query) ? true : false; $query = 'insert into table2 (col1, col2) values (col1, col2)'; $commit = $commit == true $conn-Execute($query) ? true : false; $query = 'insert intod table3 (col1, col2) values (col1, col2)'; $commit = $commit == true $conn-Execute($query) ? true : false; $commit == true ? $conn-BeginTrans() : $conn-RollbackTrans(); None of the above will be committed since there is a syntax error in the third query. HTH. Pablo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] transactions
Matthew Oatham wrote: Is there an elegant way to recover from DB errors in MySQL using PHP, i.e. transactions and rolling back - basically I have an insert statement then an update statement. if the insert succeeds the update is run but if the update fails I want to undo the insert! Use transactions? You just need to use Innodb table types and you can use transactions. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] transactions
Though I know there are many people out there who cringe at the thought of using DB abstraction layers, I really like ADOdb and it has very nice transaction support built in (as long as the underlying database supports it, obviously). I don't know why people hate them sothey make life so much easier. Processing time is cheap, developer time costs a lot more. -- paperCrane Justin Patrin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] transactions
yup - use transactions. http://www.mysql.com/doc/en/COMMIT.html --- Matthew Oatham [EMAIL PROTECTED] wrote: Hi, Is there an elegant way to recover from DB errors in MySQL using PHP, i.e. transactions and rolling back - basically I have an insert statement then an update statement. if the insert succeeds the update is run but if the update fails I want to undo the insert! Any suggestions, I guess I could get the last inserted row id and perform an sql delete but is there a more elegant way? Cheers Matt __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] transactions
Is there anyway to do something similar to Transactions in mysql? -- -- Diana Castillo Global Reservas, S.L. C/Granvia 22 dcdo 4-dcha 28013 Madrid-Spain Tel : 00-34-913604039 Fax : 00-34-915228673 email: [EMAIL PROTECTED] Web : http://www.hotelkey.com http://www.destinia.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] transactions
Is there anyway to do something similar to Transactions in mysql? Yes, use transactions http://www.mysql.com/doc/en/Transactional_Commands.html -Craig -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] transactions
Is there anyway to do something similar to Transactions in mysql? Yes -- http://www.mysql.com/doc/en/ANSI_diff_Transactions.html -- Lowell Allen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] transactions
Diana Castillo wrote: Is there anyway to do something similar to Transactions in mysql? Maybe the transaction section of the MySQL manual will help you out? http://www.mysql.com/doc/en/Transactional_Commands.html -- By-Tor.com It's all about the Rush http://www.by-tor.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Hi. I thought I had abstracted out the SQL querying part of my code out, just to find out today that it doesn't work when it comes to transactions. I had come up with this code: function sql_query($sql) { $conn = pg_connect(dbname=JC user=postgres); $res = pg_exec($conn, $sql); if (!$res) { echo CONNECTION: could not execute query ($sql)br; die; } else return $res; } I had transactions in my code implemented like this: $sql = BEGIN; sql_query($sql); [some sql that should be in a transaction ...] $sql = COMMIT; sql_query($sql); This doesn't work. Now that I look at my code I clearly see why. All sql queries are executed using a new Postgres connection, hence the use of BEGIN/COMMIT as I was using them have no effect. Can someone recommend a way to abstract out my DB layer while still being able to use transactions? I was thinking of using the same function but if the incoming query contained the word BEGIN, saving that and all future queries in a session var and when the COMMIT comes in executing all the saved queries as one (i.e. BEGIN;[];COMMIT). One drawback is that all queries will be written out to disk (as session vars) and that will slow things down. Another drawback is that I have to abort if not COMMIT comes in. And a few more drawbacks ... I was also thinking about maybe the $sql a GLOBAL or first building up my query as as long string (BEGIN;[];COMMIT) and *then* sending it to my sql_query() function. The last two seem easier to implement, safer, and more efficient but they don't seem elegant because I haven't abstracted out the fact that I want a transaction. Whenever I write an SQL query I have to think does this need to be in a transaction and then use a different coding technique depending on the answer. And if the future something that didn't need to be in a transaction now needs to be in a transaction I have to revisit my code and change the code. I'm sure someone out there must have thought about this and come up with an elegant solution and way of abstracting out the DB layer from PHP. Can anyone share their solution with me or give me some pointers to reference material? Thanks, Jc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Not exactly sure why your transactions aren't working but if your script already has an open connection to the database and you issue another call to pg_connect with the same connect string PHP will return the existing connection and should not create another connection to the database, that is provided you do not pg_close the connection between calls. Manual Excerpt: If a second call is made to pg_connect() with the same connection_string, no new connection will be established, but instead, the connection resource of the already opened connection will be returned. You can have multiple connections to the same database if you use different connection string. The manual page for pg_connect is: http://www.php.net/manual/en/function.pg-connect.php Storing your db connection as a global will work. On Thu, 2003-01-09 at 21:56, Jean-Christian Imbeault wrote: Hi. I thought I had abstracted out the SQL querying part of my code out, just to find out today that it doesn't work when it comes to transactions. I had come up with this code: function sql_query($sql) { $conn = pg_connect(dbname=JC user=postgres); $res = pg_exec($conn, $sql); if (!$res) { echo CONNECTION: could not execute query ($sql)br; die; } else return $res; } I had transactions in my code implemented like this: $sql = BEGIN; sql_query($sql); [some sql that should be in a transaction ...] $sql = COMMIT; sql_query($sql); This doesn't work. Now that I look at my code I clearly see why. All sql queries are executed using a new Postgres connection, hence the use of BEGIN/COMMIT as I was using them have no effect. Can someone recommend a way to abstract out my DB layer while still being able to use transactions? I was thinking of using the same function but if the incoming query contained the word BEGIN, saving that and all future queries in a session var and when the COMMIT comes in executing all the saved queries as one (i.e. BEGIN;[];COMMIT). One drawback is that all queries will be written out to disk (as session vars) and that will slow things down. Another drawback is that I have to abort if not COMMIT comes in. And a few more drawbacks ... I was also thinking about maybe the $sql a GLOBAL or first building up my query as as long string (BEGIN;[];COMMIT) and *then* sending it to my sql_query() function. The last two seem easier to implement, safer, and more efficient but they don't seem elegant because I haven't abstracted out the fact that I want a transaction. Whenever I write an SQL query I have to think does this need to be in a transaction and then use a different coding technique depending on the answer. And if the future something that didn't need to be in a transaction now needs to be in a transaction I have to revisit my code and change the code. I'm sure someone out there must have thought about this and come up with an elegant solution and way of abstracting out the DB layer from PHP. Can anyone share their solution with me or give me some pointers to reference material? Thanks, Jc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Jason Sheets wrote: Manual Excerpt: If a second call is made to pg_connect() with the same connection_string, no new connection will be established, but instead, the connection resource of the already opened connection will be returned. You can have multiple connections to the same database if you use different connection string. You're right! I did some more testing and the problem is with my testing code. I don't know why but the following code times out *but*, PHP throws an error saying the code has timed out *but* calling connection_status() says the code did *not* time out! Any idea why connection_status() returns 0 when it should return 2?? My code: set_time_limit(2); echo set execution limit to 2 seconds BR; register_shutdown_function(timed_out); require_once(db_functions/sql_query.inc); $sql = BEGIN;; $res = sql_query($sql); $sql = insert into test(test) values('testing 4');; $res = sql_query($sql); //This will cause the script to time out $i = 0; while(true) {$i++;} $sql = COMMIT;; $res = sql_query($sql); function timed_out() { $status = connection_status(); if ($status == 2) { echo the script timed out BR; } else echo no time out. Connection status is $status BR; } The OUPUT: set execution limit to 2 seconds Fatal error: Maximum execution time of 2 seconds exceeded in /www/htdocs/jc/shut.php on line 16 no time out. Connection status is 0 Jc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
Jean-Christian If you are only doing an insert then you do not need the transactions BEGIN and COMMIT because that is already done for you on a single insert. PGSQL is transaction based so if it does not go then it will not work. -Ray On Thu, 2003-01-09 at 22:23, Jean-Christian Imbeault wrote: Jason Sheets wrote: Manual Excerpt: If a second call is made to pg_connect() with the same connection_string, no new connection will be established, but instead, the connection resource of the already opened connection will be returned. You can have multiple connections to the same database if you use different connection string. You're right! I did some more testing and the problem is with my testing code. I don't know why but the following code times out *but*, PHP throws an error saying the code has timed out *but* calling connection_status() says the code did *not* time out! Any idea why connection_status() returns 0 when it should return 2?? My code: set_time_limit(2); echo set execution limit to 2 seconds BR; register_shutdown_function(timed_out); require_once(db_functions/sql_query.inc); $sql = BEGIN;; $res = sql_query($sql); $sql = insert into test(test) values('testing 4');; $res = sql_query($sql); //This will cause the script to time out $i = 0; while(true) {$i++;} $sql = COMMIT;; $res = sql_query($sql); function timed_out() { $status = connection_status(); if ($status == 2) { echo the script timed out BR; } else echo no time out. Connection status is $status BR; } The OUPUT: set execution limit to 2 seconds Fatal error: Maximum execution time of 2 seconds exceeded in /www/htdocs/jc/shut.php on line 16 no time out. Connection status is 0 Jc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PostgreSQL/PHP: transactions: how-to abstract out?
You could try leaving off the ;... Try $sql = BEGIN Try $sql = COMMIT That should work... On Thu, 2003-01-09 at 22:23, Jean-Christian Imbeault wrote: Jason Sheets wrote: Manual Excerpt: If a second call is made to pg_connect() with the same connection_string, no new connection will be established, but instead, the connection resource of the already opened connection will be returned. You can have multiple connections to the same database if you use different connection string. You're right! I did some more testing and the problem is with my testing code. I don't know why but the following code times out *but*, PHP throws an error saying the code has timed out *but* calling connection_status() says the code did *not* time out! Any idea why connection_status() returns 0 when it should return 2?? My code: set_time_limit(2); echo set execution limit to 2 seconds BR; register_shutdown_function(timed_out); require_once(db_functions/sql_query.inc); $sql = BEGIN;; $res = sql_query($sql); $sql = insert into test(test) values('testing 4');; $res = sql_query($sql); //This will cause the script to time out $i = 0; while(true) {$i++;} $sql = COMMIT;; $res = sql_query($sql); function timed_out() { $status = connection_status(); if ($status == 2) { echo the script timed out BR; } else echo no time out. Connection status is $status BR; } The OUPUT: set execution limit to 2 seconds Fatal error: Maximum execution time of 2 seconds exceeded in /www/htdocs/jc/shut.php on line 16 no time out. Connection status is 0 Jc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: PostgreSQL/PHP: transactions: how-to abstract out?
To all who replied to my initial question ... I actually did *not* have problems with transactions in the way I first implemented my abstraction layer. In the case of PHP If a second call is made to pg_connect() with the same connection_string, no new connection will be established, but instead, the connection resource of the already opened connection will be returned. The bug was in my transaction testing code. I was forcing PHP to time out to check weather the transaction went through or not. In the script I was catching the time-out but it so happens that there is a bug in PHP when it comes to catching connection time outs using the connection_status() function ... Jc -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] transactions
look at the syntax for locking the tables. Curtis - Original Message - From: "Christian Dechery" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, February 11, 2001 7:14 PM Subject: [PHP] transactions Hi, I was reading mysql's manual, about transactions and all... and I didn't find what they said about 'atomic operations' being as safe as transactions. I couldn't figure out HOW to update 5 tables at a time ENSURING that ALL will be update or NONE. How can this be done without transactions? With code? I don't think so... can anyone clear my mind here... I have this problem... I need to update 4 tables at once, and if something goes wrong I have to UNDO everything . Christian Dechery (lemming) . http://www.tanamesa.com.br . Gaita-L Owner / Web Developer -- PHP General 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] -- PHP General 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]