Subject | Default column values not used by stored procedure |
---|---|
Author | Rick Debay |
Post date | 2005-06-27T16:17:28Z |
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');
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');