Configuration	Is configured in the Business Connector (Input Agent cron).
Class name	com.ebd.hub.datawizard.util.CrossSQLCron

Description

With this class, it is possible to define a separate database alias for every select statement. This allows the connection with different database systems.

Almost all parameters of the DefaultSQLCron can be used in the configuration file, but there are some differences:

Additional aliases of the form db_alias.<n> can be specified, which refer to the corresponding select statements select.<n>.
The splitting (result.split) also works if no dependencies are defined or if there is only one select statement.
An additional logging can be used (log.settings and log.filename).
The parameters pre.statement and post.statement cannot be used.

Example

./conf/samples/sample_CrossSQLCron.properties

# 
# Sample configuration file for CrossSQLCron
 
# All parameters (apart from pre.statement and post.statement) in the properties of DefaultSQLCron work as well. Additionally different db-aliases for the select statements, a logging
# and time profile tracing are implemented. The result.split now works for single selects too.
#
 
# Which database connection to use
# The default alias for select statements without an assigned dedicated alias.
db_alias = hub
# The dedicated alias for select.1
#db_alias.1 = otherDatabase
 
# This database agent allowes an additional logging for the performed statements and assigned value sets. Each line in the log 
# contains the relative start time in milliseconds from the start. One goal of this feature is to find performance leaks.
# 'log.settings = true' will start the logging feature.
#log.settings = true
 
# Parameter 'log.filename' controls whether the logging should be done by writing directly in a file or, if the parameter is omitted, by means
# of an LogManager. The name of the LogManager, if used, equals to the class name, i.e. 'CrossSQLCron'. If the CrossSQLCron is subclassed,
# the name of the LogManager is carried along the name of this subclass.
log.filename = /tmp/sqllog.txt
 
# If there are no dependencies defined, but you need the result of all statements within one result (record),
# set the following option to 'true'.
force_one_result = false
 
# Create CSV file for debugging - comment out if not needed.
save_as_csv=/tmp/sqldump.csv
 
# --- defining statements ---
# Here are the statements... must start with 0, step size is 1!
# 
# No specific alias defined -> use default (db_alias)
select.0 = select 'A', datim, profil, test, dateiname from logtable where datim > '2007-12-17‘
# Specific alias db_alias.1 defined -> use db_alias.1
select.1 = select 'B', datim, notiz from logtable where datim = @1:s@
# No specific alias defined -> use default (db_alias)
select.2 = select 'C', cp from mp_data_sub1 where id = @1:i@
#
 
# --- Defining dependencies ---
# select.1 needs value (column at position 2) from statement 0 (select.0) for execution (join); multiple columns are seperated by ','
# Offset is always 1 for counting column positions. First dependency references to @1:xxx@, etc.
# Note: To use the time of the last cron job run, use tag @0:d@ or @0:t@ for date or timestamp.
# 
dependencies.1 = 0:2
 
#
# 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 applied.
#
 
# If an SQL statement finds no values, you can define a dummy record instead.
# Define a valid CSV line. The delimeter is ;
# Example for 'dummy' record for select.0
#empty.0 = A;0;Nothing;No one;1.0
 
# Splitting the result means that for each result of select.0 a unique job is started. Unlike the DefaultSQLCron, the
# splitting here works even if no dependencies are defined or if there is only a single select statement. If there are multiple select 
# statements without dependencies, the results of all selects are appended and then this result is split line by line.
#result.split = true
 
#If inactive, profile encoding is used
#csv.encoding=8859_1

Blanks in query result (MSSQL/Informix)

In MSSQL and Informix databases, the result values of SQL calls are trimmed of preceding and following spaces by default. The "skipTrimResultValues" parameter can be used in the database alias configuration file to change this behaviour.