|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Transactions in PHP on SQL Server 20052005... 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 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"); I do not know PHP, but it is obviously that how do you handle error in your
code: if error occurs during a transaction, your routine should continue to run so that the kine of code calling "ROLLBACK TRAN" can be reached and executed. So, this line of code it where problem is: $result = mssql_query($sql); You have to make sure msssql_query() ALWAYS return something no matter what happens. I do not know what the statement does behind the sceen, besides executing the sql statement, but certainly the simple "INSERT INTO..." sql statement does not do anything if the SQL Server fails the insertion. Unless the PHP's mssql_query() method guarantees you that it always returns something and never break, you have to handle the possible runtime exception (I do believe PHP has some exception handling mechanism, does it?) to ensure the ROLLBACK TRAN call can be executed if necessary. I have to say, this has little to do with SQL Server itself. Show quote "~john" <SonVolt***@gmail.com> wrote in message news:1152978014.550173.293830@35g2000cwc.googlegroups.com... > 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 > > 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"); > In addition to Norman's suggestion, why are you not using stored
procedures to handle the transaction for you? You minimize the possibility of SQL injection becuase you'll be using parameters, and you increase the likelihood of a performance gain, and you're avoiding some of the pain of trying to manage transactions at the client level. Of course, there are reasons to NOT use stored procedures, but you didn't specify one, so I'm assuming that a stored proc is a aviable option. Stu Norman Yuan wrote: Show quote > I do not know PHP, but it is obviously that how do you handle error in your > code: if error occurs during a transaction, your routine should continue to > run so that the kine of code calling "ROLLBACK TRAN" can be reached and > executed. > > So, this line of code it where problem is: > > $result = mssql_query($sql); > > You have to make sure msssql_query() ALWAYS return something no matter what > happens. I do not know what the statement does behind the sceen, besides > executing the sql statement, but certainly the simple "INSERT INTO..." sql > statement does not do anything if the SQL Server fails the insertion. Unless > the PHP's mssql_query() method guarantees you that it always returns > something and never break, you have to handle the possible runtime exception > (I do believe PHP has some exception handling mechanism, does it?) to ensure > the ROLLBACK TRAN call can be executed if necessary. > > I have to say, this has little to do with SQL Server itself. > > "~john" <SonVolt***@gmail.com> wrote in message > news:1152978014.550173.293830@35g2000cwc.googlegroups.com... > > 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 > > > > 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"); > > Stu wrote:
> In addition to Norman's suggestion, why are you not using stored Well... yeah, I suppose it's an option but this is an applicatoin for a> procedures to handle the transaction for you? You minimize the > possibility of SQL injection becuase you'll be using parameters, and > you increase the likelihood of a performance gain, and you're avoiding > some of the pain of trying to manage transactions at the client level. corporate intranet cut off from the outside world so I'm not overly concerned with SQL injection and what not. I've tried using stored procs in the past but I found SQL programming to be combersome (what, no arrays? :P).. maybe I'll give it another shot. Let's say I do use a stored procedure... When I'm inserting multiple rows inside the loop, how would I handle this in a stored proc? I'm guessing you can't pass in an array to loop through your inserts, so that leaves you just calling the stored proc from within my PHP code's loop... if that's the case how would i handle the transaction since I need a START and COMMIT before and after all the INSERTS and DELETES ~john ~john wrote:
> Stu wrote: Funny; I felt the same way about PHP :) Iterations?> > In addition to Norman's suggestion, why are you not using stored > > procedures to handle the transaction for you? You minimize the > > possibility of SQL injection becuase you'll be using parameters, and > > you increase the likelihood of a performance gain, and you're avoiding > > some of the pain of trying to manage transactions at the client level. > > > Well... yeah, I suppose it's an option but this is an applicatoin for a > corporate intranet cut off from the outside world so I'm not overly > concerned with SQL injection and what not. I've tried using stored > procs in the past but I found SQL programming to be combersome (what, > no arrays? :P).. maybe I'll give it another shot. > Actually, there are ways to emulate arrays in SQL; Erland has an> Let's say I do use a stored procedure... When I'm inserting multiple > rows inside the loop, how would I handle this in a stored proc? I'm > guessing you can't pass in an array to loop through your inserts, so > that leaves you just calling the stored proc from within my PHP code's > loop... if that's the case how would i handle the transaction since I > need a START and COMMIT before and after all the INSERTS and DELETES > > ~john excellent page on this at http://www.sommarskog.se/arrays-in-sql.html. Although he advocates NOT using OpenXML to emulate an array, I've used this method before to pass in several rows at a time to be processed within a single stored procedure. Ultimately, though, it does come down to handling the transaction with both a commit and a rollback, and unfortunately, I don't know enough about PHP to help you much further than what you have already done. It does appear to me that you are not opening a transaction that encompasses the commands following it; rather, you tell SQL Server to Begin a Transaction, and then you return from it back to the client layer which implicitly commits that transaction. Does PHP use ADO to manage the connection to SQL Server? Stu Stu (stuart.ainswo***@gmail.com) writes:
> Actually, there are ways to emulate arrays in SQL; Erland has an Nah, I would say that all if you need is a list of single values, then> excellent page on this at http://www.sommarskog.se/arrays-in-sql.html. > Although he advocates NOT using OpenXML to emulate an array, I've used > this method before to pass in several rows at a time to be processed > within a single stored procedure. XML is a bit of overkill. But if you want to send down a set of rows for insertion, XML is indeed the way to go. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ~john wrote:
Show quote > Stu wrote: You can pass a delimited string to a stored proc and then parse within >> In addition to Norman's suggestion, why are you not using stored >> procedures to handle the transaction for you? You minimize the >> possibility of SQL injection becuase you'll be using parameters, and >> you increase the likelihood of a performance gain, and you're avoiding >> some of the pain of trying to manage transactions at the client level. > > > Well... yeah, I suppose it's an option but this is an applicatoin for a > corporate intranet cut off from the outside world so I'm not overly > concerned with SQL injection and what not. I've tried using stored > procs in the past but I found SQL programming to be combersome (what, > no arrays? :P).. maybe I'll give it another shot. > > Let's say I do use a stored procedure... When I'm inserting multiple > rows inside the loop, how would I handle this in a stored proc? I'm > guessing you can't pass in an array to loop through your inserts, so > that leaves you just calling the stored proc from within my PHP code's > loop... if that's the case how would i handle the transaction since I > need a START and COMMIT before and after all the INSERTS and DELETES > > ~john > the proc: http://realsqlguy.com/twiki/bin/view/RealSQLGuy/ParseDelimitedStringToTable ~john (SonVolt***@gmail.com) writes:
> Well... yeah, I suppose it's an option but this is an applicatoin for a There can be intruders on the inside as well. SQL Injection is something> corporate intranet cut off from the outside world so I'm not overly > concerned with SQL injection and what not. you should consider in any application. > I've tried using stored procs in the past but I found SQL programming to No arrays? Sure there are, but they are called tables and are far more> be combersome (what, no arrays? general in nature than arrays. > Let's say I do use a stored procedure... When I'm inserting multiple The simple-minded way would of course to be call the an SP to> rows inside the loop, how would I handle this in a stored proc? insert one row. A more effective way is to form an XML document and then have the procedure to unpack it with OPENXML and insert all rows in one go. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ***UPDATE***
Well, I'm not sure what I was smokin' the day I thought the Transactions were not working... they do in fact appear to be working as expected. Thanks everyone for all your help. And Stu... I'm using PHP's Sql Server calls that come as an included DLL with the PHP installation. ~john  |
|||||||||||||||||||||||
Other interesting topics