Re: SQL*Forms and the 21st Century

Here is a procedure implementing our solution to the 2000 rollover in Forms 3. It is true that you cannot apply the RR mask directly on a field. However, the RR mask can be used by a SQL statement. We set the field type to DATETIME and the field mask to DD-MON-YY, then placed the following statements in the ON-VALIDATE-FIELD trigger:

declare x date;
begin
x := year2000;
if :block.field != x
then :block.field := x;
end if;
end;

The year2000 procedure is defined below:

DEFINE PROCEDURE    NAME = YEAR2000
   DEFINITION = <<<
   function year2000 return date is

      x char(20); y date;
      cursor z is select to_date(x,'DD-MON-RR') from dual;

• This function is a workaround until the RR format can be used
• to directly mask a field. It just takes the current field value
• and adjusts it as though it had been entered using an RR mask.
• So far, this solution only works for DATETIME fields and will
• not work with DATE fields. This note aplies to 3.0.16.12.0, and
• may not apply to later releases. Once RR is supported, this
• function should be obsolete. Curtis Copley, May 25, 1994. x := name_in(:system.trigger_field); x := substr(x,1,7)||substr(x,10,2); open z; fetch z into y; close z; return y; end; >>>

ENDDEFINE PROCEDURE It would take a lot of space to explain exactly how and why this works and other things do not work. Some gotchas I can tell you about now, just to save you the pain of experimentation are: 1: COPY will first convert to DD-MON-YY, losing the century you just worked out, so you'll have to explicitly name the field in the on-validate-field trigger.
2: The IF statement in On-validate-field is necessary to prevent the field from being changed unless it is necessary. If I remember right, ON-VALIDATE-FIELD could be fired if it was on a non-enterable field before the first enterable field, causing a real change and a subsequent commit condition. Bottom line: keep the IF statement or you'll eventually run into trouble.
3: The DATETIME format allows name_in to get the century from the field, just in case it is already a year 2000 date. This will prevent unnecessary changes from being made to field values.

If you decide to monkey with this, and DO manage to shorten or generalize it, I'd like to know. For now, this is the best solution I know of in Forms 3. In forms 4, just set the format to DD-MON-RR and be done with it! Received on Fri Jul 21 1995 - 00:00:00 CEST