This documentation is for WSO2 Enterprise Service Bus 3.0.0. View documentation for the latest release.
Skip to end of metadata
Go to start of metadata

The DBReport Mediator is very similar to the DBLookup Mediator, but writes information to a Database, using the specified insert SQL statement.



Syntax

<dbreport>
   <connection>
     <pool>
      (
       <driver/>
       <url/>
       <user/>
       <password/>
     |
       <dsName/>
       <icClass/>
       <url/>
       <user/>
       <password/>
     )
       <property name="name" value="value"/>*
     </pool>
   </connection>
   <statement>
     <sql>insert into something values(?, ?, ?, ?)</sql>
     <parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/>*
    </statement>+
</dbreport>

UI Configuration

Connection Information of the DBReport Mediator can be chosen as:

It is possible to add #Properties ans #SQL Statements to the DBReport Mediator as well.

Connection Information - Pool

DBReport Mediator options field descriptions:

  • Use Transaction - (Yes/No)
  • Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
  • Driver - Database driver.
  • Url - JDBC URL of the database that data will be looked up.
  • User - Username used to connect to the database.
  • Password - Password used to connect to the database.
Connection Information - Data Source
Data Source Type - External

DBReport Mediator options field descriptions:

  • Use Transaction  - (Yes/No)
  • Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
  • Database Type:
    • External
    • Carbon Datasource
  • Initial Context
  • Data Source Name
  • Url - JDBC URL of the database that data will be looked up.
  • User - Username used to connect to the database.
  • Password - Password used to connect to the database.
Connection Information - Data Source
Data Source Type - Carbon Datasource

  • Use Transaction - (Yes/No)
  • Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
  • Database Type:
    • External
    • Carbon Datasource
  • Data Source Name

    Tip

    If existing data source is chosen, clicking "Load Data Sources" will give the available datasource. Then a datasource can be selected.

Adding Properties

Property field descriptions:

  • Name - Name of the property.
  • Value - The value of the property.
  • Action - Deletes the property.

Name

Value

autocommit

true / false

isolation

Connection.TRANSACTION_NONE / Connection.TRANSACTION_READ_COMMITTED / Connection.TRANSACTION_READ_UNCOMMITTED / Connection.TRANSACTION_REPEATABLE_READ / Connection.TRANSACTION_SERIALIZABLE

initialsize

int

maxactive

int

maxidle

int

maxopenstatements

int

maxwait

long

minidle

int

poolstatements

true / false

testonborrow

true / false

testwhileidle

true / false

validationquery

String

Adding SQL Statements

Tip

The SQL statement may specify parameters which could be specified as values or XPath expressions.
The types of parameters could be any valid SQL types. Only the first row of a result set will be considered and any others are ignored.

  • SQL- One or more SQL Statements.
  • Parameters - Specify how the values of parameter in the SQL would be calculated. A value can be a static literal text and can be calculated at runtime based on the given expression.
    • Parameter Type
      • CHAR
      • VARCHAR
      • NUMERIC
      • DECIMAL
      • BIT
      • TINYINT
      • SAMLLINT
      • INTEGER
      • BIGINT
      • REAL
      • DOUBLE
      • DATE
      • TIME
      • TIMESTAMP
    • Property Type
      • Value
      • Expression
    • Value/Expression
    • Namespace - Gives possibility to choose an Expression from the list.

      Tip

      You can click this link to add namespaces if you are providing an expression. You will be provided another panel named "Namespace Editor," where you can provide any number of namespace prefixes and URL that you have used in the XPath expression.

    • Action - Delete the parameter.
  • Results - Specify how to deal with rerun result from Database query execution.
    • Result Name
    • Column
    • Action - Deletes the result.

      Note

      You can configure the Mediator using XML. Click on "switch to source view" in the "Mediator" window.


Example

<dbreport xmlns="http://ws.apache.org/ns/synapse">
    <connection>
        <pool>
            <driver>org.apache.derby.jdbc.ClientDriver</driver>
            <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
            <user>esb</user>
            <password>esb</password>
        </pool>
    </connection>
    <statement>
        <sql>update company set price=? where name =?</sql>
        <parameter expression="//m0:return/m1:last/child::text()"
                   xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="DOUBLE"/>
        <parameter expression="//m0:return/m1:symbol/child::text()"
                   xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>
    </statement>
</dbreport>

This example demonstrate simple database write operations. The DBReport Mediator writes to a table using the message details. It works the same as the DBLookup Mediator. In this sample DBReport Mediator is used for updating the stock price of the company using the last quote value which is calculated by evaluating an XPath against the response message.

  • No labels