DefaultSQLCron

Description

Class name: com.ebd.hub.datawizard.util.DefaultSQLCron

DefaultSQLCron reads data from multiple database tables simultaneously. The class reads SQL statements and table dependencies from a configuration file.

Assign the SQL statements ascending names select.<n>. Use all numbers without gaps. Start at 0. Define dependencies between the SQL statements using the syntax dependencies.<n>=<s>:<m>:

• s is the index of the SQL statement.

• m is the index of the column within the result set.

• n is the index of the dependency.

Example: dependencies.1=0:2 defines that the SQL statement select.1 depends on the second column from the result set of select.0. To define multiple columns, separate them with commas, for example dependencies.1=0:2,0:3.

Configuration: in the Business Connector (Input Agent cron).

IMPORTANT Creating the configuration file manually for the first time is not easy. Use the SQL Wizard instead.

NOTE You can create the source structure for the input file from the source structure menu.

Parameters

result.split=true
select.0=select 'T001', date_at, order_no from orders where amount>10
select.1=select 1 from dual where 1=2
dependencies.1=0:2

select.1=select 1 from dual \
where 1=2

T001;1253698848119000;0;ASM
T001;1256310126906000;0;Lobster
T001;1304947631473000;0;CONRAD

T00112536988481190000ASMT00112563101269060000LobsterT00113049476314730000CONRAD

In the WHERE clauses of select.<n>, use placeholders to reference the dependencies. The placeholder @1:i@ replaces the first column from the associated dependencies. The placeholder @2:i@ replaces the second column. The i stands for the data type, here Integer. The following table lists all data types.

You can also use values from variables. Only variables from previous profiles or parameter values from HTTP triggers are allowed. The variables must be defined in the profile. Example: Define MSG_CALL_MYVAR=100 in the configuration file. The value of the variable then fills the placeholder @100:i@.

IMPORTANT The placeholder number for variables must not be smaller than 100.

You can also use the time of the last successful profile start in a WHERE clause. Three placeholders are available.

IMPORTANT @0:s#<template>@ is only resolved once. A further occurrence of @0:s#<template>@ with a different template has no effect. Instead, the system uses the cached value of the first occurrence. Examples for W3C-compliant format: @0:s#yyyyMMdd'T'HH:mm:ss.SSS@ returns e.g. 20260315T093045.123 for 15 March 2026 at 09:30:45.123. The short form @0:s# uses the system-internal W3C standard format.

Example

NOTE  You can also use system constants with the syntax %CONSTANT%.

IMPORTANT Suppose you have a string variable MSG_CALL_MYVAR with the content 'USA','UK'. In the configuration file shown below, you define MSG_CALL_MYVAR=100. The SQL query SELECT id, name FROM supplier WHERE country IN (@100:s@) does not work.

The reason: the placeholder represents a string. The query does not resolve to SELECT id, name FROM supplier WHERE country IN ('USA','UK'). Instead, it resolves to SELECT id, name FROM supplier WHERE country IN (''USA','UK'').

You can only solve such queries with dependencies, as shown below. The SQL Wizard helps you with this.

# 
# Sample config file for DefaultSQLCron
#
 
#Which db connection to use
db_alias=hub
 
#You can define multiple pre- and post-statements here (separated by ";"), like:
#pre.statement=lock table table_a in exclusive mode;lock table_b in exclusive mode
#post.statement=
 
#Create CSV file for debugging - uncomment if needed.
#save_as_csv=/tmp/sqldump.csv
 
#For each row of select.0 create a new request (job) if param is set to true.
result.split=false
 
#If BLOB columns are selected, you may want to encode the content with Base64.
#encode_blob=true
 
#
#Here are the statements... must start with 0, step size is 1!
# 
select.0=select 'A',id,name from order where date_at > @0:d@
select.1=select 'B',id,item_number from item where order_id = @1:i@
select.2=select 'C',id,text from item_text where item_id = @1:i@
#
#select.1 needs value (column at position 2) from statement 0 (select.0) for execution (join); multiple columns are separated by ','.
#select.2 needs value (column at position 2) from statement 1 (select.1) for execution (join); multiple columns are separated by ','.
#Offset for counting column positions is always 1. First dependency references to @1:xxx@, etc.
#Note: To use the time of the last time a cron job was executed, use tag @0:d@ or @0:t@ for date or timestamp.
#
dependencies.1=0:2
dependencies.2=1:2
#
#(Passed) variables can be used as well, if defined in the profile.
MSG_CALL_MYVAR=100
# This will replace filler @100:i@ in any SQL select statement by the integer value of the named variable.
#
#
#Use limit.xxx to limit the result set, e.g.
#limit.0 = 500
#will limit select.0 to 500 rows. If not set, no limit is given.
 
#If an SQL statement returns no results, you can define a dummy result instead.
#Define a valid CSV line with delimiter ';'.
#An example for a 'dummy' result for select.0:
#empty.0=A;0;Nothing

You want to read three tables: order, item, and item_text. The tables are linked by 1:n relations. An order (order) can have any number of items (item). An item can have any number of item texts (item_text).

The order table:

The item table:

The item_text table:

The system executes the first SQL statement select.0. It replaces the variable @0:d@ with the date of the last profile run. The database returns the following result:

The first row

of the result set of select.0 creates the dependencies.1:

The system runs select.1 with the dependencies.1:

From this result set, the system creates the dependencies.2:

The system runs select.2 with the dependencies.2:

The system combines the result rows into the first result subset:

The system processes the second row

of the result set of select.0 in the same way.

The dependencies.1 become:

Then select.1 returns:

The dependencies.2 become:

select.2 returns no result for the first row of dependencies.2. For the second row of dependencies.2, select.2 returns:

The system combines these result rows into a second result subset:

The system combines the result subsets into the overall result set and passes it to the profile:

Blanks in query results (MSSQL/Informix)

In MSSQL and Informix databases, the system trims leading and trailing spaces from SQL result values by default. Use the skipTrimResultValues parameter to change this behavior. Set the parameter in the configuration file of the database alias.