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

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(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.
String	execSQL(String sql) Executes an SQL statement.
String	execSQLSelect(String sql) Executes an SQL statement, the returned values can be retrieved using getNextSQLSelectEntry()
Entry	findEntry(SearchCriteria search) Finds an existing entry.
Connection	getConnection() Returns the Connection handle for the currently open session.
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
Statement	getStatement() Returns the Statement handle for the currently open session
String	getSubClause(SearchCriteria.rscSearch rs, boolean ps) Constructs an SQL where expression from an rscSearch class.
String	getVersion() Return version information
String	getWhereClause(SearchCriteria search, boolean ps) Creates where clause for SQL statement
void	initialize(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(Throwable e) Method checks exception type
boolean	isLookupPaddingDisabled() Returns true if padding is disabled for lookup
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.
Object	querySchema(Object table) Query for schema of table
Vector<String>	queryTables() Queries for list Of tables
void	rollback() Rolls back the transactions since the last commit
void	saveMetaData(ResultSetMetaData md) Saves the Meta Data info
void	selectEntries() Prepares for getNextEntry().
boolean	setCommitMode(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
PreparedStatement	setPreparedDeleteStatement(String preparedSql) Sets a prepared statement for future calls to deleteEntry().
PreparedStatement	setPreparedFindStatement(String preparedSql) Sets a prepared statement for future calls to findEntries().
PreparedStatement	setPreparedInsertStatement(String preparedSql) Sets a prepared statement for future calls to putEntry().
PreparedStatement	setPreparedModifyStatement(String preparedSql) Sets a prepared statement for future calls to modEntry().
PreparedStatement	setPreparedSelectStatement(String preparedSql) Sets a prepared statement for future calls to selectEntries().
void	setResultSet(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
String	sqlValue(String name, 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

Constructor Detail

JDBCConnector

public JDBCConnector()

Constructor

Method Detail

initialize

public void initialize(Object o)
                throws Exception

Initialize the connector and discover syntax

Specified by:

initialize in interface ConnectorInterface

Overrides:

initialize in class Connector

Parameters:

o - Ignored

Throws:

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

setSessionParameters

public void setSessionParameters()
                          throws SQLException

Sets the sessionParameters attribute of the JDBCConnector object

Throws:

SQLException - Contains exception thrown during database operations

alterSession

public void alterSession(String command)
                  throws SQLException

Create a ALTER SESSION Sql statement, and execute it.

Parameters:

command - The rest of the ALTER SESSION statement

Throws:

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(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 Exception

Prepares for getNextEntry().

Specified by:

selectEntries in interface ConnectorInterface

Overrides:

selectEntries in class Connector

Throws:

Exception - Exception thrown during database operations

See Also:

getNextEntry()

getNextEntry

public Entry getNextEntry()
                   throws 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:

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 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:

Exception - derived from the connector's underlying classes

modEntry

public void modEntry(Entry entry,
                     SearchCriteria search)
              throws 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:

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

putEntry

public void putEntry(Entry entry)
              throws Exception

Adds a new entry.

Specified by:

putEntry in interface ConnectorInterface

Overrides:

putEntry in class Connector

Parameters:

entry - The entry object

Throws:

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

deleteEntry

public void deleteEntry(Entry entry,
                        SearchCriteria search)
                 throws 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:

Exception - derived from the connector's underlying classes

execSQL

public String execSQL(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 String execSQLSelect(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 Exception

Gets the nextSQLSelectEntry entry

Returns:

The nextSQLSelectEntry value, null if no value available

Throws:

Exception - if an error occurs

See Also:

execSQLSelect(java.lang.String)

commit

public void commit()
            throws SQLException

Commit the last transactions

Throws:

SQLException - Thrown if an error occurs

rollback

public void rollback()
              throws SQLException

Rolls back the transactions since the last commit

Throws:

SQLException - Thrown if an error occurs

See Also:

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

setCommitMode

public boolean setCommitMode(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 Vector<String> queryTables()
                           throws Exception

Queries for list Of tables

Overrides:

queryTables in class Connector

Returns:

Vector List of tables

Throws:

Exception - Thrown if error occurs

See Also:

Vector

querySchema

public Object querySchema(Object table)
                   throws 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:

Exception - Thrown if error occurs

See Also:

Entry, Vector

getStatement

public Statement getStatement()

Returns the Statement handle for the currently open session

Returns:

The statement value

setResultSet

public void setResultSet(ResultSet rs)
                  throws Exception

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

Parameters:

rs - The new resultSet value

Throws:

Exception - Thrown if error occurs

getConnection

public Connection getConnection()

Returns the Connection handle for the currently open session.

Returns:

The connection value

getWhereClause

public String getWhereClause(SearchCriteria search,
                             boolean ps)
                      throws 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:

Exception - Thrown if error occurs

getSubClause

public String getSubClause(SearchCriteria.rscSearch rs,
                           boolean ps)
                    throws 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:

Exception

sqlValue

public String sqlValue(String name,
                       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(ResultSetMetaData md)
                  throws SQLException

Saves the Meta Data info

Parameters:

md - ResultSetMetaData

Throws:

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 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 PreparedStatement setPreparedSelectStatement(String preparedSql)
                                             throws 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:

SQLException - if the PreparedStatement could not be created

Exception

Since:

7.1

setPreparedFindStatement

public PreparedStatement setPreparedFindStatement(String preparedSql)
                                           throws 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:

SQLException - if the PreparedStatement could not be created

Exception

Since:

7.1

setPreparedModifyStatement

public PreparedStatement setPreparedModifyStatement(String preparedSql)
                                             throws 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:

SQLException - if the PreparedStatement could not be created

Exception

Since:

7.1

setPreparedInsertStatement

public PreparedStatement setPreparedInsertStatement(String preparedSql)
                                             throws 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:

SQLException - if the PreparedStatement could not be created

Exception

Since:

7.1

setPreparedDeleteStatement

public PreparedStatement setPreparedDeleteStatement(String preparedSql)
                                             throws 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:

SQLException - if the PreparedStatement could not be created

Exception

Since:

7.1