Re: How to put validation/code in perform file using isql ?

On Thu, 3 Sep 1998, June Tong wrote: > Jonathan Leffler wrote: > > On Thu, 3 Sep 1998 n_kolekar@hotmail.com wrote: > > > Hey Folks, Is there a way to put code in instruction section of the > > > perform file generated by isql? We are doing input using the perform > > > screen using ISQL. So we want to validate the data in minimal before it > > > goes into the database. Example: My valid date range is from 80 to 99. > > > So if the user enters any other dates, I want him to re-enter the date. > > > And I want this to be done using ISQL only (not 4gl). > > > > You can create a custom version of Perform with the cperf script, and you > > can add validation functions which can be called in the instructions > > section of the form. > > You could put in your Perform screen: > f001 = tabname.date_fld, include = (80 to 99); > > Or did you mean that the valid dates are '1/1/80' to '12/31/99' (using > MDY2/ format)? I don't know if INCLUDE handles dates, but you could try > include = ('1/1/80' to '12/31/99') The dates should be specified with 4-digit years because otherwise your database will get screwed by someone's screwy DBCENTURY setting. For example, if DBCENTURY is F, the date range is empty because the first date is 1st January 2080 and the second is still 31st December 1999 and the test is for greater than first and less than second value. Or if DBCENTURY is P, the date range is also empty because the first date is treated as 1st January 1980 and the second is 31st December 1899. Note that there doesn't seem to be a compile time check that the first date in a range is earlier than the second; this could be regarded as a bug. There is a runtime check that a date entered is both greater than or equal to the first and less than or equal to the second, so if your end dates are reversed, you cannot enter any valid dates. I went to check what happens if I compile the form with DBDATE=DMY4/ and then run it with DBDATE=MDY4/. It worked OK. I then tried to compile the form with the 'wrong' DBDATE format and the compilation failed. That means that the date values in the form are converted to date numbers at compile time, so as long as the date strings are convertible using the compile time setting of DBDATE. That's a relief; I was afraid that you couldn't readily put the date ranges into and INCLUDE clause in a locale-independent date notation because the strings were converted at runtime. I did a little more checking and, of course, the DBCENTURY setting at compile time determines how the date strings in the INCLUDE clause are interpreted. > Sorry, I don't have I-SQL, so I can't test this (life's gotten so tough). > I'd be curious to know whether it works, though. Yes, it does, provided that the range of dates to be included is static. If you need more dynamic date ranges (eg next year is OK; this year is OK; the last 15 years are OK), then you can't simply use an INCLUDE clause unless you rework it each year and recompile it each year. Come to think of it, that might not be so bad a discipline; at least you'd get to know that you'd lost the source code within a year (so you might be able to recover it from a backup) rather than waiting until you've been missing the source for five years and have no backups which might contain the source. But I digress... Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn