Database Connection Management

This section is necessary for database upadate. You can choose to skip this section if you are not a professional programmer. It won’t affect your learning about the other contents of this Tutorial.

During our dealing with database transactions, errors may arise and unexpected results could be produced, particularly during batch processes. In order to prevent errors from happening, database connection needs to be appropriately managed and possible errors should be specifically handled.

4.3.1 Database error messages

First let’s look at database error messages. Here the target database is an Access file – DbCon.accdb, for which an ODBC data source is created in esProc to directly retrieve data from the file by entering 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 field is the primary key. A limitation on POPULATION field that its data should be greater than 1,000,000 has been set.

Let’s fill the data of CITIES table in the 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 query the data of CITIES in the demo database:

A2 creates a connection to database DbCon. A3 retrieves the data of CityBak, which, as a newly-created table, hasn’t any records.

An error is reported during the execution of A4. Because according to the limitation that population should be greater than 1,000,000, the 10^th record Detroit is ineligible. By default, a database operation terminates when an error message appears.

In this case, you can view the data written to the CityBak in another script file:

	A
1	=connect("DbCon")
2	=A1.query("select * from CityBak")
3	>A1.close()

The query result of A2 is as follows:

Though the execution of the program in the first cellset fails because of the limitation on the data of CityBak, still some data has been written to the targeted table successfully before the first ineligible record appears.

The program stops 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 the program itself to handle the error. 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()

A2 uses @e option in creating database connection to let the program handle the error automatically if there is one, without terminating the program. To maintain data consistency, A3 first deletes the existing data from CityBak. After the program is executed, A5 gets the same query result A2 in the previous execution:

While A4 is updating the data of CityBak, an operation that is not in line with the above requirement is performed. You can see the error value in B4:

Note: Once an error occurs during batch database update, the program will stop and report an error. By modifying the expression in B4 into =A2.error@m(), you can see the error message:

Let’s take a step further:

	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 deletes the existing data from the target database table before executing the update, without needing a separate statement to do this. A3 specifies that only the top 5 records are retrieved and written to CityBak. Since all these records meet the requirement, the statement in A3is executed successfully and the error code in B3 is 0:

As can be seen from A4, the requested records have been written to the table successfully:

4.3.2 Commit and rollback transaction management

Whether a batch update will be successful and which record could cause an error are unpredicatable because each record update is automatically committed.This is bad for database management.

Sometimes whether a database upadate transaction should be committed or canceled depends on the current situation. db.commit() and db.rollback() functions are used to perform commit and rollback respectively.

By default, the execution of an esProc statement is committed automatically and beyond control. In order to control the transaction commitment through db.commit() and db.rollback(), @k option is added to an execute or update statement to decide which function should be used. Data validity is determined according to the error value. As the following example shows, if an error occurs during batch update, the transaction will be aborted. In this way, the database can be saved from receiving any 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 data from CityBak. It commits the transaction automatically because @k option is absent in db.execute(). An error occurs in A4 when batch update is executing and shown in A5:

So the rollback operation in B6, instead of the commit operation in B5, is executed. The query result in A7 is as follows:

If there’s no error during a batch update with the use of @k option, like the statement executed in A8, you can know according to the error value. Here’s A9’s value:

In this case, the commit operation in B9 will be executed and data will be written to the database. The query result of A11 is as follows: