#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2004
    Posts
    1
    Rep Power
    0

    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
    Juan Pla

    /*-------------CODE--------------------------*/
    SET TERM ^ ;
    CREATE PROCEDURE PK (FIELD VARCHAR(25), TABLE VARCHAR(25))
    RETURNS (INC INTEGER)
    AS
    DECLARE VARIABLE VALUE INTEGER;
    begin
    SELECT MAX(:FIELD) FROM :TABLE
    INTO :VALUE;
    if (:VALUE IS NOT NULL) then :INC = :VALUE + 1
    ELSE :inc = 1;
    suspend;
    end
    ^
    SET TERM ; ^
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    249
    Rep Power
    14
    Hi,

    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?

    --
    Martijn Tonies
    Database Workbench: works with Firebird, MySQL, InterBase and MSSQL Server
    Upscene Productions
    http://www.upscene.com
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com

IMN logo majestic logo threadwatch logo seochat tools logo