Function unknown.SQL error code = -804.

Hi,

i've created a procedure that should give me the position of a substring in a string:

--------------------------------------------------------
CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
RETURNS (Pos INTEGER) AS
DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
DECLARE VARIABLE Tmp VARCHAR(100);
BEGIN
IF (SubStr IS NULL OR Str IS NULL)
THEN BEGIN Pos = NULL; EXIT; END

SubStr2 = SubStr || '%';
Tmp = '';
Pos = 1;
WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
SubStr2 = '_' || SubStr2;
Tmp = Tmp || '_';
Pos = Pos + 1;
END

IF (Str LIKE Tmp) THEN Pos = 0;
END
-------------------------------------------------------

There are no compilation errors. but when i try to use this procedure in another procedure or in a select statement, it occurs:
Function unknown.SQL error code = -804.

i've given grants and it seems, that the procedure is visible. In IBExpert it auto-identifies the procedure Pos, also shows it in the explorer and when i'm debuging the Pos it works right.

i've tried to declare the function. executing this sql

Code (found in this forum):

DECLARE EXTERNAL FUNCTION Pos
varchar(100),varchar(100)
RETURNS integer FREE_IT
ENTRY_POINT 'IB_UDF_pos' MODULE_NAME 'ib_udf';

after this, i can use the procedure, but the results are totally wrong! values over 39.000 an such things.

does anyone knows a solution?

i'm using firebird 1.5.3 and IBExpert 2006.08.16

thanks a lot!

Ok, i've found that i have to use the procedure like a table with the select statement.

select pos from pos('e','rrerr');

but in the select statement the result must be 3, but it is null!!!!

why?!? when i create a simple procedure with one fix outup parameter it is also null.

Solutions?????

I'd rather declare it as per Ivan Prenosil's site

Sorry,i don't want to say that i,ve created the procedure shown above in the sense of making it new. but its the first procedure that i've put in the firebird database. but the results i get, are all wrong and i don't know why and the documentation is poor.

do know anybody a possible solution?

Well, I have not analyzed your procedure itself; BUT as it is written in Firebird it does not/ should not be declared EXTERNAL.

EXTERNAL is for importing routines created in another language and compiled to a DLL (in Windows).

The Problem is still there!

I've tried an easier procedure:
-----------------------------------------------
CREATE PROCEDURE TEST
returns
(
result integer
)
as
BEGIN
Result = 1;
END;
-----------------------------------------------

When i use a select like this

select * from TEST;

the result is null!!!


What is going wrong?Do i forget something?

Try

select Result from TEST;

ALSO.

I just noticed that the name of your function and the name of your return parameter are the same. A prime candidate for confusion. Change one or the other.

Clive.

Thank you for reply. I've found my mistake. I've forgotten the suspend!

-----------------------------------------------
CREATE PROCEDURE TEST
returns
(
result integer
)
as
BEGIN
Result = 1;
suspend;
END;
-----------------------------------------------

After this the result is given back and it can be selected.

I think i have to learn a lot for firebird

Anyway I'd not mix case.