Subject Re: [IBO] newbie: DEFAULT & NOT NULL
Author Helen Borrie
At 12:04 AM 18-07-02 +0000, you wrote:
>Hi there,
>(sorry if this is posted twice, my form just disappeared before)
>
>Using Firebird & IBO 4
>I have two issues confusing me a little:
>
>1)
>I have a table with a smallint field which has a default value of 25.
>When I use the TIBOQuery, this default value is obeyed, but when I
>use the TIB_Query this default value seems to be ignored.

There will be some difference in the SQL of these queries. A metadata
default will only fire if the insert statement does not contain the column
to which the default applies. So...

insert into aTable(col1, col2, defaultedcol)
values('x', 'y', null)
will not fire the default...but

insert into aTable(col1, col2)
values('x', 'y')
will fire it.

>I'm aware
>that I can set this default value through the actual TIB_Query
>component, but this seems to defeat the purpose of setting the
>default values in the gdb metadata.

It depends on how you go about inserting rows. If you want the default to
be written from the insert method of a dataset which contains the column,
then you should configure a client-side default.

>2) (maybe more of a firebird question)
>In this same table, I have four fields with NOT NULL. When I do an
>insert into this table, and try to post without filling in one of
>these fields, I get the relevant message saying this field cannot be
>null. But when I go back and edit a record, I can clear out these
>values, and I can post the record with blanks in these fields.
>What am I not understanding here??

You just need to understand that data are posted from the dataset, not the
control. If you don't touch the column in the control, no value will be
written to the dataset and its value will remain null.

Once you make some kind of entry in the control, the datasource recognises
that it has to update the dataset. If make an entry and then "blank it
out", the datasource writes an empty string to the dataset. ('' = two
single quotes). Empty string is not null.

You can make a setting in the dataset column BLANKISNULL=true to have the
datasource treat a blank or empty string as null. You can also manipulate
it yourself, e.g.

if MyDataset.FieldByName('ACol').AsString = '' then
MyDataset.FieldByName('ACol').Clear;

or

if MyDataset.FieldByName('ACol').IsNull then
MyDataset.FieldByName('ACol').AsString := '';


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