concurrent updates on firebird db in php5 application

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ynoc
Forum Newbie
Posts: 3
Joined: Sat Oct 23, 2004 5:56 am

concurrent updates on firebird db in php5 application

Post by ynoc »

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

Code: Select all

SELECT x FROM myDB WHERE primaryKey=XXXX їb]FOR UPDATEї/b]
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 :

Code: Select all

update CUSTOMER set cust_number=$cust_number where cust_number=$cust_number;
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

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
	&#123;
		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)
	&#123;
		$this->USER = $user;
		$this->PASS = $pass;
		
		$_SESSION&#1111;'user_name'] = $user;
		$_SESSION&#1111;'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;
	&#125;
	
	function logout()
	&#123;
//		ibase_free_query($this->DBQUERY);
		if ($this->RESULTSET != NULL)
			ibase_free_result($this->RESULTSET); 
		ibase_close($this->DBHANDLE);
		echo "logged out<br>";
	&#125;
	
//	*****************************************************************
//					  Firebird Specific methods
//	*****************************************************************

	function get_errorcode()
	&#123;
		return $this->ERRORCODE;
	&#125;
	
	function get_errormessage()
	&#123;
		return $this->ERRORMESSAGE;
	&#125;

	function get_data()  //returns die daten aus dem aktuellen resultset
	&#123;
		if ($this->RESULTSET == NULL)
		&#123;
			echo "keine daten <br>";
			return false;
		&#125;
		//$row_cnt = 0; // Counter für Anzahl Rows!
		$data_array = array();
		while ($row = ibase_fetch_row($this->RESULTSET)) //solange eine Zeile vorhanden ist
		&#123;
			//$row_cnt++;
			$data_array&#1111;] = $row;
		&#125;
		return $data_array;
	&#125;

	function execute_query($query)
	&#123;
		$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;
	&#125;
	
	function execute_trans($query)
	&#123;
		$this->TRANSHANDLE = ibase_trans(IBASE_WRITE|IBASE_CONCURRENCY|IBASE_NOWAIT);
		$this->RESULTSET = ibase_query($this->TRANSHANDLE,$query);
		if (ibase_errcode() != 0)
		&#123;
			$this->ERRORCODE = ibase_errcode();	
			$this->ERRORMESSAGE = ibase_errmsg();
			echo "$this->ERRORMESSAGE<br>\n";
			return false;
		&#125; 
		return true;
	&#125;
	
	function free_result()
	&#123;
		if ($this->RESULTSET != NULL)
			ibase_free_result($this->RESULTSET);
	&#125;
	
	function commit_trans()
	&#123;
		ibase_commit($this->TRANSHANDLE);
	&#125;
	
	function rollback_trans()
	&#123;
		ibase_rollback($this->TRANSHANDLE);
	&#125;
	
	function gettemp()
	&#123;
		return $this->temp;
	&#125;
	
	function settemp($wert)
	&#123;
		$this->temp = $wert;
	&#125;
	
	&#125; //class FirebirdDB

?>
now i post the following information to "LOCKRECORD.PHP"

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>
this does the following :

Code: Select all

if (isset($_SESSION&#1111;'db']))
&#123;
	$dbo = $_SESSION&#1111;'db'];
	$dbo->login('SYSDBA','masterkey');
	if ($dbo->execute_trans("UPDATE KUNDEN SET KUNDENNUMMER = 1001 WHERE KUNDENNUMMER = 1001"))
		&#123;
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]
Post Reply