JDBC Driver Interface
This section details the JDBC Driver interface class files and their associated properties. It also includes instructions for loading and accessing the driver.
JDBC Driver and Data Source Classes
The JDBC Driver and data source classes are located in the Java package, com.ingres.jdbc.
These packages are contained in the Java archive iijdbc.jar, which includes the following class files:
Note: The original JDBC Driver and DataSources classes contained in the Java archive iijdbc.jar under the Java package path of "ca.ingres.jdbc" are moved to the package path of "com.ingres.jdbc". The iijdbc.jar archive included with Ingres 9.0 also contains the original classes for backward compatibility. Starting with Ingres 10.0, the “ca.ingres.jdbc” package is no longer included in the iijdbc.jar archive. Existing references to “ca.ingres.jdbc” classes must be changed to “com.ingres.jdbc”.
JDBC Driver Properties
Driver properties allow applications to establish connection parameters that are driver-dependent. JDBC Driver properties can be specified as follows:
• As connection URL attributes as a Java Properties parameter to a DriverManager.getConnection() method
• As Java system properties
• In a properties file
Attribute and property names are given below.
When specified as system properties or in a property file, the property key must be of the form:
ingres.jdbc.property.property_name
During Vector installation, the
JDBC Driver Properties Generator (iijdbcprop) reads the Ingres configuration and generates the corresponding JDBC driver properties file, named iijdbc.properties.
A default properties file (typically iijdbc.properties) is loaded automatically by the JDBC Driver when the driver class is loaded. The file must reside in a location accessible by the class loader used to load the driver. In general, this requires that the properties file directory be included in the Java environment variable CLASSPATH.
An alternate properties file can also be specified using the system property ingres.jdbc.property_file. The directory path of the property file can be specified or the property file can be placed in a directory accessible as described above for the default properties file. Properties are searched in the following order: URL attributes, getConnection() property set, system properties, alternate properties file, and default properties file.
The JDBC Driver supports the following properties:
Property | Attribute | Description |
|---|
autocommit_mode | AUTO | Autocommit cursor handling mode. Valid values are: dbms–(Default) autocommit processing is done by the DBMS Server single–DAS enforces single cursor operation during autocommit multi–DAS simulates autocommit operations when more than one cursor is open. |
char_encode | ENCODE | Specifies the Java character encoding used for conversions between Unicode and character data types. Generally, the character encoding is determined automatically by the driver from the DAS installation character set. This property allows an alternate character encoding to be specified (if desired) or a valid character encoding to be used when the driver is unable to map the server's character set. |
compression | COMPRESS | Whether data is compressed over the network. Valid values are: off–Compression off on–(Default) Compression on |
connect_pool | POOL | Server connection pool control. Valid values are: off–Requests a non-pooled connection when server pooling is enabled on–Requests a pooled connection when server pooling is optional. The default is to allow the DAS configuration to determine pooling. |
cursor_mode | CURSOR | Default cursor concurrency mode, which determines the concurrency of cursors that have no concurrency explicitly assigned. Valid values are: dbms–Concurrency is determined by the DBMS Server update–Provides updateable cursors readonly–(Default) Provides non-updateable cursors |
date_alias | DATE | Specifies the data type of columns created using the alias keyword "date". Valid values are: "ansidate" or "ingresdate". Default value is "ansidate" if the property send_ingres_dates is set to false, otherwise it is "ingresdate". This property is not used directly by the driver but is sent to the DBMS and affects the processing of query text. |
date_format | DATE_FMT | Specifies the Vector format for date literals. Corresponds to the Vector environment variable II_DATE_FORMAT and is assigned the same values. This property is not used directly by the driver, but is sent to the DBMS and affects the processing of query text. |
dbms_password | DBPWD | The user's DBMS password (Vector -P flag). |
dbms_user | DBUSR | The user name associated with the DBMS session (Vector -u flag, can require admin privileges). |
decimal_char | DECIMAL | Specifies the character to be used as the decimal point in numeric literals. Corresponds to the Vector environment variable II_DECIMAL and is assigned the same values. This property is not used directly by the driver but is sent to the DBMS and affects the processing of query text. |
encryption | ENCRYPT | Defines the default encryption mode used by client connections. Valid values are: on–Encryption is required and occurs unless no compatible encryption mechanism is available. Connection fails if encryption is not possible. off–(Default) Encryption is disabled. |
group | GRP | The user's group identifier. |
identity_query | IDENTITY | Enables or disables the identity query. Valid values are: on–(Default) Identity query is issued if no table key or object key is received and other conditions permit. This setting provides the best behavior for applications that use only table or object keys or only identity columns. off–Identity query is not issued and identity values are not returned. This setting is appropriate if only table or object keys are used. |
metadata_underscore | META_UNDER | Enables or disables the underscore character, ‘_’, as a pattern wildcard in DatabaseMetaData methods whose parameters are defined as patterns. Valid values are: true–(Default) Underscore character is treated as a wildcard in metadata patterns. false–Underscore is treated as a literal character in metadata patterns. |
money_format | MNY_FMT | Specifies the Vector format for money literals. Corresponds to the Vector environment variable II_MONEY_FORMAT and is assigned the same values. This property is not used directly by the driver but is sent to the DBMS and affects the processing of query text. |
money_precision | MNY_PREC | Specifies the precision of money data values. Corresponds to the Vector environment variable II_MONEY_PREC and is assigned the same values. This property is not used directly by the driver but is sent to the DBMS and affects the processing of money values. |
password | PWD | The user's operating system password. |
role | ROLE | The desired role identifier. If a role password is required, include it with the role name as follows: name/password. |
select_loop | LOOP | Select loop vs. cursor queries. Valid values are: on–Uses select loops to retrieve query results off–(Default) Uses cursors For further details, see Cursors and Select Loops. |
send_ingres_dates | SEND_INGDATE | Specifies whether datetime values should be sent as INGRESDATE data type. Valid values are: false–Values sent as ANSI TIMESTAMP WITH TIMEZONE type true–Values sent as INGRESDATE type Default value is false if date_alias property is set to "ansidate"; otherwise it is true. Unlike date_format and date_alias, this property is internal to the driver, not sent to the DBMS, and affects the processing of query text. |
send_integer_booleans | SEND_INTBOOL | Specifies if Boolean parameters are converted to tinyint values when sent to the DBMS. Valid values are: true–Boolean parameters are converted to tinyint values false–(Default) Boolean parameters are sent as Boolean values |
timezone | TZ | Specifies the time zone associated with the client's location. Corresponds to the Vector environment variable II_TIMEZONE_NAME and is assigned the same values. This property is not used directly by the driver but is sent to the DBMS and affects the processing of dates. |
user | UID | The user ID on the target DBMS Server machine. See the description of the vnode_usage property in this table. This property can be used if the user has no DBMS user ID and password assigned (see dbms_user and dbms_password in this table). |
vnode_usage | VNODE | Allows the JDBC application to control the portions of the vnode information that are used to establish the connection to the remote DBMS server. Valid values are: connect–(Default) Only the vnode connection information is used to establish the connection. login–Both the vnode connection and login information are used to establish the connection. For further details, see JDBC User Authentication. |
Attributes can also be specified using the property name as the attribute name. Thus "UID=user1" and "user=user1" are semantically the same.
JDBC Driver Properties Generator (iijdbcprop)
The iijdbcprop utility automatically generates all supported JDBC properties. The utility runs automatically during installation but can be run at any time.
This utility provides the following benefits:
• Automatically generates all JDBC properties that match related Vector environment variables, such as II_TIMEZONE_NAME, II_DECIMAL, II_DATE_FORMAT, II_MONEY_FORMAT and II_MONEY_PREC. These properties can keep the JDBC Driver behavior synchronized with an Ingres or Vector installation, since the JDBC Driver does not have access to the Ingres or Vector environment variables and is often deployed on a machine separate from Ingres or Vector.
• Reduces typing errors when entering JDBC properties by hand. All available JDBC properties are generated, but properties not in the Vector configuration files are commented out.
The iijdbcprop command writes the iijdbc.properties file into the directory:
Linux: $II_SYSTEM/ingres/files
Windows: %II_SYSTEM%\ingres\files
During loading of the JDBC Driver by the class loader, the directory containing the iijdbc.properties file must be specified in the Java environment variable CLASSPATH.
If a Vector JDBC application is connecting to a remote Data Access Server (that is, Vector is not installed on the machine running iijdbc.jar), the iijdbc.properties file from the remote Vector installation can be copied to the machine running iijdbc.jar. The directory of the iijdbc.properties file must be included in the
CLASSPATH (see
Set CLASSPATH Environment).
Example Entries—For example, to turn on tracing in the driver, uncomment the desired trace entries in the iijdbc.properties file by removing the leading # sign, and then provide appropriate values for them as shown here.
On Linux, the following entries in iijdbc.properties file will produce a JDBC Driver trace file under the /tmp directory called jdbc_driver.log, provided the $II_SYSTEM/ingres/files directory is in the CLASSPATH.
ingres.jdbc.trace.log=/tmp/jdbc_driver.log
ingres.jdbc.trace.drv=5
ingres.jdbc.trace.msg=3
For details on the iijdbcprop command, see the “Command Reference” chapter in the User Guide.
Data Source Properties
A data source configuration is a collection of information that identifies the target database to which the driver connects. The Data Source classes support the following data source properties and associated getter/setter methods.
The data source properties marked as “required” correspond to parameters contained in a connection URL. For a description of these parameters, see
DriverManager.getConnection() Method--Establish JDBC Driver Connection. The remaining Data Source properties correspond to the driver properties defined in JDBC Driver Properties.
Additional Data Source Properties
In addition to the DataSource class properties, the ConnectionPoolDataSource and XADataSource classes support the following properties and associated getter/setter methods:
System Properties
The following system properties can be used to configure the driver:
ingres.jdbc.property_file
Path and filename containing configuration system properties. Default is iijdbc.properties (must reside in CLASSPATH directory).
ingres.jdbc.property.<property name>
Driver connection properties. Defaults are property dependent.
ingres.jdbc.batch.enabled
Specifies whether batch statement processing is enabled. If set to true, batch statements will be executed together in a batch. If set to false (or if batch processing is not supported by the DBMS), batch statements will be executed individually. Default is true.
ingres.jdbc.date.empty
Replacement value, in standard JDBC datetime format YYYY-MM-DD hh:mm:ss, for Vector empty dates. Can also be set to null to have empty dates returned as null values. For default behavior, set to default or empty, as described in
Datetime Columns and Values.
ingres.jdbc.dbms.trace.log
Path and filename of the DBMS trace log. Default is no trace log.
ingres.jdbc.encryption.aes.key.size
Size of the AES key in bits. Valid values are: 128, 192, 256 (limited by Java encryption provider capabilities). When client and server have different capabilities, the largest common key size will be used. Default is 128.
ingres.jdbc.encryption.rsa.key.size
Size of RSA key in bits. Valid values are: 1024, 2048, 3072, 4096. Default is 1024.
ingres.jdbc.encryption.rsa.key.scope
Defines the scope where an RSA key is used:
process
(Default) One RSA key is generated in a process and used for all connections originating from that process.
connection
An RSA key is generated for each connection and used only for that connection.
ingres.jdbc.lob.cache.enabled
Enable/disable caching Large Objects (true/false). Default is false.
ingres.jdbc.lob.cache.segment_size
Number of bytes/characters per segment in LOB cache. Default is 8192.
ingres.jdbc.lob.locators.enabled
Enable/disable Locators for Large Objects if supported by DBMS (true/false). Default is true.
ingres.jdbc.lob.locators.autocommit.enabled
Enable/disable Locators for Large Objects during autocommit (true/false). LOB Locators must also be generally enabled. Default is false.
ingres.jdbc.lob.locators.select_loop.enabled
Enable/disable Locators for Large Objects during select loops (true/false). LOB Locators must also be generally enabled. Default is false.
ingres.jdbc.lob.locators.stream.enabled
Enable or disable streaming access of Large Objects using locators. When enabled, access methods such as Clob.getCharacterStream() issue a single request to the DBMS to retrieve LOB data. No other requests can be made on the associated connection until all result data has been retrieved. When disabled, access methods issue individual requests for sequential blocks of LOB data, permitting other requests to be made on the associated connection while LOB data is being retrieved. Default is true.
ingres.jdbc.metadata.pattern.underscore.enabled
Enable or disable the underscore character, ‘_’, as a metadata pattern wildcard (true/false). Default is true.
ingres.jdbc.prefetch.blocks
The number of data blocks to be prefetched. By default, the rows that will fit in one communication data block are prefetched. When this property is set, the prefetch block count is multiplied by the default prefetch row count to determine the number of rows to be prefetched. Depending on the row size and unused space in the rows, the number of blocks retrieved may be different.
ingres.jdbc.scroll.enabled
Enable or disable scrollable result sets (true/false). If disabled, all result sets will be forward-only and an SQLWARNING will be generated if a scrollable result set is requested. Default is true.
ingres.jdbc.sql.comment.doubledash.enabled
Enable or disable removal of double dash ('--') comments in SQL text (true/false). Default is true.
ingres.jdbc.trace.log
Path and filename of the driver trace log. Default is no trace log.
ingres.jdbc.trace.drv
Driver trace level. Default is 0 (no tracing).
ingres.jdbc.trace.ds
DataSource trace level. Default is 0 (no tracing).
ingres.jdbc.trace.msg
Messaging system trace level. Default is 0 (no tracing).
ingres.jdbc.trace.msg.tl
Transport layer trace level. Default is 0 (no tracing).
ingres.jdbc.trace.msg.nl
Network layer trace level. Default is 0 (no tracing).
ingres.jdbc.trace.timestamp
Include timestamps in traces (true/false). Default is false.
How the Driver Is Loaded
The JDBC Driver can be loaded by an application or applet by using one of these methods:
• Adding the driver class, com.ingres.jdbc.IngresDriver, to the JDBC DriverManager system property "jdbc.drivers"
• Adding the following Java statement to the application/applet prior to attempting to establish a connection using the JDBC Driver:
Class.forName( "com.ingres.jdbc.IngresDriver" ).newInstance();
Depending on the Java environment, calling the forName() method can be sufficient to load and initialize the JDBC Driver classes. Some environments, most notably older releases of Microsoft Internet Explorer, require the instantiation of a JDBC Driver object to fully initialize the driver.
While only one method is needed, both methods can be used without conflict.
DriverManager.getConnection() Method--Establish JDBC Driver Connection
A JDBC Driver connection can be established using a DriverManager.getConnection() method with a URL in the following format:
jdbc:ingres://host:port{,port}{;host:port{,port}}/db{;attr=value}
where:
host
Specifies the network name or address of the host on which the target Data Access Server (DAS) is running. TCP/IPv6 addresses (colon-hexadecimal format) must be enclosed in square brackets, for example: [::1]. Multiple hosts with associated ports must be separated by semi-colons.
port
Specifies the network port used by the DAS. This can be a numeric port number or a symbolic port address such as II7 (the Ingres default) or VW7 (the Vector default), which is the instance ID followed by the number 7.
Multiple ports can be specified, one for each configured DAS. For example, if DAS is configured with a startup count of 4 and a listen port of II7+:
II7,II8,II9,II10
The driver attempts to connect to each host/port combination until a successful connection is made. The connection request between the JDBC application and the DAS fails only if all port attempts fail. If a successful connection to DAS is made, but the subsequent connection between DAS and the DBMS fails, then the connection request fails immediately and subsequent ports are not tried.
db
Specifies the target database. Any valid Ingres or Vector database designation can be used including vnode and server class (that is, vnode::dbname/server_class).
attr=value
(Optional) Specifies the attribute name and value pair. Multiple attribute pairs are separated by a semi-colon.
Attributes represent driver properties that are implementation-specific and can be used to configure the new connection. For details, see J
JDBC Driver Properties.
Note: A user ID and password are required when making remote connections. They can be included as parameters to the getConnection() method as driver properties or as URL attributes.
Access to a Remote Instance
You can connect to a remote Ingres DBMS, Vector DBMS, Enterprise Access, or EDBC instance using either a vnode or dynamic vnode.