At 02:16 PM 6/04/2004 -0700, you wrote:

>Of course no solution is perfect. With this one I have run into issues
>regarding
>the 80 characters limit on the substr and substrlen functions. I tried
>increasing this in the function declaration, but no matter what I increase
>it to
>I always get an overflow error when I pass more then 80 char to it. Has
>anybody
>experienced the same issue with substr and substrlen?

According to Claudio, who has done a lot of stuff with the UDF libraries,
the "token length" rule for UDF declarations has been gone since IB
5.0. So it seems the "token" string argument size is a long-standing
documentation bug.

There are two possible issues here. One is that you have to declare string
UDFs with an input string long enough to take the data they will
receive. The other is that, for Firebird, the substr UDF is deprecated in
favour of the internal SUBSTRING function. SUBSTRING is not particular
about the length of a string, as long as it is within the 32,765-byte limit
for varchars.

"Changing the declaration" won't change the size of input that your SP or
trigger expects to get. You need to
1) drop the procedure or trigger
2) drop the function (DROP EXTERNAL FUNCTION) (if you have no other objects
dependent on it) and declare it anew
** or **
add another declaration for that function, with a different <function-name>
and an input that is large enough
3) recreate the trigger or procedure using the name of the newly-declared
function
4) and, of course, if you are on Windows, reboot the machine to unload the
old dll.

I don't use the substr() function myself at all for Firebird; but we'll
use it as an example, because the same applies to all string functions.

Here's the sample declaration from ib_udf.sql:

DECLARE EXTERNAL FUNCTION substr
CSTRING(80), SMALLINT, SMALLINT
RETURNS CSTRING(80) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

You can keep this if you have other objects using it that can tolerate the
80-byte maximum. For this SP and others that need e.g. a 1 Kb max. byte
size, you add:

DECLARE EXTERNAL FUNCTION substr1K
CSTRING(1024), SMALLINT, SMALLINT
RETURNS CSTRING(1024) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

Then, you might need a function that takes a longer string. You need
another declaration:
DECLARE EXTERNAL FUNCTION substr2K
CSTRING(2048), SMALLINT, SMALLINT
RETURNS CSTRING(2048) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

Now, you could argue that it would be better to declare substr once with an
argument of 32765 and be done with it. However, it's a rare requirement to
be trying to do a string function on a string so large. Yet this would
force the system to allocate memory this large each time it calls the
function, even if it's only analysing a string of 10 bytes. If you are
using it for grouping or ordering, the function is going to be called twice
per row (ordering) or once per row and once per group (grouping). IOW,
each time it gets named explicitly, or implicitly by its degree number, it
gets called and has to allocate 32 Kb.

With respect to substr, you'd be better to replace it with SUBSTRING(). In
SPs, to massage strings, there are more efficient things you can do using
SUBSTRING(), in preference to calling a UDF. However, ordering and
grouping by UDF expressions isn't one of those things.

Have you considered this solution, for example:

CREATE PROCEDURE PROC_TEST (
M_LIST VARCHAR (100)) /* replace with the max.bytelength you need */
RETURNS (
R_ID INTEGER)
AS
declare variable r_id_string varchar(10);
declare variable cur_char char;
declare variable isdone char = '0';
BEGIN
r_id = -1;
r_id_string = '';
while (isdone = '0') do
begin
cur_char = substring(m_list from 1 for 1);
if (cur_char <> ',') then
r_id_string = r_id_string || cur_char;

if (cur_char = '') then
isdone = '1';
if (cur_char = ',' or isdone = '1') then
begin
if (r_id_string <> '') then
r_id = cast(r_id_string as integer);
SUSPEND;
r_id = -1;
r_id_string = '';
end
m_list = substring(m_list from 2);
end
end

/heLen