Difference between revisions of "DBOUpdate"

From GreenVulcano Wiki
Jump to: navigation, search
(→‎{{GVESB}} configuration)
 
(5 intermediate revisions by 2 users not shown)
Line 16: Line 16:
  
 
In the second mode, the input XML (or its transformation) must contain the command (row) valid with all parameters (COL) required by the statement.
 
In the second mode, the input XML (or its transformation) must contain the command (row) valid with all parameters (COL) required by the statement.
 +
 +
It is used by [[DBOBuilder]].
  
 
The following table shows the DBOUpdate attributes:
 
The following table shows the DBOUpdate attributes:
Line 45: Line 47:
 
| autogenerate-keys || optional || Tell the engine to handle auto generated keys at runtime (ex. autoincrement primary key fields). Default to false.
 
| autogenerate-keys || optional || Tell the engine to handle auto generated keys at runtime (ex. autoincrement primary key fields). Default to false.
 
|}
 
|}
 +
</div>
 +
<div class="version_ge3.5">
 +
It's possible to use positional parameters ''?'' notation or named parameters '':name'' notation into statement definition, but not mixed in the same statement.
 
</div>
 
</div>
  
−
For example:
+
For example, given the following table:
 +
<syntaxhighlight lang="SQL">
 +
CREATE TABLE "TEST_GV"."CREDIT_CARD" (
 +
  "ID"      NUMBER NOT NULL,
 +
  "ID_OWNER" NUMBER NOT NULL,
 +
  "CNUMBER"  VARCHAR2(20 CHAR) NOT NULL,
 +
  "CREDIT"  NUMBER(15,5) NOT NULL,
 +
  "ENABLED"  VARCHAR2(1 CHAR) NOT NULL,
 +
  CONSTRAINT "CCRED_PK" PRIMARY KEY ("ID")
 +
)
 +
</syntaxhighlight>
 +
the following input XML:
 +
<syntaxhighlight lang="XML">
 +
<ser:Pay xmlns:ser="http://www.credit.com/services">
 +
    <ser:cnumber>1111222233334444</ser:cnumber>
 +
    <ser:amount>10</ser:amount>
 +
</ser:Pay>
 +
</syntaxhighlight>
 +
the following DBOUpdate can update the table:
 +
<syntaxhighlight lang="XML">
 +
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
 +
            jdbc-connection-name="gv_test" name="CCardPay" type="dbobuilder">
 +
    <DHVariables>
 +
        <DHVariable name="decimal-separator" value="."/>
 +
        <DHVariable name="grouping-separator" value=","/>
 +
        <DHVariable name="format" value="#0.00"/>
 +
    </DHVariables>
 +
    <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"
 +
                name="CCardPay" transformation="CCardPay" type="dbo">
 +
        <statement id="1" type="update"><![CDATA[update CREDIT_CARD  set CREDIT= CREDIT - ?
 +
where CNUMBER=?
 +
and ENABLED='Y'
 +
and CREDIT >= ?]]></statement>
 +
    </DBOUpdate>
 +
</DBOBuilder>
 +
</syntaxhighlight>
 +
through the following XSL transformation:
 +
<syntaxhighlight lang="XML">
 +
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl int fs" version="2.0"
 +
                xmlns:fs="http://www.w3.org/2005/xpath-functions"
 +
                xmlns:int="http://www.credit.com/services"
 +
                xmlns:xs="http://www.w3.org/2001/XMLSchema">
 +
    <xsl:output encoding="utf-8" indent="yes" method="xml"/>
 +
    <xsl:template match="/">
 +
        <xsl:element name="RowSet">
 +
            <xsl:apply-templates select="/int:Pay"/>
 +
        </xsl:element>
 +
    </xsl:template>
 +
    <xsl:template match="/int:Pay">
 +
        <xsl:element name="data">
 +
            <xsl:element name="row">
 +
                <xsl:attribute name="id">1</xsl:attribute>
 +
                <xsl:element name="col">
 +
                    <xsl:attribute name="type">float</xsl:attribute>
 +
                    <xsl:value-of select="int:amount"/>
 +
                </xsl:element>
 +
                <xsl:element name="col">
 +
                    <xsl:value-of select="int:cnumber"/>
 +
                </xsl:element>
 +
                <xsl:element name="col">
 +
                    <xsl:attribute name="type">float</xsl:attribute>
 +
                    <xsl:value-of select="int:amount"/>
 +
                </xsl:element>
 +
            </xsl:element>
 +
        </xsl:element>
 +
    </xsl:template>
 +
</xsl:stylesheet>
 +
</syntaxhighlight>
 +
 
 +
<div class="version_ge3.5">
 +
Using named parameters, the following DBOUpdate can update the table:
 +
<syntaxhighlight lang="XML">
 +
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
 +
            jdbc-connection-name="gv_test" name="CCardPay" type="dbobuilder">
 +
    <DHVariables>
 +
        <DHVariable name="decimal-separator" value="."/>
 +
        <DHVariable name="grouping-separator" value=","/>
 +
        <DHVariable name="format" value="#0.00"/>
 +
    </DHVariables>
 +
    <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"
 +
                name="CCardPay" transformation="CCardPay" type="dbo">
 +
        <statement id="1" type="update"><![CDATA[update CREDIT_CARD  set CREDIT= CREDIT - :amount
 +
where CNUMBER=:cnumber
 +
and ENABLED='Y'
 +
and CREDIT >= :amount]]></statement>
 +
    </DBOUpdate>
 +
</DBOBuilder>
 +
</syntaxhighlight>
 +
through the following XSL transformation:
 +
<syntaxhighlight lang="XML">
 +
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl fs" version="2.0"
 +
                xmlns:fs="http://www.w3.org/2005/xpath-functions"
 +
                xmlns:int="http://www.credit.com/services"
 +
                xmlns:ns0="http://www.greenvulcano.com/database"
 +
                xmlns:xs="http://www.w3.org/2001/XMLSchema"
 +
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 +
    <xsl:output encoding="utf-8" indent="yes" method="xml"/>
 +
    <xsl:template match="/">
 +
        <xsl:element name="RowSet" namespace="http://www.greenvulcano.com/database">
 +
            <xsl:element name="data" namespace="http://www.greenvulcano.com/database">
 +
                <xsl:apply-templates select="/int:Pay"/>
 +
            </xsl:element>
 +
        </xsl:element>
 +
    </xsl:template>
 +
    <xsl:template match="/int:Pay">
 +
        <xsl:element name="row" namespace="http://www.greenvulcano.com/database">
 +
            <xsl:attribute name="id">1</xsl:attribute>
 +
            <xsl:element name="cnumber" namespace="http://www.greenvulcano.com/database">
 +
                <xsl:attribute name="type">float</xsl:attribute>
 +
                <xsl:value-of select="int:cnumber"/>
 +
            </xsl:element>
 +
            <xsl:element name="amount" namespace="http://www.greenvulcano.com/database">
 +
                <xsl:attribute name="type">float</xsl:attribute>
 +
                <xsl:value-of select="int:amount"/>
 +
            </xsl:element>
 +
        </xsl:element>
 +
    </xsl:template>
 +
</xsl:stylesheet>
 +
</syntaxhighlight>
 +
</div>
 +
 
 +
<div class="version_ge3.5">
 +
DBOUpdate can be used to make update on a local or remote neo4j graph database.
 +
The statement must be write using the cypher (Cypher Query Language) language [http://neo4j.com/docs/stable/cypher-query-lang.html] as detailed in the following example:
 
<syntaxhighlight lang="XML"><?xml version="1.0" encoding="UTF-8"?>
 
<syntaxhighlight lang="XML"><?xml version="1.0" encoding="UTF-8"?>
−
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
+
<?xml version="1.0" encoding="UTF-8"?>
−
              jdbc-connection-name="ds.gv_test" name="PersonInsertFull" type="dbobuilder">
+
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"  
−
      <DHVariables>
+
            jdbc-connection-name="openejb:Resource/testDHDataSourceN4J"  
−
            <DHVariable name="decimal-separator" value="."/>
+
    name="GVESB::TestUpdateWithInputDataN4J" type="dbobuilder">
−
            <DHVariable name="grouping-separator" value=","/>
+
      <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"  
−
            <DHVariable name="format" value="#0.00"/>
+
        name="TestUpdateWithInputDataN4J-InputData"
−
      </DHVariables>
+
        type="dbo">
−
      <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"
+
            <statement id="0" type="update">MATCH (n { title: {1} }) SET n.tagline = {2}</statement>
−
              name="CCardPay" transformation="CCardPay" type="dbo">
+
      </DBOUpdate>
−
              <statement id="0" type="update">
+
</DBOBuilder>
−
                        update CREDIT_CARD  set CREDIT= CREDIT - ?
 
−
                        where CNUMBER=?
 
−
                        and ENABLED=‘Y’
 
−
                        and CREDIT  >= ?
 
−
              </statement>
 
−
      </DBOUpdate>
 
−
</DBOBuilder>
 
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
As you can see in the above section you can make any queries update achievable with cypher language using the parameters in input.
  
−
According with the data present into the database, this statement will return the following XML:
+
This DBO can be used also to delete elements present in the neo4j graph database usign the keyword and the related cypher language syntax.
 +
In the following example is shown a cypher query used to delete all nodes and the related relationships that meet the MATCH condition:
 +
<syntaxhighlight lang="XML"><?xml version="1.0" encoding="UTF-8"?>
 +
<?xml version="1.0" encoding="UTF-8"?>
 +
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
 +
            jdbc-connection-name="openejb:Resource/testDHDataSourceN4J"
 +
            name="GVESB::TestDeleteWithPropsN4J" type="dbobuilder">
 +
      <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"
 +
        name="TestDeleteWithPropsN4J-Props" type="dbo"  >
 +
            <statement id="0" type="update">MATCH (n { title: '@{{TITLE_INPUT}}' }) OPTIONAL MATCH (n)-[r]-() DELETE n</statement>
 +
      </DBOUpdate>
 +
</DBOBuilder>
 +
</syntaxhighlight>
  
−
<syntaxhighlight lang="XML">
+
</div>
−
<RowSet>
 
−
    <data>
 
−
        <row>
 
−
            <col type="float">10</col>
 
−
            <col>1111222233334444</col>
 
−
            <col type="float">10</col>
 
−
      </row>
 
−
    </data>
 
−
</RowSet>
 
−
</syntaxhighlight>
 
  
 
DBOUpdate might contain the following sub-elements:
 
DBOUpdate might contain the following sub-elements:

Latest revision as of 16:57, 26 January 2016

Description

The DBOUpdate element represents the DBO optimized for update operations.

GreenVulcano® ESB configuration

DBOUpdate with VulCon

DBOUpdate allows you to update data into the database.

Each statement is identified by the id.

Actions can be defined in two ways:

  • Single execution of a statement
  • Complex execution guided by XML

In the first modality any input is ignored and the code executes the first statement configured.

In the second mode, the input XML (or its transformation) must contain the command (row) valid with all parameters (COL) required by the statement.

It is used by DBOBuilder.

The following table shows the DBOUpdate attributes:

Attribute Type Description
type fixed This attribute must assume the value dbo
class fixed This attribute must assume the value it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate
name required DBOUpdate name
transformation required Transformation to be applied on the input data to obtain the internal XML commands.
force-mode optional (caller or xml2db) Force mode of the DBO, can be used in DBOBuilder whit more heterogeneous DBOs.
Mode:
caller - inherit the mode of the caller
xml2db - data entry mode
jdbc-connection-name optional DataSource JNDI name.
Override connection defined in DBOBuilder.
ignore-input optional (true or false) Tells the engine do not use the input data.
input-data optional The default is the value of @name-Input.
Can be set as the @output-data of a preceding DBO to use its output as input.
output-data optional The default is the value of @name-Output.
Attribute Type Description
autogenerate-keys optional Tell the engine to handle auto generated keys at runtime (ex. autoincrement primary key fields). Default to false.

It's possible to use positional parameters ? notation or named parameters :name notation into statement definition, but not mixed in the same statement.

For example, given the following table: 

CREATE TABLE "TEST_GV"."CREDIT_CARD" (
  "ID"       NUMBER NOT NULL,
  "ID_OWNER" NUMBER NOT NULL,
  "CNUMBER"  VARCHAR2(20 CHAR) NOT NULL,
  "CREDIT"   NUMBER(15,5) NOT NULL,
  "ENABLED"  VARCHAR2(1 CHAR) NOT NULL,
  CONSTRAINT "CCRED_PK" PRIMARY KEY ("ID")
)

the following input XML: 

<ser:Pay xmlns:ser="http://www.credit.com/services">
    <ser:cnumber>1111222233334444</ser:cnumber>
    <ser:amount>10</ser:amount>
</ser:Pay>

the following DBOUpdate can update the table: 

<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
            jdbc-connection-name="gv_test" name="CCardPay" type="dbobuilder">
    <DHVariables>
        <DHVariable name="decimal-separator" value="."/>
        <DHVariable name="grouping-separator" value=","/>
        <DHVariable name="format" value="#0.00"/>
    </DHVariables>
    <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"
                name="CCardPay" transformation="CCardPay" type="dbo">
        <statement id="1" type="update"><![CDATA[update CREDIT_CARD  set CREDIT= CREDIT - ?
where CNUMBER=?
and ENABLED='Y'
and CREDIT >= ?]]></statement>
    </DBOUpdate>
</DBOBuilder>

through the following XSL transformation: 

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl int fs" version="2.0"
                xmlns:fs="http://www.w3.org/2005/xpath-functions"
                xmlns:int="http://www.credit.com/services"
                xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xsl:output encoding="utf-8" indent="yes" method="xml"/>
    <xsl:template match="/">
        <xsl:element name="RowSet">
            <xsl:apply-templates select="/int:Pay"/>
        </xsl:element>
    </xsl:template>
    <xsl:template match="/int:Pay">
        <xsl:element name="data">
            <xsl:element name="row">
                <xsl:attribute name="id">1</xsl:attribute>
                <xsl:element name="col">
                    <xsl:attribute name="type">float</xsl:attribute>
                    <xsl:value-of select="int:amount"/>
                </xsl:element>
                <xsl:element name="col">
                    <xsl:value-of select="int:cnumber"/>
                </xsl:element>
                <xsl:element name="col">
                    <xsl:attribute name="type">float</xsl:attribute>
                    <xsl:value-of select="int:amount"/>
                </xsl:element>
            </xsl:element>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

Using named parameters, the following DBOUpdate can update the table: 

<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
            jdbc-connection-name="gv_test" name="CCardPay" type="dbobuilder">
    <DHVariables>
        <DHVariable name="decimal-separator" value="."/>
        <DHVariable name="grouping-separator" value=","/>
        <DHVariable name="format" value="#0.00"/>
    </DHVariables>
    <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate"
                name="CCardPay" transformation="CCardPay" type="dbo">
        <statement id="1" type="update"><![CDATA[update CREDIT_CARD  set CREDIT= CREDIT - :amount
where CNUMBER=:cnumber
and ENABLED='Y'
and CREDIT >= :amount]]></statement>
    </DBOUpdate>
</DBOBuilder>

through the following XSL transformation: 

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl fs" version="2.0"
                xmlns:fs="http://www.w3.org/2005/xpath-functions"
                xmlns:int="http://www.credit.com/services"
                xmlns:ns0="http://www.greenvulcano.com/database"
                xmlns:xs="http://www.w3.org/2001/XMLSchema"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsl:output encoding="utf-8" indent="yes" method="xml"/>
    <xsl:template match="/">
        <xsl:element name="RowSet" namespace="http://www.greenvulcano.com/database">
            <xsl:element name="data" namespace="http://www.greenvulcano.com/database">
                <xsl:apply-templates select="/int:Pay"/>
            </xsl:element>
        </xsl:element>
    </xsl:template>
    <xsl:template match="/int:Pay">
        <xsl:element name="row" namespace="http://www.greenvulcano.com/database">
            <xsl:attribute name="id">1</xsl:attribute>
            <xsl:element name="cnumber" namespace="http://www.greenvulcano.com/database">
                <xsl:attribute name="type">float</xsl:attribute>
                <xsl:value-of select="int:cnumber"/>
            </xsl:element>
            <xsl:element name="amount" namespace="http://www.greenvulcano.com/database">
                <xsl:attribute name="type">float</xsl:attribute>
                <xsl:value-of select="int:amount"/>
            </xsl:element>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

DBOUpdate can be used to make update on a local or remote neo4j graph database. The statement must be write using the cypher (Cypher Query Language) language [1] as detailed in the following example: 

<?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder" 
            jdbc-connection-name="openejb:Resource/testDHDataSourceN4J" 
	    name="GVESB::TestUpdateWithInputDataN4J" type="dbobuilder">
      <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate" 
	         name="TestUpdateWithInputDataN4J-InputData"
	         type="dbo">
             <statement id="0" type="update">MATCH (n { title: {1} }) SET n.tagline = {2}</statement>
      </DBOUpdate>
</DBOBuilder>

As you can see in the above section you can make any queries update achievable with cypher language using the parameters in input.

This DBO can be used also to delete elements present in the neo4j graph database usign the keyword and the related cypher language syntax. In the following example is shown a cypher query used to delete all nodes and the related relationships that meet the MATCH condition: 

<?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
            jdbc-connection-name="openejb:Resource/testDHDataSourceN4J" 
            name="GVESB::TestDeleteWithPropsN4J" type="dbobuilder">
      <DBOUpdate class="it.greenvulcano.gvesb.datahandling.dbo.DBOUpdate" 
	         name="TestDeleteWithPropsN4J-Props" type="dbo"  >
            <statement id="0" type="update">MATCH (n { title: '@{{TITLE_INPUT}}' }) OPTIONAL MATCH (n)-[r]-() DELETE n</statement>
      </DBOUpdate>
</DBOBuilder>

DBOUpdate might contain the following sub-elements:

Retrieved from "https://gvwiki.greenvulcano.com/gvwiki/index.php?title=DBOUpdate&oldid=4492"