BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Multiple Execquery on one IBSQL transaction          BorlandTalk.com Forum Index -> Delphi Databases (InterBase Express) View previous topic :: View next topic   Author Message Mark Reichert Guest Posted: Fri Feb 02, 2007 5:06 am    Post subject: Multiple Execquery on one IBSQL transaction In b.p.d.database.general, I posted "Reording a SQL Table?", where I explain why we need to have an order to information in an SQL table that is independent of any of the informational fields and my initial thought of doing a block delete and a reinsert of the deleted records so that a trigger and generator could regenerate the sequenceid. Is the only way to do both of those statements in one transaction using a IBSQL component is to change the SQL property in code? I certainly DO NOT want to commit after the block delete, since there's a good chance some people will see the result of that before the inserts make the records appear again. I'm just saying I don't know how to get two different IBSQL components to work together and have dedicated a separate IBSQL for each of my selects up to now (which had the benefit of allowing the prepared SQL to be preserved as long as the program is running). As usual, I tried finding an example on-line before asking, but came up empty. Either nobody has asked or I don't know how to use Google properly to look for past b.p.d. posts. Back to top Bill Todd Guest Posted: Fri Feb 02, 2007 6:14 am    Post subject: Re: Multiple Execquery on one IBSQL transaction If you want to execute multiple SQL statements sequentially use TIBScript. -- Bill Todd (TeamB) Back to top Mark Reichert Guest Posted: Fri Feb 02, 2007 8:20 pm    Post subject: Re: Multiple Execquery on one IBSQL transaction "Bill Todd" <no (AT) no (DOT) com> wrote in message news:45c28255$1 (AT) newsgroups (DOT) borland.com... Quote: If you want to execute multiple SQL statements sequentially use TIBScript. Isn't that for executing the multiple SQL statements without your interaction? I can't see using that when I have to keep feeding the Insert with new parameter values. Back to top Bill Todd Guest Posted: Fri Feb 02, 2007 10:03 pm    Post subject: Re: Multiple Execquery on one IBSQL transaction Mark Reichert wrote: Quote: Isn't that for executing the multiple SQL statements without your interaction? I can't see using that when I have to keep feeding the Insert with new parameter values. I did not notice any mention of parameters. In that case you can either change the SQL in one IBSQL or use two IBSQL components connected to the same IBTransaction. -- Bill Todd (TeamB) Back to top Wayne Niddery [TeamB] Guest Posted: Sat Feb 03, 2007 4:18 am    Post subject: Re: Multiple Execquery on one IBSQL transaction Mark Reichert wrote: Quote: Is the only way to do both of those statements in one transaction using a IBSQL component is to change the SQL property in code? I'm just saying I don't know how to get two different IBSQL components to work together and have dedicated a separate IBSQL for each of my selects up to now (which had the benefit of allowing the prepared SQL to be preserved as long as the program is running). Each IBSQL component is (must be) assigned to an IBDatabase/IBTransaction pair. You're already doing this or else they wouldn't work. Because a transaction is already linked, when you start a transaction, you can execute as many different statements as you want with any or all IBSQLs (or other datasets) connected to that transaction, e.g. IBTransaction.StartTransaction; IBSQL1.ExecQuery; IBSQL2.Params[0].AsInteger := 99; IBSQL2.ExecQuery; IBSQL1.SQL.Text := 'some new sql here'; IBSQL1.ExecQuery; IBSQL3.ExecQuery; IBSQL2.Params[0].AsInteger := 44; IBSQL2.ExecQuery; IBTransaction.Commit; -- Wayne Niddery - Winwright, Inc (www.winwright.ca) Working for yourself is great because you get to work half days, and you can choose any twelve hours you want. Back to top Mark Reichert Guest Posted: Sat Feb 03, 2007 4:40 am    Post subject: Re: Multiple Execquery on one IBSQL transaction "Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote in message news:45c3b876$1 (AT) newsgroups (DOT) borland.com... Quote: Mark Reichert wrote: Is the only way to do both of those statements in one transaction using a IBSQL component is to change the SQL property in code? I'm just saying I don't know how to get two different IBSQL components to work together and have dedicated a separate IBSQL for each of my selects up to now (which had the benefit of allowing the prepared SQL to be preserved as long as the program is running). Each IBSQL component is (must be) assigned to an IBDatabase/IBTransaction pair. You're already doing this or else they wouldn't work. Because a transaction is already linked, when you start a transaction, you can execute as many different statements as you want with any or all IBSQLs (or other datasets) connected to that transaction, e.g. IBTransaction.StartTransaction; IBSQL1.ExecQuery; IBSQL2.Params[0].AsInteger := 99; IBSQL2.ExecQuery; IBSQL1.SQL.Text := 'some new sql here'; IBSQL1.ExecQuery; IBSQL3.ExecQuery; IBSQL2.Params[0].AsInteger := 44; IBSQL2.ExecQuery; IBTransaction.Commit; Thank you. I had adopted using the IBSQL tranaction property, to make sure I was using the correct one, but as you are pointing out using the IBTransaction when working with multiple IBSQL is both necessary and standard. Back to top        BorlandTalk.com Forum Index -> Delphi Databases (InterBase Express) All times are GMT Page 1 of 1 Powered by phpBB © 2001, 2006 phpBB Group .