1. Forum
  2. Usenet
  3. COMP.DATABASES.IBM-DB2

  • UTF-8 or CODEUNITS32 issue?

    From Troels Arvin@21:1/5 to All on Fri Apr 28 11:57:20 2017
    Hello,

    I have the below quoted procedure which has worked well with non-UTF-8
    pre-11.1 DB2s for a long time. But when I try to define the procedure in
    an UTF-8 v. 11.1.1.1 database, I get:

    DB21034E The command was processed as an SQL statement because it was
    not a valid Command Line Processor command. During SQL processing it
    returned: SQL0781N Condition "ERR_STATE" is not defined or the
    definition is not in scope. LINE NUMBER=21. SQLSTATE=42737

    I suspect this has something to do with character set stuff. The new
    database has the following DB configuration parameter:
    STRING_UNITS = CODEUNITS32

    Does someone have an idea on how to proceed?

    =====================================================
    CREATE OR REPLACE PROCEDURE chkstr_date_(IN strval VARCHAR(254))
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    READS SQL DATA
    BEGIN
    DECLARE dateval DATE;
    DECLARE retval INT DEFAULT 1;
    DECLARE err_state CHAR(5) DEFAULT '?????';
    DECLARE err_code INTEGER DEFAULT 0;
    DECLARE err_msg VARCHAR(70) DEFAULT '?????';
    DECLARE SQLSTATE CHAR(5) DEFAULT '?????';
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
    VALUES (SQLSTATE,SQLCODE) INTO err_state,err_code;
    SET err_msg = 'While attempting to cast to DATE, got error
    code: '||CHAR(err_code);

    IF err_state='22007' THEN SET retval = 0;
    ELSEIF err_state='22008' THEN SET retval = 0;
    ELSE RESIGNAL SQLSTATE err_state SET MESSAGE_TEXT =
    err_msg;
    END IF;
    END;

    IF strval IS NULL THEN SET retval=NULL;
    ELSE
    SET dateval=DATE(strval);
    RETURN retval;
    END IF;
    END
    =====================================================

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Troels Arvin@21:1/5 to All on Wed May 3 11:39:25 2017
    Hello,

    Previously, I wrote:
    [...]
    DECLARE err_state CHAR(5) DEFAULT '?????';
    [...]

    Changing it to CHAR(5 OCTETS) helped.

    --
    Troels

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to Troels Arvin on Tue May 9 06:31:04 2017
    On Wednesday, May 3, 2017 at 7:39:17 AM UTC-4, Troels Arvin wrote:
    Hello,

    Previously, I wrote:
    [...]
    DECLARE err_state CHAR(5) DEFAULT '?????';
    [...]

    Changing it to CHAR(5 OCTETS) helped.

    --
    Troels

    OK But Troels why did this help?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Troels Arvin@21:1/5 to All on Tue May 9 14:46:15 2017
    Hello,

    bwmiller16 wrote:
    OK But Troels why did this help?

    I think it's because when STRING_UNITS is set to CODEUNITS32, then the following

    DECLARE err_state CHAR(5) DEFAULT '?????';

    really is a "short hand" for

    DECLARE err_state CHAR(5 CODEUNITS32) DEFAULT '?????';

    And CHAR(5 CODEUNITS32) appears to be in conflict with the "RESIGNAL
    SQLSTATE" operation which seems to only accept CHAR(5 OCTETS) values.

    --
    Troels

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)