During our dealing with the database transactions, some operations may produce errors which could bring unexpected results, particularly during batch processes. In order to prevent this from happening, we need to manage the data connection in a right way and handle those errors properly.
1. Database error messages
First let’s look at the database error messages. We’ll use an Access file – DbCon.accdb – as the target database, and then create an ODBC datasource in esProc. Use the data of the file directly and enter the connecting string DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=D:\\files\\DbCon.accdb :
Create an empty table CityBak in the Access file – DbCon.accdb:
In the table, ID is the primary key. A limitation on POPULATION that the data of it should be greater than 1,000,000 has been set.
The following will introduce how to enter the data of table CITIES of demo database in the CityBak:
| A | |
| 1 | =demo.query(“select * from CITIES”) |
| 2 | =connect(“DbCon”) |
| 3 | =A2.query(“select * from CityBak”) |
| 4 | >A2.update(A1,CityBak,ID:CID,CITY:NAME,POPULATION,STATE:STATEID) |
| 5 | >A2.close() |
A1 selects the data of CITIES from demo database:
A2 creates a connection to database DbCon. A3 gets the data of CityBak. But as a newly-created table, it hasn’t any records:
The program reports an error when A4 is executed. According to the limitation that population should be greater than 1,000,000, the 10th record Detroit is ineligible. By default the program ends when an error message is printed out.
In this case, we can view the data written to the CityBak in another dfx file:
| A | |
| 1 | =connect(“DbCon”) |
| 2 | =A1.query(“select * from CityBak”) |
| 3 | >A1.close() |
The query result of A2 is as follows:
It can be seen that, though the execution of the program in the first cellset failed because of the limitation on the data of CityBak, still some data had been written to the targeted table successfully before the first ineligible record appeared.
The program will stop when a database error occurs during the batch database update. In order to avoid this interruption, @e option can be used at the time of creating the connection to allow programmer to handle the error by codes. For example:
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =connect@e(“DbCon”) | |
| 3 | >A2.execute(“delete from CityBak”) | |
| 4 | >A2.update(A1,CityBak,ID:CID,CITY:NAME,POPULATION,STATE:STATEID) | =A2.error() |
| 5 | =A2.query(“select * from CityBak”) | |
| 6 | >A2.close() |
In A2, @e option is added to the connect function to let programmer handle the error message if there is one, without interrupting the program. A2 deletes the existing data from CityBak to maintain a data consistency. When the program is executed, the query result of A5 is the same as that of A2 in the above:
While A4 is updating the data of CityBak, an operation that is not in line with the above requirement is performed. So we can see the error code in B4:
Note: Once an error occurs during the batch database update, the program will stop and report an error. By modifying the expression in B4 into =A2.error@m(), we can see the error message, as shown below:
Let’s go on with our discussion:
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =connect@e(“DbCon”) | |
| 3 | >A2.update@a(A1.select(CID<6), CityBak,ID:CID,CITY:NAME, POPULATION,STATE:STATEID) | =A2.error() |
| 4 | =A2.query(“select * from CityBak”) | |
| 5 | >A2.close() |
By adding @a option to db.update(), A3 will delete the existing data from the database table before it execute the update, saving us the trouble of specially writing code for deleting the records. A3 selects only the top 5 records and add them to CityBak. Since all these records meet the requirement, the statement in A3 has been executed smoothly and the error code in B3 is 0:
And as can be seen from A5, the records have been written to the table successfully:
2. Commit and rollback transaction management
We cannot know beforehand if the batch update will be completely executed, or which record will produce an error. By default the update of each record will be committed automatically and the result is unpredictable. This is not good for database management.
Sometimes whether a transaction should be committed or canceled depends on different situations. And db.commit() and db.rollback() are used to determine which operation we will perform.
By default, a statement in esProc will be committed automatically after it is executed. In this case, the situation is out of our control. In order to control the transaction commit through such code as db.commit() and db.rollback(), we need to add @k option to the execute or update statement. Then the validity of the data can be determined according to the error code. As shown in the following example, if an error occurs during batch update, all the data will become invalid. In this way, the database can be saved from the unpredictable results:
| A | B | |
| 1 | =demo.query(“select * from CITIES”) | |
| 2 | =connect@e(“DbCon”) | |
| 3 | >A2.execute(“delete from CityBak”) | |
| 4 | >A2.update@k(A1,CityBak,ID:CID,CITY:NAME,POPULATION,STATE:STATEID) | |
| 5 | if A2.error()==0 | >A2.commit() |
| 6 | else | >A2.rollback() |
| 7 | =A2.query(“select * from CityBak”) | |
| 8 | >A2.update@k(A1.to(5),CityBak,ID:CID, CITY:NAME,POPULATION,STATE:STATEID) | |
| 9 | if A2.error()==0 | >A2.commit() |
| 10 | else | >A2.rollback() |
| 11 | =A2.query(“select * from CityBak”) | |
| 12 | >A2.close() |
A3 deletes the data from CityBak. It will commit the transaction automatically due to the lack of @k option in db.execute().
An error occurs in A4 when batch update is executed, which is shown in the result of A5:
That’s why the rollback transaction in B6, instead of the commit transaction in B5, has been executed. The query result in A6 is as follows:
If no error is reported during batch update with the use of @k option, like the statement executed in A8, we can still determine the data validity according to the error code. The value of A9 is as follows:
In this case, the commit transaction in B9 will be executed and the data will be written to the database. The query result of A11 is as follows:
