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

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0

    Firebird Stored Procedure


    Good morning all! I am new to Firebird and I am having some problems... I need to rewrite my stored procedures from SQL 2008 into firebird using Database Workbench Pro 3. Below is the stored procedure I am having problems with any help would really be appreciated...
    I am using the latest version of Firebird and Database Workbench.

    STORED PROCEDURE
    CREATE PROCEDURE spAddUpdateUser
    (
    InIsInsert integer = 1, --Default to Insert
    InIntrecNo bigint = NULL, --Must be set for updating user
    InLogonName varchar(50),
    InUserName varchar(50),
    InUserSurname varchar(50),
    InPassword varchar(50),
    InDepartmentCode bigint,
    InStatus varchar(8),
    InEmail varchar(200),
    InCreatedBy bigint,
    InIntrecnoRole bigint = 3 --Default to Limited User (Update this if the Intrecno changes)
    )
    RETURNS
    (
    Success bigint
    )

    AS

    DECLARE VARIABLE CreatedDate TimeStamp;
    DECLARE VARIABLE UserExists integer;
    DECLARE VARIABLE RoleExists integer;
    DECLARE VARIABLE CurrentRole bigint;

    BEGIN

    CreatedDate = CURRENT_TIMESTAMP;
    Success = 0; --Defaults to unsuccessful

    SELECT COUNT(*)
    FROM "USER"
    WHERE (:InIsInsert = 1 AND Logon_Name = :InLogonName)
    OR
    (:InIsInsert = 0 AND Intrecno = :InIntrecNo
    AND NOT :InLogonName IN
    (SELECT Logon_Name
    FROM "USER"
    WHERE Logon_Name = :InLogonName
    AND Intrecno <> :InIntrecNo))
    INTO :UserExists;

    SELECT COUNT(*)
    FROM Role
    WHERE Intrecno = :InIntrecnoRole
    INTO :RoleExists;

    IF (:InIsInsert = 1) THEN
    BEGIN
    IF (:UserExists = 0 AND :RoleExists = 1) THEN
    BEGIN
    INSERT INTO "User"
    (Logon_Name, User_Name, User_Surname,
    "Password", Department_Code, Status, Email,
    Created_Date, Created_By, Intrecno_Role)
    VALUES
    (:InLogonName, :InUserName, :InUserSurname, :InPassword,
    :InDepartmentCode, :InStatus, :InEmail, :InCreatedDate, :InCreatedBy,
    :InIntrecnoRole);

    SELECT gen_id(GENERATOR_NAME, 0)
    FROM rdb$database
    INTO :InIntrecno;

    INSERT INTO User_Right
    (Intrecno_User, Intrecno_User_Function, DefaultRight)
    SELECT :InIntrecNo, Intrecno_User_Function, 1
    FROM Role_Right
    WHERE Intrecno_Role = :InIntrecnoRole;

    Success = :InIntrecNo;
    END
    END
    ELSE
    BEGIN
    IF (UserExists = 1 AND RoleExists = 1) THEN
    BEGIN

    SELECT CurrentRole --//= :InIntrecno_Role
    FROM "User"
    WHERE Intrecno = :InIntrecNo
    INTO :InIntrecno_Role;

    UPDATE "User"
    SET
    Logon_Name = :InLogonName, User_Name = :InUserName,
    User_Surname = :InUserSurname, "Password" = :InPassword,
    Department_Code = :InDepartmentCode, Status = :InStatus,
    Email = :InEmail, Created_By = :InCreatedBy,
    Intrecno_Role = :InIntrecnoRole
    WHERE Intrecno = :InIntrecNo;

    IF (NOT CurrentRole = :InIntrecnoRole) THEN
    BEGIN
    DELETE FROM User_Right
    WHERE Intrecno_User = :InIntrecNo AND DefaultRight = 1;

    INSERT INTO User_Right
    (Intrecno_User, Intrecno_User_Function, DefaultRight)
    SELECT :InIntrecNo, Intrecno_User_Function, 1
    FROM Role_Right
    WHERE Intrecno_Role = :InIntrecnoRole;
    END
    Success = 1;
    END
    END
    RETURN Success;
    END

    ERROR MESSAGE
    < ERROR:
    ISC ERROR CODE:335544569

    ISC ERROR MESSAGE:
    Dynamic SQL Error
    SQL error code = -104
    Token unknown - line 113, column 12
    Success

    STATEMENT:
    TIB_DSQL: "<TIB_Script>.<TIB_DSQL>."

    As you can see it bombs out when it gets to my declared variable at the end and I have no idea as to why this happens...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    First remove the parameters with default values at the end of the end.
    If you call the procedure with SELECT statement you have to use SUSPEND to return the result. If you call the procedure with EXECUTE PROCEDURE you don't need enything more. Remove RETURN Success from the code at the end.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by mIRCata
    First remove the parameters with default values at the end of the end.
    If you call the procedure with SELECT statement you have to use SUSPEND to return the result. If you call the procedure with EXECUTE PROCEDURE you don't need enything more. Remove RETURN Success from the code at the end.
    Thanks this did help! But now I'm getting this error...
    ISC ERROR CODE:335544343

    ISC ERROR MESSAGE:
    invalid request BLR at offset 692
    generator GENERATOR_NAME is not defined

    What does this mean?
    The stored procedure compiles but when I go to commit the active transaction I get this error.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    224
    Rep Power
    12
    The first parameter that GEN_ID takes is a name of a generator/sequence that you have in your database. Perhaps you don't have a generator named GENERATOR_NAME.
    Because GEN ID takes the name of the generator and increases his value with the value given as second parameter and return the new value as result. In your case you are trying to get the current value of a generator named GENERATOR_NAME. Do you have any generators/sequences in your database? And why are taking old value from the generator? try to take the next with gen_id(xxxxx,1)

    Comments on this post

    • Wium agrees
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by mIRCata
    The first parameter that GEN_ID takes is a name of a generator/sequence that you have in your database. Perhaps you don't have a generator named GENERATOR_NAME.
    Because GEN ID takes the name of the generator and increases his value with the value given as second parameter and return the new value as result. In your case you are trying to get the current value of a generator named GENERATOR_NAME. Do you have any generators/sequences in your database? And why are taking old value from the generator? try to take the next with gen_id(xxxxx,1)
    Thanks for the input!
    I managed to fix this by adding RETURNING <column name> INTO <variable> after the insert so now it works 100%

IMN logo majestic logo threadwatch logo seochat tools logo