Recent

Author Topic: [solved] SQL - IBX - "returning" - primary key  (Read 2263 times)

Nicole

  • Hero Member
  • *****
  • Posts: 970
[solved] SQL - IBX - "returning" - primary key
« on: December 06, 2022, 04:09:35 pm »
So I am one step further (who read my last thread: The problem was due to a change from FireDac to IBX in the source row order)

The query below looks complex, but the question is not.
The query shall return the NEW primary key which is generated by a generator.
This has worked before in Firedac, so it shall be an easy change for those who know the trick in IBX.

so here is the query of which only the last line shall be important
Code: MySQL  [Select][+][-]
  1. INSERT INTO tbtrades
  2. (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM ,ERGEBNIS,ENTRY_PRICE,ENTRY_DATE,EXIT_DATE,ENTRY_TIME,EXIT_TIME,STOP,WORST,TARGET,BEST,PL,MARGIN) values  (:zuKonto,:STATUS_,:POSITION_ ,:FK_KONTRAKT,:NAME_,:COMM ,:ERGEBNIS,:ENTRY_PRICE,:ENTRY_DATE,:EXIT_DATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN)
  3.  returning id_trade;'

The error message reads
Code: Text  [Select][+][-]
  1. Field not found : "id_trade"

The fields shall be returned and does not exist when I submit the query.
How to solve? I remember, that I tried for a long time to do it by Delphi / Firedac.
This putting above had worked there.

Can anybody give me the hint, how to return the newly generated primary key with IBX?
Thanks.
« Last Edit: December 08, 2022, 03:43:05 pm by Nicole »

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQL - IBX - "returning" - primary key
« Reply #1 on: December 06, 2022, 05:04:13 pm »
Everything i could find it should still work.
Does it work in FlameRobin?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: SQL - IBX - "returning" - primary key
« Reply #2 on: December 06, 2022, 05:49:38 pm »
Code: Pascal  [Select][+][-]
  1.  idEintrag:=IBQuery_TradeInDB.FieldByName('id_trade').AsInteger;

I took it with the line above and am not sure, how to test it.

id_trade shall be GENERATED by the db.
So it is not part of the query.
The only thing that I remember, that it was really hard to put this code. I tried a lot with this id_trade years ago.

Would be great, if anybody KNOWS it, how to put it.
« Last Edit: December 06, 2022, 05:52:07 pm by Nicole »

korba812

  • Sr. Member
  • ****
  • Posts: 392
Re: SQL - IBX - "returning" - primary key
« Reply #3 on: December 06, 2022, 06:31:30 pm »
Try getting field directly from the insert query (I don't remember if it was from fields or from parameters):
Code: Pascal  [Select][+][-]
  1. idEintrag:=IBQuery_TradeInDB.QInsert.FieldByName('id_trade').AsInteger;
or
Code: Pascal  [Select][+][-]
  1. idEintrag:=IBQuery_TradeInDB.QInsert.ParamByName('id_trade').AsInteger;

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: SQL - IBX - "returning" - primary key
« Reply #4 on: December 06, 2022, 06:54:53 pm »
thanks, but it did not work neither.

It seems a question about IBX
catching this returning in "returning id_trade"

korba812

  • Sr. Member
  • ****
  • Posts: 392
Re: SQL - IBX - "returning" - primary key
« Reply #5 on: December 06, 2022, 07:07:52 pm »
I used this along with MDO (another fork of IBX). Try adding the return parameter:
Code: SQL  [Select][+][-]
  1. INSERT INTO tbtrades
  2.     (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM ,ERGEBNIS,ENTRY_PRICE,ENTRY_DATE,EXIT_DATE,ENTRY_TIME,EXIT_TIME,STOP,WORST,TARGET,BEST,PL,MARGIN) VALUES  (:zuKonto,:STATUS_,:POSITION_ ,:FK_KONTRAKT,:NAME_,:COMM ,:ERGEBNIS,:ENTRY_PRICE,:ENTRY_DATE,:EXIT_DATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN)
  3.      returning id_trade INTO :somevarname;'
then try to read from the parameters as I showed earlier:
Code: Pascal  [Select][+][-]
  1. idEintrag:=IBQuery_TradeInDB.QInsert.ParamByName('somevarname').AsInteger;

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQL - IBX - "returning" - primary key
« Reply #6 on: December 06, 2022, 07:15:59 pm »
Returning should work fine but we need to see more code.
Besides the actual INSERT sql (which contains the returning) we also need to see what component is used and if open is used or execsql.

Everything else is just guesswork.
At least TIBSQL supports returning (not sure about TIBQuery at the moment).

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: SQL - IBX - "returning" - primary key
« Reply #7 on: December 06, 2022, 11:56:12 pm »
Returning should work fine but we need to see more code.
Besides the actual INSERT sql (which contains the returning) we also need to see what component is used and if open is used or execsql.

Everything else is just guesswork.
At least TIBSQL supports returning (not sure about TIBQuery at the moment).
The returning clause is supported by TIBQuery, etc.

You do not use the INTO clause with IBX

The error message implies that you have no field defined called "id_trade". Checlk your select SQL to ensure that id_trade is included in the column list.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQL - IBX - "returning" - primary key
« Reply #8 on: December 07, 2022, 12:15:50 am »
The error message implies that you have no field defined called "id_trade". Checlk your select SQL to ensure that id_trade is included in the column list.
For Firebird at least, the returning columns do not need to be in the inserted columns.

Maybe the TIBQuery does need it in the SELECT statement? Why?
I can only imagine it needs it when TIBQuery.Open is used (because of the open table fields).

But if TIBQuery.ExecSql is used with INSERT in SQL then it shouldn't be needed.
That's why I wanted to see more code. It's important how the components are used.

Quote
Description: An INSERT statement adding at most one row may optionally include a RETURNING clause in order to return values from the inserted row. The clause, if present, need not contain all of the insert columns and may also contain other columns or expressions. The returned values reflect any changes that may have been made in BEFORE tiggers, but not those in AFTER triggers.
https://firebirdsql.org/refdocs/langrefupd21-insert.html
« Last Edit: December 07, 2022, 12:19:04 am by rvk »

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: SQL - IBX - "returning" - primary key
« Reply #9 on: December 07, 2022, 12:30:11 am »
The error message implies that you have no field defined called "id_trade". Checlk your select SQL to ensure that id_trade is included in the column list.
For Firebird at least, the returning columns do not need to be in the inserted columns.

Maybe the TIBQuery does need it in the SELECT statement? Why?
I can only imagine it needs it when TIBQuery.Open is used (because of the open table fields).
Because when an insert/update/delete query is executed (using an internal TIBSQL component), if the exec returns one or more columns then TIBCustomDataset.UpdateRecordFromQuery is called. This runs through the singleton row returned by the TIBSQL and if the a returned column's aliasname matches a fieldname then the returned value is used to update the field value. i.e. the column name used in returning clause must match a fieldname if the value is to be returned and saved in the current row.

Checking the code, if the alias name does not match a fieldname then it is silently ignored. Hence forget my original suggestion as to the problem being in the select sql. The error message probably comes from Firebird complaining that id_trade does not exist in the table tbtrades.

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: SQL - IBX - "returning" - primary key
« Reply #10 on: December 07, 2022, 04:18:55 am »
What did you do with ibquery? You should Open, not ExecSQL.

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: SQL - IBX - "returning" - primary key
« Reply #11 on: December 07, 2022, 05:50:23 pm »
- Mostly I choose var names by adding the purpose to the type
IBQuery_TradeInDB
means, that I use IBQuery.

- I neither opened nor executed, but set
     IBQuery_TradeInDB.Active:=true;

- Tony is right: Firebird does not accept a query which contains the primary-key, which does not exist at the start of the query.
So I cannot put it into the insert statement

- it is right, that IBX does not accept a ParamByName or FieldByName of anyhting not in the query.

 :'(

This leads to a circle.

- idEintrag:=IBQuery_TradeInDB.QInsert.ParamByName('somevarname').AsInteger;
 unfortunately is not accepted because of the QInsert

- What about this "UpdateQuery"? I do not know, how to use it.
May it solve my problem?
And if yes, please explain it VERBOSE to me, how to. I am not too experienced with databases and IBX.

« Last Edit: December 07, 2022, 06:45:24 pm by Nicole »

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQL - IBX - "returning" - primary key
« Reply #12 on: December 07, 2022, 06:06:31 pm »
What about adding a computed column, which just „copies“ id_trade?
Since it‘s not the PK it should work…

Edit: another approach might be to use a stored procedure, since you can SELECT the returnvalue.
http://www.destructor.de/firebird/storedproc.htm
« Last Edit: December 08, 2022, 12:49:29 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

egsuh

  • Hero Member
  • *****
  • Posts: 1273
Re: SQL - IBX - "returning" - primary key
« Reply #13 on: December 08, 2022, 01:05:11 am »
I have tested with following method.

Code: SQL  [Select][+][-]
  1. CREATE TABLE TEMPKEY
  2. (
  3.   KEYVALUE INTEGER,
  4.   STATUS SMALLINT,
  5.   PK_TEMPKEY INTEGER NOT NULL
  6. );

And assigned a trigger to implement autoincrement.

Code: SQL  [Select][+][-]
  1. ALTER TRIGGER TEMPKEY_BI ACTIVE
  2. BEFORE INSERT POSITION 0
  3. AS
  4. DECLARE next_id INTEGER;
  5. BEGIN
  6.    IF ((NEW.pk_tempkey IS NULL) OR (NEW.PK_TEMPKEY = 0)) THEN BEGIN
  7.       SELECT MAX(pk_tempkey) FROM TEMPKEY INTO :next_id;
  8.       :next_id = :next_id + 1;
  9.       NEW.pk_tempkey = :next_id;
  10.    END
  11. END

I searched for max value first, but this would be done using generators generally. And then following methos works fine. DBGrid shows plus one values every time I click button2.

Code: Pascal  [Select][+][-]
  1. procedure TfrmMain.Button2Click(Sender: TObject);
  2. begin
  3.    datasource1.DataSet := qr2;
  4.    qr2.Close;
  5.    qr2.SQL.text := 'insert into tempkey (keyvalue, status) values (2, 3)'
  6.             + ' returning pk_tempkey;';
  7.  
  8.    qr2.Transaction.Active:= True;
  9.    qr2.Open;
  10.    (qr2.Transaction as TSQLTransaction).CommitRetaining;
  11. end;

However, there is a system field named DB_KEY, when I browse the table data within FlameRobin. You will see it in the attached image.

Replacing qr2.SQL with following statement runs fine, but does not display the content. Only an emply cell is displayed in the DBGrid.

                   insert into tempkey (keyvalue, status) values (2, 3) returning RDB$DB_KEY;

I used TSQLQuery instead of TIBQuery, but I think it should not make any difference.
« Last Edit: December 08, 2022, 01:16:36 am by egsuh »

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQL - IBX - "returning" - primary key
« Reply #14 on: December 08, 2022, 10:13:51 am »
to expand on my idea with the computed column
https://firebirdsql.org/refdocs/langrefupd25-ddl-table.html#langrefupd25-ct-gen-always-as
You probably have a CREATE TABLE-Statement somewhere (i hope!)
Expand your table with a computed column
Code: SQL  [Select][+][-]
  1. CREATE TABLE tbtrades (
  2. id_trade INT PRIMARY KEY,
  3. otherfields.......
  4. /*Add this!*/
  5. id_trade_copy INT GENERATED ALWAYS AS (id_trade),
  6. ....
  7. )
  8.  
and then change your INSERT-Statement to
Code: SQL  [Select][+][-]
  1.     INSERT INTO tbtrades
  2.     (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM ,ERGEBNIS,ENTRY_PRICE,ENTRY_DATE,EXIT_DATE,ENTRY_TIME,EXIT_TIME,STOP,WORST,TARGET,BEST,PL,MARGIN) VALUES  (:zuKonto,:STATUS_,:POSITION_ ,:FK_KONTRAKT,:NAME_,:COMM ,:ERGEBNIS,:ENTRY_PRICE,:ENTRY_DATE,:EXIT_DATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN)
  3.      returning id_trade_copy;'

And then try to grab "id_trade_copy"
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018