Re: SQL insert using "current timestamp" across machines

It sounds reasonable that the "current timestamp" is from the machine which "hosts" the tables, because I would assume, that the SQL statement ist passed to the target machine, and executed there.

To in fact, the only solution I could think of, is to pass the value, that you want with the statement.

HTH
Daniel

Von meinem iPad gesendet

Am 20.07.2023 um 15:32 schrieb smith5646midrange@xxxxxxxxx:

I stumbled over something weird so I wanted to pass it along for you folks
to put in the back of your mind and maybe remember if it ever shows up in
something you are dealing with.

Our system has files on one machine (M1) with SQL aliases that connect to
them from other machines (M2 and M3). These work very similar to DDMFs
except you can use SQL to update the aliases. When a program does an SQL
"insert into." into one of the aliases on M2 or M3, the data is inserted
into the file on M1 as it does with a normal write to a DDMF.

Here is the weird part. When the program on M2 does an "insert into." and
one of the values specified is "current timestamp", the record that is
inserted uses the M1 "current timestamp", not the M2 "current timestamp".
How I found this is the system times between M1 and M2 are off by about a
minute and we have records with timestamps when nobody was logged on to M2
with the ID that wrote the records. If I subtract about 1 minute from the
timestamp in the record, it would match the user login time on M2.

The workaround that I have found is to create a timestamp variable in the
RPG, load it with the current timestamp, and use the variable in the insert
statement instead of "current timestamp". Obviously this will not work if
the systems are in different time zones so if anyone has a better solution
(other than keep your system's times in sync) I would love to know about it.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.