Use javascript steps and FIRETODB functions in kettle to define your own database queries

Use javascript steps and FIRETODB functions in kettle to define your own database queries

If you need to implement non-traditional database query operations. To discuss this scenario, if you need to read the regular table in the database, then check the number of field match expressions for each row entered.

Run a database query in a JavaScript step

When the JavaScript step is initialized, query the database. Gets the regular table-set of records. The input data loop of each row is then checked for matching expressions, assuming matching. Record variable plus one, and finally write the result into the new field on the move.

The idea is very clear, but how to implement database query in JavaScript steps?

Here is a very useful function firedb (), he needs to define the good one database connection in the conversion in advance as a parameter. and a SQL query statement, where SQL can only be a SELECT query statement, because the Firedb () function has no transaction or error handling. The return value is an array of record rows, each of which is an array with multiple fields.

Here is the code snippet. This code runs only once, preferably in initialization tab, which improves the efficiency of the 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 for each line to run;

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++;

}

}

To download the Demo sample Here, try the use of the Firedb () function, and demonstrate the SQL statement that demonstrates the sample data for MySQL in the sample example. In the code, "Scrapbook" is the name of the connection to the MySQL database as defined in the conversion.

Use javascript steps and FIRETODB functions in kettle to define your own database queries