Autoincrement with non-zero start and firebird backend

[coolmike]

Hi,

I’m using Open Office 3.0.1 on WinXp with Firebird 2.1. I have a table with an autoincrement field defined. I would like to perform data entry in a form, where the default autoincrement value starts at a non-zero number. Also, presently, it seems I have to actually type NULL in the autoincrement field in order for a value to be generated.

I have tried using SQL statements to change this behaviour, but I can’t quite get it right. Can you please point me in the right direction? Any suggestions would be appreciated.

Thanks

[r4zoli]

I never used firebird in everyday work, but tried to connect to OOo to FB server.
As know well firebird not use autoincrement fields, it uses generators and triggers to create autoincrment column.

The firebird FAQ says:

To make column ID autoincrement, we don't have to do anything special to the column itself, but we need to create a BEFORE INSERT trigger that will put a new value each time a new record is inserted.

I think, you can remove the ID field from Form, the ID will be automatically inserted.

[coolmike]

Thanks,

I think you are right about this. My problem then becomes: the ID field is incremented starting at 0. If I have an already populated database with numbers that started at, say, 1000, the new records will seem out-of-sync. Is there a relatively easy way to fix this?

Regards

[r4zoli]

I looked in firebird generator guide, from this document in para: Setting a generator directly to a certain value (“Update”) , you can find SQL commands to setting starting value:

Code: Select all

ALTER SEQUENCE <SequenceName> RESTART WITH <NewValue>;

Try this in Tools>SQL... command window.

If not help, may be the database admin can only set this value.

[coolmike]

With the help of your original post, I was able to alter the database so that it does, in fact, begin at the desired number. It will auto-increment from there, provided that the index field is given the NULL value. When I try to enter a record from the Table editor, and I leave the index field blank, this works. In the forms, it is never sent a null value unless I manually type it in the field. I have tried creating a form without this field. That fails as well.

So all I need to do then, I think, is find a way to automatically send NULL to that field. Is there an easy way to do this?

Thanks for all the help. I'm making good headway.

[r4zoli]

Try to use OOo 3.1 version, in it Poperties/Data/"Empty string is NULL" property refined, it is can give real NULL when empty.

You can set this ID field on form not to be tab stop, in that case you can not step into that field, and cannot add any wrong data into it. If this filed has a value, it will show.

[coolmike]

You've done it. I updated Oo, and voila!

Thanks so much