#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2004
    Location
    Bucharest
    Posts
    2
    Rep Power
    0

    Unhappy 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
  2. #2
  3. Bug Hunter
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Transylvania (Romania)
    Posts
    309
    Rep Power
    24

    Thumbs up


    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';
    Last edited by mariuz; April 23rd, 2004 at 11:04 AM.
    My home page: http://www.firebirdsql.org and work place :http://www.reea.net
  4. #3
  5. Bug Hunter
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Transylvania (Romania)
    Posts
    309
    Rep Power
    24

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


    Here is a list thread on the issue

    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.
    My home page: http://www.firebirdsql.org and work place :http://www.reea.net

IMN logo majestic logo threadwatch logo seochat tools logo