Database - functionality for front end application

[kee nethery]

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 
to do.

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
"chNj8i009fhnme"
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 
style.

on mouseup
   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
       exit mouseup
     end if
   end if
   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
end mouseup

Kee Nethery