Please do not hit reply to someone elses post unless you are
responding to that post. Even though you change the subject, the
headers still refer to the previous post, and newsreaders and the yaoo
web interface will basically nest your question as a response to
someone elses question. Compose a new message instead.
>
> Some said in old sql standard, zero-length (empty) string will be
converted to
> null, eg,
> update foo set bar=''
> will effectively become
> update foo set bar=null
>
> Is this true for FB1.5 and FB2 ?
No, these are two different statements.
update foo set bar=''
Means that you know the value of the bar field of every record in foo
which is exactly ''.
update foo set bar=null
Means that you are unsure of the value of the bar field in any record
of foo.
There is a subtle but very important difference.
A helpful example a middle name in an employee database. If you know
an employee has no middle name, then you could set it to ''. If you do
not know if they have a middle name or not, then you would set it to null.
>
> A related question, in DDL, is
> a varchar(10) default ''
> equivalent to
> bar varchar(10) default null
>
No.
The first would set the field a to '' if you did not specify the field
'a' in an insert statement. (There is no other occasion the default is
considered).
The second would set the field to null if it is not specified in an
insert statement. That is pretty useless to be honest as that is the
standard behaviour anyway.
> (For your information MS Access does allow zero-length string)
Chalk and Cheese. Microsoft does a lot of things well, complying with
standards is not exactly their strongpoints. So be very careful when
assuming that because Access behaves a certain way, that must be the
normal way to do things.
But in this case, yes Firebird also allows an empty string.
I recommend you locate the Firebird NULL Guide on www.firebirdsql.org,
probably in docs somewhere. In 99% of cases, Firebird handles NULLs
the correct way, the gotchas are all listed.