Control of database connections in the collector

When working with database transactions, some operations can produce errors, and errors can result in unpredictable operations, especially in bulk transactions. In order to avoid this situation, it is necessary to control the data connection and handle the error information in the database transaction accordingly.

1. Database error message

let's look at the error message of the database first. Here, using an Access file dbcon.accdb as the target database, in the collector, create a new ODBC data source, directly using the data in this file, the connection string filled in: Driver=microsoft access DRIVER (*.mdb, *. ACCDB);D bq=d:\\files\\dbcon.accdb:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/FC/wKioL1Qg2jmxcCS7AADYQ-GIFIg129.jpg "style=" float: none; "title=" esproc_database_connect_1.jpg "alt=" Wkiol1qg2jmxccs7aadyq-gifig129.jpg "/>

in Access file dbcon.accdb, an empty table Citybak is established:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/FA/wKiom1Qg2hnAtVy1AABA88uyFhc611.jpg "title=" Esproc _database_connect_2.jpg "style=" Float:none; "alt=" wkiom1qg2hnatvy1aaba88uyfhc611.jpg "/>

Where ID is the primary key, special, population requires data >1000000.

Now, prepare the data in the demo database, cities, into the Citybak table:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FC/wKioL1Qg2jvjMoITAACCjMWoWxE288.jpg "title=" Esproc _database_connect_3.jpg "style=" Float:none; "alt=" wkiol1qg2jvjmoitaaccjmwowxe288.jpg "/>

A1 from the demo database, remove the data from the cities table:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FA/wKiom1Qg2hvQBi5EAAD3icMGtqY584.jpg "title=" Esproc _database_connect_4.jpg "style=" Float:none; "alt=" wkiom1qg2hvqbi5eaad3icmgtqy584.jpg "/>

A2 connect the Dbcon database. A3, the data in the Citybak table is removed, because it is a new table, it is not originally recorded:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/FC/wKioL1Qg2jyg2lHPAAAdD6kIRhs241.jpg "title=" Esproc _database_connect_5.jpg "style=" Float:none; "alt=" wkiol1qg2jyg2lhpaaadd6kirhs241.jpg "/>

when executing to A4, there was an error , which was due to the qualification of population>1000000, which was not met when attempting to fill in the 10th record Detroit information. By default, execution is interrupted when there is an error in the database operation.

At this point, we can query the data filling in another DFX:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/FA/wKiom1Qg2hyS7eslAABD4DN0Bts944.jpg "title=" Esproc _database_connect_6.jpg "style=" Float:none; "alt=" wkiom1qg2hys7eslaabd4dn0bts944.jpg "/>

the query results in A2 are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/FC/wKioL1Qg2j6CP7RAAADGDnJudWo899.jpg "title=" Esproc _database_connect_7.jpg "style=" Float:none; "alt=" wkiol1qg2j6cp7raaadgdnjudwo899.jpg "/>

As you can see, although the program in the 1th grid failed to succeed due to the limitations of the data in the Citybak table, some of the data is still populated into the target table.

When the database is updated in bulk, the program's operation is terminated as soon as a database error occurs. To avoid this, we can use the @e option when generating a connection, using code to handle database errors on our own without interruption. Such as:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/FA/wKiom1Qg2h7Bp183AADCkyzASDU277.jpg "title=" Esproc _database_connect_8.jpg "style=" Float:none; "alt=" wkiom1qg2h7bp183aadckyzasdu277.jpg "/>

in A2, the @e option is used to generate a database connection so that it can be handled by the code itself when an error occurs without interrupting the program. In order to remain consistent with the previous one, empty the records already in the Citybak table in A2 first. After running, the results queried in A5 are the same as before:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FC/wKioL1Qg2kDS3OcwAADDHMvOTLQ678.jpg "title=" Esproc _database_connect_9.jpg "style=" Float:none; "alt=" wkiol1qg2kds3ocwaaddhmvotlq678.jpg "/>

When A4 performs an update to the data in the Citybak table, the error code can be seen in B4 because of an action that does not meet the data requirements when the data is updated:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FA/wKiom1Qg2h_xO52TAAAS6Ya93yY703.jpg "title=" Esproc _database_connect_10.jpg "style=" Float:none; "alt=" wkiom1qg2h_xo52taaas6ya93yy703.jpg "/>

Note that in a batch update, a database error occurs, and an error is logged while the database is being updated. We can also change the expression in A4 to [email protected] (), and we can see the error message:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FA/wKiom1Qg2s3QAcw9AAAyor9AUoQ827.jpg "style=" float: none; "title=" esproc_database_connect_11.jpg "alt=" Wkiom1qg2s3qacw9aaayor9auoq827.jpg "/>

We continue to explore:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/FC/wKioL1Qg2u-Clm_GAACqyJQVJY8180.jpg "title=" Esproc _database_connect_12.jpg "style=" Float:none; "alt=" wkiol1qg2u-clm_gaacqyjqvjy8180.jpg "/>

using the @a option in [email protected] () , you can clear the data from the database tables before you perform the update, eliminating the need to add statements to delete records. In A3, only the first 5 records are taken, filled in the Citybak, at which time the record satisfies the data requirements, the statements in A3 are performed normally, so the error code in B3 is 0:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/FA/wKiom1Qg2s-ydem9AAAWfjL66gw147.jpg "title=" Esproc _database_connect_13.jpg "style=" Float:none; "alt=" wkiom1qg2s-ydem9aaawfjl66gw147.jpg "/>

as can be seen from the A5, records are stored normally:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/FC/wKioL1Qg2vGBA9PmAAB54IqsaHI798.jpg "title=" Esproc _database_connect_14.jpg "style=" Float:none; "alt=" wkiol1qg2vgba9pmaab54iqsahi798.jpg "/>

2. Control Commit and rollback

We cannot know in advance whether the records of a batch update are all successful or not, and we do not know which record will produce an error. By default, the results of each record are automatically submitted, making the results unpredictable, which is not a good phenomenon for database management.

when working with database transactions, it is sometimes necessary to decide whether to commit the changes to the database or cancel the operation, depending on the situation, and then use db.commit () and Db.rollback () for control.

By default, statements executed in the collector are automatically committed and cannot be controlled at this time. If you want to use db.commit () and Db.rollback () for control, you need to use the @k option when executing the statement and control the commit with code. This allows you to determine whether the data is valid based on the error message. As in the following example, if an error is generated when the update is executed in bulk, all data is not valid so that unpredictable results can be avoided in the database:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/FA/wKiom1Qg2tGjzOCnAAFka3_XtYg457.jpg "title=" Esproc _database_connect_15.jpg "style=" Float:none; "alt=" wkiom1qg2tgjzocnaafka3_xtyg457.jpg "/>

A3, the data in the Citybak is emptied and is automatically committed because the @k option is not used when performing db.execute ().

in A4, when performing a bulk update, an error is generated, which can be seen from the results of the A5 operation:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/FA/wKiom1Qg2tLimN1JAAAXSqi45xc514.jpg "title=" Esproc _database_connect_16.jpg "style=" Float:none; "alt=" wkiom1qg2tlimn1jaaaxsqi45xc514.jpg "/>

Therefore, the rollback operation in B6 is performed and the data is not written to the database. The query results in A7 are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FC/wKioL1Qg2vPhIunGAAAjknAF3Pc037.jpg "title=" Esproc _database_connect_17.jpg "style=" Float:none; "alt=" wkiol1qg2vphiungaaajknaf3pc037.jpg "/>

When using the @k option for bulk updates, if no errors are generated, such as statements executed in A8, you can also determine by error code that the value in A9 is:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/FA/wKiom1Qg2tKQUjrSAAAXXlMbf1A581.jpg "title=" Esproc _database_connect_18.jpg "style=" Float:none; "alt=" wkiom1qg2tkqujrsaaaxxlmbf1a581.jpg "/>

The commit operation in B9 is performed and the data is written to the database. The query results in A11 are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/FC/wKioL1Qg2vTQ6DFZAACJISvdqC4621.jpg "title=" Esproc _database_connect_19.jpg "style=" Float:none; "alt=" wkiol1qg2vtq6dfzaacjisvdqc4621.jpg "/>

Control of database connections in the collector