Could not find stored procedure 'fnGetAutoID2' / Database error obtaining sequence number

David Registro shared this problem 2 years ago
Resolved

Error occured getting sequence Nbr: Could not find stored procedure 'fnGetAutoID2'.

Error running task: java.lang.Exception: Database error obtaining sequence number

Comments (1)

photo
1

This error occurs when the Yellowfin DB is missing a required Stored Procedure/Function that is used to create it's auto incrementing ID's for the DB tables.


In most cases this, occurs when a DB has been moved from one server to another, and the backup did not include the stored proc/function, or the DB user permissions have changed.


In order to resolve the issue, there are 2 things you need to do;


  1. Create the stored proc/function if missing
  2. Ensure the Yellowfin DB users (specified in the web.xml) has full access to execute the stored proc/function.

Before continuing with this, please ensure YF has been stopped.

For MySQL , you can run the queries below:


  1. DELIMITER //

    CREATE FUNCTION fnGetAutoID2 (sTableName varchar(40), increment int)

    RETURNS int

    BEGIN

    DECLARE seqNum INT;

    IF sTableName IS NULL THEN RETURN NULL; END IF;

    IF increment IS NULL THEN RETURN NULL; END IF;

    UPDATE KeyGenerator

    SET LastSequenceNumber = LastSequenceNumber + increment

    WHERE TableName = sTableName;

    SELECT LastSequenceNumber INTO seqNum

    FROM KeyGenerator

    WHERE TableName = sTableName LIMIT 1;

    RETURN seqNum;

    END//

    DELIMITER ;//


  1. GRANT SELECT ON MySQL.Proc to <your YF user>


For Microsoft Sql Server you can run this query;


  1. CREATE

    PROCEDURE [dbo].[fnGetAutoID2] ( @sTableName VARCHAR(40), @increment int

    )


  2. AS

    BEGIN TRAN DECLARE @seqNum INT IF @sTableName IS NULL RETURN NULL IF @increment

    IS NULL RETURN NULL UPDATE KeyGenerator


    WHERE

    TableName = UPPER(@sTableName) IF @@error <> 0 RETURN NULL COMMIT TRAN

    RETURN @seqNum

  3. SET

    @seqNum=LastSequenceNumber=LastSequenceNumber+@increment


If you still have issues please send across any errors thrown by running the above queries, and also attach the Yellowfin log files located under \yellowfin\appserver\logs.

Comments have been locked on this page!