Page 1 of 1

PHP Transactions on SQL 2005

Posted: Fri Jul 14, 2006 1:05 pm
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");

Posted: Fri Jul 14, 2006 7:09 pm
by tecktalkcm0391
post more of your code please :D

Posted: Sat Jul 15, 2006 1:03 am
by Son Volt
tecktalkcm0391 wrote:post more of your code please :D

more of my code? Why?

Posted: Sat Jul 15, 2006 1:11 am
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");
?>