Control of Database Connection in esProc

In handling database transactions, some operations may cause errors, which may bring about unpredictable results, especially in batch processing. In order to avoid this situation, database connection should be under control and error messages should be handled appropriately.

1. Database error messages
Let’s study database error messages first. Use an Access file DbCon.accdb as the target database, and create an ODBC data source in esProc. Use data of the file directly and write a connection string in the ODBC data source:DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=D:\\files\\DbCon.accdb：

An empty table CityBak was created in the Access file DbCon.accdb:

 
In the table, ID is the primary key, and especially, data of POPULATION should be>1000000.

Now prepare to write CITIES' data in database demo to table CityBak:  


A1 gets table CITIES’s data from database demo:
 

A2 connects to database DbCon. A3 gets data from table CityBak; since it is a newly-created table, there are no records in it. 
 

An error occurs in A4. Because of the criterion POPULATION>1000000, the execution of writing the tenth record of Detroit's information to the database fails. By default, the execution will be terminated when errors occur in database operations. 
But we can look up the data-writing result in another dfx:

Query results of A2 are as follows: 
 
It can be seen that some of the data has been written to the target table successfully though the execution of program in the first cellset failed due to data constraint in table CityBak. 
Once database errors occur while updating a database in batches,program will be terminated. In order to avoid this situation, @e option can be used at the beginning of connecting to handle the errors manually while the connection remains. For example:

@e option is used in A2 when the database connection is established, so the code can respond manually to possible errors. In order to keep consistent with the preceding query results, the existing records in table CityBak should be emptied in A2 first. Thus query results of A5 will be the same with the preceding ones:
 



Because there were operations disagreeing with data constraint when updating table CityBak's data in A4, error code can be found in B4:
 
It should be noted that once database errors occur while batch updating a database, the updating will stop and errors will be recorded. By modifying the expression in A4 to =A2.error@m(), we can see the error messages:

We'll make further study: 

Using @a option indb.update@a() can empty database table before updating, then it is unnecessary to add statements to delete records. Get the first 5 records in A3 and write them to CityBak. Now since all the records satisfy the data constraint, the statements execution in A3 goes well and the error code in B3 is zero:
 
In A5, the records are stored successfully: 

2.Control of submission and rollback
We cannot know beforehand whether the batch update of records is successfully completed or not, and which records may go wrong. By default, each record’s update will be automatically submitted, which makes the results unpredictable. This is far from ideal for database management. 

In handling database transactions, sometimes we need to decide whether we should submit the update to the database or cancel the execution, as appropriate. In this circumstance, we use db.commit() and db.rollback() to take control.

The executed statements in esProc are by default submitted automatically and thus,out of control. If we want to use db.commit() and db.rollback() to control the submission, @k option is needed in executing statements to make code has control of the submission. In this way, we can decide if the data are eligible in the light of error messages. In the following example, all data become ineligible if there is something wrong with the batch update. This can prevent unpredictable results in the database:

A3 empties table CityBak and executes submission automatically because it didn't use @k option when executing db.execute(). 

Errors may occur in A4 when executing batch update, which can be seen in A5's computed result: 
 
Therefore, rollback in B6 will be executed and data won’t be written to the database. Query results of A7 are as follows: 
 
We can see according to error code that all goes well when executing batch update with @k option, like the executed statements in A8. Value of A9 is: 
 
Now submission in B9 will be executed and data will be written to the database. Query results of A11 are as follows: