Recent

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

Zvoni

  • Hero Member
  • *****
  • Posts: 1663
Re: SQL - IBX - "returning" - primary key
« Reply #15 on: December 08, 2022, 10:27:28 am »
hmmm...."Execute Block"?
https://stackoverflow.com/questions/7574730/firebird-tibquery-insert-with-returning-into
Note the "SUSPEND" inside the Block
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

rvk

  • Hero Member
  • *****
  • Posts: 5128
Re: SQL - IBX - "returning" - primary key
« Reply #16 on: December 08, 2022, 11:26:34 am »
What 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:
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.
and
Can 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

  • Hero Member
  • *****
  • Posts: 1663
Re: SQL - IBX - "returning" - primary key
« Reply #17 on: December 08, 2022, 12:52:46 pm »
What 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
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

rvk

  • Hero Member
  • *****
  • Posts: 5128
Re: SQL - IBX - "returning" - primary key
« Reply #18 on: December 08, 2022, 01:17:05 pm »
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
There 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][+][-]
  1.   IBDatabase1.Connected := true;
  2.   IBTransaction1.Active := true;
  3.   IBSQL1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'', 1, ''TEST'', ''Test'')';
  4.   IBSQL1.ExecQuery;
  5.   IBSQL1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'', 1, ''TEST'', ''Test'') RETURNING LINKNUMMER';
  6.   IBSQL1.ExecQuery;
  7.   Showmessage(IBSQL1.FieldByName('LINKNUMMER').asString); // WORKS FINE
  8.  
  9.   IBQuery1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'', 1, ''TEST'', ''Test'') RETURNING LINKNUMMER';
  10.   // IBQuery1.Open;
  11.   IBQuery1.ExecSQL;
  12.   Showmessage(IBQuery1.FieldByName('LINKNUMMER').asString); // GIVES ERROR on BOTH Open and ExecSQL
  13.  
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][+][-]
  1. CREATE TRIGGER TR_BRANCHE FOR BRANCHE ACTIVE BEFORE INSERT POSITION 0 AS
  2. BEGIN
  3.   IF ((NEW.LINKNUMMER IS NULL) OR (NEW.LINKNUMMER = 0)) THEN
  4.       NEW.LINKNUMMER = GEN_ID(GEN_BRANCHE, 1);
  5. 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#msg370791
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.
« Last Edit: December 08, 2022, 01:20:37 pm by rvk »

egsuh

  • Hero Member
  • *****
  • Posts: 1087
Re: SQL - IBX - "returning" - primary key
« Reply #19 on: December 08, 2022, 01:22:30 pm »
It’s strange that TIBSQL returns something while TIBQuery does not.

rvk

  • Hero Member
  • *****
  • Posts: 5128
Re: SQL - IBX - "returning" - primary key
« Reply #20 on: December 08, 2022, 01:28:44 pm »
It’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

  • Hero Member
  • *****
  • Posts: 734
Re: SQL - IBX - "returning" - primary key
« Reply #21 on: December 08, 2022, 03:42:13 pm »
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

  • Hero Member
  • *****
  • Posts: 1087
Re: [solved] SQL - IBX - "returning" - primary key
« Reply #22 on: December 09, 2022, 04:50:55 am »
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

  • Hero Member
  • *****
  • Posts: 5128
Re: [solved] SQL - IBX - "returning" - primary key
« Reply #23 on: December 09, 2022, 08:13:38 am »
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.
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.

- 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)??

« Last Edit: December 09, 2022, 08:24:00 am by rvk »

Nicole

  • Hero Member
  • *****
  • Posts: 734
Re: [solved] SQL - IBX - "returning" - primary key
« Reply #24 on: December 09, 2022, 03:15:23 pm »
Code: Text  [Select][+][-]
  1. 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

  • Hero Member
  • *****
  • Posts: 5128
Re: [solved] SQL - IBX - "returning" - primary key
« Reply #25 on: December 09, 2022, 06:07:30 pm »
Quote
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".
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  :D).
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

  • Hero Member
  • *****
  • Posts: 734
Re: [solved] SQL - IBX - "returning" - primary key
« Reply #26 on: December 09, 2022, 06:12:25 pm »
Thank you so very much!
I downloaded it and cross fingers, it will bring it to run.
I'll report.

egsuh

  • Hero Member
  • *****
  • Posts: 1087
Re: [solved] SQL - IBX - "returning" - primary key
« Reply #27 on: December 10, 2022, 09:13:45 am »
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.

 

TinyPortal © 2005-2018