| Subject | Re: [ib-support] Default values don't work Properly |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-02-14T02:49:31Z |
At 12:05 PM 14-02-01 +1300, you wrote:
Server defaults act only on inserts and they take effect only if the insert statement does not pass a specific value for the column in question. If you have the column in your SELECT query then IBO will pass NULL if you don't specify a value. If it's a NOT NULL column, you are therefore going to get a server error if you try to post NULL. This is what *will* happen in IBO if you don't do something specific to prevent it. The GDS client doesn't return server defaults on Prepares.
If you just have IBO's default RequestLive behaviour, IBO has no way of knowing that you don't want NULL for that column in the InsertSQL. It will just set up the field list and values list to map your SELECT statement. Here are some alternative ways you can make this work properly:
Options (mutually exclusive):
1. Go to ColumnAttributes and specify a DEFAULT value for the column. This value will be passed if IBO encounters a NULL parameter.
2. Go to the ColumnAttributes and check REQUIRED false. In this case, you will need before insert and before update triggers on the table to insert the defaults you want.
3. Write a custom InsertSQL that excludes the column in question from the field and values lists. In this case, you will ALWAYS get the server default on inserted rows BUT you won't be able to accept user input for that column on inserts.
4. There is a GetServerDefaults function in the pipeline for IBO but I think it's just a stub implementation at the moment...perhaps Claudio could confirm or deny this.
hth
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
>HiMaybe what's needed is to correct false assumptions. :))
>
> I have table with default values for field defined in database as
>
>create table "Sample" (
>"Number" integer not null primary key,
>"Date" Date default current_Date,
>.
>.
>.
>.);
>
>if I use IBOTable the default values are not added to the table. I
>know there is default values property but that is like assigning
>values to the field, but default values must be filled in if they are not
>specified, which doesn't happens. Any Suggestions?
Server defaults act only on inserts and they take effect only if the insert statement does not pass a specific value for the column in question. If you have the column in your SELECT query then IBO will pass NULL if you don't specify a value. If it's a NOT NULL column, you are therefore going to get a server error if you try to post NULL. This is what *will* happen in IBO if you don't do something specific to prevent it. The GDS client doesn't return server defaults on Prepares.
If you just have IBO's default RequestLive behaviour, IBO has no way of knowing that you don't want NULL for that column in the InsertSQL. It will just set up the field list and values list to map your SELECT statement. Here are some alternative ways you can make this work properly:
Options (mutually exclusive):
1. Go to ColumnAttributes and specify a DEFAULT value for the column. This value will be passed if IBO encounters a NULL parameter.
2. Go to the ColumnAttributes and check REQUIRED false. In this case, you will need before insert and before update triggers on the table to insert the defaults you want.
3. Write a custom InsertSQL that excludes the column in question from the field and values lists. In this case, you will ALWAYS get the server default on inserted rows BUT you won't be able to accept user input for that column on inserts.
4. There is a GetServerDefaults function in the pipeline for IBO but I think it's just a stub implementation at the moment...perhaps Claudio could confirm or deny this.
hth
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________