Re: Nulls in Informix

jayallen@auragen.com (Jay Allen) wrote: :In response to my post on using default values instead of nulls, :Nils Myklebust wrote: : :> Yes, but you would have the significant problem that you would have to :> analyze every insert into the table and leave out any column that had :> a null value in your application. If it is included in the insert you :> get an error, not the default value. I think Date, Darwen and others :> are of the opinion that simpler solutions for this problem should be :> implemented in the engine. : :At worst, this is a simple if-test when you generate your DML. Of course. It doesn't work very well with the Informix 4GL put statement though which is otherwise the most efficient way of inserting data into a table in that language. Also if you do create individual insert statements for every insert it does require some extra code. Additionally such insert statements wouldn't be very efficient. Even if you don't have the Informix 4GL put statement available using an insert cursor, you can still execute prepared insert statements in most languages. If you have more than a very low number of columns that can be null that's hardly possible to use with if statments for testing null values. Such prepared insert statements are usually more efficient than preparing every statement every time. One possible solution is of course that you read the default values from the database (the system tables) and replaces any null value (or other indicator that the user didn't key in any data) with that default value. That's not a way I would like to program though. Such things should be supported in the development environement to be realy usefull. :Good high-level application interfaces, like Microsoft's ADO, can :take care of such trivia automagically. It's no show stopper. If ADO where available in anything but a release (or is it still a prerealease) with significant functionality lacking compared to RDO this might have been fine. It also presupposes that you use a language that can take advantage of ADO. If ADO reads the default values from the database (the system tables) and uses that to set the variables before the database insert is executed this would work fine. If it on the other hand leavs out the null valued columns from every insert statement it would have to create them on the fly. Hardly very efficient compared to simply executing prepared insert statements or even better using an insert cursor and put statements. :> I think Date, Darwen and others :> are of the opinion that simpler solutions for this problem should be :> implemented in the engine. : :I don't understand what this means. In the engine was a little fast perhaps. An option is to have support for it at the client side like you say ADO from Microsoft does if that support includes getting the default values from the database. : The use of null introduces another :truth value, which affects the entire relational model. Yes, agreed. I didn't ever contest that. :At any rate, Date and Darwen were the first to advocate using default values :instead of nulls to solve the missing information problem. See Date's :_Introduction to Database Systems_,_Sixth_Edition_, Chapter 20, for :commentary and references. Date has hinted that he thinks an even :better solution is possible, but I've seen nothing concrete. I generally agree with them. However there are still problems with default values that I haven't seen them discuss a lot. As I understand it you will still have to have "is default" as a clause usable in the where clause of a select and probably in a programming language usefull with such a database. In some cases this would still be a 3rd truth value as I see it. Otherwise I do understand and agree with the problems introduced by extra truth values (3 or more). All this is however another discussion entirely that there is no usefull purpose for taking any further here in c.d.i. as long as we are talking about SQL based databases with lacking support of default values or other schemes to avoide such problems. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org