| Subject | FireBird 1.02 & 1.5, IBConsole & ISQL |
|---|---|
| Author | joeny@softhome.net |
| Post date | 2003-05-27T05:07:45Z |
Hi All,
I've encountered this when I upgraded to FireBird 1.5 RC 2
and one of my old application refused to function properly.
To illustrate, I've attached a sample script (SAMPLE.SQL).
I've tested the following under Firebird 1.02 and 1.5RC2, using
IBConsole and ISQL,
1. Created the database
2. Executed the attached script
- FB102, ISQL:
unknown command "mep"
3. Executed the following commands:
a. INSERT INTO ACCOUNT (CODE, LEVEL1, LEVEL2, LEVEL3, LEVEL4, TITLE)
VALUES (1, 1, 0, 0, 0, 'ASSETS');
- FB102, ISQL or IBConsole:
exception 2 - This account does not have valid parent.
Note: This doesn't happen if I first close the database before
inserting new records.
b. INSERT INTO ACCOUNT (CODE, LEVEL1, LEVEL2, LEVEL3, LEVEL4, TITLE)
VALUES (2, 1, 5, 0, 0, 'PETTY CASH');
c. DELETE FROM ACCOUNT
WHERE CODE = 1;
- FB15RC2, IBConsole:
deadlock
update conflicts with concurrent update on line 1: DELETE FROM
ACCOUNT WHERE CODE = 1
Notes: 1. No problems with FB15RC2 / ISQL.
2. With FB102, no problems too if I restarted the database right
after creation.
Is anything wrong with my script?
Thanks :)
----------
SET SQL DIALECT 3;
/* CREATE DATABASE 'C:\Sample.gdb' PAGE_SIZE 4096
DEFAULT CHARACTER SET ISO8859_1 */
/* Domains ----------------------------------------------------------------- */
CREATE DOMAIN BOOLEAN AS SMALLINT DEFAULT 0;
CREATE DOMAIN ACCOUNTTITLETYPE AS VARCHAR(50);
/* Generators -------------------------------------------------------------- */
CREATE GENERATOR GEN_ACCOUNTCODE;
/* Exceptions -------------------------------------------------------------- */
CREATE EXCEPTION ACCOUNT_INVALIDACCOUNTNO 'Invalid account #.';
CREATE EXCEPTION ACCOUNT_NOPARENT 'This account does not have valid parent.';
CREATE EXCEPTION ACCOUNT_HASCHILDREN 'This account already has children accounts. Cannot change account #.';
/* Stored Procedures - Forward declarations -------------------------------- */
SET TERM ^ ;
CREATE PROCEDURE PROC_GETPARENTACCOUNTCODE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
PARENTACCOUNTCODE INTEGER
)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE PROC_COMPUTEPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
POSTABLE SMALLINT
)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE PROC_UPDATEPARENTPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
AS
BEGIN
EXIT;
END^
SET TERM ; ^
/* Tables ------------------------------------------------------------------ */
/* Table: ACCOUNT, Accounts */
CREATE TABLE ACCOUNT
(
CODE INTEGER NOT NULL,
LEVEL1 INTEGER DEFAULT 1 NOT NULL,
LEVEL2 INTEGER DEFAULT 0 NOT NULL,
LEVEL3 INTEGER DEFAULT 0 NOT NULL,
LEVEL4 INTEGER DEFAULT 0 NOT NULL,
TITLE ACCOUNTTITLETYPE NOT NULL,
POSTABLE BOOLEAN DEFAULT 1 NOT NULL,
ACCOUNTNO COMPUTED BY (LEVEL1 || '.' || LEVEL2 || '.' || LEVEL3 || '.' || LEVEL4),
CONSTRAINT ACCOUNT_PRIMARYKEY PRIMARY KEY (CODE),
CONSTRAINT ACCOUNT_LEVEL UNIQUE (LEVEL1, LEVEL2, LEVEL3, LEVEL4),
CONSTRAINT ACCOUNT_TITLE UNIQUE (TITLE)
);
/* Constraints ------------------------------------------------------------- */
/* Indexes ----------------------------------------------------------------- */
/* Triggers ---------------------------------------------------------------- */
SET TERM ^ ;
/* Triggers: ACCOUNT */
CREATE TRIGGER ACCOUNT_BEFOREINSERT FOR ACCOUNT
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE FCODE INTEGER;
BEGIN
/* Check if account # is valid */
IF ((NEW.LEVEL1 < 1) OR (NEW.LEVEL2 < 0) OR
(NEW.LEVEL3 < 0) OR (NEW.LEVEL4 < 0) OR
((NEW.LEVEL4 > 0) AND ((NEW.LEVEL3 = 0) OR (NEW.LEVEL2 = 0))) OR
((NEW.LEVEL3 > 0) AND (NEW.LEVEL2 = 0))) THEN
EXCEPTION ACCOUNT_INVALIDACCOUNTNO;
/* Check for a valid parent */
EXECUTE PROCEDURE PROC_GETPARENTACCOUNTCODE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
:FCODE;
IF (FCODE IS NULL) THEN
EXCEPTION ACCOUNT_NOPARENT;
/* Update POSTABLE field */
EXECUTE PROCEDURE PROC_COMPUTEPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
NEW.POSTABLE;
END^
CREATE TRIGGER ACCOUNT_AFTERINSERT FOR ACCOUNT
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
/* Update parent account's POSTABLE field */
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4;
POST_EVENT 'ACCOUNT';
END^
CREATE TRIGGER ACCOUNT_BEFOREUPDATE FOR ACCOUNT
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE FCODE INTEGER;
BEGIN
/* Check if account # is valid */
IF ((NEW.LEVEL1 < 1) OR (NEW.LEVEL2 < 0) OR
(NEW.LEVEL3 < 0) OR (NEW.LEVEL4 < 0) OR
((NEW.LEVEL4 > 0) AND ((NEW.LEVEL3 = 0) OR (NEW.LEVEL2 = 0))) OR
((NEW.LEVEL3 > 0) AND (NEW.LEVEL2 = 0))) THEN
EXCEPTION ACCOUNT_INVALIDACCOUNTNO;
/* Check for a valid parent */
EXECUTE PROCEDURE PROC_GETPARENTACCOUNTCODE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
:FCODE;
IF ((FCODE IS NULL) OR (FCODE = OLD.CODE)) THEN
EXCEPTION ACCOUNT_NOPARENT;
/* Update POSTABLE field */
EXECUTE PROCEDURE PROC_COMPUTEPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
NEW.POSTABLE;
/* Don't allow update if account # was changed & has children */
IF ((OLD.ACCOUNTNO <> NEW.ACCOUNTNO) AND (OLD.POSTABLE = 0)) THEN
EXCEPTION ACCOUNT_HASCHILDREN;
END^
CREATE TRIGGER ACCOUNT_AFTERUPDATE FOR ACCOUNT
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
/* Update parent account's POSTABLE field */
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
OLD.LEVEL1, OLD.LEVEL2, OLD.LEVEL3, OLD.LEVEL4;
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4;
POST_EVENT 'ACCOUNT';
END^
CREATE TRIGGER ACCOUNT_BEFOREDELETE FOR ACCOUNT
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
/* Delete children accounts */
IF (OLD.LEVEL2 = 0) THEN
DELETE FROM ACCOUNT
WHERE (LEVEL1 = OLD.LEVEL1) AND
(CODE <> OLD.CODE);
ELSE
IF (OLD.LEVEL3 = 0) THEN
DELETE FROM ACCOUNT
WHERE (LEVEL1 = OLD.LEVEL1) AND
(LEVEL2 = OLD.LEVEL2) AND
(CODE <> OLD.CODE);
ELSE
IF (OLD.LEVEL4 = 0) THEN
DELETE FROM ACCOUNT
WHERE (LEVEL1 = OLD.LEVEL1) AND
(LEVEL2 = OLD.LEVEL2) AND
(LEVEL3 = OLD.LEVEL3) AND
(CODE <> OLD.CODE);
END^
CREATE TRIGGER ACCOUNT_AFTERDELETE FOR ACCOUNT
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
/* Update parent account's POSTABLE field */
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
OLD.LEVEL1, OLD.LEVEL2, OLD.LEVEL3, OLD.LEVEL4;
POST_EVENT 'ACCOUNT';
END^
SET TERM ; ^
/* Stored Procedures ------------------------------------------------------- */
SET TERM ^ ;
/* Procedure: Returns the parent account code given an account.
Returns: NULL - if no parent
0 - if is main account
else - parent account code */
ALTER PROCEDURE PROC_GETPARENTACCOUNTCODE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
PARENTACCOUNTCODE INTEGER
)
AS
BEGIN
IF (LEVEL2 + LEVEL3 + LEVEL4 = 0) THEN
PARENTACCOUNTCODE = 0;
ELSE
BEGIN
IF (LEVEL4 > 0) THEN
LEVEL4 = 0;
ELSE
IF (LEVEL3 > 0) THEN
LEVEL3 = 0;
ELSE
IF (LEVEL2 > 0) THEN
LEVEL2 = 0;
SELECT CODE
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 = :LEVEL3) AND
(LEVEL4 = :LEVEL4)
INTO :PARENTACCOUNTCODE;
END
END^
/* Procedure: Determines whether an account is postable or not */
ALTER PROCEDURE PROC_COMPUTEPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
POSTABLE SMALLINT
)
AS
BEGIN
IF (LEVEL4 > 0) THEN
POSTABLE = 0;
ELSE
IF (LEVEL3 > 0) THEN
SELECT COUNT(*)
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 = :LEVEL3) AND
(LEVEL4 > 0)
INTO :POSTABLE;
ELSE
IF (LEVEL2 > 0) THEN
SELECT COUNT(*)
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 > 0)
INTO :POSTABLE;
ELSE
SELECT COUNT(*)
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 > 0)
INTO :POSTABLE;
IF (POSTABLE = 0) THEN
POSTABLE = 1;
ELSE
POSTABLE = 0;
END^
/* Procedure: Updates postable field of parent accounts */
ALTER PROCEDURE PROC_UPDATEPARENTPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
AS
DECLARE VARIABLE FPOSTABLE SMALLINT;
BEGIN
IF (LEVEL2 + LEVEL3 + LEVEL4 = 0) THEN
EXIT;
IF (LEVEL4 > 0) THEN
LEVEL4 = 0;
ELSE
IF (LEVEL3 > 0) THEN
LEVEL3 = 0;
ELSE
IF (LEVEL2 > 0) THEN
LEVEL2 = 0;
EXECUTE PROCEDURE PROC_COMPUTEPOSTABLE
:LEVEL1, :LEVEL2, :LEVEL3, :LEVEL4
RETURNING_VALUES
:FPOSTABLE;
UPDATE ACCOUNT
SET POSTABLE = :FPOSTABLE
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 = :LEVEL3) AND
(LEVEL4 = :LEVEL4);
END^
SET TERM ; ^
/* Roles ------------------------------------------------------------------- */
/* Grants ------------------------------------------------------------------ */
/* User Grants ------------------------------------------------------------- */
/* Default data ------------------------------------------------------------ */
[Non-text portions of this message have been removed]
I've encountered this when I upgraded to FireBird 1.5 RC 2
and one of my old application refused to function properly.
To illustrate, I've attached a sample script (SAMPLE.SQL).
I've tested the following under Firebird 1.02 and 1.5RC2, using
IBConsole and ISQL,
1. Created the database
2. Executed the attached script
- FB102, ISQL:
unknown command "mep"
3. Executed the following commands:
a. INSERT INTO ACCOUNT (CODE, LEVEL1, LEVEL2, LEVEL3, LEVEL4, TITLE)
VALUES (1, 1, 0, 0, 0, 'ASSETS');
- FB102, ISQL or IBConsole:
exception 2 - This account does not have valid parent.
Note: This doesn't happen if I first close the database before
inserting new records.
b. INSERT INTO ACCOUNT (CODE, LEVEL1, LEVEL2, LEVEL3, LEVEL4, TITLE)
VALUES (2, 1, 5, 0, 0, 'PETTY CASH');
c. DELETE FROM ACCOUNT
WHERE CODE = 1;
- FB15RC2, IBConsole:
deadlock
update conflicts with concurrent update on line 1: DELETE FROM
ACCOUNT WHERE CODE = 1
Notes: 1. No problems with FB15RC2 / ISQL.
2. With FB102, no problems too if I restarted the database right
after creation.
Is anything wrong with my script?
Thanks :)
----------
SET SQL DIALECT 3;
/* CREATE DATABASE 'C:\Sample.gdb' PAGE_SIZE 4096
DEFAULT CHARACTER SET ISO8859_1 */
/* Domains ----------------------------------------------------------------- */
CREATE DOMAIN BOOLEAN AS SMALLINT DEFAULT 0;
CREATE DOMAIN ACCOUNTTITLETYPE AS VARCHAR(50);
/* Generators -------------------------------------------------------------- */
CREATE GENERATOR GEN_ACCOUNTCODE;
/* Exceptions -------------------------------------------------------------- */
CREATE EXCEPTION ACCOUNT_INVALIDACCOUNTNO 'Invalid account #.';
CREATE EXCEPTION ACCOUNT_NOPARENT 'This account does not have valid parent.';
CREATE EXCEPTION ACCOUNT_HASCHILDREN 'This account already has children accounts. Cannot change account #.';
/* Stored Procedures - Forward declarations -------------------------------- */
SET TERM ^ ;
CREATE PROCEDURE PROC_GETPARENTACCOUNTCODE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
PARENTACCOUNTCODE INTEGER
)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE PROC_COMPUTEPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
POSTABLE SMALLINT
)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE PROC_UPDATEPARENTPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
AS
BEGIN
EXIT;
END^
SET TERM ; ^
/* Tables ------------------------------------------------------------------ */
/* Table: ACCOUNT, Accounts */
CREATE TABLE ACCOUNT
(
CODE INTEGER NOT NULL,
LEVEL1 INTEGER DEFAULT 1 NOT NULL,
LEVEL2 INTEGER DEFAULT 0 NOT NULL,
LEVEL3 INTEGER DEFAULT 0 NOT NULL,
LEVEL4 INTEGER DEFAULT 0 NOT NULL,
TITLE ACCOUNTTITLETYPE NOT NULL,
POSTABLE BOOLEAN DEFAULT 1 NOT NULL,
ACCOUNTNO COMPUTED BY (LEVEL1 || '.' || LEVEL2 || '.' || LEVEL3 || '.' || LEVEL4),
CONSTRAINT ACCOUNT_PRIMARYKEY PRIMARY KEY (CODE),
CONSTRAINT ACCOUNT_LEVEL UNIQUE (LEVEL1, LEVEL2, LEVEL3, LEVEL4),
CONSTRAINT ACCOUNT_TITLE UNIQUE (TITLE)
);
/* Constraints ------------------------------------------------------------- */
/* Indexes ----------------------------------------------------------------- */
/* Triggers ---------------------------------------------------------------- */
SET TERM ^ ;
/* Triggers: ACCOUNT */
CREATE TRIGGER ACCOUNT_BEFOREINSERT FOR ACCOUNT
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE FCODE INTEGER;
BEGIN
/* Check if account # is valid */
IF ((NEW.LEVEL1 < 1) OR (NEW.LEVEL2 < 0) OR
(NEW.LEVEL3 < 0) OR (NEW.LEVEL4 < 0) OR
((NEW.LEVEL4 > 0) AND ((NEW.LEVEL3 = 0) OR (NEW.LEVEL2 = 0))) OR
((NEW.LEVEL3 > 0) AND (NEW.LEVEL2 = 0))) THEN
EXCEPTION ACCOUNT_INVALIDACCOUNTNO;
/* Check for a valid parent */
EXECUTE PROCEDURE PROC_GETPARENTACCOUNTCODE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
:FCODE;
IF (FCODE IS NULL) THEN
EXCEPTION ACCOUNT_NOPARENT;
/* Update POSTABLE field */
EXECUTE PROCEDURE PROC_COMPUTEPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
NEW.POSTABLE;
END^
CREATE TRIGGER ACCOUNT_AFTERINSERT FOR ACCOUNT
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
/* Update parent account's POSTABLE field */
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4;
POST_EVENT 'ACCOUNT';
END^
CREATE TRIGGER ACCOUNT_BEFOREUPDATE FOR ACCOUNT
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE FCODE INTEGER;
BEGIN
/* Check if account # is valid */
IF ((NEW.LEVEL1 < 1) OR (NEW.LEVEL2 < 0) OR
(NEW.LEVEL3 < 0) OR (NEW.LEVEL4 < 0) OR
((NEW.LEVEL4 > 0) AND ((NEW.LEVEL3 = 0) OR (NEW.LEVEL2 = 0))) OR
((NEW.LEVEL3 > 0) AND (NEW.LEVEL2 = 0))) THEN
EXCEPTION ACCOUNT_INVALIDACCOUNTNO;
/* Check for a valid parent */
EXECUTE PROCEDURE PROC_GETPARENTACCOUNTCODE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
:FCODE;
IF ((FCODE IS NULL) OR (FCODE = OLD.CODE)) THEN
EXCEPTION ACCOUNT_NOPARENT;
/* Update POSTABLE field */
EXECUTE PROCEDURE PROC_COMPUTEPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4
RETURNING_VALUES
NEW.POSTABLE;
/* Don't allow update if account # was changed & has children */
IF ((OLD.ACCOUNTNO <> NEW.ACCOUNTNO) AND (OLD.POSTABLE = 0)) THEN
EXCEPTION ACCOUNT_HASCHILDREN;
END^
CREATE TRIGGER ACCOUNT_AFTERUPDATE FOR ACCOUNT
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
/* Update parent account's POSTABLE field */
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
OLD.LEVEL1, OLD.LEVEL2, OLD.LEVEL3, OLD.LEVEL4;
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
NEW.LEVEL1, NEW.LEVEL2, NEW.LEVEL3, NEW.LEVEL4;
POST_EVENT 'ACCOUNT';
END^
CREATE TRIGGER ACCOUNT_BEFOREDELETE FOR ACCOUNT
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
/* Delete children accounts */
IF (OLD.LEVEL2 = 0) THEN
DELETE FROM ACCOUNT
WHERE (LEVEL1 = OLD.LEVEL1) AND
(CODE <> OLD.CODE);
ELSE
IF (OLD.LEVEL3 = 0) THEN
DELETE FROM ACCOUNT
WHERE (LEVEL1 = OLD.LEVEL1) AND
(LEVEL2 = OLD.LEVEL2) AND
(CODE <> OLD.CODE);
ELSE
IF (OLD.LEVEL4 = 0) THEN
DELETE FROM ACCOUNT
WHERE (LEVEL1 = OLD.LEVEL1) AND
(LEVEL2 = OLD.LEVEL2) AND
(LEVEL3 = OLD.LEVEL3) AND
(CODE <> OLD.CODE);
END^
CREATE TRIGGER ACCOUNT_AFTERDELETE FOR ACCOUNT
ACTIVE AFTER DELETE POSITION 0
AS
BEGIN
/* Update parent account's POSTABLE field */
EXECUTE PROCEDURE PROC_UPDATEPARENTPOSTABLE
OLD.LEVEL1, OLD.LEVEL2, OLD.LEVEL3, OLD.LEVEL4;
POST_EVENT 'ACCOUNT';
END^
SET TERM ; ^
/* Stored Procedures ------------------------------------------------------- */
SET TERM ^ ;
/* Procedure: Returns the parent account code given an account.
Returns: NULL - if no parent
0 - if is main account
else - parent account code */
ALTER PROCEDURE PROC_GETPARENTACCOUNTCODE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
PARENTACCOUNTCODE INTEGER
)
AS
BEGIN
IF (LEVEL2 + LEVEL3 + LEVEL4 = 0) THEN
PARENTACCOUNTCODE = 0;
ELSE
BEGIN
IF (LEVEL4 > 0) THEN
LEVEL4 = 0;
ELSE
IF (LEVEL3 > 0) THEN
LEVEL3 = 0;
ELSE
IF (LEVEL2 > 0) THEN
LEVEL2 = 0;
SELECT CODE
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 = :LEVEL3) AND
(LEVEL4 = :LEVEL4)
INTO :PARENTACCOUNTCODE;
END
END^
/* Procedure: Determines whether an account is postable or not */
ALTER PROCEDURE PROC_COMPUTEPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
RETURNS
(
POSTABLE SMALLINT
)
AS
BEGIN
IF (LEVEL4 > 0) THEN
POSTABLE = 0;
ELSE
IF (LEVEL3 > 0) THEN
SELECT COUNT(*)
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 = :LEVEL3) AND
(LEVEL4 > 0)
INTO :POSTABLE;
ELSE
IF (LEVEL2 > 0) THEN
SELECT COUNT(*)
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 > 0)
INTO :POSTABLE;
ELSE
SELECT COUNT(*)
FROM ACCOUNT
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 > 0)
INTO :POSTABLE;
IF (POSTABLE = 0) THEN
POSTABLE = 1;
ELSE
POSTABLE = 0;
END^
/* Procedure: Updates postable field of parent accounts */
ALTER PROCEDURE PROC_UPDATEPARENTPOSTABLE
(
LEVEL1 INTEGER,
LEVEL2 INTEGER,
LEVEL3 INTEGER,
LEVEL4 INTEGER
)
AS
DECLARE VARIABLE FPOSTABLE SMALLINT;
BEGIN
IF (LEVEL2 + LEVEL3 + LEVEL4 = 0) THEN
EXIT;
IF (LEVEL4 > 0) THEN
LEVEL4 = 0;
ELSE
IF (LEVEL3 > 0) THEN
LEVEL3 = 0;
ELSE
IF (LEVEL2 > 0) THEN
LEVEL2 = 0;
EXECUTE PROCEDURE PROC_COMPUTEPOSTABLE
:LEVEL1, :LEVEL2, :LEVEL3, :LEVEL4
RETURNING_VALUES
:FPOSTABLE;
UPDATE ACCOUNT
SET POSTABLE = :FPOSTABLE
WHERE (LEVEL1 = :LEVEL1) AND
(LEVEL2 = :LEVEL2) AND
(LEVEL3 = :LEVEL3) AND
(LEVEL4 = :LEVEL4);
END^
SET TERM ; ^
/* Roles ------------------------------------------------------------------- */
/* Grants ------------------------------------------------------------------ */
/* User Grants ------------------------------------------------------------- */
/* Default data ------------------------------------------------------------ */
[Non-text portions of this message have been removed]