[SOLVED] TSQLQuery Transaction Returning ID

Forum > Database

(1/1)

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

I have

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:

--- 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.
--- End quote ---
This is the simplest solution I can think of:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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).Commit(); SQLQuery2.ApplyUpdates();TSQLTransaction(SQLQuery2.Transaction).Commit();

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

mizarra:

--- 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.
--- End quote ---
This is the simplest solution I can think of:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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).Commit(); SQLQuery2.ApplyUpdates();TSQLTransaction(SQLQuery2.Transaction).Commit();
--- End quote ---

Thanks, it work like a charm, only need to add SQLQuery1.Close :D

Navigation

[0] Message Index