Transactions in PHP on SQL Server 2005

[~john]

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");

[Jerry Stuckle]

~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
==================

[~john]

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

[Jerry Stuckle]

~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
==================

[~john]

***UPDATE***

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

~john