At 04:55 PM 24-12-01 +0100, you wrote:

>I use the TIBOTable in a converted non-SQL-application.
>I use a field, not null with a default value.
>When I insert a record using TIBOTable.Insert and Post without
>giving the field a value, then an exception is generated saying
>that the field cannot be null.

Some facts about server-side defaults:
1. They work only in an insert.
2. They work only if the insert doesn't specify the defaulted field at all in the SQL.

Let's say you have this table:

create table test(
ID integer not null,
field1 integer not null default 0,
field2 integer));

On test.ID you have a trigger named my_gen_test:

create trigger test_BI for test
active before insert position 0
as
begin
if ID is null then
ID = gen_id(my_gen_test, 1);
end

If you do some test inserts interactively....

This statement will work:

insert into test(field2) values(99)

Assuming the generator stood at 15 before the statement, the row stored will be:

ID Field1 Field2
=============
16 0 99

This statement won't work:

insert into test(field1, field2)
values(null, 99)

You get a violation of the not null constraint on Field1. The default doesn't operate on this input because you explicitly sent null to the column.

Now, going to your client application...

At Prepare time, the client side of the API receives the NOT NULL constraint from the server and flags that field as REQUIRED.

The Insert method of the IBOQuery automatically constructs this statement for your dataset:

insert into test ( ID, Field1, Field2)
values ( :parameter1, :parameter2, :parameter3)

Now, in your application, you click Insert, type 99 into Field2 and click Post. This is what the Insert statement looks like:

insert into test ( ID, Field1, Field2)
values ( null, null, 99)

This raises two db errors. The database requires non-null values for ID and Field1.

The first one is solved by using the GeneratorLinks property of the dataset. Set it like this:

ID=my_gen_test

The second one (Field1) you can solve in one of two ways:

a) Use a custom InsertSQL statement that doesn't include Field1, thereby forcing the server default to kick in. For example,

insert into test( ID, Field2)
values( :ID, :Field2 )

GeneratorLinks will supply the value for :ID and the client simply does not deal with Field1. This is not ideal, because you want the client to pass a Field1 value if the user supplies one.

b) Supply a default value yourself, from within the application, so that the automatic InsertSQL statement will work regardless of whether the user supplies a value, i.e.

insert into test ( ID, Field1, Field2)
values ( :ID, :Field1, :Field2)

How to do this? Well, there are several ways...

1. Call GetServerDefaults in the query's BeforeInsert, e.g.

MyQuery.InternalDataset.GetServerDefaults;

This is expensive because it requires a metadata query before every insert. It's not worth the cost. Also, I'm not sure that GetServerDefaults works for TIB_Dataset, which is the class to which InternalDataset belongs. GetServerDefaults seems to kick in higher up the chain than TIB_Dataset.

2. Set the default value you want in the IBOQuery's DefaultValues property, a stringlist which allows one line for each column for which you want to assign a default value, e.g:

Field1=0

3. If you want the default to apply to this column everywhere in your application, set it in the DefaultValues property of the TIBODatabase. In this case, include the table identifier:

test.Field1=0

4. The DefaultExpression property of the TField object is also available and achieves the same as (2) above.

In summary, what you described is correct behaviour. Clients always need to supply default values if they include the defaulted fields in their InsertSQL.

regards,
Helen