On 22 May 2013 08:59, dale janus wrote:
In your DDL did you tell it a DEFAULT of current timestamp or some
such thing?
In your RPG did you use an index or view which omitted that column?
You have to do both if you're sticking with RLA.
So what I am understanding is that I can use these great DDL tools,
but if I don't use SQL, they don't work.
Not a case of "does not work", but a case of not doing what happens
to be what is desired of some programmer in a specific situation.
The timestamp is defined as DEFAULT current. I am not using a view
or index, so the field is included. Therefore it defaults to 0001 01
01 etc.
I am very disappointed that I cannot use this new tools that we are
encouraged to use without taking extra steps of a view or filling
the field in RPG.
Yet if DDS had been used instead of DDL, and a default value had been
established using DFT(value), the same issue occurs whereby the WRITE
does not effect the default-value. Would that same disappointment be
expressed in that scenario with the "old tools"? My point... the issue
is with the RPG, not with the Database [file] or the SQL. See the
following KB article; not sure what can be inferred from the last
applicable release being listed as v5r4... I submitted feedback to
inquire of any intended implication:
http://www-912.ibm.com/s_dir/SLKBase.nsf/1ac66549a21402188625680b0002037e/bd2cadb20504ce94862571fc0068f8ea?OpenDocument
"Document Number: 428709941
OS/400 Release: V5R1M0; V5R2M0; V5R3M0; V5R3M5; V5R4M0
_Document Title_
Does RPG Support the DFT Keyword in a PF?
_Document Description_
The keyword DFT can be used on the field in a PF. This keyword can be
used by the DB2 database to determine what default value will be updated
into a field at output time..
The HLL RPG/RPGLE does not use the same defaults that for [sic] defined
by DB. It uses whatever is in the output buffer for that field/s when a
output operation is done.
Example: The output operation WRITE. A PF has the keyword DFT defined on
a date field. If doing a WRITE operation to the file, these fields are
not used in the output operation. The High Level Language RPG will
generate a default value for that field. In the case of a date field,
the date format used will be 'DMY'. The information that is placed in
the date field will be 01/01/40.
"
FWiW the transition to using SQL is best done by first changing to
start using the non-DDL SQL statements to work with the data; i.e. using
the SQL DML. Delay the change to using TABLE from using DDS PF until
afterward, or do so only when necessary to resolve some problem; e.g.
preventing bad decimal data being written to the file. If changing to
TABLE for added function [that is not available in DDS], then the move
to SQL for DML will effectively be a requirement; this is somewhat the
case, for using CURRENT registers for default values.
I suppose the correct answer is don't use record level access, use
SQL.
Both the LF [which could be an SQL VIEW vs DDS LF] omitting the
column or an OPNQRYF referencing a FORMAT() with that column omitted
would enable the RLA to effect the current value being implicitly set by
the DataBase.
Or an enhancement in RPG RLA that allows naming the fields which are
being assigned a value, so the RPG can tell the DM\DB such that the DB
can assign the default value for those fields not included in the WRITE.
The following may just describe such an enhancement.?:
http://archive.midrange.com/rpg400-l/200905/msg00104.html
"...
If you want to use the default value in RPG you have to use an
externally described data structure with INZ(*EXTDFT)
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/index.jsp?topic=/rzasd/sc092508548.htm
...
"
I don't have enough time to learn everything, I must get this stuff
written by deadline. So I picked DDL and RDI as stuff to learn.
Thanks for helping me along my learning journey, another new thing
today.
Assuming the low-value for the TIMESTAMP is always undesirable on a
HLL WRITE or INSERT, as a hardened rule, and the CURRENT TIMESTAMP value
is desirable instead, then a very simple TRIGGER can be added to the
TABLE very easily to ensure that effect:
create trigger the_file_BI /* <- trigger name; see below */
before insert
on the_file /* <- trigger name added _BI suffix to file name */
referencing new as N
for each row mode db2row
begin
if n.t = TIMESTAMP '0001-01-01 00:00:00.000000' then
set n.t = CURRENT TIMESTAMP ;
end if ;
end
;
Oddly the above trigger did not function as expected when tested...
and I am having difficulties [due mostly to my user stg limit] in some
test and debug, so I can not determine what is wrong presently. If I do
not forget, I might followup with any change; the problem may be
something specific to v5r3 vs an error in what I coded.
midrange.com
