XML-Querys im Database Connector: Struktur und Beispiele

Struktur von XML-Querys im Database Connector

Die grundlegende Struktur ist für alle XML-Querys identisch:

Groß- und Kleinschreibung wird in den folgenden Tags unterstützt:

<table> <tableNames> <tableName>
<fields> <field> <fieldName>
<conditions> <condition> <leftValue>
<conditions> <condition> <rightValue> (wenn ein Spaltenname als rightValue verwendet wird)

Groß- und Kleinschreibung wird in den folgenden Tags NICHT unterstützt:

<group> <field> <fieldName>
Spaltennamen, die im <having>-Tag im HAVING-Ausdruck verwendet werden.
<sortOrders> <sort>

<queries>

Umschließendes Element für ein Set von SQL-Statements.

<query>

Initiale Definition eines SQL-Statements. Kann mehrfach auftreten, z.B. um mehrere SQL-Anweisungen innerhalb einer Abfrage abzusenden

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`type`	Ja	select insert update delete call updateOrInsert`	Art des SQL-Statements
`properties`	Nein	`force`	Mit `force` wird das SQL-Statement, das im Element `<value>` angegeben ist, direkt ausgeführt. Alle weiteren Angaben werden ignoriert.
`forceResult`	Nein	`true` `false`	SELECT-Anweisungen, die mit SELECT beginnen, werden automatisch erkannt. In allen anderen Fällen (WITH, Stored-Procedure) kann die Rückgabe eines ResultSets über "forceResult" =` true `erzwungen werden.
`queryID`	Nein		Wird in Abhängigkeit vom gewählten `type` im `ResultSet` mit dem Wert der zugehörigen Query gefüllt. Gilt für `type = select`.

type

select
insert
update
delete
call
updateOrInsert`

Art des SQL-Statements

properties

Nein

force

Mit force wird das SQL-Statement, das im Element <value> angegeben ist, direkt ausgeführt. Alle weiteren Angaben werden ignoriert.

forceResult

Nein

true
false

SELECT-Anweisungen, die mit SELECT beginnen, werden automatisch erkannt.
In allen anderen Fällen (WITH, Stored-Procedure) kann die Rückgabe eines ResultSets über "forceResult" =` true `erzwungen werden.

queryID

Nein

Wird in Abhängigkeit vom gewählten type im ResultSet mit dem Wert der zugehörigen Query gefüllt. Gilt für type = select.

<value>

Zur direkten Übergabe eines SQL-Statements. Dazu muss im Element <query> das Attribut properties den Wert force haben. Alle weiteren Angaben werden ignoriert.

<tables>

Einleitendes XML-Element für die Definition eines SQL-Statements.

<table>

Einleitendes XML-Element für ein einzelnes SQL-Statement. Kann mit dem Attribut type="subselect" als Subquery in einer Hauptabfrage verwendet werden und mehrfach auftreten.

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`alias`	Nein	Beliebiger Bezeichner
`type`	Nein	`subselect`	Gibt an, dass es sich um eine Subquery handelt

alias

Nein

Beliebiger Bezeichner

type

Nein

subselect

Gibt an, dass es sich um eine Subquery handelt

<tableNames>

Umschließendes XML-Element für alle Tabellennamen, die im SQL-Statement abgefragt werden.

<tableName>

Tabellenname, der im SQL-Statement abgefragt wird. Kann mehrfach auftreten. Entspricht dem FROM-Teil eines SQL-Statements.

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`alias`	Nein	Beliebiger Bezeichner	Alias-Name der Tabelle

alias

Nein

Beliebiger Bezeichner

Alias-Name der Tabelle

<fields>

Umschließendes XML-Element für alle im SQL-Statement zu verwendenden Spaltennamen.

<field>

XML-Element für den Namen der im SQL-Statement abgefragten Tabellenspalte. Kann mehrfach auftreten. Entspricht dem Select-Teil eines SQL-Statements.

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`alias`	Nein	Beliebiger Bezeichner	Alias-Name der Spalte.

alias

Nein

Beliebiger Bezeichner

Alias-Name der Spalte.

<fieldName>

Name einer im SQL-Statement zu verwendenden Tabellenspalte.

<fieldValue>

Werte für einzelne Felder oder der Name einer Subquery. (nur INSERT, UPDATE)

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`type`	Nein	`subselect` `force`	`subselect`: Zum Ausführen einer Subquery `force`: Die automatische Typerkennung wird für dieses Feld ausgeschaltet. Der Text von `<fieldValue>` wird direkt an die Datenbank übergeben. Z. B. sinnvoll, um Datenbankfunktionen wie AVG oder MAX ausführen zu lassen.

type

Nein

subselect
force

subselect: Zum Ausführen einer Subquery
force: Die automatische Typerkennung wird für dieses Feld ausgeschaltet. Der Text von <fieldValue> wird direkt an die Datenbank übergeben. Z. B. sinnvoll, um Datenbankfunktionen wie AVG oder MAX ausführen zu lassen.

<conditions>

(nicht für INSERT)

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`type`	Ja	AND OR NOT AND NOT OR NOT	Operator zur Verknüpfung mit den Kriterien im vorausgehenden `<conditions>`- bzw. `<condition>`-Element gleicher Ebene. Standard ist AND.

type

AND
OR
NOT
AND NOT
OR NOT

Operator zur Verknüpfung mit den Kriterien im vorausgehenden <conditions>- bzw. <condition>-Element gleicher Ebene. Standard ist AND.

Umschließendes XML-Element für die WHERE-Bedingung eines SQL-Statements.
Mehrere <conditions>-Elemente können geschachtelt werden, um Statements zu gruppieren oder zu klammern.

module guide 1010 0

<condition>

Werte für ein Kriterium in der WHERE-Bedingung eines SQL-Statements. Kann mehrfach auftreten.

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`type`	Ja	AND OR NOT AND NOT OR NOT	Operator zur Verknüpfung mit den Kriterien im vorausgehenden `<conditions>`- bzw. `<condition>`-Element gleicher Ebene. Standard ist AND.

type

AND
OR
NOT
AND NOT
OR NOT

Operator zur Verknüpfung mit den Kriterien im vorausgehenden <conditions>- bzw. <condition>-Element gleicher Ebene. Standard ist AND.

<leftValue>

Spaltenname auf der linken Seite eines Kriteriums in einer WHERE-Bedingung.

<operation>

Operator innerhalb eines Kriteriums in einer WHERE-Bedingung, z.B. =, <, >, >=, ⇐, IN, NOT IN …

<rightValue>

Enthält den Wert auf der rechten Seite eines Kriteriums in einer WHERE-Bedingung. Dabei kann es sich um einen Wert, um einen Spaltennamen oder um den Namen eines Subselect handeln.

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`type`	Nein	`subselect` `force`	`subselect`: Zum Ausführen einer Subquery `force`: Die automatische Typenerkennung wird für dieses Feld ausgeschaltet. Der Text von `<rightValue>` wird direkt an die Datenbank übergeben. Ist z.B. sinnvoll, um eingebaute Datenbankfunktionen AVG oder MAX auszuführen.
`isValue`	Nein	`true` `false`	`true`: `rightValue` wird als ein Wert betrachtet. `false`: `rightValue` wird als ein Spaltenname betrachtet. Dies ist der Standardwert.

type

Nein

subselect
force

subselect: Zum Ausführen einer Subquery
force: Die automatische Typenerkennung wird für dieses Feld ausgeschaltet. Der Text von <rightValue> wird direkt an die Datenbank übergeben. Ist z.B. sinnvoll, um eingebaute Datenbankfunktionen AVG oder MAX auszuführen.

isValue

Nein

true
false

true: rightValue wird als ein Wert betrachtet.
false: rightValue wird als ein Spaltenname betrachtet. Dies ist der Standardwert.

<sortOrders>

(nur für SELECT)

Sortiert die Ausgaben nach einem Feld auf- oder absteigend.

<sort>

Feld, das als Sortierkriterium verwendet wird

Attribute Pflichtfeld Zulässige Werte Erläuterung

Attribute	Pflichtfeld	Zulässige Werte	Erläuterung
`order`	Nein	`ASC` - aufsteigend `DESC` - absteigend	Sortierreihenfolge

order

Nein

ASC - aufsteigend
DESC - absteigend

Sortierreihenfolge

<tail>

Der Wert des Elements wird an die erzeugte SQL-Anfrage angehängt. Damit können Datenbank-spezifische SQL Erweiterungen hinzugefügt werden.

Die folgende Abbildung zeigt ein SELECT als Baumstruktur:

module guide 1012 0

Groß- und Kleinschreibung in Datenbankanfragen berücksichtigen

Die Tabellen- und Feldnamen mit Berücksichtigung der Groß- und Kleinschreibung (mit Anführungszeichen erstellten Tabellen- und Feldnamen) müssen den folgenden Standards in XML entsprechen, damit sie fehlerfrei funktionieren:

Wenn sie in XML mit Anführungszeichen oder mit dem force-Attribut versehen werden, müssen sie in Groß- und Kleinschreibung wie in der Datenbank sein, unabhängig davon, ob die Option Automatische Typenerkennung der Datenbankobjekte im Dialog Datenbankverbindungen und Query-Typ aktiviert ist oder nicht.
Wenn sie in XML ohne Anführungszeichen versehen werden, muss die Option Automatische Typenerkennung der Datenbankobjekte im Dialog Datenbankverbindungen und Query-Typ aktiviert sein.
Für Systemtabellen sollten Großbuchstaben ohne Anführungszeichen verwendet werden und die Option Automatische Typenerkennung der Datenbankobjekte im Dialog Datenbankverbindungen und Query-Typ deaktiviert sein.

Mit Anführungszeichen erzeugte Tabelle und Felder

XML-Anfrage für die Tabelle Test mit zwei Spalten Col1 und Col2. Automatische Typerkennung der Datenbankobjekte kann aktiviert oder deaktiviert sein.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>"Test"</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>"Test"."Col1"</fieldName>
                    </field>
                    <field>
                        <fieldName>"Test"."Col2"</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Ohne Anführungszeichen erzeugte Tabelle und Felder

XML-Anfrage für die Tabelle test mit zwei Spalten col1 und col2. Automatische Typerkennung der Datenbankobjekte muss aktiviert sein.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>test</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>test.col1</fieldName>
                    </field>
                    <field>
                        <fieldName>test.col2</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Eine Systemtabelle anfragen

Mit dieser XML-Anfrage greifen Sie auf die Systemtabelle DUAL zu. Automatische Typerkennung der Datenbankobjekte muss deaktiviert sein.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>DUAL</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>*</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Select: Daten anzeigen mit XML-Querys

SQL

SELECT name, address, phone, fax
FROM addresses
WHERE name = "Georg Miller"
ORDER BY name ASC, address DESC

XMLQuery

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>name</fieldName>
                    </field>
                    <field>
                        <fieldName>address</fieldName>
                    </field>
                    <field>
                        <fieldName>phone</fieldName>
                    </field>
                    <field>
                        <fieldName>fax</fieldName>
                    </field>
                </fields>
                <conditions>
                    <condition>
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Georg Miller</rightValue>
                    </condition>
                    <condition type="OR">
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Bob Brown</rightValue>
                    </condition>
                </conditions>
                <sortOrders>
                    <sort>name</sort>
                    <sort order="DESC">address</sort>
                </sortOrders>
            </table>
        </tables>
    </query>
</queries>

Select Distinct im Database Connector

Mit einem SELECT DISTINCT werden alle unterschiedlichen Werte einer Tabelle zurückgegeben.

SQL

SELECT DISTINCT abteilung
FROM mitarbeiter

XMLQuery

Um ein SELECT DISTINCT zu formulieren, haben Sie zwei Möglichkeiten:

DISTINCT beim Spaltennamen angeben:

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>Mitarbeiter</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>DISTINCT Abteilung</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Mit dem Attribut properties="force" die Abfrage direkt übergeben:

<queries>
    <query type="select" properties="force">
        <value>SELECT DISTINCT Abteilung FROM Mitarbeiter</value>
    </query>
</queries>

Daten aus verschiedenen Tabellen: Join

Ein Join ist eine Abfrage, die Datensätze aus zwei oder mehr verschiedenen Tabellenspalten zueinander in Beziehung setzt, z.B.:

SQL

SELECT *
FROM Gartenmoebel, Kategorien
WHERE Gartenmoebel.KategorieNr = Kategorien.Kategorienummer

XMLQuery

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>Gartenmoebel</tableName>
                    <tableName>Kategorien</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>*</fieldName>
                    </field>
                </fields>
                <conditions>
                    <condition>
                        <leftValue>Gartenmoebel.KategorieNr</leftValue>
                        <operation>=</operation>
                        <rightValue>Kategorien.Kategorienummer</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

Datensätze im Database Connector einfügen: Insert

Um einer Tabelle einen Datensatz hinzuzufügen, verwenden Sie INSERT.

Um Datensätze mit einer Bulk-Operation einzufügen, verwenden Sie ein Prepared-Statement, siehe Prepared Statement in einer Abfrage.

SQL

INSERT INTO addresses (name, address, phone, fax)
VALUES ("Georg Miller", "Hauptstrasse 7", "253263", "253264")

XMLQuery

<queries>
    <query type="insert">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>name</fieldName>
                        <fieldValue>Georg Miller</fieldValue>
                    </field>
                    <field>
                        <fieldName>address</fieldName>
                        <fieldValue>Hauptstrasse 7</fieldValue>
                    </field>
                    <field>
                        <fieldName>phone</fieldName>
                        <fieldValue>253263</fieldValue>
                    </field>
                    <field>
                        <fieldName>fax</fieldName>
                        <fieldValue>253264</fieldValue>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Datensätze im Database Connector aktualisieren: Update

Um bestehende Datensätze in einer Tabelle zu aktualisieren, verwenden Sie UPDATE.

Um Datensätze mit einer Bulk-Operation einzufügen, verwenden Sie ein Prepared-Statement, siehe Prepared Statement in einer Abfrage.

SQL

UPDATE addresses
SET address = "Hauptstrasse 8"
WHERE name = "Georg Miller"

XMLQuery

<queries>
    <query type="update">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>address</fieldName>
                        <fieldValue>Hauptstrasse 8</fieldValue>
                    </field>
                </fields>
                <conditions>
                    <condition>
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Georg Miller</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

UpdateOrInsert im Database Connector

Dieses SQL-Statement bildet die SQL-Statements Insert und Update ab. Damit wird versucht, einen Datensatz zu aktualisieren (Update); falls dies nicht möglich ist, weil der Datensatz noch nicht vorhanden ist, wird der Datensatz eingefügt (Insert). Das Update wird zuerst ausgeführt, weil es weniger zeitintensiv ist als das Insert.

<queries>
    <query type="updateOrInsert">
        <tables>
            <table>
                <tableNames>
                    <tableName>OrderTable</tableName>
                </tableNames>
                <fields>
                    <field skip="update">
                        <fieldName>OrderNr</fieldName>
                        <fieldValue>Kunde11</fieldValue>
                    </field>
                    <field>
                        <fieldName>Kunde</fieldName>
                        <fieldValue>123</fieldValue>
                    </field>
                    <field>
                        <fieldName>Text1</fieldName>
                        <fieldValue>info1</fieldValue>
                    </field>
                    <field>
                        <fieldName>Text2</fieldName>
                        <fieldValue>info2</fieldValue>
                    </field>
                </fields>
                <conditions>
                    <condition type="OR">
                        <leftValue>OrderNr</leftValue>
                        <operation>=</operation>
                        <rightValue>Kunde11</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

Datensätze im Database Connector löschen: Delete

Zum Löschen von Datensätzen verwenden Sie Delete. Im folgenden Beispiel werden alle Daten von Georg Miller gelöscht.

SQL

DELETE
FROM addresses
WHERE name="Georg Miller"

XMLQuery

<queries>
    <query type="delete">
        <tables>
            <table>
                <tableNames>
                    <tableName>addresses</tableName>
                </tableNames>
                <conditions>
                    <condition>
                        <leftValue>name</leftValue>
                        <operation>=</operation>
                        <rightValue>Georg Miller</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
</queries>

Mehrere SQL-Statements in einer Abfrage

XML-Query

Dynamische XML-Querys können mehrere Statements enthalten:

<queries>
    <query type="insert">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>id</fieldName>
                        <fieldValue>1</fieldValue>
                    </field>
                    <field>
                        <fieldName>meldung</fieldName>
                        <fieldValue>
                            spießbraten köcheln
                        </fieldValue>
                    </field>
                    <field>
                        <fieldName>datum</fieldName>
                        <fieldValue>
                            2003-03-20 10:20:00.000000000
                        </fieldValue>
                    </field>
                    <field>
                        <fieldName>wert</fieldName>
                        <fieldValue>10.12</fieldValue>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>*</fieldName>
                    </field>
                </fields>
                <conditions>
                    <condition type="OR">
                        <leftValue>id</leftValue>
                        <operation>=</operation>
                        <rightValue>1</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
    <query type="update">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>id</fieldName>
                        <fieldValue>2</fieldValue>
                    </field>
                </fields>
                <conditions>
                    <condition type="OR">
                        <leftValue>id</leftValue>
                        <operation>=</operation>
                        <rightValue>1</rightValue>
                    </condition>
                </conditions>
            </table>
        </tables>
    </query>
    <query type="delete">
        <tables>
            <table>
                <tableNames>
                    <tableName>eins</tableName>
                </tableNames>
            </table>
        </tables>
    </query>
</queries>

SQL-Statements im Database Connector direkt übergeben: Force

XMLQuery

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="ISO-8859-1"/>
    <xsl:template match="/">
        <queries>
            <query type="select" properties="force">
                <value>
                    create table trade_user ( id_user NUMBER(20,0) PRIMARY KEY, since_user NUMBER(15,0),
                    lastlogin_user NUMBER(15,0), sex_user NUMBER(1,0), language_user VARCHAR2(5), title_user
                    VARCHAR2(4000), firstname_user VARCHAR2(200), lastname_user VARCHAR2(200), company_user
                    NUMBER(20,0), function_user VARCHAR2(4000), street_user VARCHAR2(4000), zip_user
                    VARCHAR2(4000), land_user VARCHAR2(4000), city_user VARCHAR2(4000), phone_user
                    VARCHAR2(4000), mobile_user VARCHAR2(4000), fax_user VARCHAR2(4000), email_user
                    VARCHAR2(200) NOT NULL UNIQUE, web_user VARCHAR2(4000), via_user VARCHAR2(4000),
                    enable_user NUMBER(2,0), comment_user VARCHAR2(4000), abbo_news_user NUMBER(1,0),
                    abbo_mail_user NUMBER(1,0), keyaccount_user NUMBER(20,0), lastforum_user NUMBER(20,0),
                    contentarea_user VARCHAR2(4000), deliveryplace_user VARCHAR2(4000) DEFAULT '1',
                    originplace_user VARCHAR2(4000) DEFAULT '1', sms_mail_user VARCHAR2(1900),
                    customer_protection_user NUMBER(1,0) )
                </value>
            </query>
            <query type="select" properties="force">
                <value>
                    insert into xtrade_user( id_user, firstname_user, lastname_user, company_user,
                    email_user ) values (1,'Hans','Müller',1,'h.mueller@karstadt.de')
                </value>
            </query>
            <query properties="force">
                <value>DROP TABLE "ORATEST"."TESTTABLE"</value>
            </query>
        </queries>
    </xsl:template>
</xsl:stylesheet>

Prepared Statement in einer Abfrage

Sie können ein Prepared Statement verwenden, um beliebige Abfragen ohne das Risiko von SQL-Injection-Attacken auszuführen.

XMLQuery

<queries>
    <query type="preparedStatement">
        <preparedStatement>SELECT NAME FROM TESTTABLE WHERE ID = ? </preparedStatement>
        <params>
            <paramValue>10</paramValue>
        </params>
    </query>
</queries>

In diesem Fall kann ein preparedStatement nur für SELECT-, INSERT-, UPDATE- und DELETE-Abfragen ausgeführt werden.

Ausführung mehrerer Abfragen mit einem Prepared Statement

Sie können ein Prepared Statement verwendet, um mehrere beliebige Abfragen auszuführen.

XMLQuery

<queries>
    <query type="preparedStatement">
        <preparedStatement>INSERT INTO TESTTABLE(id, name) VALUES(?,?)</preparedStatement>
        <params>
            <paramValue>10</paramValue>
            <paramValue>test</paramValue>
        </params>
        <params>
            <paramValue>20</paramValue>
            <paramValue>test2</paramValue>
        </params>
    </query>
</queries>

In diesem Fall kann ein preparedStatement nur für INSERT-, UPDATE- und DELETE-Abfragen ausgeführt werden.

Mehrere Prepared Statements in einem XML

Sie können mehrere Prepared Statement Abfragen in demselben XML verwenden.

XMLQuery

<queries>
    <query type="preparedStatement">
        <preparedStatement>INSERT INTO TESTTABLE(id, name) VALUES(?,?)</preparedStatement>
        <params>
            <paramValue>10</paramValue>
            <paramValue>test</paramValue>
        </params>
    </query>
    <query type="preparedStatement">
        <preparedStatement>SELECT NAME FROM TESTTABLE WHERE ID = ? </preparedStatement>
        <params>
            <paramValue>10</paramValue>
        </params>
    </query>
</queries>

In diesem Fall kann ein preparedStatement nur für SELECT-, INSERT-, UPDATE- und DELETE-Abfragen ausgeführt werden.

Subqueries mit SubSelects im Database Connector

Sie können SQL-Abfragen verschachteln, um das Ergebnis eines SubSelects in demselben Select auszuwerten.

SQL

SELECT LieferscheinId , ErstellungsDatum
FROM LieferscheinTable
WHERE
        factory=(SELECT factory FROM SpeditionsTable
                 WHERE status!=X AND TransportId=10000037)
  AND gate=(SELECT gate FROM SpeditionsTable
WHERE status!=X AND TransportId=10000037 )
  AND status = F

XMLQuery

Für jedes SubSelect wird ein zusätzliches table-Element mit den Attributen type="subselect" und alias=[NameDesSubselects] erstellt. Dieses table-Element muss vor dem Haupt-Select stehen.

Das Ergebnis des SubSelects wird in das Haupt-Select in field und/oder condition-Elementen über das Attribut type="subselect" eingebunden, dabei ist der Wert des field und/oder condition-Elements dann der Name des SubSelects:

<tables>
    <table type="subselect" alias="subFactory">
        <tableNames>
            <tableName>SpeditionsTable</tableName>
        </tableNames>
        <fields>
            <field>
                <fieldName>factory</fieldName>
            </field>
        </fields>
        <conditions>
            <condition type="AND">
                <leftValue>status</leftValue>
                <operation>!=</operation>
                <rightValue>X</rightValue>
            </condition>
            <condition type="AND">
                <leftValue>TransportId</leftValue>
                <operation>=</operation>
                <rightValue>
                    <xsl:value-of select="FormPrepare/
            Response/Panel/Transporte/SLB-Nr"/>
                </rightValue>
            </condition>
        </conditions>
    </table>
    <table type="subselect" alias="subGate">
        <tableNames>
            <tableName>SpeditionsTable</tableName>
        </tableNames>
        <fields>
            <field>
                <fieldName>gate</fieldName>
            </field>
        </fields>
        <conditions>
            <condition type="AND">
                <leftValue>status</leftValue>
                <operation>!=</operation>
                <rightValue>X</rightValue>
            </condition>
            <condition type="AND">
                <leftValue>TransportId</leftValue>
                <operation>=</operation>
                <rightValue>
                    <xsl:value-of select="FormPrepare/
          Response/Panel/Transporte/SLB-Nr"/>
                </rightValue>
            </condition>
        </conditions>
    </table>
    <table>
        <tableNames>
            <tableName>LieferscheinTable</tableName>
        </tableNames>
        <fields>
            <field>
                <fieldName>LieferscheinId</fieldName>
            </field>
            <field>
                <fieldName>ErstellungsDatum</fieldName>
            </field>
        </fields>
        <conditions>
            <condition type="AND">
                <leftValue>factory</leftValue>
                <operation>=</operation>
                <rightValue type="subselect">
                    subFactory
                </rightValue>
            </condition>
            <condition type="AND">
                <leftValue>gate</leftValue>
                <operation>=</operation>
                <rightValue type="subselect">
                    subGate
                </rightValue>
            </condition>
            <condition type="AND">
                <leftValue>status</leftValue>
                <operation>=</operation>
                <rightValue>F</rightValue>
            </condition>
        </conditions>
    </table>
</tables>

Stored Procedures im Database Connector aufrufen

In einer Stored Procedure können ganze Abfolgen von Anweisungen unter einem Namen gespeichert, auf dem Datenbankserver zur Verfügung gestellt und ausgeführt werden.

Das folgende Beispiel illustriert den Aufruf von Stored Procedures mithilfe des Elements <method name="[NAME]"> mit und ohne Parameterübergabe.

XML-SQL

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" encoding="ISO-8859-1"/>
    <xsl:template match="/">
        <queries>
            <query type="call">
                <method name="TESTPROC_WITHOUTPARAM"/>
            </query>
            <query type="call">
                <method name="TESTPROC_IN">
                    <parameter direction="IN" type="DECIMAL">2</parameter>
                </method>
            </query>
            <query type="call">
                <method name="TESTPROC_OUT">
                    <parameter direction="OUT" type="VARCHAR"/>
                </method>
            </query>
            <query type="call">
                <method name="TESTPROC_INOUT">
                    <parameter direction="INOUT" type="NUMERIC">4</parameter>
                </method>
            </query>
            <query type="call">
                <method name="TESTPROC_INOUT2">
                    <parameter direction="IN" type="NUMERIC">5</parameter>
                    <parameter direction="OUT" type="NUMERIC">5</parameter>
                </method>
            </query>
            <query type="call">
                <method name="TESTFUNC_OUT">
                    <result type="DATE"/>
                </method>
            </query>
            <query type="call">
                <method name="TESTFUNC_INOUT">
                    <parameter type="NUMERIC" direction="IN">2</parameter>
                    <result type="NUMERIC"/>
                </method>
            </query>
        </queries>
    </xsl:template>
</xsl:stylesheet>

Benutzung des Name-Attributs

Wird kein name-Attribut für die Parameter in der Query benutzt, dann muss die Parameterreihenfolge exakt der in der Query definierten entsprechen.

Beispiel:

<query type="call">
    <method name="TESTPROC_INOUT2">
        <parameter direction="IN" type="NUMERIC">5</parameter>
        <parameter direction="IN" type="VARCHAR">5</parameter>
        <parameter direction="OUT" type="NUMERIC">5</parameter>
    </method>
</query>

Es wird dringend empfohlen, bei Stored Procedures und Funktionen das optionale name-Attribut zu verwenden. Eine Änderung der Parameterreihenfolge hat dann keine Auswirkungen auf den Workflow, da dann die Parameterwerte anhand des Parameternamens an die Prozedur übergeben werden.

Beispiel mit optionalem name-Attribut:

<query type="call">
    <method name="TESTPROC_INOUT2">
        <parameter direction="IN" name="MyNumericParam" type="NUMERIC">5</parameter>
        <parameter direction="IN" name="MyVarcharParam" type="VARCHAR">5</parameter>
        <parameter direction="OUT" type="NUMERIC">5</parameter>
    </method>
</query>

Nur für Oracle: Das Attribut UseDefault zum Aufrufen von Oracle-Funktionen verwenden

Zum Aufrufen von Oracle-Funktionen, deren Eingangsparameter einen Standardwert haben, müssen Sie im method tag das Attribut useDefault=true verwenden. Das Attribut useDefault ist kein Pflichtparameter und auf false gesetzt, wenn es nicht angegeben ist.

Das Attribut useDefault wird nur für Oracle-Funktionen unterstützt. Wenn es verwendet wird, muss in allen <parameter>-Tags das Attribut name angegeben werden, anderenfalls kann ein Fehler auftreten.

Beispiel

<query type="call">
    <method name="GET1" useDefault="true">
        <parameter type="VARCHAR" name="P1" direction="IN">a</parameter>
        <parameter type="VARCHAR" name="P3" direction="OUT"></parameter>
        <result type="NUMERIC"/>
    </method>
</query>

Unterstützung der ResultSet-Ausgabe (nur für MS SQL Server)

Soll eine Stored-Procedure, die ein ResultSet zurückliefert, von einem Database Connector aufgerufen werden, muss der folgende Tag in der Eingangs-XML-Struktur verwendet werden.

Beispiel:

<queries>
    <query type="call">
        <method name="Test">
            <result type="RESULTSET"/>
        </method>
    </query>
</queries>

TO_TIMESTAMP-Funktion in Oracle-Datenbanken verwenden

In Oracle-Datenbanken können Sie die TO_TIMESTAMP-Funktion sowohl in SELECT-, INSERT-, UPDATE- und INSERT- oder UPDATE-Query-Typen als auch in Bedingungen in WHERE-Klauseln nutzen.

SELECT: TO_TIMESTAMP-Funktion verwenden

SQL-Query

SELECT ID, MESSAGE, TO_TIMESTAMP(DATE_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF'), TEST_DATE
FROM TABLE_TIMESTAMP

XML-Format

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">
                    <fieldName/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
            <group>
                <field>
                    <fieldName/>
                </field>
                <expression/>
            </group>
            <having>
                <expression/>
            </having>
            <sortOrders>
                <sort order="ASC"/>
            </sortOrders>
            <tail/>
        </table>
    </tables>
</query>

Beispiel

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>ID</fieldName>
                </field>
                <field>
                    <fieldName>MESSAGE</fieldName>
                </field>
                <field>
                    <fieldName function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">DATE_TIMESTAMP</fieldName>
                </field>
                <field>
                    <fieldName>TEST_DATE</fieldName>
                </field>
            </fields>
        </table>
    </tables>
</query>

INSERT: TO_TIMESTAMP-Funktion verwenden

SQL-Query

INSERT INTO TABLE_TIMESTAMP(ID, DATE_TIMESTAMP, TEST_DATE)
        VALUES (101, TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF'),'2017-08-15 10:10:12.231')

XML-Format

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
                </field>
            </fields>
        </table>
    </tables>
</query>

Beispiel

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>ID</fieldName>
                    <fieldValue>101</fieldValue>
                </field>
                <field>
                    <fieldName>DATE_TIMESTAMP</fieldName>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">2017-07-20 15:28:42.124</fieldValue>
                </field>
                <field>
                    <fieldName>TEST_DATE</fieldName>
                    <fieldValue>2017-08-15 10:10:12.231</fieldValue>
                </field>
            </fields>
        </table>
    </tables>
</query>

UPDATE: TO_TIMESTAMP-Funktion verwenden

SQL-Query

UPDATE TABLE_TIMESTAMP
SET MESSAGE='anotherString', DATE_TIMESTAMP=TO_TIMESTAMP('2017-07-20 15:28:42.123','YYYY-MM-DD HH24:MI:SS.FF')
WHERE ID = 101

XML-Format

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Beispiel

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>MESSAGE</fieldName>
                    <fieldValue>anotherString</fieldValue>
                </field>
                <field>
                    <fieldName>DATE_TIMESTAMP</fieldName>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">2017-07-20 15:28:42.123</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>ID</leftValue>
                    <operation>=</operation>
                    <rightValue>101</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

UPDATE oder INSERT: TO_TIMESTAMP-Funktion verwenden

SQL-Query

UPDATE TABLE_TIMESTAMP
SET ID=102,
    MESSAGE='anotherString',
    DATE_TIMESTAMP=TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF'),
    TEST_DATE='2017-07-20 15:28:42.122'
WHERE ID = 102
   OR
INSERT INTO TABLE_TIMESTAMP(ID, MESSAGE, DATE_TIMESTAMP, TEST_DATE)
VALUES (102, 'anotherString', TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF'),'2017-07-20 15:28:42.122')

XML-Format

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field skip="update">
                    <fieldName/>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Beispiel

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName>TABLE_TIMESTAMP</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>ID</fieldName>
                    <fieldValue>102</fieldValue>
                </field>
                <field>
                    <fieldName>MESSAGE</fieldName>
                    <fieldValue>anotherString</fieldValue>
                </field>
                <field>
                    <fieldName>DATE_TIMESTAMP</fieldName>
                    <fieldValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF">2017-07-20 15:28:42.124</fieldValue>
                </field>
                <field>
                    <fieldName>TEST_DATE</fieldName>
                    <fieldValue>2017-07-20 15:28:42.122</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>ID</leftValue>
                    <operation>=</operation>
                    <rightValue>102</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Bedingung in einer WHERE-Klausel beim Benutzen der TO_TIMESTAMP-Funktion

SQL-Query

TO_TIMESTAMP as
    left value:
SELECT *
FROM TABLE_TIMESTAMP
WHERE TO_TIMESTAMP(DATE_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF') = '2017-07-20 15:28:42.122'
    Right Value:
SELECT *
FROM TABLE_TIMESTAMP
WHERE TEST_DATE = TO_TIMESTAMP('2017-07-20 15:28:42.124','YYYY-MM-DD HH24:MI:SS.FF')

XML-Format

<conditions>
    <condition type="OR">
        <leftValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
        <operation/>
        <rightValue function="to_timestamp" format="YYYY-MM-DD HH24:MI:SS.FF"/>
    </condition>
</conditions>

Null-Werte in Oracle-Datenbanken verwenden

In Oracle-Datenbanken kann die NULL-Funktion in INSERT-, UPDATE- und INSERT oder UPDATE-Query-Typen verwendet werden.

INSERT: Null-Werte verwenden

SQL-Query

INSERT INTO NULLTABLE(COL1,COL2) VALUES (123,null)

XML-Format

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue/>
                </field>
            </fields>
        </table>
    </tables>
</query>

Beispiel

<query type="insert">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                    <fieldValue>123</fieldValue>
                </field>
                <field>
                    <fieldName>COL2</fieldName>
                    <fieldValue>@null</fieldValue>
                </field>
            </fields>
        </table>
    </tables>
</query>

UPDATE: Null-Werte verwenden

SQL-Query

UPDATE NULLTABLE
SET COL1=null, COL2='anotherString'
WHERE COL1=123

XML Format

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field>
                    <fieldName/>
                    <fieldValue/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Beispiele

<query type="update">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                    <fieldValue>@null</fieldValue>
                </field>
                <field>
                    <fieldName>COL2</fieldName>
                    <fieldValue>anotherString</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>COL1</leftValue>
                    <operation>=</operation>
                    <rightValue>123</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

UPDATE oder INSERT: Null-Werte verwenden

SQL Query

UPDATE NULLTABLE
SET COL1=50, COL2=null
WHERE COL1=50
   OR
INSERT INTO NULLTABLE(COL1,COL2)
VALUES (50,null)

XML Format

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName/>
            </tableNames>
            <fields>
                <field skip="update">
                    <fieldName/>
                    <fieldValue/>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue/>
                    <operation/>
                    <rightValue/>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Beispiel

<query type="updateOrInsert">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                    <fieldValue>50</fieldValue>
                </field>
                <field>
                    <fieldName>COL2</fieldName>
                    <fieldValue>@null</fieldValue>
                </field>
            </fields>
            <conditions>
                <condition type="OR">
                    <leftValue>COL1</leftValue>
                    <operation>=</operation>
                    <rightValue>50</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

Bedingungen is null/is not null im Condition Tag von SQL/Querys verwenden

In der Query-Typen select, update und delete können Sie die beiden Bedingungen is null und is not null verwenden.

Beispiele

XML-Query mit einer Bedingung is null

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>COL2</leftValue>
                    <operation>is</operation>
                    <rightValue>@null</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

XML-Query mit einer Bedingung is not null

<query type="select">
    <tables>
        <table>
            <tableNames>
                <tableName>NULLTABLE</tableName>
            </tableNames>
            <fields>
                <field>
                    <fieldName>COL1</fieldName>
                </field>
            </fields>
            <conditions>
                <condition type="AND">
                    <leftValue>COL2</leftValue>
                    <operation>is not</operation>
                    <rightValue>@null</rightValue>
                </condition>
            </conditions>
        </table>
    </tables>
</query>

SELECT von Dual-Tabellen

Verwenden Sie dual im <tableName>-Tag in Klein- oder Großbuchstaben ohne Anführungszeichen wie folgt:
- <tableName>dual</tableName>
- <tableName>DUAL</tableName>
Deaktivieren Sie die Option Automatische Typenerkennung der Datenbankobjekte im Dialog Datenbankverbindungen und Query-Ty.

Wert eines Feldes abrufen

XML-Anfrage für die Tabelle DUAL, um den Wert des Benutzernamen abzurufen.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>user</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

XML-Anfrage für die Tabelle DUAL, um den sequence-Wert abzurufen.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>sequenceA.nextval</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

XML-Anfrage für die Tabelle DUAL, um den Systemdatum-Wert abzurufen.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>sysdate</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Einen Wert durch Ausführen einer Operation abrufen

XML-Anfrage für die Tabelle DUAL, um den Wert 15+10 abzurufen, indem die Operation 15+10 ausgeführt wird.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>15+10</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>

Einen Wert durch Ausführen einer Funktion abrufen

XML-Anfrage für die Tabelle DUAL, um den Wert EXAMPLE abzurufen, indem die Konvertierungsfunktion UPPER('Example') ausgeführt wird.

<queries>
    <query type="select">
        <tables>
            <table>
                <tableNames>
                    <tableName>dual</tableName>
                </tableNames>
                <fields>
                    <field>
                        <fieldName>UPPER('Example')</fieldName>
                    </field>
                </fields>
            </table>
        </tables>
    </query>
</queries>