[SOLVED] TSQLQuery Transaction Returning ID
lazarus / 63-Database

----------------------------------------------------------------------
mizarra · 2021-06-15 16:06

Hi friends, I was searching in all forum but I can't find the answer
to my question.

I have

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
SQLQuery1.SQL.Text :=  ' BEGIN; '+' INSERT INTO MASTERDATA (THINGS)
VALUES (THINGS) RETURNING ID; '+' INSERT INTO DETAILDATA
(IDMASTER,THINGS) VALUES (currval(''MASTERDATA
_ID_seq''::regclass),THINGS); '+' 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.

----------------------------------------------------------------------
valdir.marcos · 2021-06-21 02:29

Quote from: mizarra on June 15, 2021, 06:06:16 pmHi friends, I was
searching in all forum but I can't find the answer to my question.

I have

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
SQLQuery1.SQL.Text :=  ' BEGIN; '+' INSERT INTO MASTERDATA (THINGS)
VALUES (THINGS) RETURNING ID; '+' INSERT INTO DETAILDATA
(IDMASTER,THINGS) VALUES (currval(''MASTERDATA
_ID_seq''::regclass),THINGS); '+' 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.Close;SQLQuery1.SQL.Text := 'INSERT INTO MASTERDATA (THINGS)
VALUES (THINGS) RETURNING
ID;';SQLQuery1.Open; SQLQuery2.Close;SQLQuery2.SQL.Text := 'INSERT
INTO DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ', THINGS);';SQLQuery2.ExecSQL;
 SQLQuery1.ApplyUpdates();TSQLTransaction(SQLQuery1.Transaction).Commi
t(); SQLQuery2.ApplyUpdates();TSQLTransaction(SQLQuery2.Transaction).C
ommit();

----------------------------------------------------------------------
egsuh · 2021-06-21 05:15

Do you have any reason for not using external TSQLTransaction?
I can think of writing a stored procedure.

----------------------------------------------------------------------
mizarra · 2021-06-30 14:45

Quote from: valdir.marcos on June 21, 2021, 04:29:26 amQuote from:
mizarra on June 15, 2021, 06:06:16 pmHi friends, I was searching in
all forum but I can't find the answer to my question.

I have

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
SQLQuery1.SQL.Text :=  ' BEGIN; '+' INSERT INTO MASTERDATA (THINGS)
VALUES (THINGS) RETURNING ID; '+' INSERT INTO DETAILDATA
(IDMASTER,THINGS) VALUES (currval(''MASTERDATA
_ID_seq''::regclass),THINGS); '+' 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLQuery1.Close;SQLQuery1.SQL.Text := 'INSERT INTO MASTERDATA (THINGS)
VALUES (THINGS) RETURNING
ID;';SQLQuery1.Open; SQLQuery2.Close;SQLQuery2.SQL.Text := 'INSERT
INTO DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ',
THINGS);';SQLQuery2.ExecSQL;SQLQuery2.SQL.Text := 'INSERT INTO
DETAILDATA (IDMASTER, THINGS) VALUES (' +
SQLQuery1.FieldByName('ID').AsString + ', THINGS);';SQLQuery2.ExecSQL;
 SQLQuery1.ApplyUpdates();TSQLTransaction(SQLQuery1.Transaction).Commi
t(); SQLQuery2.ApplyUpdates();TSQLTransaction(SQLQuery2.Transaction).C
ommit();
Thanks, it work like a charm, only need to add SQLQuery1.Close