[RESOLVED] Multiple INSERTS, transactions and rollback HELP

darkgr33n

Initially, this was going to be a question about transactions, but perhaps I'm doing the basic INSERTs incorrectly ...

I need to INSERT into 6 tables, if any of these fail, I don't want to write anything.

So, I headed off into the unknown waters of transactions [i'm a front-end developer delving in the dark of back-end work].

However, I kept receiving the following error:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

In the end, I faked the rollback by just not commiting the data [see below].

Is this way off of what I should be doing ??

Thanks for reading, if you can help, I'd appreciate it.

$commit = TRUE;

if(mssql_query("BEGIN TRAN"))
{
foreach($insert_array as $value)
{
$result = @mssql_query(${$value});
if(!$result)
{
$commit = FALSE;
}
}
if ($commit)
{
mssql_query("COMMIT TRAN");
}
else
{
// mssql_query("ROLLBACK"); // Doesn't work, ignore
echo ("DO SOMETHING TO DEAL WITH THE ERROR");
}
}

darkgr33n

UPDATE:

One thing I have noticed with this method, is that despite the INSERTS not being written, the auto-incremented ID field is still incremented. Perhaps the proper ROLLBACK prevents this.

Help ?!

darkgr33n

Of course, if I surpress the ROLLBACK error by adding a @ in front of the mssql_query, then everything works as expected, but not sure that's right thing to do !??

//start a transaction
mssql_query("BEGIN TRAN");

foreach($insert_array as $value)
{
$result = @mssql_query(${$value});

if(!$result)
{
@mssql_query("ROLLBACK TRAN");
}
}

@mssql_query("COMMIT TRAN");

someone must use transactions in this way ?

Roger_Ramjet

Personally, if I wanted to do this I would be using a stored procedure with ms sql. As I would indeed with mysql these days. Just pass in all the data you want to insert and let the stored proc run the transaction and multiple inserts.

darkgr33n

Roger Ramjet;10929722 wrote:
Personally, if I wanted to do this I would be using a stored procedure with ms sql. As I would indeed with mysql these days. Just pass in all the data you want to insert and let the stored proc run the transaction and multiple inserts.

Hey Roger

Thanks for that. I'm totally new to back-end DB design, and have read a little about stored procedures, but didn't know enough to pursue that avenue.

I guess I need to check them out, thanks.

darkgr33n

UPDATE:

Managed to get this working if anyone else is interested. I just needed to query the whole statement, rather than running separate queries:

$sql_query = "BEGIN TRANSACTION\n";
$sql_query .= "BEGIN TRY\n";

foreach($insert_array as $value)
{
$sql_query .= "${$value}\n";
}

$sql_query .= "COMMIT TRANSACTION\n";
$sql_query .= "END TRY\n";

$sql_query .= "BEGIN CATCH\n";
$sql_query .= "ROLLBACK TRANSACTION\n";
$sql_query .= "END CATCH\n";