At 06:51 PM 11-01-02 +0000, you wrote:

>Imagine the follwing:
>
>1.) You have a db field "Description" which must not be NULL.
>2.) You have a trigger which sets this field to a computed value if
>the user leave it blank but if the user specifies it the user's value
>is taken.
>
>The problem seems to be that the field "Description" is allways
>included in the sql insert statement even when NULL and so you are
>running into the "must not be null" exception.
>
>I found out how to get this to work with generators (by the property
>GeneratorsLink) or with default values (with the property
>GetServerDefaults) but I'm absolutely clueless how to solve it with
>this kind of trigger.
>
>Maybe I missed anything but is there a property to specify to omit
>column values in the sql insert statement if they are NULL?

Frank,
the answer is "no". You have either the automatic INSERT statement created by the dataset (which includes all columns in the dataset) or you have you own custom INSERT statement which you have entered in the InsertSQL property.

Perhaps you need to look at your problem from a different perspective.

You have two broad scenarios for NOT NULL columns:

If you use a generator for your primary key, you want to insist on the generator being used, i.e. you never want a user to enter a primary key, either for inserts or updates. So you don't show the primary key column to the user but you have it in the dataset. You use GeneratorLinks to ensure that IBO will fetch it at the beginning of the sequence of Insert events that will culminate in an INSERT statement being passed to the server.

If you have a non-key column which is NOT NULL, it's because you REQUIRE the user to enter a value here. On the server, you have defined a default so that, if an insert statement comes through not containing this column, the server will write the default into that column.

But that doesn't please Delphi, which makes every NOT NULL column a REQUIRED field. So you are stuck, and you feel that, if only you could somehow eliminate that required Description column from your INSERT statement, the problem would go away.

Well, unfortunately, you can't have your cake and eat it too.

So, enter a couple of IBO tricks that fix this problem in two different ways.

In the field attributes (DefaultValues) you can enter a server-side default for that field. You can do it at Dataset level or, even better, you can do it globally by using the DefaultValues property of the connection object (ib_connection or database object). This seems to be the option that best suits your purpose here.

The other thing you can do is set off the column's REQUIRED ColumnAttribute and get past Delphi's insistence upon throwing an error for Null in a non-nullable column. Where this is useful is if you have a Before Insert trigger in the table that checks for both blank and null and writes in a default value if so.

Delphi will send blank for inserts in any field that started empty and didn't receive a value during the insert sequence. There is another ColumnAttribute you can set to force it to clear the blank and send NULL instead - see BlankIsNull.

I won't go into details of how to implement these properties here in the list. It's all in the Working with Datasets TI sheet which you can download from the IBO website's TechInfo page. There is also a number of FAQ items on and around the subject of defaults.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com