Technical Guide Reference Guide Frequently Asked Questions Tutorials
Show menu

Database gateway

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 of 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

Set this value to true, if you want this service to be enabled.

DataSource Reference

Reference to a DataSource Bean. If a bean is not specified the 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. For example, 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 synax for using message-headers is:

:headers[headername]

Where headername  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>