Code examples for the management of database transactions by the collector

The collector can write data to the database and support database transaction management. Here is an example of a programmatic approach to controlling transaction commit and rollback, and so on.

1. Auto COMMIT TRANSACTION

The collector can easily realize the deletion and modification, the simplest wording is as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/47/D3/wKioL1QAGgnBTNliAACxd4Kt2SI997.jpg "style=" float: none; "title=" esproc_manage_database_1.jpg "alt=" Wkiol1qaggnbtnliaacxd4kt2si997.jpg "/>

in the A2 to A4 are inserted, modified, and deleted respectively. automatically commits after each SQL execution. It should be stated that:

1,3 Statements were submitted 3 times, the database operation is more frequent.

2, three SQL does not have a transactional relationship, and subsequent SQL if execution fails, does not affect the preceding SQL.

The following is an example of a programming batch-commit TRANSACTION with multiple SQL components and a single transaction.

2. Batch Commit transactions

By importing student information from the Students.txt file to update the STUDENTS1 table in the database , it is reasonable to use a batch submission database because there are many records that need to be modified.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/47/D1/wKiom1QAGPLwQJ5uAADDAxUMqh4453.jpg "style=" float: none; "title=" esproc_manage_database_2.jpg "alt=" Wkiom1qagplwqj5uaaddaxumqh4453.jpg "/>

A1: Defines a file object that holds student information in the file.

A2: Import file contents.

A3: Use student information in A2 to modify the contents of Student table 1 in bulk . This is how you can use bulk-commit SQL to avoid frequent database access. At the same time, the entire batch of data can be guaranteed to be successfully written or failed, that is, data consistency.

3. Program Control Affairs

below, we want to add a student, after inserting the data, to change the student's ID to 9. In order to ensure data consistency, insertions and modifications are successful before they can be submitted, or they will be rolled back.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/47/D3/wKioL1QAGgqQBmkxAADSOjhVgY8858.jpg "style=" float: none; "title=" esproc_manage_database_3.jpg "alt=" Wkiol1qaggqqbmkxaadsojhvgy8858.jpg "/>

A1: Connect to the database. Note that the Connect function uses the @e option, which returns an error message that is handled by the code behind itself when an error occurs. If you do not use this option, the database will be terminated directly by the collector program when it goes wrong.

A2: Executes the Insert SQL statement. Note thatexecute uses the @k option, which means that the transaction is not committed at the end of execution. If it is not used, the INSERT statement is submitted directly.

A3: Gets the last database operation, that is, the result of inserting the statement execution, if the Err variable is 0, then the execution succeeds, otherwise it is the error code.

A4: Determines whether the execution result of the Err variable is 0, and if 0 indicates that the previous INSERT statement has succeeded, perform the modify operation in B4.

C4: Gets The result of modifying SQL execution.

A5: Determine The Err variable, if it is 0, commit the database, or roll back.

A6: Closes the database connection.

Code examples for the management of database transactions by the collector