By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,643 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

Transactions in PHP on SQL Server 2005

P: n/a
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");

Jul 14 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
~john wrote:
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");
John,

Why not try asking in a MySQL newsgroup, like comp.databases.mysql?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 15 '06 #2

P: n/a
Jerry Stuckle wrote:
John,

Why not try asking in a MySQL newsgroup, like comp.databases.mysql?


SQL Server... but anyway, I'm asking because the calling code is PHP.
The SQL by itself runs fine in SQL Server's query analzyer.

~john

Jul 15 '06 #3

P: n/a
~john wrote:
Jerry Stuckle wrote:

>>John,

Why not try asking in a MySQL newsgroup, like comp.databases.mysql?


SQL Server... but anyway, I'm asking because the calling code is PHP.
The SQL by itself runs fine in SQL Server's query analzyer.

~john
John,

Sorry - shouldn't try to replay late at night. I say mysql when you
were saying mssql.

OK, it works with SQL Server's query analyzer, so your logic is OK. But
it looks you're not actually using transactions - it's seems to be
committing after each request. There are no options in PHP to control
this. But what about SQL Server?

I would still start with a SQL Server newsgroup. That's where you're
seeing the problem, not in PHP. And that's where your troubleshooting
should start.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 15 '06 #4

P: n/a
***UPDATE***

Not sure what I was smokin' that day but the transactions do in fact
appear to be working as expected.

~john

Jul 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.