Re: Defaults in SE

Hi, Column DEFAULTs work. In the specific case of a CHAR column, the default value of USER works. I specifically tested version 7.10.UC1 SE on Solaris 2.4, and it works; I also tested 6.00.UE1 OnLine, and it works there too. I also tested the default of TODAY on a DATE column, and it works in both versions. If you try to mix the types (eg using TODAY on a CHAR column, or USER on a DATE column), you get error -591 "Invalid default value for column/variable (column-name)". As I noted in a previous message (I've updated the text to add a DATE column with a default of TODAY, and a CHAR column with a default of USER): Date: Mon Apr 24 14:12:46 1995 From: johnl (Jonathan Leffler) I suspect that you are suffering because you are using SPERFORM rather than raw SQL statements. The defaults only kick into effect when no value is specified for the column in the INSERT statement. By default, SPERFORM provides values for all columns, so the defaults wouldn't take effect. Try: CREATE TABLE X ( Col01 SERIAL NOT NULL PRIMARY KEY, Col02 INTEGER DEFAULT 10 NOT NULL, Col03 CHAR(20) DEFAULT USER NOT NULL, Col04 DATE DEFAULT TODAY NOT NULL, Col05 CHAR(20) NOT NULL ); INSERT INTO X(Col01, Col05) VALUES(0, "ABC"); SELECT * FROM X; This certainly works with 6.00 OnLine. Next, generate and run a default form, adding a row. No DEFAULT is used. Edit a copy of the form to remove Col02, Col03 and Col04, compile and run it, and add a row. With OnLine, this works and gives the defaults in Col02, Col03, Col04. You can use the original form to verify this. The defaults only apply to the columns you can't see on the form. I do not know of any bugs in DEFAULT in tables in any version of the Engines. I may just be ignorant, but I believe it is, and always has been, correctly behaving functionality. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: gordonh@acslink.net.au (Gordon Hooker) >Date: Tue, 25 Apr 1995 06:14:40 GMT >X-Informix-List-Id: <news.13337> > >alan@po.den.mmc.com (Alan Popiel) wrote: > >>} From: exg9537@cs.rit.edu (Edward X Garbowski) >>} Date: Mon, 24 Apr 1995 15:25:35 GMT >>} >>} Hey all, I'm trying to create/alter tables so that certain >>} columns will have default values. It takes the changes and >>} everything, and looks like it should work...but the defaults >>} are never used! >>} >>} Ex: have a column of char(10) with a default of user >>} of column of date with default today >>} >>} none of the defaults work. I've tried other constraints (ie - keys, >>} or checks...) and they all work. But not defaults. >>} >>} For reference, I'm using Informix SE 7.10 and SQL 6.01. I've also >>} tried SQL 4.00 (w/ comparible SE) and it still didn't work. >>} >>} I find it hard to believe that Informix has never had defaults >>} working....what's wrong?! > >>Defaults to constant values work. Defaults to function values, like >>USER and TODAY do not work. I do not know why the use of function >>defaults is not flagged as an error, since it doesn't work (as you >>found out). Maybe the plan is for defaults to work with functions >>in later product releases. In that case, a warning message would >>still be nice. > >Are you saying it never worked on 6.x, 7.x or never worked at all. I >have been using 5.x and I use functions extensively to create an audit >table with no problems at all.