Lock table during update?

tduncklee · #1 (**permalink**) 06-01-08, 02:10

I am using Pervasive 9.5 windows server (via Linux ODBC client) and have a situation I do not know how to handle. The app I'm working with has a table with one record in it. The record is used to keep the next available unique id. The way the system is developed, to get the next id you lock the table, create a new record with the id incremented by 1. Then delete the old record and unlock the table.

I can not figure out how to lock a table with PSQL and I don't see a way to both get the id and update the record in one statement so the locking is done for me.

I need to find a way to get the ID and increment it and be sure no one else managed to get the ID also before i increment it.

TIA for any help!
TD

mirtheil · #2 (**permalink**) 06-01-08, 08:37

Take a look at the "FOR UPDATE" section of the SELECT:
http://www.pervasive.com/library/doc...html#wp2010939

You might meed to update to tje latest v9.52 for it to work though.

tduncklee · #3 (**permalink**) 06-01-08, 16:59

mirtheil,

Looks like FOR UPDATE is exactly what I need. Still having problems though. Here's what I'm trying to do:

Code:

create procedure GetNextOrderID(IN :user CHAR(8))
RETURNS( nxtid INTEGER)
AS
BEGIN
	DECLARE :nxtid INTEGER;
	SELECT Nextsysdocid FROM Oenextsy FOR UPDATE;
	SET :nxtid = Nextsysdocid + 1;
	INSERT INTO Oenextsy (Nextsysdocid, Recuserid) VALUES(':nxtid',':user');
	RETURN :nxtid;
END;

The above gives me the semi bogus error:

Code:

Invalid Variable name: 'Nextsysdocid' missing ':' prefix.

I say semi bogus because i also get an error if I put a : in front of it.

I have searched and searched and I can not find how to do a select and pass the selected data back to the calling procedure. Seems so basic but I can't figure it out...

mirtheil · #4 (**permalink**) 06-01-08, 17:11

When using a Stored Procedure, any values you want to use in the SP from a Select needs to be selected INTO a variable. Something like:

Code:

create procedure GetNextOrderID(IN :user CHAR(8))
RETURNS( nxtid INTEGER)
AS
BEGIN
	DECLARE :nxtid INTEGER;
        DECLARE :NextSysDocId INTEGER;
	SELECT Nextsysdocid into :NextSysDocId FROM Oenextsy FOR UPDATE;
	SET :nxtid = :NextSysDocId  + 1;
	INSERT INTO Oenextsy (Nextsysdocid, Recuserid) VALUES(':nxtid',':user');
	RETURN :nxtid;
END;

tduncklee · #5 (**permalink**) 06-01-08, 17:43

Not quite there yet...

When creating the procedure the above gives me:

Code:

 'FOR': Syntax error

Just for jollies I removed the FOR UPDATE and I get this when i call it:

Code:

Invalid 'Return'. Procedure does not return a value.

Maybe this is why?
http://www.pervasive.com/kb/kb.asp?f...RETURNS+clause.

I hate to think that I have to create another table just to get the next id...

mirtheil · #6 (**permalink**) 06-01-08, 19:36

That KB applies to PSQL 2000i SP4. You're running PSQL v9.5. I tested the steps in the KB on v10 and it worked correctly.

I just went back to the Create Procedure docs and found the following example:

Code:

DECLARE c1 CURSOR FOR SELECT name FROM course  
        WHERE name = :CourseName FOR UPDATE; 
    OPEN c1; 
    FETCH NEXT FROM c1 INTO :CourseName; 
    DELETE WHERE CURRENT OF c1; 
    CLOSE c1;

You'll probably need to declare the cursor and select for update that way..

tduncklee · #7 (**permalink**) 06-01-08, 19:36

Got it almost working. I create a temp table to stick the id in before I call the stored procedure then fetch the id from the temp table afterwards.

Code:

create procedure GetNextOrderID(IN :user CHAR(8))
AS
START TRANSACTION
	DECLARE :nxtid INTEGER;
	DECLARE :curid INTEGER;
	SELECT TOP 1 Nextsysdocid into :curid FROM Oenextsy ORDER BY Nextsysdocid ASC FOR UPDATE;
	SET :nxtid = :curid  + 1;
	INSERT INTO Oenextsy ( Nextsysdocid, Recuserid, Recdate, Rectime ) VALUES( :nxtid, :user, curdate(), CURRENT_TIME() );
	INSERT INTO ##tmpidtable (ID) VALUES(:nxtid);
	DELETE FROM Oenextsy WHERE Nextsysdocid=:curid;
COMMIT WORK;

With the above code I get the following error when creating the procedure:

Code:

'FOR': Syntax error

If I remove "FOR UPDATE" then it works fine. However, as I understand it, another user could fetch the same id before the Nextsysdocid table is updated. Any idea why Pervasive does not like the FOR UPDATE?

tduncklee · #8 (**permalink**) 06-01-08, 19:39

Oops. Posted same time you did. I will try your example now.

tduncklee · #9 (**permalink**) 06-01-08, 19:53

For anyone else trying to do this, here's the working code:

Code:

$query = 'create procedure GetNextOrderID(IN :user CHAR(8)) AS
BEGIN
	DECLARE :nxtid INTEGER;
	DECLARE :curid INTEGER;
	DECLARE c1 CURSOR FOR SELECT TOP 1 Nextsysdocid FROM Oenextsy ORDER BY Nextsysdocid ASC FOR UPDATE;
	OPEN c1;
	FETCH NEXT FROM c1 INTO :curid;
	SET :nxtid = :curid  + 1;
	INSERT INTO Oenextsy ( Nextsysdocid, Recuserid, Recdate, Rectime ) VALUES( :nxtid, :user, curdate(), CURRENT_TIME() );
	INSERT INTO ##tmpidtable (ID) VALUES(:nxtid);
	DELETE FROM Oenextsy WHERE Nextsysdocid=:curid;
END;';
odbc_exec($connect, $query);

The above stored procedure is called from php like this:

Code:

$query = "CREATE TABLE ##tmpidtable( ID UBIGINT );";
$result = odbc_exec($connect, $query);

$user = "TIM";
$query = "CALL GetNextOrderID('".$user."');";
odbc_exec($connect, $query);

$query = "SELECT * from ##tmpidtable";            
$result = odbc_exec($connect, $query);
odbc_fetch_row($result);
echo "result from tmp table:".odbc_result($result, 1);

mirtheil, THANK YOU!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech