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)

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)

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)

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)