MSSQL 2000 THreads

[chr158]

Hi there,

Does anybody now how to implement MSSQL 2000 transactions into PHP? I now that MySQL allows the transactions to be forged but as I am using MSSQL that is no good. Untill I can start and end a transaction before a commit my so called mini project can not go ahead. Any help would be much appreciated.

Cheers and Thanks In Advance

Chris 😕

[Sxooter]

Does

connect
begin;
update ...
insert ...
delete...
commit;
disconnect

not work?

[chr158]

Yes all of my other PHP works fine. To stop any crap data flying around and to prevent any mishaps I am trying use transactions to create repeatable reads.

$sql_trans_start = mssql_query("BEGIN TRANSACTION;");
$sql_trans = mssql_query("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;");
$sql_trans_end = mssql_query("COMMIT TRANSACTION;");

$execute_start = @mssql_execute($sql_trans_start);
$execute = @mssql_execute($sql_trans);
$sql = "SELECT * FROM TABLE order by COLUMN asc ";

$sql_result = mssql_query($sql) or die("Couldn't execute query.");
$num = @mssql_num_rows($sql_result);

$row = @mssql_fetch_array($sql_result);

$execute_end = @mssql_execute($sql_trans_end);

This code now starts and ends the transactions but does not fire the sql for the select. If I take the $executes out then the sql query works but no transactions are created.

cheers

Chris B
UK

[Sxooter]

Do you have to include a ; at the end of your select query to make MSSQL happy?

[chr158]

I had seen that after I had posted but it is not required, because if I take all of the EXECUTES and SQL_TRANS_XXXX variables out then it works.

It certainly is baffling but your help here is very much apprciated.

All sites that I have looked for reference have no connection to MSSQL and transactions with PHP code. Somebody somewhere must have done this before.

Cheers

[Sxooter]

Maybe you have to commit to see the data? Just guessing. 🙂

[bastien]

yes...he would have to commit the transaction before the data is viewable...

[Sxooter]

Wow, that's remarkably different from how Postgresql works. Any chance he could try that? It pretty much still requires Unix for a production environment, but the windows native 7.2.1 port is pretty good performance wise for workstation usage and such.

[chr158]

That seems to work fine for a single page such as an input page for adding new records, but do you know if it should work over multiple pages? Say a 3 step delete page section. Pg1 Select, Pg 2 display selected record Pg 3 deleted record?

Thank you very much you guys you have been amazing.

[chr158]

Finally got it cracked. Thank you very much for your help:

8¬)

I have my transactions set on only the final delete page - this principal should work for modifying data also.

Heres the code. Do you think it looks right?

<?php

//start a transaction
@mssql_query("BEGIN WORK");

$sql = "DELETE FROM TABLE WHERE COLUMN = \"$VAR1\" ";

$sql_result = @mssql_query($sql) or die("Couldn't execute query.");
if (!$sql_result)
{
$feedback .= pg_errormessage($s);
$feedback .= ' Error - Database didn\'t return next value ';
@mssql_query("ROLLBACK");
return false;
}

else
{

#########################################################

The Connection and PHP will pause for 5 seconds -

A good test to test transactions is to up to 20,

open a new session to see if the record is

available for selection

#########################################################

sleep(5);
echo "
<TR>
<TD ALIGN=\"CENTER\">
<FONT FACE=Arial SIZE=3>Your selected record has been removed </FONT>
</TD>
</TR>
";
//commit this transaction
@mssql_query("COMMIT");
return true;
}

?>

Cheers