Recent

Author Topic: [SOLVED] TSQLQuery Transaction Returning ID  (Read 6456 times)

mizarra

  • Newbie
  • Posts: 5
[SOLVED] TSQLQuery Transaction Returning ID
« 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.
« Last Edit: June 30, 2021, 04:46:16 pm by mizarra »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: TSQLQuery Transaction Returning ID
« Reply #1 on: June 21, 2021, 04:29:26 am »
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();

egsuh

  • Hero Member
  • *****
  • Posts: 1266
Re: TSQLQuery Transaction Returning ID
« Reply #2 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.

mizarra

  • Newbie
  • Posts: 5
Re: TSQLQuery Transaction Returning ID
« Reply #3 on: June 30, 2021, 04:45:47 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
« Last Edit: June 30, 2021, 04:49:16 pm by mizarra »

 

TinyPortal © 2005-2018