Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183

    Unanswered: DB91. User Defined Function problem

    We are facing an interesting problem. I want to write a casting function from char to int, that return the integer value. If the cast fails then the value 0 must be returned.

    I did the following:
    CREATE FUNCTION char2int(p_value varchar(100)) RETURNS INT
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE v_result INT;
    SET RESULT = CAST(p_value AS INTEGER);
    END

    This function works, until a non number value is specified as input. We tried to catch this with declaring a condition fro sqlstate '22018' and define and UNDO handler.

    This fails, when we create a procedure it (the error catching) works, but then I'm unable to return a result.

    Is it impossible to catch errors in functions, or am I overlooking something.

    Thanks,

    RobP

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You could use this UDF to verify if a char string is a valid representation of an integer.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by RobP
    We are facing an interesting problem. I want to write a casting function from char to int, that return the integer value. If the cast fails then the value 0 must be returned.

    I did the following:
    CREATE FUNCTION char2int(p_value varchar(100)) RETURNS INT
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE v_result INT;
    SET RESULT = CAST(p_value AS INTEGER);
    END

    This function works, until a non number value is specified as input. We tried to catch this with declaring a condition fro sqlstate '22018' and define and UNDO handler.

    This fails, when we create a procedure it (the error catching) works, but then I'm unable to return a result.

    Is it impossible to catch errors in functions, or am I overlooking something.

    Thanks,

    RobP

    I think you are going to have to write the UDf to check the parameter manually and then return accordingly. Something like this:

    CREATE FUNCTION char2int(p_value varchar(100)) RETURNS INT
    LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE v_result INT;
    DECLARE len int;
    DECLARE cur_pos int;
    DECLARE is_valid int;
    DECLARE cur_char char(1);
    DECLARE work_string varchar(100);

    SET work_string = rtrim(ltrim(p_value));
    set len = length(work_string);
    set cur_pos = 1;
    set is_valid = 1; -- assume it is valid
    while (cur_pos <= len) do
    set cur_char = substr(work_string,cur_pos,1);
    IF (cur_char < '0' or cur_char > '9') then set is_valid = 0; end if;
    set cur_pos = cur_pos + 1;
    end while;

    if (is_valid = 1)
    THEN SET v_Result = integer(work_string);
    else set v_Result = 0;
    END IF;
    return(v_Result);
    END

    Andy

  4. #4
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183

    Additional question

    Hi All,

    Thanks for the reply's. This will solve the proble to convert to int. But I have to do more checkings. If possible I want to use DB2 logic as far as possible. Trapping error codes in procedures seems to do that job, but it is impossible to return a resul (I think). Is it really true that we cannot do error trapping in UDF's like in procedures. If this is so I think that IBM has missed this in their design. Why wouldn't you allow that???

    Greetz,

    RobP

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No, you cannot do error handling in a UDF. I think the reason is because UDF are "in line" code (can be imbedded in other SQL), where Stored Procedures (SP) are not. You should be able to write an SP that has OUT parameters, then wrap it in a UDF to get what you want.

    Andy

  6. #6
    Join Date
    May 2006
    Posts
    16
    To validate the input string (p_value) you can do this:
    1. Convert all invalid characters, including special characters (ABCD..Z@#$...) to null, using TRANSLATE and REPLACE
    2. If previuos result is = to p_value, the string is valid. Otherwise, CASE result will be 0.
    3. CAST will be applied to CASE result.

    SELECT CAST ( CASE WHEN p_value =
    REPLACE ( TRANSLATE ( UCASE ( p_value ),'','ABCDEF...' ),' ','' )
    THEN p_value
    ELSE '0' END AS INTEGER )
    FROM SYSIBM.SYSDUMMY1

    Note: 0 could be a valid result.
    Saludos, ARLF.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Andy is right: put the conversion into a stored procedure and trap the error there. Then write a UDF that simply calls the procedure (copied Serge's version):
    Code:
    --#SET TERMINATOR @
    CREATE PROCEDURE softintp(IN arg VARCHAR(255), OUT res INTEGER)
    CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
    BEGIN
       DECLARE CONTINUE HANDLER FOR SQLSTATE '22018'
        BEGIN SET res = NULL; END;
       SET res = INTEGER(arg);
    END
    @
    
    CREATE FUNCTION softint(arg VARCHAR(255))
    RETURNS INTEGER CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
    BEGIN ATOMIC
       DECLARE res INTEGER;
       CALL softintp(arg, res);
       RETURN res;
    END
    @
    --#SET TERMINATOR ;
    
    db2 => VALUES softint('17');
    
    1
    -----------
              17
    
       1 record(s) selected.
    
    db2 => VALUES softint('hello');
    
    1
    -----------
               -
    
       1 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •