Understanding execSQL

, ,
1

a. Considering the default model of working with database inserts - loop over a recordList and then call execSQL within :

    try (exit on failure) 
         --  initialize 
         --  other steps and invocations 
          loop over recordList 
                   execSQL(s) 
    catch 
          error handling

Does that mean execSQL is going to
Connect,
Insert record
Commit (AutoCommit in case of SQLServer)
Close
for each loop pass through?

If the above is true, then a better approach would be to use single database connection and loop through the recordList

    try (exit on failure) 
         connect 
         start transaction 
         inner try (exit on failure) 
                loop over recordList 
                        execSQL(s) 
         inner catch 
                 error handling 
                 rollback  
                 clear transaction 
                 close 
   catch  
         error handling

Wanted to know if the second approach is better than the first for performance and better eror handling?

b. Also how does one code this in flow

if transaction is not cleared 
      cleartransaction 

if connection is open 
      close
2

Lets talk about the Case1, as i am using that approach with some modification.

It looks like if you put a startTransaction, the tranactions are not commited (connection is not returned) till you invoke a commit service. And this works fine for me.

So, it looks like this:

try (exit on failure)
initialize
other steps and invocations
clearTransaction
startTransaction
loop over recordList
execSQL(s)
commit (outside the loop)
clearTransaction

catch (exit on DONE)
getLastError
rollback
sendMail/ other errorHandling stuff

Hope this helps!