Input - getJDBC

Description

This input performs SQL queries on JDBC compliant databases.  Each row of the resulting table becomes an iPAM product contained by a vector product.

You must have the JDBC driver required to access the target database.  The driver classes must be in the classpath prior to starting iPAM.

You can substitute the agents last update time and date values into the SQL query based on the user definable escape character. This is done using the escape chacter followed by "date" or "time" keywords.  You may also specify the format that the date and time strings should take on by filling in the corresponding fields with strings that will be interpreted by Java's SimpleDateFormat class.  It is also possible to substitute in an agent property by identifying the appropriate property name.

 

Configuration Variables

dbDriver
This is the class name of the JDBC driver needed to access the target database.  Example: "jdbc.idbDriver" identifies a class named "idbDriver" inside the package "jdbc" that is a driver for InstantDB.

dbURL

This string identifies the JDBC driver you want to use and the database you would like the driver to access.  Example: "jdbc:idb:e:\dev\InstantDB\sample\sample.prp" identifies the "idb" driver and provides a filename that represents the database to open.  This URL is passed directly to the DriverManager class for processing.  The syntax of the URL will be dependent upon the driver that you are using so you may need to consult the documentation provided with the driver in order to properly format this string.

escapeChar

This character is used as a prefix to identify a keyword or property whos value should be substituted into the string.  Keywords currently supported are "date" and "time".  The PIM will replace these keywords with the last date or time that the PIM was activated.

Default value = '@'

See the description of SQLStatement for more information.

SQLStatement

The SQL command to perform on the database.  Example: "SELECT * FROM myTable WHERE @dateField AFTER @date".  This string contains two fields which are tagged with the default escapeChar '@'.  These fields will be replaced with new values.  "@dateField" identifies a PIM property named "dateField".  The value of this property will be substituted into the string.  "@date" indentifies the "date" keyword.  The date when this PIM was last activated will be substituted into the string.

dateFormat

This string identifies how the date will be formatted for use in the SQL statement.

Default value = "yyyy-mm-dd"

timeFormat

This string identifies how the time will be formatted for use in the SQL statement.

Default value = "hh:mm:ss"

lastUpdateTime

The last time that this agent updated.  The value of this property will be automatically changed whenever the
agent activates.  There is no default value and you are not required to provide one.
 

Product

The values of table fields become product attributes. Each row becomes one product. A set of rows in a table is represented as a vector product containing other products.

If the SQL statement produces multiple tables then the agent will produce multiple vector products wrapped by single containing vector product. 

How it works

When the agent activates it first attempts to load the JDBC driver.  It then attempts to use the dbURL to create a connection with the database.  The SQL statement is then executed and the results are translated into iPAM products.

Revised: 14 September 1999