concurrent updates on firebird db in php5 application
Posted: Sat Oct 23, 2004 6:17 am
hi folks,
got a little problem.
i try to implemt a customer management running on a firebird1.5 using php.
its not a fancy thing but i got a huge problem which i seem to be unable to solve by myself.
i figures it would be simple in fb to use row level locking to detect concurrent updates on my customer database. the fb-manual hinted one should use
ok, i tried that. it does not seem to work that well. alternatively i tried to solve it using uncommited queries. finally it worked.
so now i use the following to lock a row in my customer db :
at first sight it seems to be quite stupid but if one does that in an ibase_trans and does not commit this transaction the database row seems to be locked.
now i execute my update query.
so much for the theory. now the problem implementing this.
i create an object "class FIREBIRD_DB" that looks like this:
now i post the following information to "LOCKRECORD.PHP"
this does the following :
now comes the problem, here i want to edit the customers data (e.g. name, street, city...). after that i want to be able to push a button or something else that executes the update query and commits the transaction so if anyone in the system would try to edit the specific record would receive a message that it is currently being edited.
if anyone has an insight please tell me.
thanks in advance.
[/php_man]
got a little problem.
i try to implemt a customer management running on a firebird1.5 using php.
its not a fancy thing but i got a huge problem which i seem to be unable to solve by myself.
i figures it would be simple in fb to use row level locking to detect concurrent updates on my customer database. the fb-manual hinted one should use
Code: Select all
SELECT x FROM myDB WHERE primaryKey=XXXX їb]FOR UPDATEї/b]so now i use the following to lock a row in my customer db :
Code: Select all
update CUSTOMER set cust_number=$cust_number where cust_number=$cust_number;now i execute my update query
Code: Select all
update CUSTOMER set ...... where cust_number = $cust_number;so much for the theory. now the problem implementing this.
i create an object "class FIREBIRD_DB" that looks like this:
Code: Select all
<?php
class FirebirdDB
{
var $DBHANDLE = "";
var $DBASE = "e:\firebird\db\gop.fdb";
var $USER = "";
var $PASS = "";
var $SERVER = "localhost";
var $TRANSHANDLE = ""; //das handle, wo eine offene, gelockte transaction drin steht
// var $DBQUERY = ""; //die aktuelle anfrage
var $RESULTSET = NULL; //das aktuelle resultset
var $ERRORCODE = 0; //no error - initwert
var $ERRORMESSAGE = "no error"; //init wert
var $temp = "";
function login ($user, $pass)
{
$this->USER = $user;
$this->PASS = $pass;
$_SESSIONї'user_name'] = $user;
$_SESSIONї'user_password'] = $pass;
$firebird_host = "$this->SERVER:$this->DBASE";
$conn = ibase_connect($firebird_host, $this->USER, $this->PASS,'ISO8859_1',0,3)
or die(ibase_errmsg());
echo "connected to $this->SERVER<br>";
$this->DBHANDLE = $conn;
return true;
}
function logout()
{
// ibase_free_query($this->DBQUERY);
if ($this->RESULTSET != NULL)
ibase_free_result($this->RESULTSET);
ibase_close($this->DBHANDLE);
echo "logged out<br>";
}
// *****************************************************************
// Firebird Specific methods
// *****************************************************************
function get_errorcode()
{
return $this->ERRORCODE;
}
function get_errormessage()
{
return $this->ERRORMESSAGE;
}
function get_data() //returns die daten aus dem aktuellen resultset
{
if ($this->RESULTSET == NULL)
{
echo "keine daten <br>";
return false;
}
//$row_cnt = 0; // Counter für Anzahl Rows!
$data_array = array();
while ($row = ibase_fetch_row($this->RESULTSET)) //solange eine Zeile vorhanden ist
{
//$row_cnt++;
$data_arrayї] = $row;
}
return $data_array;
}
function execute_query($query)
{
$this->RESULTSET = ibase_query($this->DBHANDLE, $query) or die(ibase_errmsg());
// $this->RESULTSET=ibase_query($query); //speichert das anfrageergebnis in RESULTSET
if ($this->RESULTSET != NULL)
return true;
else
return false;
}
function execute_trans($query)
{
$this->TRANSHANDLE = ibase_trans(IBASE_WRITE|IBASE_CONCURRENCY|IBASE_NOWAIT);
$this->RESULTSET = ibase_query($this->TRANSHANDLE,$query);
if (ibase_errcode() != 0)
{
$this->ERRORCODE = ibase_errcode();
$this->ERRORMESSAGE = ibase_errmsg();
echo "$this->ERRORMESSAGE<br>\n";
return false;
}
return true;
}
function free_result()
{
if ($this->RESULTSET != NULL)
ibase_free_result($this->RESULTSET);
}
function commit_trans()
{
ibase_commit($this->TRANSHANDLE);
}
function rollback_trans()
{
ibase_rollback($this->TRANSHANDLE);
}
function gettemp()
{
return $this->temp;
}
function settemp($wert)
{
$this->temp = $wert;
}
} //class FirebirdDB
?>Code: Select all
<form name="form1" method="post" action="lockrecord.php">
<input name="textfield" type="text" value="1001">
<input name="action" type="submit" id="action" value="lock">
</form>Code: Select all
if (isset($_SESSIONї'db']))
{
$dbo = $_SESSIONї'db'];
$dbo->login('SYSDBA','masterkey');
if ($dbo->execute_trans("UPDATE KUNDEN SET KUNDENNUMMER = 1001 WHERE KUNDENNUMMER = 1001"))
{if anyone has an insight please tell me.
thanks in advance.
[/php_man]