DefaultSQLCron

Gruppe

Zeitgesteuerte eigene Klassen

Konfiguration

Wird konfiguriert im Business Connector (Eingangsagent Cron).

Beschreibung


In der Praxis tritt häufig das Problem auf, dass Daten aus verschiedenen Tabellen einer Datenbank ausgelesen werden sollen. Um diese Daten einfach einzulesen, gibt es eine Klasse, die dies einfach ermöglicht. Die Klasse DefaultSQLCron liest aus einer Konfigurationsdatei die auszuführenden SQL-Anweisungen und Abhängigkeiten zwischen den Tabellen aus.

Die auszuführenden SQL-Anweisungen müssen aufsteigend mit einem Namen select.<n> versehen werden. Die Nummern müssen lückenlos vergeben werden, die Nummerierung beginnt mit 0. Zwischen den SQL-Anweisungen werden Abhängigkeiten festgelegt. Diese Festlegungen werden in der Form dependencies.<n>=<s>:<m> getroffen, wobei s für den Index der SQL-Anweisung und m für den Index der Spalte innerhalb der Ergebnismenge und n für den Index der Abhängigkeit steht. Die Abhängigkeit dependencies.1= 0:2 legt fest, dass die SQL-Anweisung select.1 von der zweiten Spalte aus dem Ergebnis von select.0 abhängt. Hier können auch mehrere Spalten angegeben werden, also z. B. dependencies.1= 0:2,0:3.

Wichtiger Hinweis: Da es gerade das erste mal nicht ganz einfach ist die Konfigurationsdatei manuell zu erstellen, kann dies mit dem SQL-Konfigurations-Assistenten gemacht werden.

Hinweis: Die Quellstruktur für die Eingangsdatei kann im Menü der Quellstruktur erzeugt werden.

Parameterbeschreibung


Parameter

Beschreibung

db_alias

Dieser Parameter gibt an, auf welche Datenbank (Alias) zugegriffen werden soll. Siehe Abschnitt Datenbanken/Connectoren (Verwaltung). Dabei können auch Variablen von vorangegangenen Profilen, oder Parameterwerte von HTTP-Triggern verwendet werden. Zum Beispiel db_alias=@MSG_CALL_DB_ALIAS@.

pre.statement

Es können mehrere Pre-Statements formuliert werden. Diese werden durch ; getrennt.

post.statement

Es können mehrere Post-Statements formuliert werden. Diese werden durch ; getrennt.

save_as_csv

Durch Angabe eines Pfades kann eine CSV-Datei der Eingangsdaten erstellt werden.

result.split

(true, oder false) Bei true wird für jeden select.0 ein neuer Job erzeugt. Wirkt nur, wenn mindestens zwei Select-Statements (select.0 und select.1) und eine Abhängigkeit (dependencies.1) existieren. Hinweis: Hat man nur ein Select-Statement, dann kann man sich mit einem kleinen Trick behelfen. Einfach ein Dummy-Statement select.1 erzeugen, dessen Bedingung nie greift (damit keine zusätzliche Ergebniszeile erzeugt wird) und ein beliebiges dependencies.1. Beispiel:

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.<n>

Hier werden die Select-Statements festgelegt, wobei mit 0 gestartet wird und immer um 1 erhöht wird. Hinweis: Zeilenumbrüche können mit \ angegeben werden. Beispiel:

select.1=select 1 from dual \

where 1=2

dependencies.<n>

Hier werden die Abhängigkeiten der jeweiligen select.<n> hinterlegt.

limit.<n>

Kann ein Limit für die Anzahl an Zeilen der select.<n> bestimmen. Ein Limit von 0 bedeutet keine Einschränkung. Dies kann auch durch Weglassen der Beschränkung erreicht werden.

empty.<n>

Definiert ein Dummy-Ergebnis für select.<n>, falls select.<n> kein Ergebnis liefert.

MSG_CALL_<VARNAME>

Siehe Erläuterungen unten.

encode_blob

(true, oder false) Bei true werden BLOB/CLOB-Felder mit Base64 kodiert.

use_raw_content

Bei false (Default) wird das Resultset als CSV-Zeilen zurück geliefert. Nehmen wir an, wir hätten 4 Ergebnis-Spalten und 3 Ergebnis-Zeilen.

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

Bei true wird das gesamte Resultset in einer Zeile, ohne CSV-Trennzeichen, zurück geliefert.

T00112536988481190000ASMT00112563101269060000LobsterT00113049476314730000CONRAD

In den WHERE-Bedingungen der select.<n> werden Platzhalter eingesetzt, um die Abhängigkeiten festzulegen. Der Platzhalter @1:i@ wird durch die erste Spalte aus den zugehörigen Abhängigkeiten (dependencies) ersetzt, der Platzhalter @2:i@ durch die zweite Spalte. Das i im Platzhalter steht für den Datentyp, hier Integer. Siehe folgende Tabelle. Es können auch Werte von Variablen verwendet werden. Dabei kann es sich nur um Variablen von vorangegangenen Profilen, oder Parameterwerte von HTTP-Triggern handeln, zudem müssen die Variablen im Profil definiert sein. Wird in der Properties-Datei z. B. MSG_CALL_MYVAR=100 definiert, dann füllt der Werte der Variable den Platzhalter @100:i@. Wichtiger Hinweis: Die Platzhalter-Nummer für Variablen darf nicht kleiner 100 sein.


Kürzel

Datentyp

i

Integer

f

Float

r

Real

d

Date

t

Timestamp

s

String

l

Long

v

Boolean

b

Blob

x

Textstream

Hinweis: Es ist auch möglich den Zeitpunkt des letzten erfolgreichen Profilstarts, mittels eines Platzhalters in einer WHERE-Bedingung zu verwenden. Hierzu gibt es drei Möglichkeiten.


  • @0:t@ gibt den Zeitpunkt des letzten Profillaufs als Timestamp wieder.

  • @0:d@ gibt den Zeitpunkt des letzten Profillaufs als Date wieder.

  • @0:s#<template>@ gibt den Zeitpunkt als formatierten String wieder. Beispiel: @0:s#yyyyMMdd@ gibt 20150310 für den 10.3.2015 zurück. Wichtiger Hinweis: @0:s#<template>@ wird nur einmal aufgelöst. Eine weiteres Vorkommen von @0:s#<template>@ mit einem abweichenden Template hat keinen Effekt! Stattdessen wird bei weiteren Vorkommen der gecachte Wert des ersten Vorkommens verwendet.

Beispiel


Wichtiger Hinweis: Nehmen wir an wir hätten eine String-Variable MSG_CALL_MYVAR mit Inhalt 'USA','UK'. Verwenden Sie nun in der unten gezeigten Konfigurationsdatei die Definition MSG_CALL_MYVAR=100, dann würde die SQL-Abfrage SELECT id, name FROM supplier WHERE country IN (@100:s@) nicht funktionieren. Der Grund ist, dass dies nicht aufgelöst wird in SELECT id, name FROM supplier WHERE country IN ('USA','UK'), sondern in SELECT id, name FROM supplier WHERE country IN (''USA','UK''), da der Platzhalter ja einen String repräsentiert. Gelöst werden können solche Abfragen leider nur über die Dependencies, wie unten gezeigt. Der SQL-Konfigurations-Assistent wird Sie dabei unterstützen.


./conf/samples/sample_sql_cron.properties
#
# 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 exclusvie 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 seperated by ','.
#select.2 needs value (column at position 2) from statement 1 (select.1) for execution (join); multiple columns are seperated 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 delimeter ';'.
#An example for a 'dummy' result for select.0:
#empty.0=A;0;Nothing



Drei Tabellen (order, item, item_text) sollen ausgelesen werden. Die Tabellen sind jeweils durch 1:n Beziehungen verknüpft. Zu einem Auftrag (order) kann es beliebig viele Positionen (item) geben. Zu einer Position kann es beliebig viele Positionstexte (item_text) geben.

Die Tabelle order:


id

name

date_at

10

order_1

01.01.2014

11

order_2

01.01.2014

Die Tabelle item:


id

item_number

order_id

20

item_1

10

21

item_1

11

22

item_2

11

Die Tabelle item_text:


id

text

item_id

30

text_1

20

31

text_2

20

32

text_3

22

Das erste SQL-Statement select.0 wird ausgeführt und dabei die Variable @0:d@ durch das Datum des letzten Profildurchlaufs ersetzt. Nachfolgend das Ergebnis der Datenbank:


A

10

order_1

A

11

order_2

Mit der ersten Zeile


A

10

order_1

des Ergebnis von select.0 werden die dependencies.1 erstellt:


10

Mit den dependencies.1 wird select.1 ausgeführt:


B

20

item_1

Aus diesem Ergebnis werden die dependencies.2 erstellt:


20

Mit den dependencies.2 wird select.2 ausgeführt:


C

30

text_1

C

31

text_2

Dann werden die Ergebniszeilen zusammengefasst zum ersten Teilergebnis:


A

10

order_1

B

20

item_1

C

30

text_1

C

31

text_2

Analog dazu wird mit der zweiten Zeile


A

11

order_2

des Ergebnis aus select.0 verfahren.

Die dependencies.1 werden erstellt:


11

Dann liefert select.1:


B

21

item_1

B

22

item_2

Die dependencies.2 ergeben:


21

22

select.2 für die erste Zeile von dependencies.2 liefert kein Ergebnis. select.2 für die zweite Zeile von dependencies.2 liefert:


C

32

text_3

Auch diese Ergebniszeilen werden zu einem weiteren Teilergebnis zusammengefasst:


A

11

order_2

B

21

item_1

B

22

item_2

C

32

text_3

Aus den Teilergebnissen wird das Gesamtergebnis erstellt und an das Profil übergeben:


A

10

order_1

B

20

item_1

C

30

text_1

C

31

text_2

A

11

order2

B

21

item_1

B

22

item_2

C

32

text_3

Leerzeichen im Abfrage-Ergebnis (MSSQL/Informix)


Bei MSSQL- und Informix-Datenbanken werden die Ergebniswerte von SQL-Aufrufen per Default um voranstehende und nachfolgende Leerzeichen bereinigt (trimmed). Mit dem Parameter skipTrimResultValues kann in der Konfigurationsdatei des Datenbank-Aliases dieses Verhalten geändert werden.