[solved] IBX - returning generated primary key lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2024-02-02 09:16 Why doesn't this work?! It should?! What I want to do: I insert a new row into a table (works fine), let it generate a primary key and this very key - this is the problem - I want to know. 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    s := 'Insert into TBKONTRAKTE(NAME_,COMM,JAHR_AUS_NAME,MONATSKENNZAHL)' + //  ID_KONTRAKT,        ' values (:NAME_,:COMM,:JAHR_AUS_NAME,:MONATSKENNZAHL)'; // :ID_KONTRAKT   IBQuery_Kontrakte_B_24.SQL.Text := s;   IBQuery_Kontrakte_B_24.ParamByName('NAME_').AsString := Trim(name_);   IBQuery_Kontrakte_B_24.ParamByName('COMM').AsString := Trim(comm);   IBQuery_Kontrakte_B_24.ParamByName('JAHR_AUS_NAME').asSmallInt := Jahr_aus_Namen;   s1 := name_;   s1 := RightStr(s1, 2); // ich brauche die VORLETZTE Stelle   s1 := LeftStr(s1, 1);   i := Zeit.MONAT_BUCHSTABE_IN_ZAHL(s1);   IBQuery_Kontrakte_B_24.ParamByName('MONATSKENNZAHL').asSmallInt := i;   IBQuery_Kontrakte_B_24.ExecSQL;   i:= IBQuery_Kontrakte_B_24.FieldByName('ID_KONTRAKT').AsInteger; // ===> error message   result:= i;             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 [Break]Projekt project1 hat Exception-Klasse »EDatabaseError« ausgelöst mit der Meldung:IBQuery_Kontrakte_B_24 : Field not found : "ID_KONTRAKT"  Bei Adresse 10027A973  [Diesen Ausnahmetyp übergehen] [Continue] Firebird 3 IBX most recent (2.5?) Lazarus 3 ---------------------------------------------------------------------- tonyw · 2024-02-02 09:30 You need to add a "returning" clause to the insert statement. E.g.        s := 'Insert into TBKONTRAKTE(NAME_,COMM,JAHR_AUS_NAME,MONATSKENNZAHL)' +             ' values (:NAME_,:COMM,:JAHR_AUS_NAME,:MONATSKENNZAHL)' + ' returning ID_KONTRAKT' ---------------------------------------------------------------------- tonyw · 2024-02-02 09:36 Also, you will probably find that TIBSQL is a better choice than TIBQuery for direct execution of an insert statement. ---------------------------------------------------------------------- Nicole · 2024-02-02 09:55 Thank you for the replies. returning does not work. Is there something else I have to do? I  have not changed to IBSQ from  IBQuery. Probably this makes the difference? I have so many questions abou those twot, here are some: - What is the difference of those both? - They seem so similar to me. - Is there a rule of the thumb when to use which? And most of all: How to change the type in an existing code? There was a way I used in former times, but I cannot remember for one thing. And for the other thing, I am not sure, if this is a good idea for such complex Vars. ---------------------------------------------------------------------- rvk · 2024-02-02 10:07 You either need to use TIBSQL when using ExecSQL i.c.w. RETURNING. Or you need to use TIBQuery.Open. Because TIBQuery.ExecSQL (as far as I know) doesn't return a dataset (it's just for one way execution). TIBQuery.Open will return a dataset containing your RETURNING field. BTW. If you are using returning everywhere, you can eventually get rid of your triggers to generate a PK and you could use the new "generated by default as identity" clause. But that's up to you (the triggers etc will still work). ---------------------------------------------------------------------- Nicole · 2024-02-02 10:31 No, it does not work with open. Neither with active = true. I read "file not found". ---------------------------------------------------------------------- rvk · 2024-02-02 10:35 Quote from: Nicole on February 02, 2024, 11:31:19 amNo, it does not work with open. Neither with active = true. I read "file not found". "file not found" ???? I think you are using the wrong component or code. Show a more complete example of your code. IBX can't generate an error like "file not found". ---------------------------------------------------------------------- Nicole · 2024-02-02 10:50 FIELD, sorry typo. My code is in my first posting. ---------------------------------------------------------------------- rvk · 2024-02-02 10:59 Quote from: Nicole on February 02, 2024, 11:50:17 amFIELD, sorry typo. My code is in my first posting. Well, in the code in your first post you don't have RETURNING. So that's not going to work without it. And if you changed your code by then... it's not the same as in your first post anymore  I have this in my archive so that should work: 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 TForm1.Button1Click(Sender: TObject);begin   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.RecordCount.ToString);  Showmessage(IBSQL1.FieldByName('LINKNUMMER').asString);   IBQuery1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'', 1, ''TEST'', ''Test'') RETURNING LINKNUMMER';  IBQuery1.ForcedRefresh := true;  IBQuery1.Open;  // IBQuery1.ExecSQL; // execsql doesn't work  Showmessage(IBQuery1.FieldByName('LINKNUMMER').asString); end; ---------------------------------------------------------------------- Nicole · 2024-02-02 11:26 I have added this id returning in the meanwhile. The first answer told me to do. It does not work. The open / exec / active - line  (I tried them all) passes. The line for the "field by name" - does not work. This is how it looks in the meanwhile 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    s := 'Insert into TBKONTRAKTE(NAME_,COMM,JAHR_AUS_NAME,MONATSKENNZAHL)' + //  ID_KONTRAKT,        ' values (:NAME_,:COMM,:JAHR_AUS_NAME,:MONATSKENNZAHL) returning ID_KONTRAKT'; // :ID_KONTRAKT   IBQuery_Kontrakte_B_24.SQL.Text := s;   IBQuery_Kontrakte_B_24.ParamByName('NAME_').AsString := Trim(name_);   IBQuery_Kontrakte_B_24.ParamByName('COMM').AsString := Trim(comm);   IBQuery_Kontrakte_B_24.ParamByName('JAHR_AUS_NAME').asSmallInt := Jahr_aus_Namen;   s1 := name_;   s1 := RightStr(s1, 2); // ich brauche die VORLETZTE Stelle   s1 := LeftStr(s1, 1);   i := Zeit.MONAT_BUCHSTABE_IN_ZAHL(s1);   IBQuery_Kontrakte_B_24.ParamByName('MONATSKENNZAHL').asSmallInt := i;//   IBQuery_Kontrakte_B_24.ExecSQL;   IBQuery_Kontrakte_B_24.Open;//   IBQuery_Kontrakte_B_24.Active:=true;//   i:= IBQuery_Kontrakte_B_24.ParamByName('ID_KONTRAKT').AsInteger;   i:= IBQuery_Kontrakte_B_24.FieldByName('ID_KONTRAKT').AsInteger;    // ==> Exception-Klasse »EDatabaseError« ausgelöst mit der Meldung: IBQuery_Kontrakte_B_24 : Field not found : "ID_KONTRAKT" ---------------------------------------------------------------------- rvk · 2024-02-02 11:30 Quote from: Nicole on February 02, 2024, 12:26:56 pmThe line for the "field by name" - does not work. That IBQuery_Kontrakte_B_24 is a IBQuery I assume? Did you have static fields assigned to it? (I know you were messing around with static fields in the other topic) If the IBQuery has static fields it doesn't retrieve the correct field in the Open. Right click and choose Edit fields and check if the list is empty. (It's one of the reason why I NEVER use the "Edit fields" option and just let IBX populate the fields so I can reach them with FieldByName) Otherwise you can use the simpler more direct TIBSQL. ---------------------------------------------------------------------- Zvoni · 2024-02-02 11:39 Is the INSERT even arriving in your Database? Execute your code until Line 13, and then look into your Database ("BlameRobert" or whatever it's called) if the Insert has arrived. ---------------------------------------------------------------------- rvk · 2024-02-02 11:44 Quote from: Zvoni on February 02, 2024, 12:39:28 pmIs the INSERT even arriving in your Database? Execute your code until Line 13, and then look into your Database ("BlameRobert" or whatever it's called) if the Insert has arrived. We don't see any code for commit so in line 13 the insert would be in its own transaction, invisible to other transactions, is my guess. But the returning should have increased the generator and returned something or there should have been an error. You can also check the TIBQuery.RecordCount to see if it returned something. Also check the TIBQuery.Fields.Count to see  if the count is 1 (just 1 field returning) so you know it's not static fields defined). ---------------------------------------------------------------------- Zvoni · 2024-02-02 11:57 Quote from: rvk on February 02, 2024, 12:44:06 pmQuote from: Zvoni on February 02, 2024, 12:39:28 pmIs the INSERT even arriving in your Database? Execute your code until Line 13, and then look into your Database ("BlameRobert" or whatever it's called) if the Insert has arrived. We don't see any code for commit so in line 13 the insert would be in its own transaction, invisible to other transactions, is my guess. But the returning should have increased the generator and returned something or there should have been an error. You can also check the TIBQuery.RecordCount to see if it returned something. Also check the TIBQuery.Fields.Count to see  if the count is 1 (just 1 field returning) so you know it's not static fields defined). Exactly my thoughts, though the Auto-ID should have been generated independant if the Transaction has been committed or not. Even if it's not commited the Auto-ID should already exist, though if a RollBack happens it would get "nuked". Maybe an Auto-Rollback due to an error (Constraint-Violation)? ---------------------------------------------------------------------- rvk · 2024-02-02 12:20 Quote from: Zvoni on February 02, 2024, 12:57:50 pmEven if it's not commited the Auto-ID should already exist, though if a RollBack happens it would get "nuked". Maybe an Auto-Rollback due to an error (Constraint-Violation)? That wouldn't explain the "Field not found" error. My guess is still on static fields in the TIBQuery (which were meant for a dbgrid or something). ---------------------------------------------------------------------- Zvoni · 2024-02-02 12:29 Quote from: rvk on February 02, 2024, 01:20:19 pmQuote from: Zvoni on February 02, 2024, 12:57:50 pmEven if it's not commited the Auto-ID should already exist, though if a RollBack happens it would get "nuked". Maybe an Auto-Rollback due to an error (Constraint-Violation)? That wouldn't explain the "Field not found" error. My guess is still on static fields in the TIBQuery (which were meant for a dbgrid or something). Ugh. Then i have to pass. I haven't used DB-Bound Controls for the last 20 years (in VB6, and the last 6 Years in Lazarus), so no idea if that might be the issue. ---------------------------------------------------------------------- Nicole · 2024-02-02 14:39 No, it does not arrive in my database.  :'( At the moment I am clueless. All looks fine to me and the generator of the pk has increased some numbers. Not sure, what is going on. ---------------------------------------------------------------------- rvk · 2024-02-02 14:44 Quote from: Nicole on February 02, 2024, 03:39:38 pmNo, it does not arrive in my database.  :'( At the moment I am clueless. All looks fine to me and the generator of the pk has increased some numbers. Not sure, what is going on. Did you check what I asked you???? You're getting an error so it's logical the record doesn't arrive in your database. ---------------------------------------------------------------------- Nicole · 2024-02-02 14:54 yes, I did. And you are right. It does not arrive. The problem is: I have no idea, WHY it does not arrive and I am that clueless, that I cannot even ask anything. I will go and write some tests to find out, what is wrong. I changed too many things in one step on re-design. ---------------------------------------------------------------------- rvk · 2024-02-02 14:57 Quote from: Nicole on February 02, 2024, 03:54:06 pmyes, I did. And you are right. It does not arrive.I said you needed to check if you have static fields defined in your query with which you execute insert. Quote from: rvk on February 02, 2024, 12:30:54 pmQuote from: Nicole on February 02, 2024, 12:26:56 pmThe line for the "field by name" - does not work. That IBQuery_Kontrakte_B_24 is a IBQuery I assume? Did you have static fields assigned to it? (I know you were messing around with static fields in the other topic) If the IBQuery has static fields it doesn't retrieve the correct field in the Open. Right click and choose Edit fields and check if the list is empty. (It's one of the reason why I NEVER use the "Edit fields" option and just let IBX populate the fields so I can reach them with FieldByName) Otherwise you can use the simpler more direct TIBSQL. So right click that IBQuery_Kontrakte_B_24 and choose "Edit fields". Is that list EMPTY !!! If it's not... then remove all those defined fields. You CAN'T use statically defined fields and read a returning value which isn't defined in those fields. ---------------------------------------------------------------------- Nicole · 2024-02-02 15:27 This is empty. You told me to check it and I did hours ago. The query I work at the moment is "non-visual". There never have been any static fields. The problem is: The insert does not arrive in my database. No idea why. The trigger triggers, the generator generates a figure - but the line is not there. Performing exact the same insert-statement by FlameRobin does the job and returns the id_kontrakt. I think, as next I will change the compentent to TSQLQuery. ---------------------------------------------------------------------- rvk · 2024-02-02 15:48 Quote from: Nicole on February 02, 2024, 04:27:43 pmThis is empty. You told me to check it and I did hours ago.Yes, but you didn't say you checked it. You only said things about not being able to see the record. Quote from: Nicole on February 02, 2024, 04:27:43 pmThe insert does not arrive in FlameRobin. No idea why.Simple... you got an error and execution didn't continue. So the commit (I assume there is a commit somewhere) never got executed so the record can't be in the db. Quote from: Nicole on February 02, 2024, 04:27:43 pmThe trigger triggers, the generator generates a figure - but the line is not there.Yes. But that's because the generator is executed outside the transaction. Quote from: Nicole on February 02, 2024, 04:27:43 pmPerforming exact the same insert-statement by FlameRobin does the job and returns the id_kontrakt.Yes, and then it works. No error message. Quote from: Nicole on February 02, 2024, 04:27:43 pmI think, as next I will change the compentent to TSQLQuery. I'll give a shout at tony. I would also prefer TIBSQL but as far as I know it used to work with TIBQuery too. ---------------------------------------------------------------------- rvk · 2024-02-02 15:48 Quote from: tonyw on February 02, 2024, 10:30:48 amYou need to add a "returning" clause to the insert statement. E.g.        s := 'Insert into TBKONTRAKTE(NAME_,COMM,JAHR_AUS_NAME,MONATSKENNZAHL)' +             ' values (:NAME_,:COMM,:JAHR_AUS_NAME,:MONATSKENNZAHL)' + ' returning ID_KONTRAKT' @tonyw HEEEEEEEEELP. My example from earlier with TIBQuery.Open and INSERT RETURNING ID doesn't work anymore and throws an error. (or was this only possible for SQLdb / TSQLQuery ?? and TIBSQL of course... ) This worked in the past or am I going crazy??? Now it throws the error IBQuery1 : Field not found : "LINKNUMMER" 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 TForm1.Button1Click(Sender: TObject);begin   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.RecordCount.ToString);  Showmessage(IBSQL1.FieldByName('LINKNUMMER').asString);   IBQuery1.SQL.Text := 'INSERT INTO BRANCHE VALUES (NULL, ''T'', 1, ''TEST'', ''Test'') RETURNING LINKNUMMER';  IBQuery1.ForcedRefresh := true;  IBQuery1.Open;  // IBQuery1.ExecSQL; // execsql doesn't work  Showmessage(IBQuery1.FieldByName('LINKNUMMER').asString); end; ---------------------------------------------------------------------- Nicole · 2024-02-02 16:16 * exhausted * After the change to IBSQL it worked. However it is strange, that it did not work with IB_Query. Thank you all so much.