JDBC Driver Support

The InterSystems IRIS® JDBC Driver is a fully compliant type 4 implementation of the JDBC 4.2 standard. This chapter lists all classes and interfaces of the JDBC 4.2 API, indicates the level of support for each one, and describes all InterSystems-specific features. The following topics are discussed:

JDBC and the InterSystems JDBC Driver — provides an overview and resource links for the JDBC driver.
JDBC Driver Compliance — lists all classes and interfaces specified by the JDBC standard, and indicates the current level of support.
Variants and Unsupported Optional Methods — provides details on classes that include permitted variances from the standard.
InterSystems Enhancements and Extensions — lists and discusses InterSystems extensions to the standard JDBC API.

JDBC and the InterSystems JDBC Driver

The Java JDBC API is the industry standard for vendor-neutral database connectivity. It provides a reliable way for Java applications to connect to data sources on any supported platform and to query or perform operations on them with SQL.

InterSystems JDBC is implemented in a type 4 driver to deliver the highest possible performance. Type 4 means that it is a direct-to-database pure Java driver, installed inside the client JVM and requiring no external software support. It is fully compliant with the JDBC 4.2 API specification, supporting all required interfaces and adhering to all JDBC 4.2 guidelines and requirements. InterSystems IRIS supports all features except SQL Exception handling enhancements, National Character Set conversions, and the XML data type.

See Using Java with InterSystems Software for a full description of API features and usage. That book also provides an overview of all InterSystems IRIS Java technologies enabled by the JDBC driver (see “InterSystems Java Connectivity Options”).

Installation and Configuration

The InterSystems JDBC driver is included in the standard InterSystems IRIS installation package. No extra installation or setup procedures are required. See “Client-Server Configuration” in Using Java with InterSystems Software for information on client requirements and usage.

JDBC Driver Compliance

This section provides information on the level of support for each JDBC interface.

Required java.sql Interfaces

The following interfaces must be implemented. Some classes contain methods that are optional if the implementation depends on a feature that the database does not support. The standard implementation annotation indicates that the generic implementation of the class has been used without alteration:

java.sql.CallableStatement — implemented with some permitted variances (see “CallableStatement: Unsupported Methods” and “CallableStatement getBinaryStream() Extension Method”).
java.sql.ClientInfoStatus — standard implementation.
java.sql.Connection — implemented with some permitted variances (see Connection: Unsupported or Restricted Methods).
java.sql.DatabaseMetaData — implemented with some permitted variances (see “DatabaseMetaData: Variant Methods”).
java.sql.Date — standard implementation.
java.sql.Driver — implemented with some permitted variances (see “Driver: Unsupported Methods”).
java.sql.DriverManager — standard implementation.
java.sql.DriverPropertyInfo — standard implementation.
java.sql.ParameterMetaData — all methods fully supported.
java.sql.PreparedStatement — implemented with some permitted variances (see “PreparedStatement: Unsupported Methods”).
java.sql.ResultSet — implemented with some permitted variances (see “ResultSet: Unsupported or Restricted Methods”) .
java.sql.ResultSetMetaData — all methods fully supported.
java.sql.RowIdLifeTime — standard implementation.
java.sql.SQLPermission — standard implementation.
java.sql.Statement — implemented with some permitted variances (see “Statement: Unsupported or Restricted Methods”).
java.sql.Time — standard implementation.
java.sql.Timestamp — standard implementation.
java.sql.Types — standard implementation.
java.sql.Wrapper — all methods fully supported.

Optional java.sql Interfaces

All optional java.sql interfaces are listed below. Italicized items are not implemented:

java.sql.Array
java.sql.Blob — all methods fully supported.
java.sql.Clob — all methods fully supported.
java.sql.NClob — all methods fully supported.
java.sql.Ref
java.sql.RowId — all methods fully supported.
java.sql.Savepoint — all methods fully supported.
java.sql.SQLData
java.sql.SQLInput
java.sql.SQLOutput
java.sql.SQLXML
java.sql.Struct

java.sql Exceptions

The InterSystems JDBC driver throws only the following exceptions:

java.sql.BatchUpdateException
java.sql.SQLException
java.sql.SQLWarning

The following exceptions are listed here for completeness, but are not required and are never used:

DataTruncation
SQLClientInfoException
SQLDataException
SQLFeatureNotSupportedException
SQLIntegrityConstraintViolationException
SQLInvalidAuthorizationSpecException
SQLNonTransientConnectionException
SQLNonTransientException
SQLRecoverableException
SQLSyntaxErrorException
SQLTimeoutException
SQLTransactionRollbackException
SQLTransientConnectionException
SQLTransientException

Required javax.sql Interfaces

The following required interfaces are supported. The standard implementation annotation indicates that the generic implementation of the class has been used without alteration:

javax.sql.ConnectionEvent — standard implementation.
javax.sql.DataSource — implemented with enhancements and additional methods (see “DataSource Extensions and Enhancements” for details).
javax.sql.RowSetEvent — standard implementation.
javax.sql.StatementEvent — standard implementation.

Optional javax.sql Interfaces

All optional javax.sql interfaces are listed below. Italicized items are not implemented:

javax.sql.CommonDataSource — not implemented. Use javax.sql.DataSource instead (see “DataSource Extensions and Enhancements” for related information).
javax.sql.ConnectionEventListener — all methods fully supported.
javax.sql.ConnectionPoolDataSource — implemented with variants and additional methods (see “ConnectionPoolDataSource Extensions and Enhancements” for details).
javax.sql.PooledConnection — all methods fully supported.
javax.sql.Rowset
javax.sql.RowSetInternal
javax.sql.RowSetListener
javax.sql.RowSetMetaData
javax.sql.RowSetReader
javax.sql.RowSetWriter
javax.sql.StatementEventListener
javax.sql.XAConnection
javax.sql.XADataSource

Variants and Unsupported Optional Methods

The following interfaces have optional methods that the InterSystems JDBC driver does not support, or methods implemented in a non-standard manner:

CallableStatement: Unsupported Methods

Unsupported Optional Methods

java.sql.CallableStatement does not support the following optional methods:

getArray()

Array getArray(int i)
Array getArray(String parameterName)

getObject()

Object getObject(int i, java.util.Map map)
Object getObject(String parameterName, java.util.Map map)

getRef()

Ref getRef(int i)
Ref getRef(String parameterName)

getRowId() and setRowId()

java.sql.RowId getRowId(int i)
java.sql.RowId getRowId(String parameterName)

void setRowId(String parameterName, java.sql.RowId x)

getURL() and setURL()

java.net.URL getURL(int i)
java.net.URL getURL(String parameterName)

void setURL(String parameterName, java.net.URL val)

getSQLXML() and setSQLXML()

java.sql.SQLXML getSQLXML(int parameterIndex)
java.sql.SQLXML getSQLXML(String parameterName)

void setSQLXML(String parameterName, java.sql.SQLXML xmlObject)

Note:

The java.sql.CallableStatement class also has one InterSystems extension method, which is discussed elsewhere (see “CallableStatement getBinaryStream() Extension Method”).

Connection: Unsupported or Restricted Methods

Unsupported Optional Methods

java.sql.Connection does not support the following optional methods:

abort()
```
void abort(Executor executor)
```

createArrayOf()

java.sql.Array createArrayOf(String typeName, Object[] elements)

createBlob()
```
Blob createBlob()
```
createClob()
```
Clob createClob()
```
createNClob()
```
java.sql.NClob createNClob()
```
createSQLXML()
```
java.sql.SQLXML createSQLXML()
```

createStruct()

java.sql.Struct createStruct(String typeName, Object[] attributes)

getTypeMap()
```
java.util.Map getTypeMap()
```
setTypeMap()
```
void setTypeMap(java.util.Map map)
```

Optional Methods with Restrictions

The following optional java.sql.Connection methods are implemented with restrictions or limitations:

prepareCall()
Only TYPE_FORWARD_ONLY is supported for resultSetType. Only CONCUR_READ_ONLY is supported for resultSetConcurrency.
```
java.sql.CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)
```
setReadOnly()
A no-op (the InterSystems IRIS driver does not support READ_ONLY mode)
```
void setReadOnly(Boolean readOnly)
```
setCatalog()
A no-op (the InterSystems IRIS driver does not support catalogs)
```
void setCatalog(String catalog)
```
setTransactionIsolation()
Only TRANSACTION_READ_COMMITED and TRANSACTION_READ_UNCOMMITED are supported for level.
```
void setTransactionIsolation(int level)
```

The following java.sql.Connection methods do not support CLOSE_CURSORS_AT_COMMIT for resultSetHoldability:

createStatement()

java.sql.Statement createStatement(int resultSetType, int result, int resultSetHoldability)

prepareCall()

java.sql.CallableStatement prepareCall(String sql,
                                       int resultSetType,
                                       int resultSetConcurrency,
                                       int resultSetHoldability)

prepareStatement()

java.sql.PreparedStatement prepareStatement(String sql,
                                       int resultSetType,
                                       int resultSetConcurrency,
                                       int resultSetHoldability)

InterSystems IRIS currently supports only zero or one Auto Generated Keys. An exception is thrown if the java.sql.Connection methods below provide columnIndexes or columnNames arrays whose lengths are not equal to one.

prepareStatement()

java.sql.PreparedStatement prepareStatement(String sql, int[] columnIndexes)
java.sql.PreparedStatement prepareStatement(String sql, String[] columnNames)

DatabaseMetaData: Variant Methods

Variant Methods

java.sql.DatabaseMetaData is fully supported, but has methods that vary from the JDBC standard due to InterSystems-specific handling of their return values. The following methods are affected:

supportsMixedCaseQuotedIdentifiers()
InterSystems IRIS returns false, which is not JDBC compliant.
```
boolean supportsMixedCaseQuotedIdentifiers()
```
getIdentifierQuoteString()
If delimited id support is turned on, InterSystems IRIS returns " (double quote character), which is what a JDBC compliant driver should return; otherwise InterSystems IRIS returns a space.
```
String getIdentifierQuoteString()
```

Driver: Unsupported Methods

Unsupported Optional Method

java.sql.Driver does not support the following optional method:

getParentLogger()
```
void getParentLogger()
```

PreparedStatement: Unsupported Methods

Unsupported Optional Methods

java.sql.PreparedStatement does not support the following optional methods:

setArray()
```
void setArray(int i, Array x)
```
setRef()
```
void setRef(int i, Ref x)
```

setRowId()

void setRowId(int parameterIndex, RowId x)

setSQLXML()

void setSQLXML(int parameterIndex, SQLXML xmlObject)

setUnicodeStream()
Deprecated in Java JDK specification.
```
void setUnicodeStream(int i, InputStream x, int length)
```
setURL()
```
void setURL(int i, java.net.URL x)
```

ResultSet: Unsupported or Restricted Methods

Optional Method with Restrictions

InterSystems IRIS does not support TYPE_SCROLL_SENSITIVE result set types. The following method is implemented with restrictions:

setFetchDirection()
Does not support ResultSet.FETCH_REVERSE (instead, use afterLast to move the result set's cursor to after the last row, and use previous to scroll backwards).
```
void setFetchDirection(int direction)
```

Unsupported Optional Methods

java.sql.ResultSet does not support the following optional methods:

getArray()

Array getArray(int i)
Array getArray(String colName)

getCursorName()
```
String getCursorName()
```

getObject()

Object getObject(int i, java.util.Map map)
Object getObject(String colName, java.util.Map map)

getRef()

Ref getRef(int i)
Ref getRef(String colName)

getHoldability()
```
int getHoldability()
```

getUnicodeStream()

Deprecated in Java JDK specification.

java.io.InputStream getUnicodeStream(int i)
java.io.InputStream getUnicodeStream(String colName)

getURL()

java.net.URL getURL(int i)
java.net.URL getURL(String colName)

updateArray()

void updateArray(int i, Array x)
void updateArray(String colName, Array x)

updateRef()

void updateRef(int i, Ref x)
void updateRef(String colName, Ref x)

Statement: Unsupported or Restricted Methods

Unsupported Optional Methods

java.sql.Statement does not support the following optional methods:

cancel()
```
void cancel()
```
closeOnCompletion()
```
void closeOnCompletion()
```
isCloseOnCompletion()
```
boolean isCloseOnCompletion()
```

Optional Methods with Restrictions

The following optional java.sql.Statement methods are implemented with restrictions or limitations:

getResultSetHoldability()
Only HOLD_CURSORS_OVER_COMMIT
```
int getResultSetHoldability()
```
setCursorName()
A no-op.
```
void setCursorName(String name)
```
setEscapeProcessing()
A no-op (does not apply)
```
void setEscapeProcessing(Boolean enable)
```
setFetchDirection()
Does not support ResultSet.FETCH_REVERSE (instead, use afterLast to move the result set's cursor to after the last row, and use previous to scroll backwards).
```
void setFetchDirection(int direction)
```

InterSystems IRIS currently supports only zero or one auto-generated key. An exception is thrown if the java.sql.Statement methods below provide columnIndexes or columnNames arrays whose lengths are not equal to one:

execute()

boolean execute(String sql, int[] columnIndexes)
boolean execute(String sql, String[] columnNames)

executeUpdate()

int executeUpdate(String sql, int[] columnIndexes)
int executeUpdate(String sql, String[] columnNames)

InterSystems Enhancements and Extensions

The following classes provide additional InterSystems-specific extension methods:

CallableStatement getBinaryStream() Extension Method
ConnectionPoolDataSource Extensions and Enhancements discusses com.intersystems.jdbc.IRISConnectionPoolDataSource, which is the InterSystems implementation of the javax.sql.ConnectionPoolDataSource interface.
DataSource Extensions and Enhancements discusses com.intersystems.jdbc.IRISDataSource, which is the InterSystems implementation of javax.sql.DataSource.

CallableStatement getBinaryStream() Extension Method

java.sql.CallableStatement implements the following additional InterSystems-specific extension method:

getBinaryStream()
Retrieves the value of the designated parameter (where i is the index of the parameter) as a java.io.InputStream object.
```
java.io.InputStream getBinaryStream(int i)
```
This method is a complement to the standard setBinaryStream() method, and an alternative to getCharacterStream() (which returns java.io.Reader).

ConnectionPoolDataSource Extensions and Enhancements

The com.intersystems.jdbc.IRISConnectionPoolDataSource class fully implements the javax.sql.ConnectionPoolDataSource interface. This class does not inherit the methods of javax.sql.CommonDataSource, which is not supported by the InterSystems JDBC driver.

Restricted Method

getPooledConnection() is implemented because it is required by the JDBC standard, but the InterSystems IRIS implementation should never be called directly. InterSystems IRIS driver connections must always be obtained by calling the getConnection() method. (See “Using a Connection Pool” in Using Java with InterSystems Software for more information).

getPooledConnection()

javax.sql.PooledConnection getPooledConnection()
javax.sql.PooledConnection getPooledConnection(String usr,String pwd)

Caution:

Calling applications should never use the getPooledConnection() methods or the PooledConnection class. InterSystems IRIS driver connections must always be obtained by calling the getConnection() method (which is inherited from IRISDataSource). The InterSystems IRIS driver provides pooling transparently through the java.sql.Connection object that it returns.

IRISConnectionPoolDataSource inherits from IRISDataSource (see “DataSource Extensions and Enhancements”), which provides additional InterSystems extension methods.

ConnectionPoolDataSource Extension Methods

IRISConnectionPoolDataSource also supports the following additional InterSystems IRIS-only management methods (see “Using a Connection Pool” in Using Java with InterSystems Software for more information):

restartConnectionPool()
Restarts a connection pool. Closes all physical connections, and empties the connection pool.
```
void restartConnectionPool()
```
getPoolCount()
Returns the current number of entries in the connection pool.
```
int getPoolCount()
```
setMaxPoolSize()
Sets a maximum connection pool size. If the maximum size is not set, it defaults to 40.
```
void setMaxPoolSize(int max)
```
getMaxPoolSize()
Returns the current maximum connection pool size
```
int getMaxPoolSize()
```

DataSource Extensions and Enhancements

The com.intersystems.jdbc.IRISDataSource class fully implements the javax.sql.DataSource interface. This class does not inherit the methods of javax.sql.CommonDataSource, which is not supported by the InterSystems JDBC driver.

Enhanced Required Method

The InterSystems IRIS implementation of this method is enhanced to provide automatic, transparent connection pooling. (See “Using a Connection Pool” in Using Java with InterSystems Software for more information).

getConnection()

java.sql.Connection getConnection()
java.sql.Connection getConnection(String usr,String pwd)

DataSource Extension Methods

In addition to the methods defined by the interface, IRISDataSource also implements the following methods that can be used to get or set DataSource properties supported by InterSystems IRIS. (See “Setting Connection Properties” in Using Java with InterSystems Software for more information).

getConnectionSecurityLevel()
Returns an int representing the current Connection Security Level setting.
```
int getConnectionSecurityLevel()
```
getDatabaseName()
Returns a String representing the current database (InterSystems IRIS namespace) name.
```
String getDatabaseName()
```
getDataSourceName()
Returns a String representing the current data source name.
```
String getDataSourceName()
```
getDefaultTransactionIsolation()
Gets the current default transaction isolation.
```
int getDefaultTransactionIsolation()
```
getDescription()
Returns a String representing the current description.
```
String getDescription()
```
getEventClass()
Returns a String representing an Event Class object.
```
String getEventClass()
```
getKeyRecoveryPassword()
Returns a String representing the current Key Recovery Password setting.
```
getKeyRecoveryPassword()
```
getNodelay()
Returns a boolean representing a current TCP_NODELAY option setting.
```
boolean getNodelay()
```
getPassword()
Returns a String representing the current password.
```
String getPassword()
```
getPortNumber()
Returns an int representing the current port number.
```
int getPortNumber()
```
getServerName()
Returns a String representing the current server name.
```
String getServerName()
```
getServicePrincipalName()
Returns a String representing the current Service Principal Name setting.
```
String getServicePrincipalName()
```
getSSLConfigurationName()
Returns a String representing the current SSL Configuration Name setting.
```
getSSLConfigurationName()
```
getURL()
Returns a String representing a current URL for this connection.
```
String getURL()
```
getUser()
Returns a String representing the current username.
```
String getUser()
```
setConnectionSecurityLevel()
Sets the connection security level
```
Sets the Connection Security Level for this DataSource object. 
```
setDatabaseName()
Sets the database name (InterSystems IRIS namespace) for this connection.
```
void setDatabaseName(String dn)
```
setDataSourceName()
Sets the data source name for this connection. DataSourceName is an optional setting and is not used by IRISDataSource to connect.
```
void setDataSourceName(String dsn)
```
setDefaultTransactionIsolation()
Sets the default transaction isolation level.
```
void setDefaultTransactionIsolation(int level)
```
setDescription()
Sets the description for this connection. Description is an optional setting and is not used by IRISDataSource to connect.
```
void setDescription(String d)
```
setEventClass()
Sets the Event Class for this connection. The Event Class is a mechanism specific to InterSystems IRIS JDBC. It is completely optional, and the vast majority of applications will not need this feature.
The InterSystems JDBC server will dispatch to methods implemented in a class when a transaction is about to be committed and when a transaction is about to be rolled back. The class in which these methods are implemented is referred to as the “event class.” If an event class is specified during login, then the JDBC server will dispatch to %OnTranCommit just prior to committing the current transaction and will dispatch to %OnTranRollback just prior to rolling back (aborting) the current transaction. User event classes should extend %ServerEvent. The methods do not return any values and cannot abort the current transaction.
```
void setEventClass(String e)
```
setKeyRecoveryPassword()
Sets the Key Recovery Password for this connection.
```
setKeyRecoveryPassword(java.lang.String password) 
```
setLogFile()
Unconditionally sets the log file name for this connection.
```
setLogFile(java.lang.String logFile)
```
setNodelay()
Sets the TCP_NODELAY option for this connection. Toggling this flag can affect the performance of the application. If not set, it defaults to true.
```
void setNodelay(boolean nd)
```
setPassword()
Sets the password for this connection.
```
void setPassword(String p)
```
setPortNumber()
Sets the port number for this connection
```
void setPortNumber(int pn)
```
setServerName()
Sets the server name for this connection.
```
void setServerName(String sn)
```
setServicePrincipalName()
Sets the Service Principal Name for this connection.
```
void setServicePrincipalName(String name)
```
setSSLConfigurationName()
Sets the SSL Configuration Name for this connection.
```
setSSLConfigurationName(java.lang.String name)
```
setURL()
Sets the URL for this connection.
```
void setURL(String u)
```
setUser()
Sets the username for this connection.
```
void setUser(String u)
```

Apache Spark Support

Introduction

JDBC Driver Support

Copy link to this sectionJDBC and the InterSystems JDBC Driver

Installation and Configuration

Copy link to this sectionJDBC Driver Compliance

Required java.sql Interfaces

Optional java.sql Interfaces

java.sql Exceptions

Required javax.sql Interfaces

Optional javax.sql Interfaces

Copy link to this sectionVariants and Unsupported Optional Methods

Copy link to this sectionCallableStatement: Unsupported Methods

Copy link to this sectionConnection: Unsupported or Restricted Methods

Copy link to this sectionDatabaseMetaData: Variant Methods

Copy link to this sectionDriver: Unsupported Methods

Copy link to this sectionPreparedStatement: Unsupported Methods

Copy link to this sectionResultSet: Unsupported or Restricted Methods

Copy link to this sectionStatement: Unsupported or Restricted Methods

Copy link to this sectionInterSystems Enhancements and Extensions

Copy link to this sectionCallableStatement getBinaryStream() Extension Method

Copy link to this sectionConnectionPoolDataSource Extensions and Enhancements

ConnectionPoolDataSource Extension Methods

Copy link to this sectionDataSource Extensions and Enhancements

DataSource Extension Methods

JDBC and the InterSystems JDBC Driver

JDBC Driver Compliance

Variants and Unsupported Optional Methods

CallableStatement: Unsupported Methods

Connection: Unsupported or Restricted Methods

DatabaseMetaData: Variant Methods

Driver: Unsupported Methods

PreparedStatement: Unsupported Methods

ResultSet: Unsupported or Restricted Methods

Statement: Unsupported or Restricted Methods

InterSystems Enhancements and Extensions

CallableStatement getBinaryStream() Extension Method

ConnectionPoolDataSource Extensions and Enhancements

DataSource Extensions and Enhancements