Difference between revisions of "DBOCallSP"

From GreenVulcano Wiki
Jump to: navigation, search
(Created page with "==Description== The DBOCallSP element represents the DBO optimized for operations on store procedures. =={{GVESB}} configuration== [[File:DBOCallSP.jpg|thumb|DBOCallSP with Vul...")
 
 
(2 intermediate revisions by 2 users not shown)
Line 11: Line 11:
  
 
Its attributes are:
 
Its attributes are:
 +
{|class="gvtable"
 +
! 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.DBOCallSP'''
 +
|-
 +
| name || required || DBOCallSP name
 +
|-
 +
| transformation || optional || Transformation to be applied on the input data to obtain the internal XML commands.
 +
|-
 +
| force-mode || required || 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/>  call - call procedure mode.
 +
The attribute's admitted values are:
 +
* caller
 +
* xml2db
 +
|-
 +
| execute-query || required || This parameter specifies is the callable statements must be executed with API CallableStatement#executeQuery(). <br/>  Default is false.
 +
The attribute's admitted values are:
 +
* true
 +
* false
 +
|-
 +
| jdbc-connection-name || required || DataSource JNDI name. <br/>  Override connection defined in [[DBOBuilder]].
 +
|-
 +
| ignore-input || required || Tell the engine do not use the input data.
 +
The attribute's admitted values are:
 +
* true
 +
* false
 +
|-
 +
| input-data || required || The default is the value of @name-Input. <br/> Can be set as the @output-data of a preceding DBO to use its output as input.
 +
|-
 +
| output-data || required || The default is the value of @name-Output.
 +
|}
  
−
* ''type'': dbo
+
As an example, see the following stored procedure:
−
* ''class'': it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP
+
<syntaxhighlight lang="sql">
−
* ''name''
+
PROCEDURE LOAD_XML_FILE (v_file_name IN VARCHAR2, v_file IN CLOB, v_file_id OUT NUMBER) AS
−
* ''transformation''
+
....
−
* ''force-mode'' (caller|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/> call - call procedure mode
+
</syntaxhighlight>
−
* ''execute-query'' (true|false): This parameter specifies is the callable statements must be executed with API CallableStatement#executeQuery(). <br/>  Default is false.
+
<syntaxhighlight lang="xml">
−
* ''jdbc-connection-name'':  DataSource JNDI name. <br/>  Override connection defined in [[DBOBuilder]].
 
−
* ''ignore-input'' (true|false):  Tell the engine do not use the input data.
 
−
* ''input-data'':    The default is the value of @name-Input. <br/> Can be set as the @output-data of a preceding DBO to use its output as input.
 
−
* ''output-data'':  The default is the value of @name-Output.
 
−
 
 
−
For example:
 
−
<syntaxhighlight lang="XML"><?xml version="1.0" encoding="UTF-8"?>
 
 
<dh-call class="it.greenvulcano.gvesb.virtual.datahandler.DataHandlerCallOperation"
 
<dh-call class="it.greenvulcano.gvesb.virtual.datahandler.DataHandlerCallOperation"
 
       name="TestInsertXML" type="call">
 
       name="TestInsertXML" type="call">
Line 46: Line 71:
 
     </DBOBuilder>
 
     </DBOBuilder>
 
</dh-call>
 
</dh-call>
 +
</syntaxhighlight>
 +
The service receive as input an XML document (and a '''FILE_NAME''' property) and load it into a table, returning a number representing the key of the file in the table.
 +
The '''InsertXML''' transformation create the following XML in order to provide the parameters for the procedure:
 +
<syntaxhighlight lang="xml">
 +
<RowSet>
 +
    <data>
 +
        <row id="0">
 +
            <col>Test.txt</col>
 +
            <col type="long-string">&lt;PersonsData&gt;
 +
    &lt;PersonData&gt;
 +
    &lt;Name&gt;ANTONIO ROSSI&lt;/Name&gt;
 +
    &lt;BirthDate&gt;05/02/1980&lt;/BirthDate&gt;
 +
    &lt;City&gt;MILANO&lt;/City&gt;
 +
    &lt;/PersonData&gt;
 +
    &lt;/PersonsData&gt;</col>
 +
        </row>
 +
    </data>
 +
</RowSet>
 +
</syntaxhighlight>
 +
The '''DBOCallSP''' output is:
 +
<syntaxhighlight lang="xml">
 +
<RowSet>
 +
    <data id="0">
 +
        <row id="sp_result">
 +
            <col id="RESULT"  type="string">11</col>
 +
        </row>
 +
    </data>
 +
</RowSet>
 +
</syntaxhighlight>
 +
 +
 +
In order to invoke a stored function, as the following:
 +
<syntaxhighlight lang="sql">
 +
FUNCTION LOAD_XML_FILE (v_file_name IN VARCHAR2, v_file IN CLOB) RETURN NUMBER AS
 +
....
 +
</syntaxhighlight>
 +
<syntaxhighlight lang="xml">
 +
<dh-call class="it.greenvulcano.gvesb.virtual.datahandler.DataHandlerCallOperation"
 +
      name="TestInsertXML" type="call">
 +
    <DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
 +
          isXA="true" jdbc-connection-name="ds.gv_test"
 +
          name="TestInsertXML" transacted="true"
 +
          type="dbobuilder">
 +
        <DBOCallSP class="it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP"
 +
                name="InsertXML" transformation="InsertXML"
 +
                type="dbo">
 +
                    <CallDescriptor>
 +
                        <statement id="0" type="callsp">begin ? := load_xml_file(?, ?); end;</statement>
 +
                        <SPOutputParameters>
 +
                              <SPOutputParameter db-type="number"
 +
                                      java-type="string" position="1"
 +
                                      prop-name="RESULT"
 +
                                      return-in-prop="false"/>
 +
                        </SPOutputParameters>
 +
                    </CallDescriptor>
 +
        </DBOCallSP>
 +
    </DBOBuilder>
 +
</dh-call>
 +
</syntaxhighlight>
 +
The service receive as input an XML document (and a '''FILE_NAME''' property) and load it into a table, returning a number representing the key of the file in the table.
 +
The '''InsertXML''' transformation create the following XML in order to provide the parameters for the procedure:
 +
<syntaxhighlight lang="xml">
 +
<RowSet>
 +
    <data>
 +
        <row id="0">
 +
            <col out-only="true"/>
 +
            <col>Test.txt</col>
 +
            <col type="long-string">&lt;PersonsData&gt;
 +
    &lt;PersonData&gt;
 +
    &lt;Name&gt;ANTONIO ROSSI&lt;/Name&gt;
 +
    &lt;BirthDate&gt;05/02/1980&lt;/BirthDate&gt;
 +
    &lt;City&gt;MILANO&lt;/City&gt;
 +
    &lt;/PersonData&gt;
 +
    &lt;/PersonsData&gt;</col>
 +
        </row>
 +
    </data>
 +
</RowSet>
 +
</syntaxhighlight>
 +
Please note the first '''col''' element, containing the '''out-only''' attribute: it's required as a placeholder to tell the DataHandler the correct order of the input parameters to populate.
 +
The '''DBOCallSP''' output is:
 +
<syntaxhighlight lang="xml">
 +
<RowSet>
 +
    <data id="0">
 +
        <row id="sp_result">
 +
            <col id="RESULT"  type="string">11</col>
 +
        </row>
 +
    </data>
 +
</RowSet>
 
</syntaxhighlight>
 
</syntaxhighlight>

Latest revision as of 08:10, 12 January 2016

Description

The DBOCallSP element represents the DBO optimized for operations on store procedures.

GreenVulcano® ESB configuration

DBOCallSP with VulCon

DBOCallSP might contain the following sub-elements:

Its attributes are:

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.DBOCallSP
name required DBOCallSP name
transformation optional Transformation to be applied on the input data to obtain the internal XML commands.
force-mode required Force mode of the DBO, can be used in DBOBuilder whit more heterogeneous DBO.
Mode:
caller - inherit the mode of the caller
call - call procedure mode.

The attribute's admitted values are:

  • caller
  • xml2db
execute-query required This parameter specifies is the callable statements must be executed with API CallableStatement#executeQuery().
Default is false.

The attribute's admitted values are:

  • true
  • false
jdbc-connection-name required DataSource JNDI name.
Override connection defined in DBOBuilder.
ignore-input required Tell the engine do not use the input data.

The attribute's admitted values are:

  • true
  • false
input-data required 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 required The default is the value of @name-Output.

As an example, see the following stored procedure: 

PROCEDURE LOAD_XML_FILE (v_file_name IN VARCHAR2, v_file IN CLOB, v_file_id OUT NUMBER) AS
....

<dh-call class="it.greenvulcano.gvesb.virtual.datahandler.DataHandlerCallOperation"
      name="TestInsertXML" type="call">
    <DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
           isXA="true" jdbc-connection-name="ds.gv_test"
           name="TestInsertXML" transacted="true"
           type="dbobuilder">
        <DBOCallSP class="it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP"
                name="InsertXML" transformation="InsertXML"
                type="dbo">
                    <CallDescriptor>
                        <statement id="0" type="callsp">begin load_xml_file(?, ?, ?); end;</statement>
                        <SPOutputParameters>
                              <SPOutputParameter db-type="number"
                                       java-type="string" position="3"
                                       prop-name="RESULT"
                                       return-in-prop="false"/>
                        </SPOutputParameters>
                    </CallDescriptor>
        </DBOCallSP>
    </DBOBuilder>
</dh-call>

The service receive as input an XML document (and a FILE_NAME property) and load it into a table, returning a number representing the key of the file in the table. The InsertXML transformation create the following XML in order to provide the parameters for the procedure: 

<RowSet>
    <data>
        <row id="0">
            <col>Test.txt</col>
            <col type="long-string">&lt;PersonsData&gt;
    &lt;PersonData&gt;
    &lt;Name&gt;ANTONIO ROSSI&lt;/Name&gt;
    &lt;BirthDate&gt;05/02/1980&lt;/BirthDate&gt;
    &lt;City&gt;MILANO&lt;/City&gt;
    &lt;/PersonData&gt;
    &lt;/PersonsData&gt;</col>
        </row>
    </data>
</RowSet>

The DBOCallSP output is: 

<RowSet>
    <data id="0">
        <row id="sp_result">
            <col id="RESULT"  type="string">11</col>
        </row>
    </data>
</RowSet>


In order to invoke a stored function, as the following: 

FUNCTION LOAD_XML_FILE (v_file_name IN VARCHAR2, v_file IN CLOB) RETURN NUMBER AS
....

<dh-call class="it.greenvulcano.gvesb.virtual.datahandler.DataHandlerCallOperation"
      name="TestInsertXML" type="call">
    <DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"
           isXA="true" jdbc-connection-name="ds.gv_test"
           name="TestInsertXML" transacted="true"
           type="dbobuilder">
        <DBOCallSP class="it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP"
                name="InsertXML" transformation="InsertXML"
                type="dbo">
                    <CallDescriptor>
                        <statement id="0" type="callsp">begin ? := load_xml_file(?, ?); end;</statement>
                        <SPOutputParameters>
                              <SPOutputParameter db-type="number"
                                       java-type="string" position="1"
                                       prop-name="RESULT"
                                       return-in-prop="false"/>
                        </SPOutputParameters>
                    </CallDescriptor>
        </DBOCallSP>
    </DBOBuilder>
</dh-call>

The service receive as input an XML document (and a FILE_NAME property) and load it into a table, returning a number representing the key of the file in the table. The InsertXML transformation create the following XML in order to provide the parameters for the procedure: 

<RowSet>
    <data>
        <row id="0">
            <col out-only="true"/>
            <col>Test.txt</col>
            <col type="long-string">&lt;PersonsData&gt;
    &lt;PersonData&gt;
    &lt;Name&gt;ANTONIO ROSSI&lt;/Name&gt;
    &lt;BirthDate&gt;05/02/1980&lt;/BirthDate&gt;
    &lt;City&gt;MILANO&lt;/City&gt;
    &lt;/PersonData&gt;
    &lt;/PersonsData&gt;</col>
        </row>
    </data>
</RowSet>

Please note the first col element, containing the out-only attribute: it's required as a placeholder to tell the DataHandler the correct order of the input parameters to populate. The DBOCallSP output is: 

<RowSet>
    <data id="0">
        <row id="sp_result">
            <col id="RESULT"  type="string">11</col>
        </row>
    </data>
</RowSet>
Retrieved from "https://gvwiki.greenvulcano.com/gvwiki/index.php?title=DBOCallSP&oldid=4488"