Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    6

    Unanswered: Lock table during update?

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jun 2008
    Posts
    6
    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...

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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;
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Jun 2008
    Posts
    6
    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...

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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..
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Jun 2008
    Posts
    6
    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?

  8. #8
    Join Date
    Jun 2008
    Posts
    6
    Oops. Posted same time you did. I will try your example now.

  9. #9
    Join Date
    Jun 2008
    Posts
    6

    Followup

    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!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •