[SOLVED] Transaction Rollback Error 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 to the database, but I'm constantly getting the error above.

 

ANY IDEAS ?

 

Thanks for reading.

 

Paul

 

[darkgr33n]

UPDATE:

 

While ROLLBACK is still giving me errors, I can impersonate/bodge this by not calling the COMMIT. I've also added the error supressing @ on the looped query and am testing by passing a varchar to an int field.

 

 

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

  }

  else

  {

    echo ("DEAL WITH THE ERROR");

  }

}

 

While it seems to work, I have no idea whether this is good practice or if it will cause a bucket of problems.

 

 

Just to recap on the basic question:

I have multiple inserts [account table, application table, customer table etc], and want to only write to the database if all inserts were successful. Tried using ROLLBACK, but was getting the error in the first post. I have since read, that you can't use ROLLBACK on multiple mssql_query's, as it can only hold one in memory ?

 

Any help or guidance would be appreciated, thanks.

[darkgr33n]

 

    mssql_query($commit_transaction);

 

 

This is set outside of the above code, but is effectively:

 

mssql_query("COMMIT TRAN");

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