I actually generally abide by the listing of fields. This scenario is occurring in a file encapsulated service program. Before on every write it would set the last change date to %timestamp. I still have to manually set the Last Changed by User field to the current user. When it inserts/updates, it's not keeping track of each field that was set/changed. The set procedures are loading the value into a file data structure, hence the insert/update using the file data structure.
The 'fix' I had to make to use the auto-updating timestamp field was to list out each field individually anyway. The view would have worked, but I want to be able to present the last changed date to the user even though they can't set it.
I appreciate all the feedback. Our shop is gradually embracing the features SQL provides.
Thanks,
Kurt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, March 15, 2013 12:42 PM
To: Midrange Systems Technical Discussion
Subject: Re: AW: SQL Table with ROW CHANGE TIMESTAMP
Amen sister!
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>,
Date: 03/15/2013 01:37 PM
Subject: AW: SQL Table with ROW CHANGE TIMESTAMP
Sent by: midrange-l-bounces@xxxxxxxxxxxx
That specification "Indicates the column is not visible in SQL
statements
unless it is referred to explicitly by name. For example, SELECT * does
not
include any hidden columns in the result." Perhaps the DS defined with
the
external TABLE honors [or should honor] that?
But if he defines an external data structure all columns (implicitly
hidden
or not are included) and when reading a record with SELECT * you'll get
huge
problems!
Again another reason why columns should be explicitly listed.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Friday, 15.3 2013 18:06
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: SQL Table with ROW CHANGE TIMESTAMP
On 15 Mar 2013 07:54, Anderson, Kurt wrote:
Just got back from the WMCPA conference and I'm using today to apply
some of the learning that occurred earlier this week.
One thing I learned about was being able to have my SQL table
automatically set the last change timestamp field w/o me doing it.
Example:
ChgDate TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT
NULL
I added some records via DBU and saw that this worked great.
Unfortunately this seems to be de-simplifying the embedded SQL in my
program.
I had Insert into BillAgentP Values(:ds_Insert); Where ds_Insert is a
Qualified DS defined like the file. But now that doesn't work b/c it
doesn't like that I'm updating ChgDate. Is my only recourse to list
out all the fields except for ChgDate?
I'm at IBM i 7.1
And if that column is defined using IMPLICITLY HIDDEN specification on
the ROW CHANGE TIMESTAMP? That specification "Indicates the column is not
visible in SQL statements unless it is referred to explicitly by name. For
example, SELECT * does not include any hidden columns in the result."
Perhaps the DS defined with the external TABLE honors [or should honor]
that?
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
