Loading...

Database gateway

Table of Contents

    Database gateway

     This service acts as a gateway to a JDBC Database. The user can specify either a datasource reference (using a bean) or specify connection settings directly for the JDBC driver.

    The data from messagepart-in is used to populate queries. A user can specify two types of queries:

    • SQL Update: used to specify an UPDATE, INSERT or DELETE statement.
    • SQL Select: used to specify a SELECT statement.

    When used simultaneously the service will first execute the SQL Update and then the SQL Select in that order.

    The result sets from either queries will be populated on the messagepart-out specified messagepart in XML format.

    In the table below, you will find an explanation about these properties. All attributes with a ‘*’ are mandatory.

    Attribute

    Description

    Name*

    By default, we fill this out with the technical ‘tag’, followed by a serial number. Changing the name is optional.

    Enabled

    Check this box if you want this service to be enabled.

    DataSource Reference

    Reference to a DataSource Bean. If not presented user should specify JDBC settings below.

    Max Returned Rows

    Maximum number of rows to return. The default is 0, all returned. Only valid in combination with query attribute.

    JDBC Driver

    Class name of JDBC Driver. Either DataSource bean or JDBC settings must be set.

    JDBC URL

    JDBC URL to connect to. Should be like jdbc:[type]://[host]:[port]/[dbname]. Either DataSource bean or JDBC settings must be set.

    JDBC Username

    JDBC username. Either DataSource bean or JDBC settings must be set.

    JDBC Password

    JDBC Password. Either DataSource bean or JDBC settings must be set.

    SQL Update Query

    SQL INSERT/UPDATE/DELETE statement to be executed. By default it returns the number of modified rows. The result is returned in messagepart-out.

    SQL Select Query

    SQL SELECT Query to be executed. Result returned in messagepart-out. Attributes update and query can use parametrized variables which have to be provided in header variables. select * from USERS where username = :headers[username]" where username is added to the ConnectMessage headers in a previous service or by a header enricher."

    MessagePart-In

    Name of the MessagePart in a ConnectMessage where the content of the file is being stored.

    MessagePart-Out

    Name of the MessagePart in a ConnectMessage that is being used for the response. The Message Queue or Time-out Message will be put on this MessagePart before the message is being sent to the next service or producer.

    Result Type

    Result type to return. Either TEXT (String) or DOCUMENT (XML Document).

    Description

    Description of the specific service. This is for documentation purposes.

     

    How to use INSERT/UPDATE/DELETE statements

    When attempting to insert a record into the database, the user can specify the INSERT statement into the SQL Update field like so:

    insert into TABLE(COL1, COl2, COl3) VALUES ('value1’, ‘value2’, ‘value3’)
    
    update TABLE SET COL=’value’1 WHERE COL2=’value2’
    
    delete from TABLE where COL2=’value2’

    This will insert a static record into the database.

    When you want to populate parameters in the Query using the contents of the message, the query can be populated using message-headers. The synaxis for using message-headers is

    Using header information

    You can add header information using the spell format :headers[header-name]

    Where header-name  is substituted for the name of the header that we want to use. When the header does not exist, it will result in a NULL value. You can create and populate headers using the Header Enricher component. Header substitution can be used on all Queries.

    example

    insert into TABLE(COL1, COl2, COl3) VALUES (:headers[someheader1], :headers[someheader2], :headers[someheader3])

    Warning: Be aware that the use of values in queries are type sensitive. The use of Strings, integers and doubles may be required by the JDBC/Database. For correct substitution you must correctly specify the type when creating the header using the Header Enricher.

    How to use SELECT statements:

    When attempting to select one or more records from the database, the user can specify a SELECT statement into the SQL Select field like so:

    SELECT * from TABLE

    This will execute the SELECT statement and return the resultset in XML.

    When you want to populate parameters in the Query using the contents of the message, the query can be populated using message-headers.

    The resultset is returned in XML format like so:

    <?xml version="1.0" encoding="UTF-8"?>
    <Results>
       <Row>
          <COL1>value1</COL1>
          <COL2>value2</COL2>
          <COL3>value3</COL3>
       </Row>
       <Row>
          <COL1>value4</COL1>
          <COL2>value5</COL2>
          <COL3>value6</COL3>
       </Row>
       <Row>
          <COL1>value7</COL1>
          <COL2>value8</COL2>
          <COL3>value9</COL3>
       </Row>
    </Results>

     


    Copyright © 2018 ConnectPlaza.   For pricing, account management and more go to https://www.connectplaza.com

    About Connectplaza

    Si components version:   

    Lorem ipsum delore set

    Lorem ipsum delore set

    Window size is not optimal. Please enlarge for optimal experience

    No connection

    No response from the server. This window will automaticly disappear by an valid connection