[SOLVED] MSSQL Transaction HELP

[darkgr33n]

Hi

 

I'm trying to use transactions, but am coming across the following error, which I can't sort out:

 

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION".

 

The code is as follows [excerpt] :

 

mssql_query("BEGIN TRAN");

 

foreach($value)

{

  $result = mssql_query($value);

  if(!$result)

  {

      mssql_query("ROLLBACK TRAN");

      exit;

  }

}

 

mssql_query("COMMIT TRAN");

 

 

The $value is one of five INSERTS, which all insert fine. If I come across an error in the insert, for example "Conversion failed when converting the varchar to data type int", I would expect ROLLBACK to be called, and nothing be written, but I'm getting the error above.

 

ANY IDEAS ?

 

Thanks for reading.

 

Paul

 

[darkgr33n]

If I do the following, it sort of works, a psudeo-rollback if you like, as it does not write to the DB on an error, but the auto-incrementing ID field in the table is incremented by one. I'm guessing a proper ROLLBACK does not increment this.

 

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

}

}

 

 

What do other people do with multiple inserts ?

 

FYI, the for loop adds about 6 INSERT commands to 6 tables. If I have no error, this works fine, but I need to make sure if I do get an error nothing is written, and ideally, nothing is incremented.

 

[Mchl]

If I do the following, it sort of works, a psudeo-rollback if you like, as it does not write to the DB on an error, but the auto-incrementing ID field in the table is incremented by one. I'm guessing a proper ROLLBACK does not increment this.

 

Don't know about MSSQL, but in MySQL it does.

[darkgr33n]

Don't know about MSSQL, but in MySQL it does.

 

Really, thanks. Perhaps then my pseudo-rollback works then ?!

 

Do you use it in a similar way to what I have above ?

 

 

 

[Mchl]

I use mysqli's autocommit and rollback methods, so it looks a bit different.

[darkgr33n]

I use mysqli's autocommit and rollback methods, so it looks a bit different.

 

Ah, ok, thanks. Don't think mssql has anything like that.

 

If I actually surpress the ROLLBACK query with @mssql_query("ROLLBACK TRAN"), everything works as expected, but not sure running and hiding from the error [The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION] is the correct thing to do ??

 

mssql_query("BEGIN TRAN");

 

foreach($insert_array as $value)

{

  $result = @mssql_query(${$value});

  if(!$result)

  {

  @mssql_query("ROLLBACK TRAN");

  }

}

 

@mssql_query("COMMIT TRAN");

 

I do remember reading something about COMMIT/ROLLBACK not working if more than one mssql_query is run as it loses its memory, but also read somewhere that was untrue. Not sure what to think.

 

Maybe I'm better using the pseudo rollback, rather than surpress the error on the real one [even though it seems to work; it doesn't write to the DB if there's an data error, and if the data is fine, it writes ok]

 

Way out of my depth on this one.

 

?

 

 

 

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

 

 

[Mchl]

Good work. Will you mark the topic as solved then?

[darkgr33n]

Good work. Will you mark the topic as solved then?

 

lol, I did try but couldn't work out how to do it!

 

EDIT: FOUND IT!

 

Thanks.