Subject RE: [IBO] Re: Trouble with Insert Into
Author Svein Erling Tysvær
Hi Dave, welcome to Firebird and IBO!

>This could get interesting as some of the fields in the DBF are encrypted passwords.
>Keeping them encrypted in the FB table, but displaying them unencrypted at run-time
>has me a little puzzled at the moment, but I am sure I will figure it out. :-)

I recently had a similar problem and OnGetDisplayText helped me out (although my additional desire to ORDER BY the displayed text rather than the encrypted value failed).

>Is there an active group for FB? I know there is a Mail List but that is cumbersome
>to use with the post and wait.

Firebird-support is a mailing list, similar to this list, just with more people participating. The main difference between this list and firebird-support is that this list is for questions regarding the use of IBO, whereas firebird-support is for questions relating to Firebird or InterBase (SQL questions should generally go to firebird-support).

>Yesterday I struggled with deleting records that had an empty AutoInc field. I
>didn't know that I needed to set a Trigger for the Generator, so the first few
>records had no AutoInc value. With no value, I finally figured out that the
>Generator needed to be Triggered.
>I tried Delete Where AutoIncField = null, 0 and '' but all were ignored. I
>finally changed data in another field in all of the records that had an AutoInc
>and was then able to Delete Where AutoIncField = null

You ran into the issue of NULL being a state, not a value. NULL isn't equal to anything (unless you use IS NOT DISTINCT FROM), even NULL isn't equal to NULL. Think of it as unknown. You neither know my name nor my sisters name, so in your mind, we're both NULL. Still, that doesn't mean that we have the same name. To check for NULL, you would have to do


The one place where = is used with NULL, is with assignments. So it is possible to do

Update MyTable
Set AutoIncField = NULL

Rather than delete where AutoIncField IS NULL, I'd recommend you to just do

Update MyTable
Set AutoIncField = GEN_ID(MyGenerator, 1)
Where AutoIncField IS NULL

>It would have been good to be able to go to an FB SQL group and ask how to do it.
>I have some other questions on why things are done the way they are in SQL. I like
>to understand why I am doing stuff as well as learning what to do.

Firebird-support is good for questions, response time varies with the type of question - it can take weeks or never be answered (most often when people don't explain their problem properly), but I've also seen answers appear before the question!

The Firebird book by Helen Borrie (she's already answered you once) is well worth buying (though it is Firebird specific, not a general SQL book). ib-phoenix is the best place to buy it from (at least those that bought it from there got an electronic update and some money were returned into the Firebird project). I'm not certain whether to recommend you to buy The Firebird book now (it is quite old, written for Firebird 1.5) or wait until a new version will be published.


(now you know what I'm called, is that the same as my sister? Since you still don't know her name, the correct answer to that question is NULL...)