Dialog Descriptions

Dialog Database Connection and Query Type

Database connection

  • Presetting

    For selecting a preconfigured database in order to preset the Database URL and Database driver class fields.

    The following database systems are supported: DB2, H2, MariaDB, Microsoft SQL Server, MySQL, Oracle Thin, and PostgreSQL.

    In case your database is not listed, select Custom and fill in the fields manually.

    Using a non-listed database can cause unexpected results or errors.

    Ensure that your entry in the Database driver class field matches with the uploaded driver. You can take the database driver class from the driver documentation.

    For the database connection with alternative authentication methods, for example, NTML authentication with an SQL server, you generally have to integrate native libraries, refer to Integrating Native Libraries.

  • Database URL

    Is preset if you selected a preconfigured database.

    Adjust the host, port and, database name.

  • SSL Configuration

    To configure encrypted connections with a certificate.

    Only for MySQL database server and without the use of connection pooling.

    The database URL must be extended by the following parameters:

    • verifyServerCertificate=true

    • useSSL=true

    • requireSSL=true

    PKCS #12 format based keystore files are not supported in conjunction with JDK 11.

    Java security must be configured accordingly to support any type of keystore file other than JKS and PKCS #12.

    The following options can be selected:

    • Server authentication

      If selected, the server, which is the connection target, must identify itself to the Database Connector.

      The icon module guide 998 0 opens a file explorer for uploading the truststore file containing the public key of the server.

    • Client authentication

      If selected, the Database Connector must identify itself to the server.

      The icon module guide 998 0 opens a file explorer for uploading the keystore file containing the private key of the client.

  • Database driver class

    Corresponds to the JDBC connection of the database used. To use a different database, you have to install a suitable driver.

    Refer to Managing Libraries.

    After installing a JDBC driver for the MS SQL server, you have to restart the INUBIT Process Engine.

  • Use static login data

    Enter User and Password. Entry depends on your database.

  • Select from Credentials Manager

    For authentication, you can also use credentials managed by the Credentials Manager. Refer to Using the Credentials Manager for Authentication.

  • Use special encoding

    If no other encoding is defined, UTF-8 is used as a default.

  • Connection Pooling

    Activates the connection pooling.

    If the connection pooling is activated, physical database connections are reused. This accelerates the processing of the SQL statements. After being used, connections are not closed but stored and thus made available for further use. So there is no need to open a new connection for each access.

    The Settings button opens the dialog for configuring the connection pooling.

    Refer to Dialog Connection Pooling Properties

  • Use IS Log Database

    When Use IS Log Database is selected, the Database Connector connects to the INUBIT logging database. The configuration of the INUBIT Process Engine is used for this connection, therefore all other fields in this area are deactivated.

    Pooling for the internal database is stored in the code. Therefore, the settings for connection pooling are no longer available when the option Use IS Log Database has been selected.

  • Specific connection parameters

    For defining parameters like, for example, timeouts, number of open connections which are used for configuring the database connection depending on your used database. You define the parameters as name/value pairs.

Connection test

  • Test connection

    For testing whether the connection can be successfully established using your configuration.

Function

You use this selection to define which task the database connector should carry out:

  • Execute input message as query

    The Database Connector executes queries contained in its input message.

    If possible, so-called prepared statements are generated. This involves checking the various database requests to make sure that they are structurally consistent. If the type is the same as that of an SQL statement that accesses the same columns of a table, a command that is carried out multiple times is generated on the database side. This improves performance.

    Refer to Creating Dynamic XML-Queries

  • Execute static query

    The Database Connector executes a static query which you define in the dialog, refer to Dialog Static Query.

    Refer to Creating Static SQL Queries Using the Database Connector

  • Read metadata

    The database connector queries the structure of the database and additional information. You need this information to create requests for mapping tables and column names, for example.

    Refer to Querying Metadata

Settings

  • Automatic type recognition of database object

    If activated, data types are recognized automatically. This means, for example, when a date is inserted it is passed to the database correctly as a date, and not, for example, as a string.

    For case-sensitive databases like Oracle, PostgreSQL, etc.:

    If a table name exists in upper-case letters but a lower-case table name is used in a query, the table name’s actual case is auto-detected. While being executed the query uses the actual upper-case table name.

  • Cache type recognition

    Activate this checkbox to have the table definitions available. Hence, it is not necessary to query the database to receive the table definitions.

  • Auto commit (disable transactions)

    During its execution, a Database Connector can carry out multiple SQL statements. With this option you define the point in time when the changes are permanently stored in the database.

    • If the Auto commit is active, every change request automatically initiates a Commit or Rollback (in case of errors).

    • If the option is inactive, all changes are carried out within one single transaction. If an error occurs during this transaction, the database request is not processes any further. All database requests that have already been executed are rolled back.

    • Ignore errors

      If activated, database requests are executed even after an error occurred.

  • Trim the strings from query

    Leading and trailing white spaces are deleted from all field and condition values. White spaces within a value or condition are not deleted.

Dialog Metadata

In the metadata dialog, you define which information the database should output.

These metadata are output as XML message, for each metadata an XML element is created. You can define the names of these elements and of the root element.

Names must comply with the following rules:

  • Names can contain letters, numbers and other characters.

  • Names must not start with a number or a punctuation character

  • Names must not start with xml, XML, Xml and so on.)

  • Names must not contain blanks

If a name is invalid, the element is named InvalidXmlTag. The metadata are output as value of the name attribute.

By clicking the Generate Preview button a preview of the output format of the metadata is displayed.

  • Only this table

    By default, the data of all tables included in the schema are read. If you select this option, you can restrict the number of tables read to only one. Enter the name of the table, depending on the database used.

  • Root element

    XML root element.

  • Data type

    Reads the data type of the table columns in the database.

  • Type name

    Reads the table column names.

  • Column width

    Specifies the maximum length of a field in the current column of the table.

  • Decimal places

    Indicates the number of digits after the decimal point that values in a table with floating-point numbers may have.

  • NULL values

    Reads which table columns may have blank entries.

  • Remarks

    Reads which table columns have remarks.

Dialog Static Query

In this dialog you define the static query. The request may only contain one statement.

Dialog Query Result

In the dialog for query results, you define how the query result is to be formatted.

Result format

  • Trim string values from database

    Leading and trailing whitespaces are deleted from string values for text fields. Whitespaces that are not at the beginning or end of a text field are ignored.

  • Mark NULL values with an attribute

    This enables distinction between a blank value and a NULL value in the output message. The value NULL is indicated with the attribute Value null="true".

  • Always wrap result with ResultSets tag

    If selected, the result of a simple Insert of one dataset is

    <ResultSets>
  <rows>1</rows>
</ResultSets>

    instead of

    <rows>1</rows>

  • Output time and date fields in XML schema format

    If selected, time and date specifications are output in the defined format and also include indication of time zones, e.g. 2009-10-19T12:25:09.00+02. Otherwise, date and time are output without time zone in local time.

XML element names

You use the text fields to determine the names of elements to be created in the XML output message.

  • Formatting type

    • Uncompressed

      The result is encapsulated by elements. You can modify the element identifier manually. Example:

      <ResultSet>
  <Row>
    <Column id="1">
      <Name>Street</Name>
      <Value>Unter den Linden</Value>
    </Column>
  </Row>
</ResultSet>

    • Compressed

      The result is encapsulated by elements (besides field names and values). You can modify the element identifier manually. Example:

      <ResultSet>
  <R>
    <C>Street</C>
  </R>
  <R>
    <C>Unter den Linden</C>
  </R>
</ResultSet>

    • Names as tags

      The names of the columns are used as tag names. If the column names do not comply with the conventions for XML elements, InvalidXmlTag elements are created and the column name is output as the value of the Name attribute.

    • User-defined

      Enter the entire name of the relevant Java class to be used for the output.

      Alternatively, you can let a Java class output the SQL result sets and transform them to XML. Enter the name of the class.

      The class must implement the interface com.inubit.ibis.utils.query.ResultSetOutput (contained in the SDK).

  • Row number

    When selected, the attribute ID with the current sequential number is added as a value to the ROW and COLUMN elements, e.g.:

    <ResultSet>
  <Row id="1">
  <Column id="1">Name</Column>
    <Column id="2">Street</Column>
  </Row>
</ResultSet>

Preview

By clicking the Generate preview button a preview of the output format of the query result is displayed.

Dialog Connection Pooling Properties

In the dialog for database pooling properties, you configure the settings of the connection pool.

If the connection pooling is activated, physical database connections are reused. This accelerates the processing of the SQL statements. After being used, connections are not closed but stored and thus made available for further use. So there is no need to open a new connection for each access.

Reuse of the connection pool:

All Database Connectors as well as Database Object Connectors whose values for the settings database URL, User, Password and Auto Commit match use the same connection pool and have to be configured with identical connection pool settings.

Connection pooling

  • Max. number of active connections in the pool

    Defines how many connections can be active at the same time.

    If there are too few connections in the connection pool, queue time might arise. However, too many connections can place unnecessary load on the database system, since the connections create a certain amount of administration effort on the database side.

  • Max. number of inactive connections in the pool

    Defines how many connections in the connection pool may be inactive at the same time.

  • Min. number of inactive connections in the pool

    Specifies how many inactive connections the connection pool permanently holds.

  • Min. time for inactive connections (ms)

    Duration of an unused pool connection to stay open until it is terminated.

  • Time between checks for inactive connections (ms)

    Time in milliseconds between the inactive connection checks. The value -1 indicates that no check takes place.

  • Max. time the pool will wait for a connection to be returned (ms)

    Specify, how long the pool should wait at the most, until the Connector gets a connection from the pool.

    Make sure that the database timeouts are less than timeouts set for the Database Connector module.

    Refer to Dialog System Connector Properties

    In the following table, timeout parameters are listed for several databases. Set the parameter(s) suited for the database the connector shall be connected with by clicking the Specific connection parameters in the Connection parameters dialog.

    Database type Connect Timeout Property Name Read Timeout Property Name

    Oracle

    oracle.net.CONNECT_TIMEOUT

    oracle.jdbc.ReadTimeout

    MySQL

    connectTimeout

    socketTimeout

    SQL Server

    loginTimeout

    H2

    h2.socketConnectTimeout

    PostgreSQL

    connectTimeout

    socketTimeout

    DB2

    loginTimeout

    blockingReadConnectionTimeout

Refer to Dialog Database Connection and Query Type

  • Max. age of a connection (ms)

    A connection is closed after the specified age in milliseconds is elapsed.

  • Enable JMX MBeans for Tomcat connection pool monitoring

    If enabled the property "jmxEnabled" is set to provide monitoring of the Tomcat JDBC Connection Pool via MBean.

  • Remove abandoned connections

    An indicator to remove connections that exceed a specified timeout threshold.

  • Timeout before abandoned connection is removed (s)

    Timeout in seconds before an abandoned (in-use) connection can be removed. The default value is set to 60 seconds.

Validation

The pool uses the connection only after successful check. In case, the check fails, a new connection is created. This way, errors in the Database Connector are avoided and the stability is improved for databases under high load.

  • Test when taking connections from the pool

    Tests the functionality of a database connection in the pool if the connection is being taken from the pool.

  • Test when putting back connections into the pool

    Tests the functionality of a database connection in the pool if the connection is being put back into the pool.

  • Test while the connections are inactive

    Tests the functionality of a database connection in the pool while it is not being used.

  • Test when the connection is first created

    Tests the functionality of a database connection in the pool when the connection is first created.

  • Validation interval (ms) (only active if at least one of the options in the Validation section is checked)

    The inactive connections are tested again if the configured validation interval in milliseconds has elapsed.

  • Pool validation query timeout (s) (only active if at least one of the options in the Validation section is checked)

    Time in seconds the pool waits for the response of a validation query

  • Validation SQL query (only active if at least one of the options in the Validation section is checked)

    Checks and validates the connection with an SQL query. The button module guide 1011 0 starts the test.

  • Set presettings (button)

    Recover the provided pre-settings of the database pooling properties.

Press Esc to exit full screen
Modal image placeholder
Press Esc to exit full screen