Helen,

thanks for the pointer to the substring function, btw. the issue with the UDF
was resolved by restarting just the FB server.

raf

Helen Borrie wrote:

> 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
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>

--
Rafael Szuminski
Email:raf@...
Phone:(949)939 - 2458
www.BDCSoftware.com