Database (SQL) Transport

Database (SQL) Transport

The eiConsole’s Database SQL Transport is used to connect to a Relational Database

The Database (SQL) Transport is used in the eiConsole and eiPlatform when you wish to connect to a relational database. As with all the components of the eiConsole, the user is presented with a graphical interface with easy to configure panels.

To connect to a relational database, select this Transport from the Transport Type drop-down menu.

Database SQL Transport Configuration Options in eiConsole

Transport (Adapter) Configuration Drop-Down List

 

Basic Database (SQL) Transport Configuration Options

On the Basic configuration tab, you can specify:

  • Input File – if specified, an SQLXML input file is executed
  • Write Query – enables writing simple SQL queries (will replace transaction body with SQLXML)
  • SQL – SQL query to be executed
  • Query Parameters – parameters used  (in order) with the query

Basic Configuration Option in Database SQL Transport by PilotFish

Database (SQL) Transport Basic Configuration Options

 

Connection Database (SQL) Transport Configuration Options

On the Connection tab, specify connection information for the relational database that you wish to update with the following options:

  • User Name – use to connect to the database
  • Password – used to connect to that database
  • Type – the type of connection to be used via the Type configuration item. The connection type may either be a raw JDBC Connection or a DataSource. When DataSource is selected, you will be prompted to provide the Data Source name. This name is a Java JNDI name where the DataSource is located. Typically, this is configured within an application server.
  • JDBC Driver – when JDBC Connection is the connection type selected, you will be prompted to provide the JDBC Driver. The JDBC Driver is the name of the Java class used to connect to the database,
  • JDBC URL – when JDBC Connection is the connection type selected, you will be prompted to provide the JCBC URL. The JDBC URL is the JDBC compliant URL string used to specify the details of that connection. This typically includes the server and database name.
  • Test Connection – once the connection is fully configured, this button can be used to ensure the credentials are correct

 SQL Transport Connection Options

Database (SQL) Transport Connection Configuration Options

 

Advanced Database (SQL) Transport Configuration Option

On the Advanced tab, the following configuration options are available:

  • Keep Connection – allows specifying whether the JDBC Connection should be held between invocations of this Transport
  • Autocommit Transactions – allows you to enable an autocommit feature. If disabled, each SQLXML document is a transaction.
  • Enable Timeout for Queries – check to enable timeout for queries
  • Timeout for Queries – the amount of time allowed to pass before a query is considered a failure and aborted. If the value is set with enhanced properties, the units are seconds.
  • Restrict Metadata to Catalog – restricts the amount of metadata retrieved to a given catalog pattern to improve performance
  • Restrict Metadata to Schema – restricts the amount of metadata retrieved to a given schema to improve performance
  • Restrict Metadata to Table(s) – restricts the amount of metadata retrieved to a given table to improve performance
  • CallBack Listener – this configuration item is used when the Transport will end with a query, and that query will return results, which will be passed to another interface for processing. When you configure the CallBack Listener, data input into this text area should match the name of a programmatic or triggerable Listener contained within another route.

SQL Transport Advanced Configuration Options

Database (SQL) Transport Advanced Configuration Options (top half of screen)

 

  • Use Single Output Stream – when enabled, query responses are executed as a single transaction
  • Error on Unknown Elements – allows you to specify whether or not to flag as an error when an unknown SQLXML function is encountered
  • Use JDBC Identity for Inserts – if enabled, identity queries will use the JDBC’s generated keys method to improve performance. Selecting this option will override any custom identity query specified in the SQLXML ‘IdentityQuery‘ attribute.

Database SQL Transport Advanced Configuration

Database (SQL) Transport Advanced Configuration Options (bottom half of screen)

 

When the Database SQL Transport is executed, it executes what is called SQLXML. This is an XML syntax that is used to wrap SQL inserts, updates and control structures so that you can execute arbitrarily complex updates or queries against a relational database.

JDBC Properties Database (SQL) Transport Configuration Options

The JDBC Properties tab can be used to specify name/value pairs to the JDBC driver. The availability and behavior of these properties vary between drivers, so referring to a particular driver’s documentation is necessary to utilize specific properties.

 JDBC Properties Configuration in SQL Transport

Database (SQL) Transport JDBC Properties Configuration Options

 

Pooling Database (SQL) Transport Configuration Options

The Polling tab can be used to specify the following options:

  • Use Connection Polling – if enabled, multiple connections are permitted and polled
  • Connections Allowed – the number of simultaneous database connections permitted

Database SQL Transport Pooling Configuration

Database (SQL) Transport Polling Configuration Options

 

Debug Database (SQL) Transport Configuration Options

The Debug tab can be used to specify:

  • Log Metadata – specifies whether or not to output debug information on database metadata loading
  • SQLXML Logging – specifies whether or not to output debug information on SQLXML execution

SQL Database Transport Debug Configuration Options in eiConsole

Database (SQL) Transport Debug Configuration Options

 

Compatibility Database (SQL) Transport Configuration Options

The Compatibility tab can be used to specify the following option:

  • Use JDBC Parameter Typing – if selected, any statement parameters will be type-checked by the JDBC driver only

 JDBC Parameter Typing Option in SQL Database Transport

Database (SQL) Transport Compatibility Configuration Options

 

When the Database (SQL) Transport is executed, it invokes what is called SQLXML. The details of SQLXML are covered in:

eiConsole SQLXML – Selecting Rows (Interface & Tutorial)
eiConsole SQLXML – Inserting and Updating Rows (Interface & Tutorial)

It should be noted that this is an XML syntax that is used to wrap SQL inserts, updates and control structures such that you can execute arbitrarily complex updates or queries against a relational database.

For more information please call us at 860 632 9900 or click the link below to email us.

This is a unique website which will require a more modern browser to work! Please upgrade today!