Kettle uses javascript steps and fireToDB functions to implement custom database queries

Kettle uses javascript steps and fireToDB functions to implement custom database queries. Suppose you need to perform non-traditional database queries. To discuss this situation, we assume that you need to read the regular expressions in the database, then, check the number of matching expressions for each field in the input line. Execute database query in javascript steps in Objective C

Kettle uses javascript steps and fireToDB functions to implement custom database queries. Suppose you need to perform non-traditional database queries. To discuss this situation, we assume that you need to read the regular expressions in the database, then, check the number of matching expressions for each field in the input line. Execute database query in javascript steps in Objective C

Kettle uses javascript steps and fireToDB functions to implement custom database queries

Assume that you need to perform non-traditional database query operations. To discuss this scenario, we assume that you need to read the regular expressions in the database and then check the number of matching expressions in each field of the input line.

Execute database query in javascript steps

When the javascript step is initialized, query the database and obtain the record set of the regular expression. Then, check whether the expression matches the input data in each row. If the expression matches, record the variable plus one and write the result to the new field in the previous step. The idea is clear, but how does one implement database query in the javascript step?

Here we will introduce a very useful function fireDb (). It needs to define a database connection as a parameter and an SQL query statement parameter in advance during the conversion. Here the SQL statement can only be a select query statement, because the fireDb () function does not handle transactions or errors, the returned value is an array of record rows, and each row is an array with multiple fields.

The following is a code snippet. This code is executed only once. It is best to place it on the initialization tab to improve the execution efficiency of javascript steps.

// Read all rules from Scrapbook DB

Var xArr = fireToDB ("Scrapbook", "SELECT rule FROM rules ;");

Var regex = [];

// For each row, save the returned rulefor later use

For (var I in xArr ){

Regex. push (xArr [I] [0]);

}

The following code is executed on each line;

Var rules_matched = 0;

Var jstr = newjava. lang. String (string_to_test );

// Count matching rules

For (var I in regex ){

Varrule = regex [I];

If (jstr. matches (rule )){

Rules_matched ++;

}

}

Here you canDownload example, Try the use of the fireDb () function, the SQL statement of the sample data of mysql In the example. In the Code, "Scrapbook" is the name of the mysql database connection defined in the conversion.