Hi Adam,

I created the UDF by the scripts with no error:
DECLARE EXTERNAL FUNCTION ADDYEAR
DATE,
INTEGER
RETURNS DATE BY VALUE
ENTRY_POINT 'IB_UDF_AddYear' MODULE_NAME 'ib_udf'

So I assume Firebird knows where 'IB_UDF_AddYear' and 'ib_udf' are.

But it only happens when I create the stored procedure, it seems not
related to the date format.

As the error message pointed to the entry_point and module_name.

Besides using UDF, any way inside the stored procedure can add 100
years to the date ?

Alan

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "alanpltse" <alanpltse@>
> wrote:
> >
> > Hi Adam,
> >
> > This is my detailed error message:
> > "Invalid token. Invalid request BLR at offset 69. function ADDYEAR
> is
> > not defined.
> > module name or entrypoint could not be found"
>
> Did you get an error when you declared the UDF??
>
> DECLARE EXTERNAL FUNCTION ADDYEAR
> TIMESTAMP, INT
> RETURNS TIMESTAMP
> ENTRY_POINT 'ADDYEAR' MODULE_NAME 'FBUDF';
>
> Expects to be playing with timestamp parameters, and I do not think
> dialect 1 understands this. Try changing it to (and have not tested).
>
> DECLARE EXTERNAL FUNCTION ADDYEAR
> DATE, INT
> RETURNS DATE
> ENTRY_POINT 'ADDYEAR' MODULE_NAME 'FBUDF';
>
> Also, as Helen points out 1910 is an integer, not a timestamp. You
> will need to fix your where condition to one of the standard formats.
> It only worked for me because my table had no data in it.
>
> Adam
>
>
>
>
> >
> > --- In firebird-support@yahoogroups.com, "Adam" <s3057043@> wrote:
> > >
> > > Alan,
> > >
> > > There must be something you are not telling us. The following
> script
> > > works fine. You should not have a suspend in this stored
> procedure
> > > because you don't return anything, but it runs anyway.
> > >
> > > ----
> > >
> > > DECLARE EXTERNAL FUNCTION ADDYEAR
> > > TIMESTAMP, INT
> > > RETURNS TIMESTAMP
> > > ENTRY_POINT 'ADDYEAR' MODULE_NAME 'FBUDF';
> > >
> > > CREATE TABLE OLDDATETABLE
> > > (
> > > TRANSACTION_DATE TIMESTAMP
> > > );
> > >
> > > COMMIT;
> > >
> > > SET TERM ^ ;
> > >
> > > CREATE OR ALTER PROCEDURE ADDYEAR_PROCEDURE
> > > AS
> > > BEGIN
> > > UPDATE OLDDATETABLE
> > > SET TRANSACTION_DATE = ADDYEAR(TRANSACTION_DATE, 100)
> > > WHERE TRANSACTION_DATE < 1910;
> > > SUSPEND;
> > > END
> > > ^
> > >
> > > SET TERM ; ^
> > >
> > > COMMIT;
> > >
> > > EXECUTE PROCEDURE ADDYEAR_PROCEDURE;
> > >
> > > COMMIT;
> > >
> > > ----
> > >
> > > Adam
> > >
> >
>