Subject Default column values not used by stored procedure
Author Rick Debay
Inserting into this table

CREATE TABLE THE_TABLE
(
THE_KEY Numeric(11,0) NOT NULL,
THE_DATA Char(14) DEFAULT 'UNKNOWN' NOT NULL,
MORE_DATA CHAR( 1) DEFAULT '?' NOT NULL COLLATE
ISO8859_1,
CONSTRAINT PK_THETABLE PRIMARY KEY (THE_KEY)
);

with this procedure fails with the error:
validation error for column MORE_DATA, value "*** null ***"

CREATE PROCEDURE P_THETABLE_IU (
THEKEY Numeric(11,0),
THEDATA Char(14))
AS
BEGIN
INSERT INTO THE_TABLE (
THE_KEY,
THE_DATA)
VALUES (
:THE_KEY,
:THE_DATA);
WHEN SQLCODE -803 DO
UPDATE THE_TABLE SET
THE_DATA = :THE_DATA
WHERE
THE_KEY = :THE_KEY;
END

But this statement by itself works fine, with MORE_DATA being set to '?'

INSERT INTO THE_TABLE (
THE_KEY,
THE_DATA)
VALUES (
123,
'123');