com.ibm.di.connector
Class JDBCConnector

java.lang.Object
  extended by com.ibm.di.connector.Connector
      extended by 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