PHP Transactions on SQL 2005

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Son Volt
Forum Newbie
Posts: 19
Joined: Tue Jul 11, 2006 1:36 pm

PHP Transactions on SQL 2005

Post by Son Volt »

I'm trying to get a transaction to work with PHP 5.1.4 and SQL Server
2005... The first SQL statement deletes several rows in the database.
The next SQL statement loops through and inserts several rows into the
same table. What I'm wanting is for the database to rollback all
transactions if any 1 of the inserts fail. What's happening now is the
DELETE statement runs successfully deleting several rows... but as soon
as one of the INSERTS fail, the page errors out WITHOUT rolling back
the DELETE statements. What am I doing wrong in my code?

thanks,

~john

Code: Select all

mssql_query("BEGIN TRAN");

$sql = "DELETE FROM MyTable WHERE Value > 0";

$result = mssql_query($sql);

foreach(loopVar as val)
{
          $sql = "INSERT INTO MyTable VALUES(1)";
          $result = mssql_query($sql);

          if( ! $result ){
                mssql_query('ROLLBACK TRAN');
                exit;
          }

}                      

mssql_query("COMMIT TRAN");
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

post more of your code please :D
Son Volt
Forum Newbie
Posts: 19
Joined: Tue Jul 11, 2006 1:36 pm

Post by Son Volt »

tecktalkcm0391 wrote:post more of your code please :D

more of my code? Why?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

This will probably not do anything, but try to humor me. See what you get.

Code: Select all

<?php
mssql_query("BEGIN TRAN");

$sql = "DELETE FROM MyTable WHERE Value > 0";

$result = mssql_query($sql);

foreach(loopVar as val)
{
          $sql = "INSERT INTO MyTable VALUES(1)";
          $result = mssql_query($sql);

          if( ! $result ){
                $exit = mssql_query('ROLLBACK TRAN');
                if ($exit) {
                      echo 'The rolback worked!';
                } else {
                      die('The roll back did not work!';
                }
          }

}                     

mssql_query("COMMIT TRAN");
?>
Post Reply