Problem with insert

[Rogier21]

I use the latest Zeos with firebird 2.0, but I have trouble inserting data to my tables with autoinc columns.

Ie I have this table:

Code: Select all

CREATE TABLE MAPS (
  ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) CHARACTER SET UTF8 COLLATE UTF8);

ALTER TABLE MAPS ADD PRIMARY KEY (ID);

CREATE GENERATOR MAPS_ID_GEN

SET GENERATOR MAPS_ID_GEN TO 0

CREATE TRIGGER BI_MAPS_ID FOR MAPS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(MAPS_ID_GEN, 1);
END

Simple table, 2 fields, ID and Description.
I also created a trigger so I dont have to worry about the ID's.

If I insert data with a query:

Code: Select all

INSERT INTO maps (id,description) VALUES(null,'test')

Works like a charm, even if I dont define ID at all.

But I dont use querys everywhere, sometimes I just use a good old Table.

So I want to do this:

Code: Select all

MapsTable.Append;
MapsTable.FieldByName('ID').AsInteger:=null;
MapsTable.FieldByName('Description').AsString:='test';
MapsTable.Post;

I get the error, "NULL cannot be converted to INTEGER".
Do I leave the whole line I get: ID must have a value.
Do I set .AsInteger=0 the ID's will be 0, obviously.

But how to do it right then? How to get it on NULL?

Anyone an idea?

[mdaems]

Have you tried to set MapsTable.FieldByName('ID').Required:=false? As it's a primary key we assume it is required by default as we can't know for sure a generator is used by examining the table structures, the way we can in mysql.(If autoincrement we can allow null values) Or do you know a way we can see a generator is availabe in a generic way?

Mark

[andy]

TField.Clear; // sets the Field to null

[Rogier21]

Have you tried to set MapsTable.FieldByName('ID').Required:=false? As it's a primary key we assume it is required by default as we can't know for sure a generator is used by examining the table structures, the way we can in mysql.(If autoincrement we can allow null values) Or do you know a way we can see a generator is availabe in a generic way?

Mark

Tried it now, but doesnt work really right..

If i do that, it puts the value actually to null, so nothing will be filled, untill I close the DB and open it again?

I tried doing applyupdates, commit etc, but nothign wanted to give me the result.

TField.Clear; // sets the Field to null

Gives 'cannot focus on disabled window', i dont know which window he wants to access, but it doesnt give me any result

[mdaems]

That's a problem, indeed. The data should be refreshed (close/open or Refresh) to see the results. (Works excellent without refresh in Mysql as we have a function there to retrieve the autoincremented value)
I don't think it's necessary to open and close or commit on the connection itself as long as updating and refreshing is done with the same connection component. Not 100% sure about this as I don't know Firebird trigger and commit behaviour, but that's what's true for the databases I know.
In SVN Testing branch there's a new feature in development which allows you to refresh one row. You could try that as well.

Mark

[Rogier21]

Ok if I refresh the table its all fine and working.

Im didnt really look in the source of the Zeos lib, but Im confused how the Table handles the request for the insert then.

Does it give a normal query out to firebird? If so it should give a result for the autoinc number, as a normal query with a TQuery does this aswell.

Well it works for now, i will look into the refresh row later, thanks anyway for the help!

[btrewern]

BTW if you want to not have to refresh the row you can use

Code: Select all

SELECT GEN_ID(MAPS_ID_GEN, 1) FROM RDB$DATABASE;

to find the next ID and then enter it manually. I do this in PostgreSQL to get round similar issues.

Ben

[mdaems]

Another solution :
Use the TZSequence component. Set it's Connection and SequenceName properties. Set Sequence and SequenceField properties of your query.
This does essentially the same as Bens solution. Only you don't have to write the full query yourself.

Mark

FYI : Zeoslib does create a normal insert query. Without an automatic refresh it's impossible to get the autoincremented value back. Doing this for all inserted rows would be a serious performance issue. If we only knew an autoincrement (or any other updating trigger!!) was involved we could do it probably. Using OnInsert together with RefreshRow would do the same, I think.

[btrewern]

mdaems,

Does the TZSequence component work for all databases? I haven't tried it yet but I'll have a go now you mention it.

Thanks,

Ben

[Rogier21]

Im trying to do it with the sequencer now.

The refreshrow from the latest test release does not really work as it gives me a message its only to be used with updates

It is not such a big deal as its the embedde firebird for only 1 user. The problem is not really getting the autoinc key, as I found out, after a refresh the cursor jumps to the first record not to the last inserted record. I can do a .Last to go to the last one, but if its going to need multiple users, it can be tricky..

[mdaems]

As far as I understand from source it says it can only be used with the TZUpdateSQL component. I think it may work when you use it with a TZUpdateSQL component linked to your query.
As it's new and therefore even more undocumented I can't tell how you should exactly use it. My guess : In AfterInsertSQL of TZUpdateSql you could call RefreshCurrentRow of ZQuery component.

The sequencer should work fine, however.

Mark

[Bottleneck]

Hello Rogier21

I use Firebird with Triggers like you since years and never had problems.

I created my tables with triggers like the following

CREATE TRIGGER Trg_Sec_User_GenID FOR Users
ACTIVE BEFORE INSERT AS
begin
/* this creats a unique userid at every insert */
if ( (NEW.US_LFDNr is null) OR (NEW.US_LFDNr < 1) ) then
NEW.US_LFDNr = GEN_ID(Sec_User, 1);

My tables look like:

CREATE TABLE Users (
US_LFDNr INTEGER DEFAULT 0 NOT NULL UNIQUE, /* eindeutige Datensatznummer - wird bei automatisch vom SQL-Server vergeben */
US_Aktiv CHAR(1) DEFAULT 'T' NOT NULL,
.........

I use normal ZQuery to add entrys and build my insert-strings manually.

Insert into Users (US_Aktiv, US_.....) VALUES ('F', ....)

And this works fine without problems for years.

The only difference between your and my way to do this is that you fill the unique field with "null". I do not fill this field at all. The field will be filled from the generator.

Try this:

INSERT INTO maps (description) VALUES('test');

So your field "ID" ist automatically "null". And please - don't use insert or updates with ZTables.


Greetings
Peter

PS: ZeosLib ist the best database componet

[Rogier21]

Hello Bottleneck,

I know what you mean, and i wasnt talking about the Query component but the table component. Try to use that one and you will see its not that easy anymore...

[Bottleneck]

Hello Rogier21

Use


MapsTable.Append;
MapsTable.FieldByName('ID').AsInteger:= 0 or -1 ;
MapsTable.FieldByName('Description').AsString:='test';
MapsTable.Post;

or as SQL

INSERT INTO maps (id,description) VALUES(0, 'test');
or

INSERT INTO maps (description) VALUES('test');

together with the trigger:


CREATE TRIGGER BI_MAPS_ID FOR MAPS
ACTIVE BEFORE INSERT POSITION 0 AS
begin
/* this creats a unique userid at every insert */
if ( (NEW.ID is null) OR (NEW.ID < 1) ) then
NEW.ID = GEN_ID(MAPS_ID_GEN, 1);
END

so you dont have to fill ID with "null".
The trigger automatically creates a unique ID - if ID is less one or "null" or not supplied (with my trigger).
"null" can cause the problem with tables.
I tried this with tables on Firebird and it worked.


Greetings
Peter

[Antz]

Hi Rogier21,

I have had a simpler problem and found the following to work each time:

Your trigger as you have created it is not complete, complete in the sense that the ID is an integer and you should test for two conditions in the trigger: NULL and ZERO, which are not the same. So alter you conditional statement to read:
[font=Courier New]
IF(NEW.ID IS NULL) OR (NEW.ID=0) THEN
NEW.ID=GEN_ID(MAPS_ID_GEN,1);
[/font]
Secondly, try not to have the insert in your application use a stored procedure instead of the TZUpdate component. Call the stored procedure from the application using TZStoredProcedure and the parameters and execute the insert within the database, this will ensure that the database takes care of the logic and your application needs not to worry about that, this will in the long run save you maintenance.