Database - functionality for front end application
kee at kagi.com
Sun Nov 16 13:12:03 CST 2003
On Nov 15, 2003, at 10:43 PM, Melvin Cox wrote:
> Problem Two: Placing the current time into a field
> Just prior to an insert or update of any row, I wish to populate the
> form's Last_update field with a datetime value reflecting the current
> date and time (ie. "2003-11-14 13:17:39.250").
> How can this best be implemented?
There are several ways to do this.
The best way if possible is to add a trigger to the table that tells it
to always put the current date and time into that column if any change
is made to the row. That is a pure SQL database kind of thing, has
nothing to do with revolution. The advantage is that regardless who or
what mods or inserts data, that value will be the date time it was
changed. Some databases do not support triggers, the ones I am exposed
The second way to do this is to use the database SQL date/time function
to cause it to insert it's current date time into that column and just
do the update for every insert statement you write. Something like:
update tableWhatever set blah = blah, etc = etc, Last_update =
getdate() where rowId = whatever
The reason I suggest you use the database SQL function is that it gives
you one time source (the database server) and a to the millisecond
consistency that you will not get if you have the client take the time
from it's computer and insert it.
The third way is as was suggested, pull the time from the client
machine and insert that. Just make sure you have all the machines
synced to the same time server and make sure they stay up to date.
> Problem Three: Forcing All Caps
> To insure data integrity, my application design requires entry of data
> into the "Pid" field in UPPER CASE format.
> While I have been successful in limiting the length of the data
> entered in this field to ten (10) characters or less, my attempts to
> force CAPITALIZATION have to date failed. The following script is
> currently applied to the field:
I let people enter whatever they wish and then data check it before
doing the insert.
If someone was to enter
I would wait until they say they are done entering (press enter or some
button) and then do validity checks and cleanup and insert. This way
you catch all text that might end up in that field, pasted, typed,
whatever. It is OK to validity check while they type, just isn't my
if the number of chars in fld "10charmax" > 10 then
answer "Ten character maximum, text truncated to: '" & char 1 to 10
of fld "10charmax" with "Cancel" or "OK"
if it is "Cancel" then
put toupper(char 1 to 10 of fld "10charmax") into fld "10charmax"
put "update theTable set thevalue = '"& fld "10charmax" & "' where
rowId = 1234" into theSQL
put executeTheSQL(theSQL) into the SQLresults
More information about the use-livecode