How to use a generator in my execute statement in a stored procedure?

Question

I'm trying to create a simple stored procedure in IBExpert for a Firebird 2.5 database. I'm having trouble to work with generators though. I think I've created a valid generator & trigger. But I don't know how to apply those in my execute statement.

Table:

ID            BIGINT NOT NULL,
DATUM         INTEGER,
KOSTENST      INTEGER,
KUNDENNUMMER  INTEGER,
DISPONENT     CHAR(5),
KONTAKTART    CHAR(2)

Trigger:

CREATE OR ALTER trigger kontakte_erw_id_bi for kontakte_erw_id
active before insert position 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(ID_KONTAKTE, 1);
END

Generator:

CREATE SEQUENCE ID_KONTAKTE;
ALTER SEQUENCE ID_KONTAKTE RESTART WITH 0;

Procedure statement (I think the problem lays here? How to I address the trigger to fill the ID column?):

for execute statement('
  SELECT
        KONTAKTE.DATUM,
        KUNDEN.KOSTENST,
        KUNDEN.KUNDENNR,
        KONTAKTE.DISPONENT,
        KONTAKTE.KONTAKTART

FROM KONTAKTE
INNER JOIN KUNDEN ON KONTAKTE.KUNDENNR = KUNDEN.KUNDENNR

')
          on external 'db'
          as user 'xxx' password xxx

          into :XDATUM, :XKOSTENST, :XKUNDENNUMMER, :XDISPONENT, :XKONTAKTART

  do
  begin

    execute statement

    ('update or insert into KONTAKTE_ERW_ID (DATUM, KOSTENST, KUNDENNUMMER, DISPONENT, KONTAKTART)

      values

    (:DATUM, :KOSTENST, :KUNDENNUMMER, :DISPONENT, :KONTAKTART)')


   (DATUM:= XDATUM, KOSTENST := XKOSTENST, KUNDENNUMMER := XKUNDENNUMMER, DISPONENT := XDISPONENT, KONTAKTART := XKONTAKTART)

        on external 'db'
        as user 'xxx' password xxx;

  end

This is the error I get when trying to populate the chart:

336003099 : UPDATE OR INSERT field list does not match primary key of table KONTAKTE_ERW_ID
Statement : update or insert into KONTAKTE_ERW_ID (DATUM, KOSTENST, KUNDENNUMMER, DISPONENT, KONTAKTART)

      values

    (?, ?, ?, ?, ?)

It is unclear what you are asking. What do you mean with _"How to I adress the Trigger to fill the ID column?"_? — Mark Rotteveel, Jul 19 '19 at 06:54
In any case, assuming you created the trigger correctly (and it looks it is), you don't need to do anything, it will be called automatically. However, I am wondering why you are using `on external` calls here? If this is the same database, then you should use normal statements. — Mark Rotteveel, Jul 19 '19 at 07:03
In what way do I have to change my execute statement to achieve that the ID Column of my Table gets populated with values from my generator. Edit: you were faster than me here — sHooP, Jul 19 '19 at 07:04
@MarkRotteveel The procedure is looped over several Firebird Databases. Without knowing too much about it I assume that is the reason for the "on external" call. This is the error I get when i run the "Master" Procedure, which is why I think there is a problem with the statement: 336003099 : UPDATE OR INSERT field list does not match primary key of table KONTAKTE_ERW_ID Statement : update or insert into KONTAKTE_ERW_ID (DATUM, KOSTENST, KUNDENNUMMER, DISPONENT, KONTAKTART) values (?, ?, ?, ?, ?) — sHooP, Jul 19 '19 at 07:07
That error is very important information. Please [edit] your question to add that error. Your problem is that `update or insert` requires something to uniquely identify the row to update or otherwise insert. — Mark Rotteveel, Jul 19 '19 at 07:12
Yes I thought that this could be the problem, which is why I try to generate a unique identifier for every row. — sHooP, Jul 19 '19 at 07:15
That is a bit of a 'chicken and egg' problem. You need something unique before you can use `update or insert`. Is there a combination of values that also uniquely identifies the row? In that case you could use the `MATCHING` clause. — Mark Rotteveel, Jul 19 '19 at 07:17
I'm afraid that won't work for me. Is there some way to generate a unique value within the statement? Thats what I was hoping to do with the Trigger. Thanks so far for your valuable advice btw.! — sHooP, Jul 19 '19 at 07:20
And how do you envision _'generating a unique value within the statement'_ to work for your problem? If that is what you want, you should forget about using `update or insert`, but just use `insert` instead as the end result will be the same. However, I assume you are trying to sync data across databases, so that would not be a real solution. You need something in the business domain that uniquely identifies records. — Mark Rotteveel, Jul 19 '19 at 07:27

Mark Rotteveel · Accepted Answer · 2021-01-01T10:20:37.683

1

Your problem is that to be able to use UPDATE OR INSERT, Firebird needs to know when a row already exists. For this it either uses the primary key, or - when provided - the MATCHING clause.

In your statement you aren't providing the primary key, but also no MATCHING clause, so Firebird cannot decided if there is a row to update or if it should insert.

In your situation, including the primary key in the statement does not seem to be an option (or you need to apply a scheme for generating identifiers that can be unique across different databases), so your only option is to use the MATCHING clause. However, looking at your columns, I don't immediately see something that could be considered unique.

edited Jan 01 '21 at 10:20

answered Jul 19 '19 at 07:24

Mark Rotteveel

100,966
191
140
197

Could this be solved by changing it to "insert" only? I assume no Primarykey or Matching clause will be necessary then? – sHooP Jul 19 '19 at 07:29
You helped me understand the problem a ton! Thanks so much, I think I will be able to find some way around it now. – sHooP Jul 19 '19 at 07:32
@sHooP Yes, changing to insert will solve that problem. But if you are trying to sync records across database, that just means that you will insert new records on each sync, and never update existing records. – Mark Rotteveel Jul 19 '19 at 07:36

How to use a generator in my execute statement in a stored procedure?

1 Answers1