UDF SUBSTR dosen't work properly :(

Dev Shed Forums Sponsor:

April 22nd, 2004, 04:59 AM

roPopa

Registered User

Join Date: Apr 2004

Location: Bucharest

Posts: 2

Time spent in forums: 29 m 11 sec
Reputation Power: 0

UDF SUBSTR dosen't work properly :(

so
I have Firebird 1.5
I want to create a SP to parse a string in order to result some numbers as records

CREATE PROCEDURE PARSESTRING(
INPUTSTRING VARCHAR(10000))
RETURNS (
ID INTEGER,
STR VARCHAR(10),
ALLSTRING VARCHAR(10000))
AS
DECLARE VARIABLE varStr VARCHAR(10000);
DECLARE VARIABLE varIndex integer;
declare VARIABLE varTmp varchar(10);
BEGIN
/* Procedure Text */
varStr = :INPUTSTRING;
WHILE (:varStr <> '') DO BEGIN
execute procedure StrPos(',', :varStr) RETURNING_VALUES :varIndex;
ALLSTRING = varStr;
vartmp = SUBSTR(:varStr, 0, :varIndex);
vartmp = rtrim(vartmp);
vartmp = ltrim(vartmp);
str = vartmp;
-- ID = CAST(vartmp as INTEGER);
varStr = SUBSTR(:varStr, :varIndex + 1, StrLen(:varStr));
SUSPEND;
if (varIndex = 0) then break;
END
END

but the SUBSTR dosen't work ...
returning only blanks

the UDF is standard received with Firebird 1.5
thanks

April 22nd, 2004, 09:42 AM

mariuz

Bug Hunter

Join Date: May 2003

Location: Transylvania (Romania)

Posts: 309

Time spent in forums: 1 Day 9 h 38 m 4 sec
Reputation Power: 22

related to substr size problem (on imput/output parameters) it can use more than 255 chars

Code:

 *
 *      s u b s t r
 *
 *****************************************
 *
 * Functional description:
 *      substr(s,m,n) returns the substring
 *      of s which starts at position m and
 *      ending at position n.


 *      Note: This function is NOT limited to
 *      receiving and returning only 80 characters,
 *      rather, it can use as long as 32767


 *      characters which is the limit on an
 *      INTERBASE character string.
 *      Change by Claudio Valderrama: when n>length(s),
 *      the result will be the original string instead
 *      of NULL as it was originally designed.
 *
 *****************************************/
DECLARE EXTERNAL FUNCTION substr
        CSTRING(80), SMALLINT, SMALLINT
        RETURNS CSTRING(80) FREE_IT
        ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

__________________
My home page: http://www.firebirdsql.org and work place :http://www.reea.net

Last edited by mariuz : April 23rd, 2004 at 11:04 AM.

April 23rd, 2004, 10:56 AM

mariuz

Bug Hunter

Join Date: May 2003

Location: Transylvania (Romania)

Posts: 309

Time spent in forums: 1 Day 9 h 38 m 4 sec
Reputation Power: 22

tested and doesn't work as expected , but there is a solution

Here is a list thread on the issue

Quote:

Paul Vinkenoog wrote:

> Hi Marius,
>
>
>> in the udf sql script it is written
>>
>> " Note: This function is NOT limited to
>> * receiving and returning only 80 characters,
>> * rather, it can use as long as 32767"
>>
>> it gives this error if i try a varchar with more than 255 it gives
>> this error
>>
>> select substr(test255,1,2) from test255;
>>
>> Statement failed, SQLCODE = -802
>>
>> arithmetic exception, numeric overflow, or string truncation
>
>
>
> This was discussed here a couple of months ago. I remember it because
> back then I was surprised too that in practice it doesn't work like it
> says in the note.
>
> Iirc you can solve the problem by changing the declaration to the
> maximum string length you need for your database. But I'm not sure -
> better search the archives before I put you on the wrong track!

Ok thanks . it works
i defined the function like this

DECLARE EXTERNAL FUNCTION substrlen_more
CSTRING(32767), SMALLINT, SMALLINT
RETURNS CSTRING(32767) FREE_IT
ENTRY_POINT 'IB_UDF_substrlen' MODULE_NAME 'ib_udf';

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

and then made a query "select substr_more(test255,1,2) from test255;"
it returned the right substrings.

UDF SUBSTR dosen't work properly :(

Developer Shed Advertisers and Affiliates