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:
- Go to the attribute map of the Update or AddOnly Connector
- 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
|