Re: Same options in i4gl, dbaccess & upscol?

On Fri, 31 Oct 1997, Nelson Fredsell wrote: > I'm learning about implementing data validation rules using the > Informix-supplied utilities i4gl, dbaccess and upscol. OK. I'll take the bait. But be clear that I am speaking as a user of I4GL rather than as a representative of Informix, despite my email address. Standard disclaimers apply, therefore. > Most specifically I'm trying to create date and time stamps in > a table via a form. How do I make the date show up in one > field and time in another? I'd like to implement these default > values at the table level, not the form level if possible. There are multiple places where the defaults can be implemented, and therefore multiple ways of doing it, and your preferred solution will depend on how you choose to do things. Starting with basics: you make the date show up in a field (f001, say) by making it a DATE field or a DATETIME YEAR TO DAY field, depending on how much control you want over the format, and you make the second field (f002, say) into a DATETIME HOUR TO SECOND field. Now, when you use an I4GL INPUT statement, there are two variants: INPUT, which is typically used for ab initio data entry, and INPUT WITHOUT DEFAULTS, which is typically used when a data record is to be updated. The difference is that INPUT WITHOUT DEFAULTS displays the initial values of the data from the variables which will receive the data, and INPUT uses the defaults set via the form. The defaults set via the form can come direct from the text in the form file, or can come from the SysColVal table maintained by UPSCOL and processed by form4gl when the form is compiled (not at runtime!). I normally only use INPUT WITHOUT DEFAULTS and use code to set the defaults for a raw INPUT (Add option); this means that I only have to have one INPUT statement with associated validation code for both Add and Update operations. But that's an implementation detail. With that ground-work out of the way, we can now consider what you want to do. You could use the form DEFAULT values to set the values in the INPUT (with defaults) statement; this will display the current date and time at the point when the INPUT statement is entered in the fields, and (unless you prohibit it), the user will be able to edit these fields. Similarly, for an INPUT WITHOUT DEFAULTS statement, you might either allow the values retrieved from the database to stand, or you might set them to the current date and time. When the user hits the accept key, the program can send the data from the screen unmodified to the database (an INSERT or UPDATE statement), thus using the values originally set by the defaults, as amended by the user. Or the program could reset the values to the current time after the accept key is entered, so that the values in the database reflect the time when the database operation occurred, according to the clock on the computer running the application. Alternatively, if the database schema is set up correctly and the I4GL code is careful, you can get the engine to record the time for you. The table will need DEFAULT CURRENT or DEFAULT TODAY clauses on the date and time fields, and will need an update trigger to reset the date and time fields. The I4GL code has to be careful never to mention these values (even implicitly) in the VALUES list of the INSERT statement or the SET clause of the UPDATE statement. This will allow the defaults and trigger to set the values to the current date and time as seen by the engine. This could be quite different from the date and time as seen by the application. > dbaccess has option Constraints, then Defaults; > upscol has option Validate, then Default. > > Are these two features the same? No, absolutely not. The DB-Access option adds a clause to the CREATE TABLE (or ALTER TABLE) statement, this changing the schema of a table. The UPSCOL option affects I4GL forms (and not ISQL forms) compiled using this table, providing the ability to change the 'default default' from NULL to some other value if the form does not explicitly set a default. > I have similar questions relating to implementation of constraints at the > field level. The column (field) level constraints implemented by DB-Access are always enforced by the database engine. The column level constraints implemented by UPSCOL are implemented by the form. The database constraints override the form constraints, always. The user will find it difficult to override the form constraints from within the I4GL application, but could bypass the form constraints by using ISQL or DB-Access to alter the database; the user cannot get passed the database constraints at all. > Being a code minimalist/lazy worker(/newbie?), I'd like to > make full use of these utilities *before* validating data at the > form/module level. The DB-Access constraints should be specified when the table is created, but will not be enforced by UPSCOL or the form system -- the form system and UPSCOL predate the database-enforced constraints, so it is unaware that they exist and does not know it should look for them. It would be sensible to have that ability, but that would require changes to I4GL. > Anywhere I can go to read up on setting constraints thru > the utilities? Manuals don't seem to comment much on > INCLUDE, DEFAULT and CHECK. (I guess default isn't > really a "constraint," is it?) It is and it isn't; it's a question of terminology. It does constrain things a little bit, but not all that much. The CHECK and DEFAULT clauses in tables are described in the Informix Guide to SQL: Syntax under CREATE TABLE. INCLUDE and DEFAULT for forms are described in one of the I4GL manuals. And that's about it. > Thanks. > > Additional, less important comments/questions: > > upscol and dbaccess don't seem to communicate constraint > information to each other: a default value of "Pissed Off" > for the "name" field implemented thru dbaccess doesn't > show up as a default value thru upscol. Correct. > Our app contains lots of routines to load ascii flat file data > into the tables. I expect some validation code is used. Only if you write it. > Because of this batch processing, should I be more inclined > to use upscol or dbaccess? DB-Access. > I haven' t explored the VALIDATE statement yet. Probably not worth it, especially if you've got DB-Access enforcing your constraints. There are all sorts of validations which you can't do with VALIDATE -- such as TODAY - 365 THROUGH TODAY. > The error checking seems so crude..."Syntax error." when > using using dbaccess. Well, syntax error probably means that you haven't formed the SQL statements correctly. This could be because you've got quotes in strings, or haven't put quotes around DATETIME values, or any of a myriad other problems. > Gee, thanks for the tip. > Guess I just > had to figure out that I couldn't implement a default value on > a primary key. Well, you can't do it usefully, in general, because you'd only be able to insert one row with the default value. The exception would be a SERIAL column where a DEFAULT 0 would be useful. Note that column-