Lazarus

Free Pascal => Database => Topic started by: mizarra on June 15, 2021, 06:06:16 pm

Title: [SOLVED] TSQLQuery Transaction Returning ID
Post by: mizarra on June 15, 2021, 06:06:16 pm
Hi friends, I was searching in all forum but I can't find the answer to my question.

I have

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text :=  ' BEGIN; '
  2. +' INSERT INTO MASTERDATA (THINGS) VALUES (THINGS) RETURNING ID; '
  3. +' INSERT INTO DETAILDATA (IDMASTER,THINGS) VALUES (currval(''MASTERDATA _ID_seq''::regclass),THINGS); '
  4. +' COMMIT; ';
  5.  

I know that I have to use "SQLQuery1.Open" if I want to get the returning ID but I can't  use a transaction SQL with Open.

My question is. How can I return values from transaction SQL using Open (or something like that)?.

Thanks.
Title: Re: TSQLQuery Transaction Returning ID
Post by: valdir.marcos on June 21, 2021, 04:29:26 am
Quote from: mizarra on June 15, 2021, 06:06:16 pm
Hi friends, I was searching in all forum but I can't find the answer to my question.

I have

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text :=  ' BEGIN; '
  2. +' INSERT INTO MASTERDATA (THINGS) VALUES (THINGS) RETURNING ID; '
  3. +' INSERT INTO DETAILDATA (IDMASTER,THINGS) VALUES (currval(''MASTERDATA _ID_seq''::regclass),THINGS); '
  4. +' COMMIT; ';
I know that I have to use "SQLQuery1.Open" if I want to get the returning ID but I can't  use a transaction SQL with Open.

My question is. How can I return values from transaction SQL using Open (or something like that)?.

Thanks.
This is the simplest solution I can think of:

Code: Pascal  [Select][+][-]
  1. SQLQuery1.Close;
  2. SQLQuery1.SQL.Text := 'INSERT INTO MASTERDATA (THINGS) VALUES (THINGS) RETURNING ID;';
  3. SQLQuery1.Open;
  4.  
  5. SQLQuery2.Close;
  6. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  7. SQLQuery2.ExecSQL;
  8. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  9. SQLQuery2.ExecSQL;
  10. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  11. SQLQuery2.ExecSQL;
  12. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  13. SQLQuery2.ExecSQL;
  14. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  15. SQLQuery2.ExecSQL;
  16.  
  17. SQLQuery1.ApplyUpdates();
  18. TSQLTransaction(SQLQuery1.Transaction).Commit();
  19.  
  20. SQLQuery2.ApplyUpdates();
  21. TSQLTransaction(SQLQuery2.Transaction).Commit();
Title: Re: TSQLQuery Transaction Returning ID
Post by: egsuh on June 21, 2021, 07:15:59 am
Do you have any reason for not using external TSQLTransaction?
I can think of writing a stored procedure.
Title: Re: TSQLQuery Transaction Returning ID
Post by: mizarra on June 30, 2021, 04:45:47 pm
Quote from: valdir.marcos on June 21, 2021, 04:29:26 am
Quote from: mizarra on June 15, 2021, 06:06:16 pm
Hi friends, I was searching in all forum but I can't find the answer to my question.

I have

Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text :=  ' BEGIN; '
  2. +' INSERT INTO MASTERDATA (THINGS) VALUES (THINGS) RETURNING ID; '
  3. +' INSERT INTO DETAILDATA (IDMASTER,THINGS) VALUES (currval(''MASTERDATA _ID_seq''::regclass),THINGS); '
  4. +' COMMIT; ';
I know that I have to use "SQLQuery1.Open" if I want to get the returning ID but I can't  use a transaction SQL with Open.

My question is. How can I return values from transaction SQL using Open (or something like that)?.

Thanks.
This is the simplest solution I can think of:

Code: Pascal  [Select][+][-]
  1. SQLQuery1.Close;
  2. SQLQuery1.SQL.Text := 'INSERT INTO MASTERDATA (THINGS) VALUES (THINGS) RETURNING ID;';
  3. SQLQuery1.Open;
  4.  
  5. SQLQuery2.Close;
  6. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  7. SQLQuery2.ExecSQL;
  8. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  9. SQLQuery2.ExecSQL;
  10. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  11. SQLQuery2.ExecSQL;
  12. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  13. SQLQuery2.ExecSQL;
  14. SQLQuery2.SQL.Text := 'INSERT INTO DETAILDATA (IDMASTER, THINGS) VALUES (' + SQLQuery1.FieldByName('ID').AsString + ', THINGS);';
  15. SQLQuery2.ExecSQL;
  16.  
  17. SQLQuery1.ApplyUpdates();
  18. TSQLTransaction(SQLQuery1.Transaction).Commit();
  19.  
  20. SQLQuery2.ApplyUpdates();
  21. TSQLTransaction(SQLQuery2.Transaction).Commit();

Thanks, it work like a charm, only need to add SQLQuery1.Close :D
TinyPortal © 2005-2018