Dates not in the future

[Bob Stearns]

My client wants to disallow a birth date in the future. First I tried
the obvious:

ALTER TABLE IS3.ANIMALS
ADD CONSTRAINT b_d_f
CHECK (birth_date<=current_date)

It failed because special registers (current_date) are not allowed in
check conditions. Then I tried the trigger below:

create trigger b_d_f
no cascade before
update of birth_date
on is3.animals
referencing
old as o
new as n
for each row
mode db2sql
when (n.birth_date>current_date)
signal sqlstate '99001' set message_text = 'Date must not be
in the future'

It created OK, but does not do what I expected, disallow the update and
return the error. Obviously I don't understand something, but what? Any
help would be greatly appreciated.

[Mark A]

"Bob Stearns" <rs**********@charter.net> wrote in message
news:oc******************@fe05.lga...

My client wants to disallow a birth date in the future. First I tried the
obvious:

ALTER TABLE IS3.ANIMALS
ADD CONSTRAINT b_d_f
CHECK (birth_date<=current_date)

It failed because special registers (current_date) are not allowed in
check conditions. Then I tried the trigger below:

create trigger b_d_f
no cascade before
update of birth_date
on is3.animals
referencing
old as o
new as n
for each row
mode db2sql
when (n.birth_date>current_date)
signal sqlstate '99001' set message_text = 'Date must not be in
the future'

It created OK, but does not do what I expected, disallow the update and
return the error. Obviously I don't understand something, but what? Any
help would be greatly appreciated.

Can you issue a rollback when you get the error back in the program?

[Serge Rielau]

Bob Stearns wrote:

My client wants to disallow a birth date in the future. First I tried
the obvious:

ALTER TABLE IS3.ANIMALS
ADD CONSTRAINT b_d_f
CHECK (birth_date<=current_date)

It failed because special registers (current_date) are not allowed in
check conditions. Then I tried the trigger below:

create trigger b_d_f
no cascade before
update of birth_date
on is3.animals
referencing
old as o
new as n
for each row
mode db2sql
when (n.birth_date>current_date)
signal sqlstate '99001' set message_text = 'Date must not be
in the future'

It created OK, but does not do what I expected, disallow the update and
return the error. Obviously I don't understand something, but what? Any
help would be greatly appreciated.

On first blush teh trigger looks all right.
I presume you have the matching INSERT trigger of course.
Can you post a complete repro?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

My client wants to disallow a birth date in the future. First I tried
the obvious:

ALTER TABLE IS3.ANIMALS
ADD CONSTRAINT b_d_f
CHECK (birth_date<=current_date)

It failed because special registers (current_date) are not allowed in
check conditions. Then I tried the trigger below:

create trigger b_d_f
no cascade before
update of birth_date
on is3.animals
referencing
old as o
new as n
for each row
mode db2sql
when (n.birth_date>current_date)
signal sqlstate '99001' set message_text = 'Date must not
be in the future'

It created OK, but does not do what I expected, disallow the update
and return the error. Obviously I don't understand something, but
what? Any help would be greatly appreciated.

On first blush teh trigger looks all right.
I presume you have the matching INSERT trigger of course.
Can you post a complete repro?

Cheers
Serge

The insert trigger would be the same except changing update to insert,
correct? To bad there is no syntax for 'update or insert'; surely that
is the commonest case of data validation.

NULLs are handled properly, since any comparison with NULL is false,
correct?

That would be hard since we're dealing with a php app. I was expecting
to get an error back like '22007--[IBM][CLI Driver][DB2/LINUX] SQL0180N
The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007' which the program catches properly. Perhaps
there is some 'magic' value of sqlstate to differentiate various error
levels and I've chosen an 'Information' one rather than an 'Error' one?

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Bob Stearns wrote:

My client wants to disallow a birth date in the future. First I tried
the obvious:

ALTER TABLE IS3.ANIMALS
ADD CONSTRAINT b_d_f
CHECK (birth_date<=current_date)

It failed because special registers (current_date) are not allowed in
check conditions. Then I tried the trigger below:

create trigger b_d_f
no cascade before
update of birth_date
on is3.animals
referencing
old as o
new as n
for each row
mode db2sql
when (n.birth_date>current_date)
signal sqlstate '99001' set message_text = 'Date must not
be in the future'

It created OK, but does not do what I expected, disallow the update
and return the error. Obviously I don't understand something, but
what? Any help would be greatly appreciated.

On first blush teh trigger looks all right.
I presume you have the matching INSERT trigger of course.
Can you post a complete repro?

Cheers
Serge

The insert trigger would be the same except changing update to insert,
correct?

Yep.
To bad there is no syntax for 'update or insert'; surely that
is the commonest case of data validation. I've heard of this request before....
NULLs are handled properly, since any comparison with NULL is false,
correct? NULLs are handled properly since very comparison to NULL is UNKNOWN....
That is not the same as FALSE.
The trigger as given would NOT fire with a NULL value since UNKNWON is
not the same as TRUE.
That would be hard since we're dealing with a php app. I was expecting
to get an error back like '22007--[IBM][CLI Driver][DB2/LINUX] SQL0180N
The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007' which the program catches properly. Perhaps
there is some 'magic' value of sqlstate to differentiate various error
levels and I've chosen an 'Information' one rather than an 'Error' one?

Not quite clear what "that" might be, but the error you are getting is a
bind-in error. You never reached the trigger, or the statement execution
for that matter.
SIGNAL always raises an error, never a warning.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Not quite clear what "that" might be, but the error you are getting is a
bind-in error. You never reached the trigger, or the statement execution
for that matter.
SIGNAL always raises an error, never a warning.

Cheers
Serge

'That' was a reference to your request for a complete repro, sorry about
the poor antecedent.

Thanks for the suggestion of a bind-in problem. The whole problem
appears to be one of timing. Apparently I made some type of error
(perhaps reloading the page to cause the error) which caused the trigger
not to be invoked. It is all working as expected.

Once I have this vetted by my client, he'll want a lot of data elements
(in different tables) which are dates, but not all dates, to have such
triggers, all identical except for the trigger name, the table name, and
the variable name. Is there any form of syntactic "sugar" in the db2
product that would allow something like C's #define for such purpose? A
metametadata definition if you would? Or perhaps making a definition of
a PREVIOUS_DATE domain (based on DATE, so all the relevant date
operations would work) which includes the trigger?

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Not quite clear what "that" might be, but the error you are getting is
a bind-in error. You never reached the trigger, or the statement
execution for that matter.
SIGNAL always raises an error, never a warning.

Cheers
Serge

'That' was a reference to your request for a complete repro, sorry about
the poor antecedent.

Thanks for the suggestion of a bind-in problem. The whole problem
appears to be one of timing. Apparently I made some type of error
(perhaps reloading the page to cause the error) which caused the trigger
not to be invoked. It is all working as expected.

Once I have this vetted by my client, he'll want a lot of data elements
(in different tables) which are dates, but not all dates, to have such
triggers, all identical except for the trigger name, the table name, and
the variable name. Is there any form of syntactic "sugar" in the db2
product that would allow something like C's #define for such purpose? A
metametadata definition if you would? Or perhaps making a definition of
a PREVIOUS_DATE domain (based on DATE, so all the relevant date
operations would work) which includes the trigger?

To script the DDL you can pick a language of your choice.
If you want to stay in SQL you could write an SQL Procedure that goes
fishing for all the date column in SYSCAT.TABLEs and glues together and
created the trigger using EXECUTE IMMEDIATE.
You can also use PHP, shell scripting....

You could overload the DATE() function generated by CREATE DISTINCT
TYPE. Something like:
CREATE DISTINCT TYPE UDT_SCHEMA.VALID_DATE FOR DATE WITH COMPARISONS;

CREATE FUNCTION MY_SCHEMA.DATE(arg DATE)
RETURNS UDT_SCEHMA.VALID_DATE
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN CASE WHEN arg <= CURRENT DATE
THEN UDT_SCHEMA.VALID_DATE(arg)
ELSE RAISE_ERROR('78000', 'Date in the future') END

CREATE FUNCTION MY_SCHEMA.DATE(arg UDT_SCHEMA.VALID_DATE)
RETURNS DATE
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN UDT_SCHEMA.DATE(arg);

Make sure UDT_SCHEMA is NOT in the function path while MY_SCHEMA is,
and/or REVOKE EXECUTE privileges from UDT_SCHEMA.DATE() and
UDT_SCHEMA.VALID_DATE()

I have never actually done this, so no guarantee :-)

Note that DB2 does not support user defined CASTs, so you need to use
the functional notation (Many folks use it anyway).
This trick should enable implicit conversion as well (which IMHO is a
good thing).

Have fun.
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

Serge Rielau wrote:

Not quite clear what "that" might be, but the error you are getting
is a bind-in error. You never reached the trigger, or the statement
execution for that matter.
SIGNAL always raises an error, never a warning.

Cheers
Serge

'That' was a reference to your request for a complete repro, sorry
about the poor antecedent.

Thanks for the suggestion of a bind-in problem. The whole problem
appears to be one of timing. Apparently I made some type of error
(perhaps reloading the page to cause the error) which caused the
trigger not to be invoked. It is all working as expected.

Once I have this vetted by my client, he'll want a lot of data
elements (in different tables) which are dates, but not all dates, to
have such triggers, all identical except for the trigger name, the
table name, and the variable name. Is there any form of syntactic
"sugar" in the db2 product that would allow something like C's #define
for such purpose? A metametadata definition if you would? Or perhaps
making a definition of a PREVIOUS_DATE domain (based on DATE, so all
the relevant date operations would work) which includes the trigger?

To script the DDL you can pick a language of your choice.
If you want to stay in SQL you could write an SQL Procedure that goes
fishing for all the date column in SYSCAT.TABLEs and glues together and
created the trigger using EXECUTE IMMEDIATE.
You can also use PHP, shell scripting....

You could overload the DATE() function generated by CREATE DISTINCT
TYPE. Something like:
CREATE DISTINCT TYPE UDT_SCHEMA.VALID_DATE FOR DATE WITH COMPARISONS;

CREATE FUNCTION MY_SCHEMA.DATE(arg DATE)
RETURNS UDT_SCEHMA.VALID_DATE
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN CASE WHEN arg <= CURRENT DATE
THEN UDT_SCHEMA.VALID_DATE(arg)
ELSE RAISE_ERROR('78000', 'Date in the future') END

CREATE FUNCTION MY_SCHEMA.DATE(arg UDT_SCHEMA.VALID_DATE)
RETURNS DATE
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN UDT_SCHEMA.DATE(arg);

Make sure UDT_SCHEMA is NOT in the function path while MY_SCHEMA is,
and/or REVOKE EXECUTE privileges from UDT_SCHEMA.DATE() and
UDT_SCHEMA.VALID_DATE()

I have never actually done this, so no guarantee :-)

Note that DB2 does not support user defined CASTs, so you need to use
the functional notation (Many folks use it anyway).
This trick should enable implicit conversion as well (which IMHO is a
good thing).

Have fun.
Serge

Thank you very much for the ideas.

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

This trick should enable implicit conversion as well (which IMHO is a
good thing).

DiSABLE that is (still a good thing).....

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab