Difference between revisions of "DBOInsert"

From GreenVulcano Wiki
Jump to: navigation, search
(→‎{{GVESB}} configuration)
(→‎{{GVESB}} configuration)
 
(6 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 DBOInsert attributes:
 
The following table shows the DBOInsert attributes:
Line 27: Line 29:
 
| name || required || DBOInsert name
 
| name || required || DBOInsert name
 
|-
 
|-
−
| transformation || optional || Transformation name
+
| transformation || optional || 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 DBO. <br/> Mode:<br/> caller - inherit the mode of the caller<br/> xml2db - data entry mode
 
| force-mode || optional || (caller or xml2db): Force mode of the DBO, can be used in [[DBOBuilder]] whit more heterogeneous DBO. <br/> Mode:<br/> caller - inherit the mode of the caller<br/> xml2db - data entry mode
Line 39: Line 41:
 
| output-data || optional || The default is the value of @name-Output.
 
| output-data || optional || The default is the value of @name-Output.
 
|}
 
|}
 +
<div class="version_ge3.4.0.6">
 +
{|class="gvtable"
 +
! Attribute !! Type !! Description
 +
|-
 +
| 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>
 +
 +
For example, given the following table:
 +
<syntaxhighlight lang="SQL">
 +
CREATE TABLE "TEST_GV"."TEST_TABLE_A" (
 +
  "C1" VARCHAR2(50 CHAR),
 +
  "C2" VARCHAR2(20 CHAR),
 +
  "C3" NUMBER,
 +
  "C4" NUMBER(10,3),
 +
  "C5" NUMBER(5,4),
 +
  "C6" DATE,
 +
  "C7" TIMESTAMP (6)
 +
)
 +
</syntaxhighlight>
 +
using the following input XML:
 +
<syntaxhighlight lang="XML">
 +
<ser:load xmlns:ser="http://www.gvtest.com/services">
 +
    <ser:c1>text field</ser:c1>
 +
    <ser:c2>another text field</ser:c2>
 +
    <ser:c3>123456</ser:c3>
 +
    <ser:c4>123456.789</ser:c4>
 +
    <ser:c5>1.2345</ser:c5>
 +
    <ser:c6>2014-10-17T10:12:23</ser:c6>
 +
    <ser:c7>2014-10-17T10:13:00.123</ser:c7>
 +
</ser:load>
 +
</syntaxhighlight>
 +
the following DBOInsert can load records into the table:
 +
<syntaxhighlight lang="XML">
 +
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
 +
            jdbc-connection-name="ds.test" name="Insert" type="dbobuilder">
 +
    <DBOInsert class="it.greenvulcano.gvesb.datahandling.dbo.DBOInsert"
 +
              name="Insert" transformation="Insert" type="dbo">
 +
        <statement id="0" type="insert">insert into TEST_TABLE_A (C1, C2, C3, C4, C5, C6, C7)
 +
values (?, ?, ?, ?, ?, ?, ?)</statement>
 +
    </DBOInsert>
 +
</DBOBuilder>
 +
</syntaxhighlight>
 +
using the following XSL transformation:
 +
<syntaxhighlight lang="XML">
 +
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl fs java gvt" version="2.0"
 +
                xmlns:fs="http://www.w3.org/2005/xpath-functions"
 +
                xmlns:java="http://xml.apache.org/xalan/java"
 +
                xmlns:gvt="http://www.gvtest.com/services"
 +
                xmlns:xs="http://www.w3.org/2001/XMLSchema">
 +
 +
    <xsl:output encoding="utf-8" indent="yes" method="xml"/>
 +
 +
    <xsl:template match="/gvt:load">
 +
        <xsl:element name="RowSet">
 +
            <xsl:element name="data">
 +
                <xsl:element name="row">
 +
                    <xsl:element name="col">
 +
                        <xsl:value-of select="gvt:c1"/>
 +
                    </xsl:element>
 +
                    <xsl:element name="col">
 +
                        <xsl:value-of select="gvt:c2"/>
 +
                    </xsl:element>
 +
                    <xsl:element name="col">
 +
                        <xsl:attribute name="type">number</xsl:attribute>
 +
                        <xsl:value-of select="gvt:c3"/>
 +
                    </xsl:element>
 +
                    <xsl:element name="col">
 +
                        <xsl:attribute name="type">float</xsl:attribute>
 +
                        <xsl:attribute name="decimal-separator">.</xsl:attribute>
 +
                        <xsl:attribute name="grouping-separator">,</xsl:attribute>
 +
                        <xsl:value-of select="gvt:c4"/>
 +
                    </xsl:element>
 +
                    <xsl:element name="col">
 +
                        <xsl:attribute name="type">float</xsl:attribute>
 +
                        <xsl:attribute name="decimal-separator">.</xsl:attribute>
 +
                        <xsl:attribute name="grouping-separator">,</xsl:attribute>
 +
                        <xsl:value-of select="gvt:c5"/>
 +
                    </xsl:element>
 +
                    <xsl:element name="col">
 +
                        <xsl:attribute name="type">timestamp</xsl:attribute>
 +
                        <xsl:attribute name="format">yyyy-MM-dd'T'HH:mm:ss</xsl:attribute>
 +
                        <xsl:value-of select="gvt:c6"/>
 +
                    </xsl:element>
 +
                    <xsl:element name="col">
 +
                        <xsl:attribute name="type">timestamp</xsl:attribute>
 +
                        <xsl:attribute name="format">yyyy-MM-dd'T'HH:mm:ss.SSS</xsl:attribute>
 +
                        <xsl:value-of select="gvt:c7"/>
 +
                    </xsl:element>
 +
                </xsl:element>
 +
            </xsl:element>
 +
        </xsl:element>
 +
    </xsl:template>
 +
</xsl:stylesheet>
 +
</syntaxhighlight>
  
−
For example:
+
<div class="version_ge3.5">
 +
DBOInsert can also be used to make insert on a local or remote neo4j graph database.
 +
The statement must be written 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" jdbc-connection-name="ds.gv_cama"  
+
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
−
             name="InsertCampaignData" type="dbobuilder">
+
            jdbc-connection-name="openejb:Resource/testDHDataSourceN4J"  
−
      <DBOInsert class="it.greenvulcano.gvesb.datahandling.dbo.DBOInsert" name="InsertCampaingDataInsert"
+
             name="GVESB::TestInsertWithInputDataN4J" type="dbobuilder">
−
                  transformation="InsertCampaignDataSeq" type="dbo">
+
    <DBOInsert class="it.greenvulcano.gvesb.datahandling.dbo.DBOInsert"  
−
              <statement id="0" type="insert">
+
        name="TestInsertWithInputDataN4J-InputData" type="dbo" >
−
                  insert into CAMA.MESSAGES(......) values(?,?,?,?,?,?,?,sysdate,sysdate,?,?,?)
+
            <statement id="0" type="insert">CREATE (Movie: Movie { title: {1}, released: {2}, tagline: {3} } )</statement>
−
              </statement>
+
    </DBOInsert>
−
      </DBOInsert>
+
</DBOBuilder>
−
</DBOBuilder>
 
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
As you can see in the above section you can make any queries insert achievable with cypher language using the parameters in input.
 +
</div>
  
 
Might contain the following sub-elements:
 
Might contain the following sub-elements:

Latest revision as of 17:23, 26 January 2016

Description

The DBOInsert element represents the DBO optimized for insert operations.

GreenVulcano® ESB configuration

DBOInsert with VulCon

DBOInsert allows you to make insert 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 DBOInsert 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.DBOInsert
name required DBOInsert name
transformation optional 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 DBO.
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"."TEST_TABLE_A" (
  "C1" VARCHAR2(50 CHAR),
  "C2" VARCHAR2(20 CHAR),
  "C3" NUMBER,
  "C4" NUMBER(10,3),
  "C5" NUMBER(5,4),
  "C6" DATE,
  "C7" TIMESTAMP (6)
)

using the following input XML: 

<ser:load xmlns:ser="http://www.gvtest.com/services">
    <ser:c1>text field</ser:c1>
    <ser:c2>another text field</ser:c2>
    <ser:c3>123456</ser:c3>
    <ser:c4>123456.789</ser:c4>
    <ser:c5>1.2345</ser:c5>
    <ser:c6>2014-10-17T10:12:23</ser:c6>
    <ser:c7>2014-10-17T10:13:00.123</ser:c7>
</ser:load>

the following DBOInsert can load records into the table: 

<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
            jdbc-connection-name="ds.test" name="Insert" type="dbobuilder">
    <DBOInsert class="it.greenvulcano.gvesb.datahandling.dbo.DBOInsert"
               name="Insert" transformation="Insert" type="dbo">
        <statement id="0" type="insert">insert into TEST_TABLE_A (C1, C2, C3, C4, C5, C6, C7)
values (?, ?, ?, ?, ?, ?, ?)</statement>
    </DBOInsert>
</DBOBuilder>

using the following XSL transformation: 

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xsl fs java gvt" version="2.0"
                xmlns:fs="http://www.w3.org/2005/xpath-functions"
                xmlns:java="http://xml.apache.org/xalan/java"
                xmlns:gvt="http://www.gvtest.com/services"
                xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xsl:output encoding="utf-8" indent="yes" method="xml"/>

    <xsl:template match="/gvt:load">
        <xsl:element name="RowSet">
            <xsl:element name="data">
                <xsl:element name="row">
                    <xsl:element name="col">
                        <xsl:value-of select="gvt:c1"/>
                    </xsl:element>
                    <xsl:element name="col">
                        <xsl:value-of select="gvt:c2"/>
                    </xsl:element>
                    <xsl:element name="col">
                        <xsl:attribute name="type">number</xsl:attribute>
                        <xsl:value-of select="gvt:c3"/>
                    </xsl:element>
                    <xsl:element name="col">
                        <xsl:attribute name="type">float</xsl:attribute>
                        <xsl:attribute name="decimal-separator">.</xsl:attribute>
                        <xsl:attribute name="grouping-separator">,</xsl:attribute>
                        <xsl:value-of select="gvt:c4"/>
                    </xsl:element>
                    <xsl:element name="col">
                        <xsl:attribute name="type">float</xsl:attribute>
                        <xsl:attribute name="decimal-separator">.</xsl:attribute>
                        <xsl:attribute name="grouping-separator">,</xsl:attribute>
                        <xsl:value-of select="gvt:c5"/>
                    </xsl:element>
                    <xsl:element name="col">
                        <xsl:attribute name="type">timestamp</xsl:attribute>
                        <xsl:attribute name="format">yyyy-MM-dd'T'HH:mm:ss</xsl:attribute>
                        <xsl:value-of select="gvt:c6"/>
                    </xsl:element>
                    <xsl:element name="col">
                        <xsl:attribute name="type">timestamp</xsl:attribute>
                        <xsl:attribute name="format">yyyy-MM-dd'T'HH:mm:ss.SSS</xsl:attribute>
                        <xsl:value-of select="gvt:c7"/>
                    </xsl:element>
                </xsl:element>
            </xsl:element>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

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

<?xml version="1.0" encoding="UTF-8"?>
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
            jdbc-connection-name="openejb:Resource/testDHDataSourceN4J" 
            name="GVESB::TestInsertWithInputDataN4J" type="dbobuilder">
     <DBOInsert class="it.greenvulcano.gvesb.datahandling.dbo.DBOInsert" 
	        name="TestInsertWithInputDataN4J-InputData" type="dbo"  >
            <statement id="0" type="insert">CREATE (Movie: Movie { title: {1}, released: {2}, tagline: {3} } )</statement>
     </DBOInsert>
</DBOBuilder>

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

Might contain the following sub-elements:

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