I made a change to Stored Proc - Im using Firebird 2.1

CREATE PROCEDURE GETSYSVAL(
CLTID INTEGER)
RETURNS(
SEQVAL INTEGER)
AS
DECLARE VARIABLE IVAL1 INTEGER;
DECLARE VARIABLE IVAL2 INTEGER;
DECLARE VARIABLE sVAL INTEGER;
BEGIN
/* Procedure body */
SELECT ORDERSEQ FROM SYSVALS WHERE CLIENTID = :CLTID for update WITH LOCK INTO :iVAL1 ;

begin

SVAL = iVAL1 + 1;
UPDATE SYSVALS SET ORDERSEQ = :SVAL WHERE CLIENTID = :CLTID AND ORDERSEQ = :iVAL1;
SELECT ORDERSEQ FROM SYSVALS WHERE CLIENTID = :CLTID INTO :IVAL2 ;
if ( SVAL <> IVAL2) then
SVAL = 0 ;
SEQVAL = SVAL;

suspend;
end
END;

I am using FIBPlus StoredProc component in my Delphi App and so far so good. Just need build up a mechanism around call to catch the exception and let it wait and try again in loop to handle the inevitable simultaneous request.

Cheers!

--- In firebird-support@yahoogroups.com, "First L" <lfeliz@...> wrote:
>
> I have following table
>
> CREATE TABLE SYSVALS (
> CLIENTID D_INTEGER NOT NULL,
> ORDERSEQ D_INTEGER NOT NULL,
> INVOICESEQ D_INTEGER NOT NULL);
>
>
> ALTER TABLE SYSVALS ADD PRIMARY KEY (CLIENTID);
>
> One record exists per customer. Customers want to choose their own order and invoice sequence.
>
> So I was thinking a simple stored procedure to all my app to get the next record for the client specified. Below is my first attempt at storedproc, but it increments the value by 2.
>
> CREATE PROCEDURE GETSYSVAL(
> CLTID INTEGER)
> RETURNS(
> SEQVAL INTEGER)
> AS
> DECLARE VARIABLE iVAL1 INTEGER;
> DECLARE VARIABLE iVAL2 INTEGER;
> BEGIN
> /* Procedure body */
> SELECT ORDERSEQ FROM SYSVALS WHERE CLIENTID = :CLTID INTO iVAL1 ;
> SEQVAL = iVAL1 + 1;
> UPDATE SYSVALS SET ORDERSEQ = :SEQVAL WHERE CLIENTID = :CLTID AND ORDERSEQ = :iVAL1;
> SELECT ORDERSEQ FROM SYSVALS WHERE CLIENTID = :CLTID INTO iVAL1 ;
> IF (iVAL1 <> SEQVAL) THEN
> SEQVAL = 0;
> SUSPEND;
> END;
>
> Any ideas? IS there a better way to do this. Generators are used in the app, but only for system wide values. Have had 2 pilot users request the ability to have their own unique value. I dont want to have to multiple sequences and complicated code to manage clients and sequences.
>
> Anyway, thanks for taking a look at this.
>
> - Lou
>