How the collector operates on the database

The collector can read data from the database, write data to the database, or call the database's stored procedures. Based on these three basic operations, the collector can be applied to a variety of database-related scenario tasks:

1. Data Analysis and presentation tasks, the process is:

2. from other data sources (other databases , file, hdfs Nosql data source, Json unified processing of heterogeneous data

2, similar to ETL batch processing task.

The process and analysis show similar tasks, unlike the last step, where the data is not presented but written to other databases or other data sources.

3, the database for batch modification tasks.

One method is to read the data from this database, and then write back to the database, and another way is to use SQL or stored procedures to directly process the database data.

Let's look at some examples of three basic operations.

First, read the data from the database.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/B0/wKiom1QZJsvgo6x4AAGzqjgE34k383.jpg "style=" float: none; "title=" 2014-09-17_141248.jpg "alt=" Wkiom1qzjsvgo6x4aagzqjge34k383.jpg "/>

A1 demo Hsql database, a2 cell use SQL statement query employee A2 cell in this variable, arg1 is an externally-passed parameter. a3 cell closed database connection, a4 sql< Span style= "font-size:14px;font-family: ' The song Body '; > statement.

Second, write data to the database.

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/M02/49/B2/wKioL1QZJuXTW7cAAACvv3qaNGw141.jpg "style=" float: none; "title=" 2014-09-17_141258.jpg "alt=" Wkiol1qzjuxtw7caaacvv3qangw141.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 collector can also be used to batch update directly using the order table, for example: importing student information from the Students.txt file to update the STUDENTS1 table in the database , because there are many records that need to be modified, Therefore, it is reasonable to use batch submission database.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/B0/wKiom1QZJtDgyt1gAAC--YWt0CI058.jpg "style=" float: none; "title=" 2014-09-17_141311.jpg "alt=" Wkiom1qzjtdgyt1gaac--ywt0ci058.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.

The collector can also handle the situation where multiple SQL forms a full database transaction, such as: we want to add a student, after inserting the data, we want 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/M00/49/B2/wKioL1QZJuvjHop0AADNWlHqNpk372.jpg "style=" float: none; "title=" 2014-09-17_141319.jpg "alt=" Wkiol1qzjuvjhop0aadnwlhqnpk372.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.

Third, call the stored procedure.

For stored procedures that do not have a return parameter, the call method of the collector is relatively straightforward:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/B0/wKiom1QZJtSAoxuYAAAv6hmFqSE962.jpg "style=" float: none; "title=" 2014-09-17_141326.jpg "alt=" Wkiom1qzjtsaoxuyaaav6hmfqse962.jpg "/>

A1: Connect to the database.

A2: Call the stored procedure with an output parameter value of 4.

For stored procedures that return a result set, the collector can invoke this:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/B2/wKioL1QZJvHzPw9jAABq0AuGJpk344.jpg "style=" float: none; "title=" 2014-09-17_141335.jpg "alt=" Wkiol1qzjvhzpw9jaabq0augjpk344.jpg "/>

The A2 cell uses the proc function to call the stored procedure:Orac.proc ("{CALLPROAA (?,?)}",: 101: "O": a,:101: "O": b), returns two result sets (order table), Sets up a set of sequential tables: a sequence that assigns values to A1. The following explains in turn the input parameters of the proc rows:

1) SQL string

"{Call PROAA (?,?)}" contains the name of the calling stored procedure, and the question mark represents the SQL parameter.

2) output parameter 1

: 101: "O": an output parameter is defined in a,101 means that its data type is a cursor (see appendix for other types), and"O" means that the parameter is an output parameter. a is defined as a variable that can be used to refer back to the result.

3) output parameter 2

: 101: "O": an output parameter is defined in B,101 represents its data type as a cursor,"O" means that the parameter is an output parameter. b is to define a variable that can be used to refer back to the result.

cell A3 returns the First order table of cell A2 ( The result set of the EMP table).

The A4,A5 cells use the output variables a,B in the A2 to get the execution result of the stored procedure, anda corresponds to the data of the EMP table and assigns the value to A3,b corresponds to the data of the test table and assigns the value to A4.

Appendix: Parameter Type definitions

the value of type is:

Public final static byte Dt_default = (byte) 0; default, automatic recognition

Public final static byte Dt_int = (byte) 1;

Public final static byte Dt_long = (byte) 2;

Public final static byte Dt_short = (byte) 3;

Public final static byte Dt_bigint = (byte) 4;

Public final static byte dt_float = (byte) 5;

Public final static byte dt_double = (byte) 6;

Public final static byte Dt_decimal = (byte) 7;

Public finalstatic byte dt_date = (byte) 8;

Public final static byte Dt_time = (byte) 9;

Public final static byte Dt_datetime = (byte) 10;

Public final static byte dt_string = (byte) 11;

Public final static byte Dt_boolean = (byte) 12;

Public final static byte Dt_int_arr = (byte) 51;

Public final static byte Dt_long_arr = (byte) 52;

Public final static byte Dt_short_arr = (byte) 53;

Public final static byte dt_bigint_arr= (byte) 54;

Public final static byte Dt_float_arr = (byte) 55;

Public final static byte dt_double_arr= (byte) 56;

Public final static byte dt_decimal_arr= (byte) 57;

Public final static byte Dt_date_arr = (byte) 58;

Public final static byte Dt_time_arr = (byte) 59;

Public final static Bytedt_datetime_arr = (byte) 60;

Public final static byte dt_string_arr= (byte) 61;

Public final static byte Dt_byte_arr = (byte) 62;

Public final static byte Dt_cursor = (byte) 101;

Public final static bytedt_autoincrement = (byte) 102;

How the collector operates on the database