June 13th, 2004, 07:25 PM
Auto Inc Procedure - Firebird
Hello, my name is Juan and I would like to have a hand to solve this problem:
I have written a Stored Procedure to simulate an autoinc field. It takes two parameters (field and table). Its algorythm is this:
Select the maximum value
if it is null then return 1
else return maximum value+1
When I tried to compile the code below I've received an error message telling me that the character on line 7 (:table) doesn't exist.
Why is this error ocurring?
Thanks for your atention
SET TERM ^ ;
CREATE PROCEDURE PK (FIELD VARCHAR(25), TABLE VARCHAR(25))
RETURNS (INC INTEGER)
DECLARE VARIABLE VALUE INTEGER;
SELECT MAX(:FIELD) FROM :TABLE
if (:VALUE IS NOT NULL) then :INC = :VALUE + 1
ELSE :inc = 1;
SET TERM ; ^
June 14th, 2004, 02:31 AM
A statement (like SELECT ... FROM mytable) needs to be compiled when creating a stored procedure. You cannot replace column names or table names with parameters.
In Firebird 1.5, you can execute dynamic SQL via the EXECUTE STATEMENT statement - check the release notes on how to do this.
Nevertheless, you DO know that you're way of simulating an auto-inc field is very very multi-user unfriendly, right?
Instead, why don't you use Generators?
Database Workbench: works with Firebird, MySQL, InterBase and MSSQL Server
Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle