Metamerge logo
Search

Advanced Search
*
*
*
* HOME DOCUMENTS & RESOURCES DOWNLOADS EARLY TECH ACCESS SUPPORT FAQ KNOWN ISSUES OLD VERSIONS
*

 

JDBC Connector

Overview

The JDBCConnector provides access to a variety of systems. In order to reach a system using JDBC you need a JDBC driver from the system provider. This provider is typically delivered with the product in a jar- or zip file. This file (or files) must be in your classpath or copied to the extensions directory.

Example

Demo package containing ready to run configurations for each of the JDBC Connector's modes is included here.  It includes an MS Access database, so in order to run it you must have access to ODBC/Access .

Configuration

All parameters described below give the means to configure the JDBC Connector. They are usually set through the MI Admin - either from the "Configure Connector" screen or by scripting (see table below).

The Connector needs the following parameters (left clicking in the Connector Configuration in the administrator will show you the internal name.  So You will see that JDBC URL is  jdbcSource)

Parameter

Description

connectorType com.architech.connector.rscJdbc
jdbcSource See documentation for your JDBC provider.  It is called JDBC URL in the administrator. See also JDBC Source Parameter Configuration
jdbcLogin The username parameter. Only the tables available to this users will be shown.
jdbcPassword The password for the user
jdbcDriver The JDBC driver class name.
jdbcExposeNullValues If this parameter is set to "true" then NULL values will be returned as an empty attribute (i.e. empty value set). If set to false, then the attribute will not be part of the entry returned.
jdbcSelect The select statement to execute when selecting entries for iteration. If you leave this blank, the default construct (SELECT * FROM TABLE) will be used.
jdbcTable The table/view to operate on. This is only used when the Connector operates in lookup or update mode. If the jdbcSelect parameter is not specified then the iterator mode Connector will also use this parameter to construct a default SELECT statement.
jdbcSessionParameters This parameter is a multi line field where you can specify ALTER SESSION commands. One good example is "SET NLS_FORMAT 'YYYY-MM-DD'".
jdbcDateFormat A format string used to parse dates when they are supplied as strings. See SimpleDateFormat for more details.
jdbcUseProperties If this parameter is set to "true" the JDCB Connector will add JDBC driver parameters from the configuration. You must prefix each additional driver parameter with "jdbc." as in "jdbc.extradriverparam". The latter will cause the JDBC driver to receive a parameter called "extradriverparam" and its associated value.

NB! If you use this method the jdbcLogin and jdbcPassword is not used. You must provide these values using the "jdbc." prefix as well.

connectorFlags A list of flags to enable specific behavior.

{ignoreFieldErrors}
If getting field values causes an error this flag will cause the Connector to return the Java exception object as the value instead of throwing the exception (i.e. calling the Connectors *Fail EventHandlers).

Other

Apart from the standard functions exposed by all Connector, this Connector also exposes several other functions you can use in your scripts.

execSQL (string) Executes an arbitrary SQL command. Returns the error string if it fails.
execSQLSelect (string) Executes SQL SELECT command. Returns the error string if it fails.
getNextSQLSelectEntry () Having excecuted execSQLSelect you can use this method to get the next entry from the result set. 

The Connector's "jdbcTable" parameter must be empty for this to work correctlry.

The above functions does not interfere with the normal flow of entries and attribute mappings for the Connector.

Notes

Timestamp

If you want to store a timestamp value containing both a date and a time, you should make sure you provide an object of type java.sql.Timestamp, as you could with this Attribute Mapping:

a = java.util.Date();
ret.value = new java.sql.Timestamp (a.getTime());

This is only available from version 1.19 of the connector (enclosed in Metamerge 4.6.4).

SQL Databases: Column names with special characters in them

If you have columns with special character in their names and use the AddOnly or Update modes:

  1. Go to the attribute  map of the Update or AddOnly Connector 
  2. Rename the Connector attribute  (not the work attribute!) from name-with-dash to "name-with-dash"  (add quotes).

The necessity of using this functionality might be dependent on the JDBC driver you are using, but standard MS Access 2000 has this problem. 

This fix is only available from version 1.20 of the connector (enclosed in Metamerge 4.6.5).

Use of Prepared Statements

(This section is here to describe how the Connector creates the SQL queries, you can ignore the section unless you are curious about the internals)

For a database, the Connector will use prepared statements or dynamic query depending on the situations:

  •  if the connector gets the schema definition from the database, it will use prepared statements.
  • otherwise, it will create a dynamic SQL query.

See Also

Oracle 8 JDBC Driver, ODBC Driver

 

 

*
  Metamerge Integrator version 4.6 ©Copyright Metamerge AS 2000-2002 Last edited 2002-06-10 contact us