Firebird3-RC2 and identity columns

[ertank]

Hi,

I couldn't figure to use "new" IDENTITY column type of Firebird3 using UniQuery/UniTable. Below is a quote from Firebird3 release notes documentation:

Code: Select all

create table objects (
id integer generated by default as identity primary key,
name varchar(15)
);
insert into objects (name) values ('Table');
insert into objects (name) values ('Book');
insert into objects (id, name) values (10, 'Computer');
select * from objects;
ID NAME
============ ===============
1 Table
2 Book
10 Computer

Documentation says, new IDENTITY type column uses assigned value, if no value it generates an increased number.

I also read from documentation that Firebird3 uses automatically created generators for its new IDENTITY columns. Those automatically created generators are not visible to users, or at least I couldn't find them.

Coming to my problem. My table SQL script is as follows:

Code: Select all

CREATE TABLE SERVIS
(
  ID bigint generated by default as identity primary key,
  KAYITTARIHI datetime NOT NULL,
  KAYITSAATI datetime NOT NULL
);

I couldn't find a way to insert more than one record in that table. If I assign 0 (zero) to ID column by code before post: My first insert sets ID column value to 0 (zero). Second insert assigns same value to ID field and exception raised saying:

Code: Select all

violation of PRIMARY or UNIQUE KEY constraint "INTEG_35" on table "SERVIS" Problematic key value is ("ID" = 0)

I couldn't find a generator to assign a value to "KeyGenerator" option in UniQuery. I couldn't leave ID column NULL, setting Required parameter of the column "ID" to False & AutoGenerateValue parameter to arAutoInc by code didn't help it either. All these trials ends up saying "ID" cannot be null.

Any help is appreciated.

Regards,
Ertan Küçükoğlu

[ViktorV]

Support for Identity fields is being implemented. We will inform you as soon as we implement it.

[FredS]

Hi,
I couldn't find a generator to assign a value to "KeyGenerator" option in UniQuery. I couldn't leave ID column NULL, setting Required parameter of the column "ID" to False & AutoGenerateValue parameter to arAutoInc by code didn't help it either. All these trials ends up saying "ID" cannot be null.

I have used this for a while now, my setup for a table is below, the field stays null and all works using RC2 and later Snapshots:

KeyFields = 'ID'
DMLRefresh = True

object tbServersID: TLargeintField
AutoGenerateValue = arAutoInc
FieldName = 'ID'
end

[ViktorV]

For correct work with IDENTITY fields, you should perform the following steps:
1. Set the TUniQuery.Options.RequiredFields property to False.
2. Set the TUniQuery.DMLRefresh proeprty to True.

[ertank]

Support for Identity fields is being implemented. We will inform you as soon as we implement it.

Is there any update on identity field support implementation?

Thanks.

[ViktorV]

Please describe in more details the functionality you are currently missing when working with identity field.

[ertank]

Hi,

I would like to use data aware components linked to a DataSource. That DataSource linked to a DataSet. Please note that there are no Field definitions imported in DataSet at design time.

My CreateTable SQL is as follows:

Code: Select all

CREATE TABLE TESTIT
(
  ID bigint not null generated by default identity primary key,
  NAME varchar(30),
  ANUMBER integer
);

After preparing my form visual components and everything, I would like to use DataSet.Append() and DataSet.Post() to let user enter information in table. ID field (identity primary key) will be always null when calling Post().

Such a design raises "Field 'ID' must have a value" error message when tried to Post(). Tested with V3.0.0.32483 version of Firebird.

However, same setup using MS-SQL Server or SQLite3 as a database back end and Post() runs just fine. Records are posted and ID fields updated with database assigned numbers.

I have a small application with firebird database file which I can post upon request.

[ViktorV]

For correct work with IDENTITY fields, you can use the following recommendations:
1. Set the TUniQuery.DMLRefresh proeprty to True.
2. If you have persistent fields - set Required parameter of the identity column to False. If you do not have persistent fields - set the TUniQuery.Options.RequiredFields property to False.
If this doesn't help, please compose a small sample reproducing the described behavior and send it to viktorv*devart*com, including scripts for creating database objects, in order for us to be able to give you a more detailed answer.

[ertank]

Hello,

Doing all of above seems to solve my problem with Firebird identity fields.

Thanks.

[ViktorV]

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about UniDAC.