[solved] SQL - IBX - "returning" - primary key
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2022-12-06 15:09

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbtrades (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM ,ERGEBNI
S,ENTRY_PRICE,ENTRY_DATE,EXIT_DATE,ENTRY_TIME,EXIT_TIME,STOP,WORST,TAR
GET,BEST,PL,MARGIN) values  (:zuKonto,:STATUS_,:POSITION_
,:FK_KONTRAKT,:NAME_,:COMM ,:ERGEBNIS,:ENTRY_PRICE,:ENTRY_DATE,:EXIT_D
ATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN) ret
urning id_trade;'
The error message reads
Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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.

----------------------------------------------------------------------
Zvoni · 2022-12-06 16:04

Everything i could find it should still work.
Does it work in FlameRobin?

----------------------------------------------------------------------
Nicole · 2022-12-06 16:49

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
 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.

----------------------------------------------------------------------
korba812 · 2022-12-06 17:31

Try getting field directly from the insert query (I don't remember if
it was from fields or from parameters):
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i idEintrag:=
IBQuery_TradeInDB.QInsert.FieldByName('id_trade').AsInteger;or
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i idEintrag:=
IBQuery_TradeInDB.QInsert.ParamByName('id_trade').AsInteger;

----------------------------------------------------------------------
Nicole · 2022-12-06 17:54

thanks, but it did not work neither.

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

----------------------------------------------------------------------
korba812 · 2022-12-06 18:07

I used this along with MDO (another fork of IBX). Try adding the
return parameter:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbtrades    (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM ,ERGE
BNIS,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_D
ATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN)   
 returning id_trade INTO :somevarname;'then try to read from the
parameters as I showed earlier:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i idEintrag:=
IBQuery_TradeInDB.QInsert.ParamByName('somevarname').AsInteger;

----------------------------------------------------------------------
rvk · 2022-12-06 18:15

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 · 2022-12-06 22:56

Quote from: rvk on December 06, 2022, 07:15:59 pmReturning 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 · 2022-12-06 23:15

Quote from: tonyw on December 06, 2022, 11:56:12 pmThe 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.

QuoteDescription: 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

----------------------------------------------------------------------
tonyw · 2022-12-06 23:30

Quote from: rvk on December 07, 2022, 12:15:50 amQuote from: tonyw on
December 06, 2022, 11:56:12 pmThe 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 · 2022-12-07 03:18

What did you do with ibquery? You should Open, not ExecSQL.

----------------------------------------------------------------------
Nicole · 2022-12-07 16:50

- 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').AsIn
teger;
 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.

----------------------------------------------------------------------
Zvoni · 2022-12-07 17:06

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

----------------------------------------------------------------------
egsuh · 2022-12-08 00:05

I have tested with following method.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TEMPKEY(  KEYVALUE INTEGER,  STATUS SMALLINT,  PK_TEMPKEY
INTEGER NOT NULL);
And assigned a trigger to implement autoincrement.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ALTER
TRIGGER TEMPKEY_BI ACTIVEBEFORE INSERT POSITION 0ASDECLARE next_id
INTEGER; BEGIN   IF ((NEW.pk_tempkey IS NULL) OR (NEW.PK_TEMPKEY = 0))
THEN BEGIN       SELECT MAX(pk_tempkey) FROM TEMPKEY INTO :next_id;  
   :next_id = :next_id + 1;      NEW.pk_tempkey = :next_id;   ENDEND
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmMain.Button2Click(Sender: TObject);begin   datasource1.DataSet :=
qr2;   qr2.Close;   qr2.SQL.text := 'insert into tempkey (keyvalue,
status) values (2, 3)'            + ' returning pk_tempkey;';  
 qr2.Transaction.Active:= True;   qr2.Open;   (qr2.Transaction as
TSQLTransaction).CommitRetaining;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.

----------------------------------------------------------------------
Zvoni · 2022-12-08 09:13

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE tbtrades (id_trade INT PRIMARY KEY, otherfields......./*Add
this!*/id_trade_copy INT GENERATED ALWAYS AS (id_trade),....) and then
change your INSERT-Statement to
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     INSERT
INTO tbtrades    (zukonto, STATUS_ ,POSITION_ ,FK_KONTRAKT,NAME_,COMM
,ERGEBNIS,ENTRY_PRICE,ENTRY_DATE,EXIT_DATE,ENTRY_TIME,EXIT_TIME,STOP,W
ORST,TARGET,BEST,PL,MARGIN) VALUES  (:zuKonto,:STATUS_,:POSITION_
,:FK_KONTRAKT,:NAME_,:COMM ,:ERGEBNIS,:ENTRY_PRICE,:ENTRY_DATE,:EXIT_D
ATE,:ENTRY_TIME,:EXIT_TIME,:STOP,:WORST,:TARGET,:BEST,:PL,:MARGIN)   
 returning id_trade_copy;'
And then try to grab "id_trade_copy"

----------------------------------------------------------------------
Zvoni · 2022-12-08 09:27

hmmm...."Execute Block"?
https://stackoverflow.com/questions/7574730/firebird-tibquery-insert-
with-returning-into
Note the "SUSPEND" inside the Block

----------------------------------------------------------------------
rvk · 2022-12-08 10:26

Quote from: Zvoni on December 07, 2022, 06:06:31 pmWhat about adding a
computed column, which just „copies“ trade_id?
Since it‘s not the PK it should work…
I think this discussion is going off the rail.
What makes you think the trade_id isn't the (already existing) primary
key.

@Nicole states:
Quote from: Nicole on December 06, 2022, 05:49:38 pmid_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.
and
Quote from: Nicole on December 06, 2022, 04:09:35 pmCan anybody give
me the hint, how to return the newly generated primary key with IBX?

My guess this trade_id is already a generated primary key by a
generator in the existing database.
Discussion on how to create a autoinc new field for returning seems to
be off-topic.

But the questions are not stated clearly. No code, No database design
(DDL).
So all we do is guess here (confusing @Nicole even more).

----------------------------------------------------------------------
Zvoni · 2022-12-08 11:52

Quote from: rvk on December 08, 2022, 11:26:34 amWhat makes you think
the trade_id isn't the (already existing) primary key.
OK, i misspoke.
I know, that id_trade is (probably) the Primary Key.
What i could find out, is that the returning clause doesn't work on
autogenerated Primary keys, since it doesn't exist at the time of
creation
(which IMO makes zero sense, since obviously any other field can be
returned, and those exist??)

btw: and i agree to the question @Nicole: HOW do you generate this
id_trade?
because i saw that in Firebird, there is an "old" style (using an
BEFORE INSERT-Trigger?) and the "new" style, depending which version
of the FB-engine you use

----------------------------------------------------------------------
rvk · 2022-12-08 12:17

Quote from: Zvoni on December 08, 2022, 12:52:46 pmWhat i could find
out, is that the returning clause doesn't work on autogenerated
Primary keys, since it doesn't exist at the time of creationThere is
no "autogenerated" in Firebird. That's all done with triggers and
GENERATORS (or SEQUENCE in later versions).
And then RETURNING works fine (on the Firebird side). It's up to the
IBX code to really do something with it.

I found this in my notes:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
IBDatabase1.Connected := true;  IBTransaction1.Active := true; 
IBSQL1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'', 1,
''TEST'', ''Test'')';  IBSQL1.ExecQuery;  IBSQL1.SQL.Text := 'INSERT
INTO BRANCHE VALUES (NULL, ''T'', 1, ''TEST'', ''Test'') RETURNING
LINKNUMMER';  IBSQL1.ExecQuery; 
Showmessage(IBSQL1.FieldByName('LINKNUMMER').asString); // WORKS
FINE   IBQuery1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'',
1, ''TEST'', ''Test'') RETURNING LINKNUMMER';  // IBQuery1.Open; 
IBQuery1.ExecSQL; 
Showmessage(IBQuery1.FieldByName('LINKNUMMER').asString); // GIVES
ERROR on BOTH Open and ExecSQL LINKNUMMER is a primary key generated
by a trigger on BRANCHE (BEFORE INSERT POSITION 0). (and it's the
first position in the fields, the NULL which is passed)

Trigger:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TRIGGER TR_BRANCHE FOR BRANCHE ACTIVE BEFORE INSERT POSITION 0
ASBEGIN  IF ((NEW.LINKNUMMER IS NULL) OR (NEW.LINKNUMMER = 0)) THEN  
  NEW.LINKNUMMER = GEN_ID(GEN_BRANCHE, 1);END^
So, for me, TIBSQL works fine with RETURNING. TIBQuery does not
(neither open or ExecSQL).
Maybe it can work if you set UpdateObject with a INSERT statement, but
I don't think it works if you set the INSERT in TIBQuery.SQL.
(Or maybe I'm missing something)

But why would you want to use a TIBQuery for a simple insert? Why not
just use TIBSQL?

Edit: I dove into my old notes for this code. The topic is here: https
://forum.lazarus.freepascal.org/index.php/topic,50690.msg370791.html#m
sg370791
Back then (2020) TIBQuery didn't support RETURNING. Maybe it's
different now but I'm not sure how or what code you need to use then.

----------------------------------------------------------------------
egsuh · 2022-12-08 12:22

It’s strange that TIBSQL returns something while TIBQuery does not.

----------------------------------------------------------------------
rvk · 2022-12-08 12:28

Quote from: egsuh on December 08, 2022, 01:22:30 pmIt’s strange that
TIBSQL returns something while TIBQuery does not.
I don't think TIBQuery is build for that (but maybe Tony can clear
that up).

In that case only TIBUpdateSQL and TIBSQL supports it.
So you could connect a TIBUpdateSQL as UpdateObject to a TIBQuery.
But in that case you set the SELECT in the TIBQuery.SQL and INSERT
RETURNING in the TIBQuery.UpdateObject.InsertSQL.

----------------------------------------------------------------------
Nicole · 2022-12-08 14:42

thank you all!

It works.
I did this (brute force and much luck)

-  added a component TIBSQL and called it IBSQL_TradeInDB
-  renamed my  IBQuery_TradeInDB into IBSQL_TradeInDB by the IDE-
editor
-  clicked away the compiler message "double var name" and deleted the
first thing, which was the declaration as TIBQuery
-  corrected some syntax differences from TIBQuery to TIBSQL

► the primary key is returned again

----------------------------------------------------------------------
egsuh · 2022-12-09 03:50

I could replicate rvk's result (from just curiosity).

With TIBQuery, insertions are done correctly but it seems not to
return returning field.

TIBSql returns returning fields, but it cannot be linked to
datasource.

----------------------------------------------------------------------
rvk · 2022-12-09 07:13

Quote from: egsuh on December 09, 2022, 04:50:55 amWith TIBQuery,
insertions are done correctly but it seems not to return returning
field.
TIBSql returns returning fields, but it cannot be linked to
datasource.
Yes indeed. But think about it... when will you be using INSERT in
TIBQuery.SQL and linking that to a datasource?? Never, because then
you don't have a SELECT anywhere and the linked datacomponents wont
work anyways (they need SELECT too to get the data, not just INSERT).

You can however use RETURNING in TIBQuery.UpdateObject.InsertSQL and
have the TIBQuery linked to a datasource. But with the INSERT directly
in TIBQuery.SQL it just wouldn't make sense. So from a design
perspective this makes sense.

Quote from: Nicole on December 07, 2022, 05:50:23 pm- 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.
BTW, @Nicole, we haven't seen any code of how your fill your data
fields and how your program looks, but if you are not familiar with
TIBQuery.Updatebject you might want to look into that. It connects
TDBEdit, TDBGrids, TDBMemo etc (data-ware edits) directly with the IBX
so you don't have to use params and insert manually. You can write a
complete edit-form without any code. (This as been discussed before).
Are you familiar with the data-aware edits (TDBxxx)??

----------------------------------------------------------------------
Nicole · 2022-12-09 14:15

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i You can
write a complete edit-form without any code. (This as been discussed
before). Are you familiar with the data-aware edits (TDBxxx)??
WHAT?!
This sounds extremely thrilling.
I have not idea of the thinks you asked me if I "am familiar with".

Not for the current task (this is old code and the field are sent by
the software), but I need this for a frame I hardly started with.

Do you have a dummy-link for me? dummy not only easy, but even short.
I downloaded the "IBX for Lazarus User Guide" and - oups - this manual
is not short nor easy. I printed 6.2., 6.3. and 6.5. and tried to
understand.
I am sure, the information is inside, but it took me that long to
find, that I gave up.

I even failed by on-using my Delphi DBTables in Lazarus. Somehow the
edit and Navigator do not react in many cases. I used FlameRobin in
the meanwhile.

What I want to do are those (2 different frames):
1)
- send a software-generated-query to a table
- have the results there and edit it directly into the db

2)
- have a form, where I can edit every field of a certain row by edit
fields

----------------------------------------------------------------------
rvk · 2022-12-09 17:07

Quote from: Nicole on December 09, 2022, 03:15:23 pmQuoteYou can write
a complete edit-form without any code. (This as been discussed
before). Are you familiar with the data-aware edits (TDBxxx)??WHAT?!
This sounds extremely thrilling.
I have not idea of the thinks you asked me if I "am familiar with".The
question is "do you know about the TDBxxx components"?
Those are data-aware and automatically connected to a datasource, the
same way your TDBGrid gets it data from a TIBQuery.
That way you don't have to keep doing Edit1.Text :=
IBQuery1.FieldByName('FIELD').asString.
TDBEdit gets the text automatically and when you change the text, it
also gets put in the database via the UPDATE SQL's.

I have made a small example. You can put it in a directory, compile
and just run it.
It automatically creates a database (without the need for FlameRobin)
when there is none (that's most of the code  ).
It show a TDBGrid with records. You can edit the records with the
DBNavigator (pencil icon) and directly change the Edit's below the
form.

The CreateDatabase is actually standalone code, it uses it's own
TIBDatabase and TIBXScript etc to create the database.
No connection to anything. So you can just use/copy it in any program
where you need a database created.
(also examine how I create TIBXScript etc in code without the need to
drop one on a form)

It also has another option of a separate form. When you select a
button and click the big button on the right, you get a separate form
for editing.
Because that form has it's own TIBQuery you can even open multiple
form-editors at the same time (just try it).

Most of the code in this example is to create the database and to sync
the editform when changes are made.
I also do a lot of setup in code and you can't connect in the IDE
(because the database might not exists yet).
(I usually do everything in code to have more control over everything.
The fact I can't design in the IDE isn't a problem for me.)

Absolutely no Edit1.Text := IBQuery1.FieldByName('FIELD').AsString is
done anywhere.
(that's what I wanted you to see when working with TDBEdit, TDBMemo
etc, the dataware edit components)

Just look at the example and try to discover and imagine the upside of
using the TDBEdit etc components more.

----------------------------------------------------------------------
Nicole · 2022-12-09 17:12

Thank you so very much!
I downloaded it and cross fingers, it will bring it to run.
I'll report.

----------------------------------------------------------------------
egsuh · 2022-12-10 08:13

Quote- send a software-generated-query to a table
- have the results there and edit it directly into the db
Try TIBDataSet for that purpose.