Examples of Database Transaction Management with esProc

esProc can write to databases and manage database transactions. Here we'll look at the programming method of rollbacks and controlling transaction submission, etc. 

A．Submit transactions automatically

esProc can conveniently execute operations like insert, delete and update. The simplest code is:

 

In the above figure, insert, update and delete are respectively executed from A2 to A4. Execution of each SQL statement will be submitted automatically. Note that:

1. That three SQL statements are submitted three times is too frequent operations for a database. 

2. There exists no transaction relation between the three SQL statements. So, if the execution of one SQL statement fails, the previous SQL statement will remain unaffected. 

The following examples are to introduce how to submit transactions in batches and how to compose a transaction with multiple SQL statements in a table sequence. 

B．Submit transactions in batches

Import students' information from students.txt to update table students1 in the database. Since there are a lot of records to be modified, using method of submitting transactions in batches is more reasonable. 

 

A1：Define a file object in which students’ information is stored. 

A2：Import file content. 

A3：Use students’ information in A2 to update table students1in batches. Submitting SQL in batches can avoid accessing the database too frequently. Meanwhile, this can ensure consistency of the data for the submission could succeed or fail simultaneously. 

C．Program control transactions

Now we’ll add a new student. The student’s id should be modified to 9 after data are inserted. In order to ensure consistency of the data, submission must be executed after the insertion and modification are proved to be successful. Otherwise rollback should be executed.

 

A1：Connect to the database. Note that connect function uses option @e and the subsequent code will return error message when something wrong happens. If the option is not used, the database will terminate esProc program directly when errors occur.  

A2：Execute the insert SQL statement. Note that execute function uses option @k, meaning the transaction will not automatically submitted after it is executed. If the option is not used, the insert SQL statement will be submitted immediately. 

A3：Get the result of last operation in the database, i.e., the insertstatement. If err variable is zero, the execution is successful; otherwise, err is the error code. 

A4：Judging whether err variable, the execution result, is zero. If the answer is yes, the last operation of the insert statement is successful and modification in B4 can be executed.

C4：Get execution result of the update SQL. 

A5：Make judgment over err variable. If it is zero, submit the database; otherwise execute rollback.  

A6：Close database connection.