com.ibm.di.connector
Class JDBCConnector

java.lang.Object
  com.ibm.di.connector.Connector
      com.ibm.di.connector.JDBCConnector

All Implemented Interfaces:

ConnectorInterface, SkipLookupInterface, VersionInfoInterface

Direct Known Subclasses:

DBChangelogConnector

public class JDBCConnector
extends Connector
implements ConnectorInterface, SkipLookupInterface

This connector provides access to JDBC/ODBC based systems. The connector will attempt to perform as much conversion between types as possible.

Field Summary

static java.lang.String[]	CONNECTOR_MODES Possible Connector modes.
static java.lang.String	VERSION_INFO The Connector version.

Fields inherited from class com.ibm.di.connector.Connector

ALL_MODES, myLog, PROPERTY_MESSAGE, PROPERTY_READER, PROPERTY_WRITER

Constructor Summary

JDBCConnector()
Constructor

Method Summary

void	alterSession(java.lang.String command) Create a ALTER SESSION Sql statement, and execute it.
void	commit() Commit the last transactions
void	deleteEntry(Entry entry, SearchCriteria search) Deletes an existing entry.
java.lang.String	execSQL(java.lang.String sql) Executes an SQL statement.
java.lang.String	execSQLSelect(java.lang.String sql) Executes an SQL statement, the returned values can be retrieved using getNextSQLSelectEntry()
void	extractExceptionInformation(Entry error) Extracts additional information about a connector specific exception.
Entry	findEntry(SearchCriteria search) Finds an existing entry.
java.sql.Connection	getConnection() Returns the Connection handle for the currently open session.
java.lang.String	getLastSqlString() Returns the last SQL String used.
Entry	getNextEntry() Gets the nextEntry attribute of the JDBCConnector object
Entry	getNextSQLSelectEntry() Gets the nextSQLSelectEntry entry
int	getNumSkipLookupAffected() Returns the number of affected entries after skipping lookup
boolean	getParameterSubstitution() Returns value of enableParamSubstitute parameter
java.lang.String	getPreparedString() Returns the last String used to construct a PreparedStatement.
java.sql.Statement	getStatement() Returns the Statement handle for the currently open session
java.lang.String	getSubClause(SearchCriteria.rscSearch rs, boolean ps) Constructs an SQL where expression from an rscSearch class.
java.lang.String	getVersion() Return version information
java.lang.String	getWhereClause(SearchCriteria search, boolean ps) Creates where clause for SQL statement
void	initialize(java.lang.Object o) Initialize the connector and discover syntax
boolean	isDeltaSupported() Returns true if this connector is able to perform delta updates
boolean	isEOCflag() Checks whether the end of cycle is reached
boolean	isInsertPaddingDisabled() Returns true if padding is disabled for insert
boolean	isIOException(java.lang.Throwable e) Method checks exception type
boolean	isLookupPaddingDisabled() Returns true if padding is disabled for lookup
boolean	isMSSqlServerDB() Returns true if the DB is a isMSSqlServerDB database
boolean	isUpdatePaddingDisabled() Returns true if padding is disabled for update
void	modEntry(Entry entry, SearchCriteria search) Modifies an existing entry.
void	putEntry(Entry entry) Adds a new entry.
java.lang.Object	querySchema(java.lang.Object table) Query for schema of table
java.util.Vector<java.lang.String>	queryTables() Queries for list Of tables
void	rollback() Rolls back the transactions since the last commit
void	saveMetaData(java.sql.ResultSetMetaData md) Saves the Meta Data info
void	selectEntries() Prepares for getNextEntry().
boolean	setCommitMode(java.lang.String mode) Set the commit behavior of this JDBC Connector.
void	setCurrent(Entry entry, SearchCriteria search) Change the SearchCriteria search to find the entry sent as a parameter.
void	setEOCflag(boolean eoc) Sets the EOC flag
void	setPaddingInInsert(boolean val) Enables and disables the padding while insert
void	setPaddingInLookup(boolean val) Enables and disables the padding while lookup
void	setPaddingInUpdate(boolean val) Enables and disables the padding while update
void	setParameterSubstitution(boolean val) set enableParamSubstitute parameter
java.sql.PreparedStatement	setPreparedDeleteStatement(java.lang.String preparedSql) Sets a prepared statement for future calls to deleteEntry().
java.sql.PreparedStatement	setPreparedFindStatement(java.lang.String preparedSql) Sets a prepared statement for future calls to findEntries().
java.sql.PreparedStatement	setPreparedInsertStatement(java.lang.String preparedSql) Sets a prepared statement for future calls to putEntry().
java.sql.PreparedStatement	setPreparedModifyStatement(java.lang.String preparedSql) Sets a prepared statement for future calls to modEntry().
java.sql.PreparedStatement	setPreparedSelectStatement(java.lang.String preparedSql) Sets a prepared statement for future calls to selectEntries().
void	setResultSet(java.sql.ResultSet rs) Instructs this connector to use the provided result set instead of its own.
void	setSessionParameters() Sets the sessionParameters attribute of the JDBCConnector object
java.lang.String	sqlValue(java.lang.String name, java.lang.Object value, boolean padString) Converts java type to SQL type
void	terminate() terminate - close handles and connections

Methods inherited from class com.ibm.di.connector.Connector

addFindEntry, allModes, clearFindEntries, debug, debugMode, findEntry, getBoolean, getClassInstance, getConfiguration, getContext, getFindEntryCount, getFirstFindEntry, getLog, getMaxDuplicateEntries, getModes, getModes, getName, getNextClient, getNextFindEntry, getParam, getParser, getProperty, getPushbackEntry, getRawConnectorConfiguration, getRSInterface, getUI, hasConfigValue, hasParser, initParser, isExceptionFatal, logError, logmsg, modEntry, pushback, queryOperations, queryReply, reconnect, reconnect, registerScriptBeans, replyEntry, setConfiguration, setContext, setDebugMode, setLog, setMaxDuplicateEntries, setModes, setModes, setName, setParam, setParser, setProperty, setRSInterface, terminateServer

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface com.ibm.di.connector.ConnectorInterface

getConfiguration, getContext, getFindEntryCount, getFirstFindEntry, getMaxDuplicateEntries, getName, getNextClient, getNextFindEntry, getParam, getPushbackEntry, getRawConnectorConfiguration, isExceptionFatal, modEntry, pushback, queryOperations, queryReply, reconnect, registerScriptBeans, replyEntry, setConfiguration, setContext, setLog, setMaxDuplicateEntries, setName, setParam, setRSInterface, terminateServer

Field Detail

VERSION_INFO

public static final java.lang.String VERSION_INFO

The Connector version.

See Also:

Constant Field Values

CONNECTOR_MODES

public static final java.lang.String[] CONNECTOR_MODES

Possible Connector modes.

Constructor Detail

JDBCConnector

public JDBCConnector()

Constructor

Method Detail

initialize

public void initialize(java.lang.Object o)
                throws java.lang.Exception

Initialize the connector and discover syntax

Specified by:

initialize in interface ConnectorInterface

Overrides:

initialize in class Connector

Parameters:

o - Ignored

Throws:

java.lang.Exception - Throws exception if required JDBC parameters are not set.

setSessionParameters

public void setSessionParameters()
                          throws java.sql.SQLException

Sets the sessionParameters attribute of the JDBCConnector object

Throws:

java.sql.SQLException - Contains exception thrown during database operations

alterSession

public void alterSession(java.lang.String command)
                  throws java.sql.SQLException

Create a ALTER SESSION Sql statement, and execute it.

Parameters:

command - The rest of the ALTER SESSION statement

Throws:

java.sql.SQLException - Contains exception thrown during database operations

isDeltaSupported

public boolean isDeltaSupported()

Returns true if this connector is able to perform delta updates

Specified by:

isDeltaSupported in interface ConnectorInterface

Overrides:

isDeltaSupported in class Connector

Returns:

true if delta updates are supported, false otherwise

isIOException

public boolean isIOException(java.lang.Throwable e)

Method checks exception type

Specified by:

isIOException in interface ConnectorInterface

Overrides:

isIOException in class Connector

Parameters:

e - Exception

Returns:

true if exception of type IOException ,if of type SQLException return false

terminate

public void terminate()

terminate - close handles and connections

Specified by:

terminate in interface ConnectorInterface

Overrides:

terminate in class Connector

selectEntries

public void selectEntries()
                   throws java.lang.Exception

Prepares for getNextEntry().

Specified by:

selectEntries in interface ConnectorInterface

Overrides:

selectEntries in class Connector

Throws:

java.lang.Exception - Exception thrown during database operations

See Also:

getNextEntry()

getNextEntry

public Entry getNextEntry()
                   throws java.lang.Exception

Gets the nextEntry attribute of the JDBCConnector object

Specified by:

getNextEntry in interface ConnectorInterface

Overrides:

getNextEntry in class Connector

Returns:

The nextEntry value

Throws:

java.lang.Exception - Exception thrown during database operations

See Also:

ConnectorInterface.selectEntries()

setPaddingInInsert

public void setPaddingInInsert(boolean val)

Enables and disables the padding while insert

Parameters:

val -

setPaddingInLookup

public void setPaddingInLookup(boolean val)

Enables and disables the padding while lookup

Parameters:

val - value to set

setPaddingInUpdate

public void setPaddingInUpdate(boolean val)

Enables and disables the padding while update

Parameters:

val - value to set

isLookupPaddingDisabled

public boolean isLookupPaddingDisabled()

Returns true if padding is disabled for lookup

Returns:

boolean

isUpdatePaddingDisabled

public boolean isUpdatePaddingDisabled()

Returns true if padding is disabled for update

Returns:

boolean

isInsertPaddingDisabled

public boolean isInsertPaddingDisabled()

Returns true if padding is disabled for insert

Returns:

boolean

findEntry

public Entry findEntry(SearchCriteria search)
                throws java.lang.Exception

Finds an existing entry. The search criteria specifies which entry to modify.

Specified by:

findEntry in interface ConnectorInterface

Overrides:

findEntry in class Connector

Parameters:

search - The search criteria used to locate the entry to be modified

Returns:

the single entry found or null

Throws:

java.lang.Exception - derived from the connector's underlying classes

modEntry

public void modEntry(Entry entry,
                     SearchCriteria search)
              throws java.lang.Exception

Modifies an existing entry. The new entry data is given by the entry parameter and the search criteria specifies which entry to modify.

Specified by:

modEntry in interface ConnectorInterface

Overrides:

modEntry in class Connector

Parameters:

entry - The entry data

search - The search criteria used to locate the entry to be modified

Throws:

java.lang.Exception - Any exceptions thrown by the connector's underlying classes

putEntry

public void putEntry(Entry entry)
              throws java.lang.Exception

Adds a new entry.

Specified by:

putEntry in interface ConnectorInterface

Overrides:

putEntry in class Connector

Parameters:

entry - The entry object

Throws:

java.lang.Exception - Any exceptions thrown by the connector's underlying classes

deleteEntry

public void deleteEntry(Entry entry,
                        SearchCriteria search)
                 throws java.lang.Exception

Deletes an existing entry. The search criteria specifies which entry to modify.

Specified by:

deleteEntry in interface ConnectorInterface

Overrides:

deleteEntry in class Connector

Parameters:

entry - The entry data

search - The search criteria used to locate the entry to be modified

Throws:

java.lang.Exception - derived from the connector's underlying classes

execSQL

public java.lang.String execSQL(java.lang.String sql)

Executes an SQL statement.

Parameters:

sql - The SQL statement to execute

Returns:

An empty string if no error, otherwise a string describing the error

execSQLSelect

public java.lang.String execSQLSelect(java.lang.String sql)

Executes an SQL statement, the returned values can be retrieved using getNextSQLSelectEntry()

Parameters:

sql - The SQL statement to execute

Returns:

An empty string if no error, otherwise a string describing the error

getNextSQLSelectEntry

public Entry getNextSQLSelectEntry()
                            throws java.lang.Exception

Gets the nextSQLSelectEntry entry

Returns:

The nextSQLSelectEntry value, null if no value available

Throws:

java.lang.Exception - if an error occurs

See Also:

execSQLSelect(java.lang.String)

commit

public void commit()
            throws java.sql.SQLException

Commit the last transactions

Throws:

java.sql.SQLException - Thrown if an error occurs

rollback

public void rollback()
              throws java.sql.SQLException

Rolls back the transactions since the last commit

Throws:

java.sql.SQLException - Thrown if an error occurs

See Also:

commit(), setCommitMode(java.lang.String)

setCommitMode

public boolean setCommitMode(java.lang.String mode)

Set the commit behavior of this JDBC Connector.

Parameters:

mode - The intended behavior. Possible values are:

• "After every database operation"
• "On Connector close"
• "Manual"

Returns:

false if the requested mode is not a legal value

See Also:

commit()

queryTables

public java.util.Vector<java.lang.String> queryTables()
                                               throws java.lang.Exception

Queries for list Of tables

Overrides:

queryTables in class Connector

Returns:

Vector List of tables

Throws:

java.lang.Exception - Thrown if error occurs

See Also:

Vector

querySchema

public java.lang.Object querySchema(java.lang.Object table)
                             throws java.lang.Exception

Query for schema of table

Specified by:

querySchema in interface ConnectorInterface

Overrides:

querySchema in class Connector

Parameters:

table - Entry object

Returns:

MetaData of queried table

Throws:

java.lang.Exception - Thrown if error occurs

See Also:

Entry, Vector

getStatement

public java.sql.Statement getStatement()

Returns the Statement handle for the currently open session

Returns:

The statement value

setResultSet

public void setResultSet(java.sql.ResultSet rs)
                  throws java.lang.Exception

Instructs this connector to use the provided result set instead of its own.

Parameters:

rs - The new resultSet value

Throws:

java.lang.Exception - Thrown if error occurs

getConnection

public java.sql.Connection getConnection()

Returns the Connection handle for the currently open session.

Returns:

The connection value

getWhereClause

public java.lang.String getWhereClause(SearchCriteria search,
                                       boolean ps)
                                throws java.lang.Exception

Creates where clause for SQL statement

Parameters:

search - SearchCriteria

ps - Boolean which determines if is a prepared statement.

Returns:

sql SQL string with where clause

Throws:

java.lang.Exception - Thrown if error occurs

getSubClause

public java.lang.String getSubClause(SearchCriteria.rscSearch rs,
                                     boolean ps)
                              throws java.lang.Exception

Constructs an SQL where expression from an rscSearch class.

Parameters:

rs - search criteria

ps - Boolean which determines if it is a prepared statement.

Returns:

The SQL where expression

Throws:

java.lang.Exception

sqlValue

public java.lang.String sqlValue(java.lang.String name,
                                 java.lang.Object value,
                                 boolean padString)

Converts java type to SQL type

Parameters:

name - name of the type

value - type

padString - true if padding enabled

Returns:

the SQL type

saveMetaData

public void saveMetaData(java.sql.ResultSetMetaData md)
                  throws java.sql.SQLException

Saves the Meta Data info

Parameters:

md - ResultSetMetaData

Throws:

java.sql.SQLException - Thrown if there is an error

setCurrent

public void setCurrent(Entry entry,
                       SearchCriteria search)

Change the SearchCriteria search to find the entry sent as a parameter. Used when multiple entries found, and you want to modify or delete one of them. Only a few connectors need to implement this.

Specified by:

setCurrent in interface ConnectorInterface

Overrides:

setCurrent in class Connector

Parameters:

entry - The entry we want to find for modification/delete

search - The SearchCriteria we want to change

getVersion

public java.lang.String getVersion()

Return version information

Specified by:

getVersion in interface VersionInfoInterface

Returns:

The version value

setParameterSubstitution

public void setParameterSubstitution(boolean val)

set enableParamSubstitute parameter

Parameters:

val - true if parameter substitution is enabled

getParameterSubstitution

public boolean getParameterSubstitution()

Returns value of enableParamSubstitute parameter

Returns:

true

getNumSkipLookupAffected

public int getNumSkipLookupAffected()

Returns the number of affected entries after skipping lookup

Specified by:

getNumSkipLookupAffected in interface SkipLookupInterface

Returns:

number of affected entries

isEOCflag

public boolean isEOCflag()

Checks whether the end of cycle is reached

Returns:

true if EOC is reached

setEOCflag

public void setEOCflag(boolean eoc)

Sets the EOC flag

Parameters:

eoc - true if EOC is reached

setPreparedSelectStatement

public java.sql.PreparedStatement setPreparedSelectStatement(java.lang.String preparedSql)
                                                      throws java.lang.Exception

Sets a prepared statement for future calls to selectEntries(). This method must be called after the connector has been initialized. As an example of how to use this, this code could be put in the Before Selection Hook:

                ps = thisConnector.connector.setPreparedSelectStatement("Select * from tableName where fieldName = ? and field2= ?")
                ps.setInteger(1, someValue)
                ps.setObject(2, someObject)
                

Once setPreparedSelectStatement is called, that PreparedStatement will be used for every selectEntries() from then on. Calling the method with null as parameter could reset the behavior to using the GUI defined parameters.

Parameters:

preparedSql - The prepared statement (string) to use.

Returns:

the PreparedStatement. null is returned if a PreparedStatement could not be created.

Throws:

java.sql.SQLException - if the PreparedStatement could not be created

java.lang.Exception

Since:

7.1

setPreparedFindStatement

public java.sql.PreparedStatement setPreparedFindStatement(java.lang.String preparedSql)
                                                    throws java.lang.Exception

Sets a prepared statement for future calls to findEntries(). This method must be called after the connector has been initialized. As an example of how to use this, this code could be put in the After Initialize Hook:

                ps = thisConnector.connector.setPreparedFindStatement("Select * from tableName where fieldName = ? and field2= ?")
                

And this code could be put in the Before Lookup Hook:

                ps.setInteger(1, someValue)
                ps.setObject(2, someObject)
                

Once setPreparedFindStatement is called, that PreparedStatement will be used for every findEntries() from then on, effectively overriding any Link Criteria. Calling the method with null as parameter could reset the behavior to using the GUI defined parameters.

Parameters:

preparedSql - The prepared statement (string) to use.

Returns:

the PreparedStatement. null is returned if a PreparedStatement could not be created.

Throws:

java.sql.SQLException - if the PreparedStatement could not be created

java.lang.Exception

Since:

7.1

setPreparedModifyStatement

public java.sql.PreparedStatement setPreparedModifyStatement(java.lang.String preparedSql)
                                                      throws java.lang.Exception

Sets a prepared statement for future calls to modEntry(). This method must be called after the connector has been initialized. As an example of how to use this, this code could be put in the Before Modify Hook:

                ps = thisConnector.connector.setPreparedModifyStatement("UPDATE tableName SET fieldName1 = ?, field2 = ? WHERE field3 = ?")
                ps.setTime(1, conn.fieldName1)
                ps.setObject(2, conn.field2)
                ps.setInteger(3, conn.field3)
                

Once setPreparedModifyStatement is called, that PreparedStatement will be used for every modEntry() from then on, effectively overriding any Link Criteria, and also ignoring all values in the conn Entry. Calling the method with null as parameter will reset the behavior to using the GUI defined parameters.

Parameters:

preparedSql - The prepared statement (string) to use.

Returns:

the PreparedStatement. null is returned if a PreparedStatement could not be created.

Throws:

java.sql.SQLException - if the PreparedStatement could not be created

java.lang.Exception

Since:

7.1

setPreparedInsertStatement

public java.sql.PreparedStatement setPreparedInsertStatement(java.lang.String preparedSql)
                                                      throws java.lang.Exception

Sets a prepared statement for future calls to putEntry(). This method must be called after the connector has been initialized. As an example of how to use this, this code could be put in the Before Add Hook:

                ps = thisConnector.connector.setPreparedInsertStatement("INSERT into tableName (fieldName1,field2,field3) VALUES (?,?,?)")
                ps.setString(1, conn.fieldName1)
                ps.setObject(2, conn.field2)
                ps.setInteger(3, conn.field3)
                

Once setPreparedInsertStatement is called, that PreparedStatement will be used for every putEntry() from then on, effectively ignoring all values in the conn Entry. Calling the method with null as parameter will reset the behavior to using the GUI defined parameters.

Parameters:

preparedSql - The prepared statement (string) to use.

Returns:

the PreparedStatement. null is returned if a PreparedStatement could not be created.

Throws:

java.sql.SQLException - if the PreparedStatement could not be created

java.lang.Exception

Since:

7.1

setPreparedDeleteStatement

public java.sql.PreparedStatement setPreparedDeleteStatement(java.lang.String preparedSql)
                                                      throws java.lang.Exception

Sets a prepared statement for future calls to deleteEntry(). This method must be called after the connector has been initialized. As an example of how to use this, this code could be put in the Before Delete Hook:

                ps = thisConnector.connector.setPreparedDeleteStatement("DELETE from tableName where fieldName1 = ? and field2 = ?")
                ps.setTime(1, conn.fieldName1)
                ps.setObject(2, conn.field2)
                

Here is an example showing how to delete several Entries:

                ps = thisConnector.connector.setPreparedDeleteStatement("DELETE from tableName where fieldName1 = ? and field2 = ?")
                while (...) {
                        ps.setTime(1, ...)
                        ps.setObject(2, ...)
                        thisConnector.connector.deleteEntry(null); //No need to provide an Entry when using prepared statement
                }
                thisConnector.connector.setPreparedDeleteStatement(null)
                

Once setPreparedDeleteStatement has been called, that PreparedStatement will be used for every deleteEntry() from then on, effectively overriding any Link Criteria. Calling the method with null as parameter will reset the behavior to using the GUI defined parameters.

Parameters:

preparedSql - The prepared statement (string) to use.

Returns:

the PreparedStatement. null is returned if a PreparedStatement could not be created.

Throws:

java.sql.SQLException - if the PreparedStatement could not be created

java.lang.Exception

Since:

7.1

getPreparedString

public java.lang.String getPreparedString()

Returns the last String used to construct a PreparedStatement. The Connector will usually try to use a PreparedStatement when modifying or retrieving information, except in Iterator Mode, but Connector parameters may change the behavior. As an example of how to use this, this code could be put in the Default Success Hook:

            ps = thisConnector.connector.getPreparedString();
            task.logmsg("The Prepared Statement was :\n" + ps);
           

Returns:

the last String used to construct a PreparedStatement.

isMSSqlServerDB

public boolean isMSSqlServerDB()

Returns true if the DB is a isMSSqlServerDB database

Returns:

extractExceptionInformation

public void extractExceptionInformation(Entry error)

Description copied from class: Connector

Extracts additional information about a connector specific exception. The method will usually be called automatically to add information to the error Entry. The default behavior is to do nothing.

Overrides:

extractExceptionInformation in class Connector

Parameters:

error - an Entry object containing the exception in its "exception" attribute.

getLastSqlString

public java.lang.String getLastSqlString()

Returns the last SQL String used. The Connector will usually try to use a PreparedStatement when modifying or retrieving information. If that fails, a SQL String is usually constructed, and this method returns that String.

Returns:

the last SQLString used.

Since:

7.2