HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.2 HP Part Number: 691127-001 Published: August 2012 Edition: J06.14 and subsequent J-series RVUs; H06.
© Copyright 2012 Hewlett-Packard Development Company L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
Contents About this manual.........................................................................................7 New and changed information...................................................................................................7 Supported Release Version Updates (RVUs)..................................................................................7 Audience.................................................................................................................................
Connection Pooling Using the Basic DataSource API...............................................................35 Connection Pooling with the DriverManager Class..................................................................36 Statement Pooling...................................................................................................................36 Guidelines for Statement Pooling..........................................................................................
6 Module File Caching................................................................................57 What is New.........................................................................................................................57 Design of MFC.......................................................................................................................57 Enabling MFC........................................................................................................................
Sample Program Accessing BLOB Data.....................................................................................97 Glossary....................................................................................................
About this manual This document describes how to use the JDBC/MX driver for NonStop SQL/MX, a type 2 driver, on HP Integrity NonStop™ servers. The JDBC/MX driver provides HP NonStop Server for Java applications with JDBC access to NonStop SQL/MX. Where applicable, the JDBC/MX driver conforms to the standard JDBC 3.0 API from Oracle.
Organization Table 1 (page 8) describes the chapters in the document. Table 1 Document Structure Chapter Description “Introduction to JDBC/MX Driver” (page 15) Describes the JDBC/MX driver architecture and the API package. “Installing and Verifying JDBC/MX” (page 17) Describes where to find information about the installation requirements and explains how to verify the JDBC/MX driver installation.
• • • Installation guides SQL/MX Installation and Management Guide Describes how to plan for, install, create, and manage an SQL/MX database. Explains how to use installation and management commands and utilities. NSM/web Installation Guide Describes how to install NSM/web and troubleshoot NSM/web installations. Reference manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements.
• Application development guides SQL/MX Programming Manual Describes how to embed SQL/MX statements in ANSI C and COBOL programs. for C and COBOL • SQL/MX Query Guide Describes how to understand query execution plans and write optimal queries for an SQL/MX database. SQL/MX Queuing and Publish/Subscribe Services Describes how NonStop SQL/MX integrates transactional queuing and publish/subscribe services into its database infrastructure.
• JDBC Data Access API FAQs for JDBC 3.0 (http://www.oracle.com/technetwork/java/index-138427.html) • JDBC API Javadoc Comments ◦ Core JDBC 3.0 API in the java.sql package (http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/package-summary.html) ◦ Optional JDBC 3.0 API in the javax.sql package (http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/package-summary.html) Notation conventions Bold type Bold type within text indicates terms defined in the Glossary.
print {objectID|objectName} ... An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: dump objectID ... Punctuation Parentheses, commas, equal signs, and other symbols not previously described must be entered as shown. For example: -D propertyName=newValue Item spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or comma.
HTTP. Hypertext Transfer Protocol IEC. International Electrotechnical Committee ISO. International Organization for Standardization J2SE. Java 2 Platform Standard Edition JAAS. Java Authentication and Authorization Service JAR. Java Archive JCK. Java Conformance Kit JDK. J2SE Development Kit JFC. Java Foundation Classes JDBC. Java Database Connectivity JDBC/MP. JDBC Driver for SQL/MP JDBC/MX. JDBC Driver for SQL/MX JNDI. Java Naming and Directory Interface JNI.
Publishing history This guide supports HP NonStop SQL/MX Release 3.2 until otherwise indicated by its replacement publication. The publication date and part number indicate the current edition of the document. 14 Part Number Product Version Published 640330-001 JDBC/MX driver for NonStop SQL/MX R 3.0 February 2011 640330-002 JDBC/MX driver for NonStop SQL/MX R 3.0 June 2011 663859-001 JDBC/MX driver for NonStop SQL/MX R 3.1 October 2011 691127-001 JDBC/MX driver for NonStop SQL/MX R 3.
1 Introduction to JDBC/MX Driver The HP JDBC/MX Driver 3.2 for NonStop SQL/MX implements the JDBC technology that conforms to the standard JDBC 3.0 Data Access API. This JDBC/MX driver enables Java applications to use HP NonStop SQL/MX to access NonStop SQL databases. For more information on the JDBC APIs associated with the JDBC/MX implementation, see “Oracle documents” (page 10).
JDBC API Packages The JDBC/MX API packages are shipped with the JDBC/MX driver software. For the API documentation, see the JDBC/MX Driver for NonStop SQL/MX for H50 API Reference in the H-series library in the NonStop Technical Library at Business Support Center (BSC). The java.sql and javax.sql packages are included as part of Java 2, Standard Edition (J2SE) SDK 1.5 and, therefore, are available with the core APIs delivered with the NonStop Server for Java 5 product.
2 Installing and Verifying JDBC/MX The Softdoc file contains the instructions on how to install the HP JDBC/MX type 2 driver on NonStop SQL/MX Release 3.2.
PINSTALL delivers the contents of T1275PAX in the /home/test/jdbcMx/T1275R32 OSS directory. NOTE: The standard location for is /usr/tandem/jdbcMx/ T1275R32. 5. To start an OSS shell session, enter the following: $SYSTEM ZOSSUTL> osh 6. To change to the working directory, enter the following: /G/SYSTEM/SYSTEM: cd /jdbcMx/T1275R32/bin 7. To run the installation script, enter the following: /jdbcMx/T1275R32/bin: .
Demo programs .../lib/libjdbcMx.so JDBC/MX driver library .../lib/jdbcMx.jar JDBC/MX Java archive file, which includes the JDBC trace facility .../bin/jdbcMxInstall JDBC/MX installation script ...
3 Accessing SQL Databases with SQL/MX This section describes the following topics: • “Connecting to SQL/MX” (page 20) • “JdbcRowSet Implementation” (page 24) • “JDBC/MX Properties” (page 24) • “Transactions” (page 32) • “Stored Procedures” (page 33) • “SQL Context Management” (page 33) • “Holdable Cursors” (page 34) • “Connection Pooling” (page 34) • “Statement Pooling” (page 36) • “Using Additional JDBC/MX Properties” (page 38) • “Supported Character Set Encodings” (page 41) Connectin
Table 3 JDBC/MX Driver Properties Used with the DriverManager Class Property Name Type Value Description contBatchOnError String on or off Communicates with JDBC driver to continue the remaining jobs in the batch even after any BatchUpdateExceptions. See “contBatchOnError property” (page 38). catalog String See “Default Catalog and Schema” (page 25). If the default catalog and schema are not specified, the JDBC/MX driver allows SQL/MX to follow its own rules for defaults.
NOTE: Do not add the jdbcmx. prefix to the property name when the properties are given as a parameter to the connection method or when using the data source. The prefix is not needed to identify the property type because the property is being passed to a JDBC/MX driver object. Use the jdbcmx. prefix only in the command line as described under “Setting Properties in the Command Line” (page 30).
Table 4 DataSource Object Properties (continued) Property Name Type Value Description BatchUpdateExceptions. See “contBatchOnError property” (page 38). catalog String See “Default Catalog and Schema” (page 25) If the default catalog and schema are not specified, the JDBC/MX driver allows SQL/MX to follow its own rules for defaults. schema String See “Default Catalog and Schema” (page 25) See catalog above.
Table 4 DataSource Object Properties (continued) Property Name Type Value Description blobTableName String See “LOB Table Name Properties” (page 25). Specifies the LOB table for using BLOB columns. clobTableName String See “LOB Table Name Properties” (page 25). Specifies the LOB table for using CLOB columns. transactionMode String See “transactionMode Property” (page 28). Sets the transaction mode, which provides control over how and when transactions are performed.
• “mploc Property” (page 26) • “maxStatements Property” (page 26) • “minPoolSize Property” (page 26) • “maxPoolSize Property” (page 27) • “initialPoolSize property” (page 27) • “maxIdletime property” (page 27) • “language Property” (page 28) • “transactionMode Property” (page 28) • “queryExecuteTime Property” (page 29) • “T2QueryExecuteLogFile Property” (page 30) • “Setting Properties in the Command Line” (page 30) These properties and additional properties can be specified in a comman
–Djdbcmx.clobTableName=mycat.myschema.myLobTable • By using the java.util.Properties parameter in the getConnection method of DriverManager class. The properties passed through the Properties parameter have precedence over the command line properties. • By setting either of these properties in the DataSource. See “Connecting Using the DataSource Interface” (page 22).
The integer can be 0 through 2147483647, but less than maxPoolSize. The default is 0. Any negative value is treated like 0. Any value greater than maxPoolSize is changed to the maxPoolSize value. This value is ignored when maxPoolSize is -1. The value determines connection pool use as follows: • When the number of physical connections in the free pool reaches the minPoolSize value, the JDBC/MX driver closes subsequent connections by physically closing them—not by adding them to the free pool.
The following conditions apply: • if maxPoolSize >= 0 and minPoolSize = 0, then maxIdleTime is not applied. • If maxPoolSize > 0 and minPoolSize > 0, ◦ and maxIdleTime > 0, a connection when closed is added to the free pool. After the number of seconds specified using the maxIdleTime property, the number of connections in the pool is equal to minPoolSize, and any extra connection is hard closed.
mixed Specifies that the driver inherits any active transaction in the current thread. The autocommit setting of the transaction is ignored. The application must either commit or rollback the transaction in this mode. If there is no active transaction, the driver creates one and begins the transaction, or aborts it if there is an SQL error. In this mode, the driver supports both autocommit and non-autocommit. The application ends the transaction in non-autocommit mode.
T2QueryExecuteLogFile Property You can use the T2QueryExecuteLogFile property to set the name of the log file. You must set the T2QueryExecuteLogFile property on a DataSource object, ConnectionPoolDataSource object, or a DriverManager object. You must consider the following settings while configuring the T2QueryExecuteLogFile property: • Data type — string • File name — OSS file name. You can assign either the full path of the log file or only the name of the log file.
Table 5 JDBC/MX Driver Properties Allowed in the Command Line (continued) JDBC/MX Prefix Property Name Description jdbcmx. batchBinding Specifies that statements are batched together in the executeBatch() operation. See “Setting Batch Processing for Prepared Statements” (page 40). jdbcmx. blobTableName Specifies the LOB table for using BLOB columns. See “LOB Table Name Properties” (page 25). jdbcmx. catalog Sets the default catalog. See “Default Catalog and Schema” (page 25). jdbcmx.
For example, using the mploc property in the OSS environment, specify the mploc property including the prefix in either of the following forms: -Djdbcmx.mploc=[\\node.]\$volume.subvolume or -Djdbcmx.mploc='[\node.]$volume.subvolume' Transactions The JDBC/MX driver provides transaction support to maintain data integrity and consistency.
Stored Procedures SQL/MX provides support for stored procedures with result sets, which are written in Java and run under an SQL/MX execution environment. Stored procedures can be run in SQL/MX by using the CALL statement. The JDBC/MX driver allows stored procedures to be called by using the standard JDBC API escape syntax for stored procedures. The escape SQL syntax is: {call procedure-name([arg1,arg2, ...])} where argn refers to the parameters sequentially, with the first parameter being arg1.
A process (JVM process) can have multiple SQL/MX contexts within a process. Holdable Cursors JDBC/MX driver supports the holdablity attribute for the ResultSet. To use holdable cursors in your JDBC applications, follow these guidelines: • Use one of the following constants for the holdablity attribute: com.tandem.sqlmx.SQLMXResultSet.HOLD_CURSORS_OVER_COMMIT Ensure that when the application calls the method Connection.commit or Connection.
object, the application looks for the suitable PooledConnection object. The lookup criteria and other methods are specific to the application server. • The application server implements the ConnectionEventListener interface and registers the listener object with the PooledConnection object. The JDBC/MX driver notifies the listener object with a connectionClosed event when the application is finished using the Connection object.
The connection is initialized with the JDBC/MX driver-specific properties when the PooledConnection is obtained. • By using the properties in the DataSource object, when the dataSourceName property is empty. Connection pooling is disabled by default. Note that the default value for the maxPoolSize property is -1, which disables connection pooling. See the DataSource interface for the details on using these properties.
Guidelines for Statement Pooling • Enable statement pooling by setting the DataSource object maxStatements property to an integer value greater than 0 and, also, by enabling connection pooling. See “Connection Pooling” (page 34) for more information. • Enabling statement pooling for your JDBC applications might dramatically improve the performance. • Explicitly close a prepared statement by using the Statement.
• JDBC/MX driver assumes that any SQL CONTROL statements in effect at the time of execution or reuse are the same as those in effect at the time of SQL/MX compilation. If this condition is not true, reuse of a PreparedStatement object might result in unexpected behavior. • You should avoid SQL/MX recompilation to yield performance improvements from statement pooling. The SQL/MX executor automatically recompiles queries when certain conditions are met.
NOTE: This property can be set either through the java command line option or through the property file of the Datasource. Statement Level Atomicity To maintain the database consistency, transactions must be controlled so that they either complete successfully or are aborted. With the earlier release versions of JDBC/MX (prior to H10 AAB), the transaction is automatically aborted on any error while performing an SQL statement. This version of JDBC/MX driver follows up with the SQL/MX 3.
You can also programmatically disable or enable nonblocking JDBC/MX by setting the sqlmx_nowait property within the program. Depending on your application, set this property as follows: • In JDBC/MX applications that obtain a JDBC connection by using the DriverManager class, set this property before the JDBC/MX driver is loaded. • In JDBC/MX applications that obtain a JDBC connection by using JNDI API with the DataSource interface, set this property before the DataSource object is created.
the number of rows affected by the corresponding statement for each item in the array. By default, the JDBC/MX driver performs batch processing by returning a row-count array. • When the jdbcmx.batchBinding property is set, the detailed information indicated in the preceding bulleted item is no longer available. If the statement execution succeeds, the row-count item is set to Statement.SUCCESS_NO_INFO in compliance with the JDBC 3.0 specification. The PreparedStatement.
Table 8 Corresponding SQL/MX Character Sets and Java Encoding Sets (continued) SQL/MX Character Set Corresponding Java Encoding Set-Canonical Name for java.io API Corresponding Java Encoding Set-Canonical Name for java.io and java.lang API Description KANJI Shift_JIS SJIS The multibyte character set widely used in Japan. It is composed for a single-byte character set and a double-byte character set. It is a subset of Shift JIS (the double character portion). Its encoding is big-endian.
4 Working with BLOB and CLOB Data This section describes working with BLOB and CLOB data in JDBC applications. You can use the standard interface described in the JDBC 3.0 API specification to access BLOB and CLOB data in NonStop SQL/MX tables with support provided by the JDBC/MX driver. BLOB and CLOB are not native data types in an SQL/MX database.
Figure 2 LOB Architecture: Tables for LOB Data Support Setting Properties for the LOB Table Before running the JDBC application that uses BLOB and CLOB data through the JDBC API, the database administrator must create the LOB tables. For information on creating LOB tables, see “Managing LOB Data by Using the JDBC/MX Lob Admin Utility” (page 54). The JDBC applications that access BLOB or CLOB data must specify the associated LOB table names and, optionally, configure the reserveDataLocator property.
Storing CLOB Data • “Inserting CLOB Columns by Using the Clob Interface” (page 45) • “Writing ASCII or Unicode Data to a CLOB Column” (page 45) • “Inserting CLOB Data by Using the PreparedStatement Interface” (page 46) • “Inserting a Clob Object by Using the setClob Method” (page 46) Inserting CLOB Columns by Using the Clob Interface When you insert a row containing a CLOB data type, and before the column can be updated with real CLOB data, you can insert a row that has an "empty" CLOB value .
Clob myClob = null; // stream begins at position 1 long pos = 1; // Example string containing the Unicode data String s = “TEST_UNICODE_DATA”; // Obtain the output stream to write Clob data Writer cw = myClob.setCharacterStream(pos); // write Clob data using Writer char[] myClobData = s.toCharArray(); cw.
Reading ASCII Data from a CLOB Column You can read ASCII or Unicode data from a CLOB column by using the Clob interface or InputStream. The following code illustrates how to read the ASCII data from the CLOB column by using the Clob interface: // Obtain the Clob from ResultSet Clob myClob = rs.getClob("myClobColumn"); // Obtain the input stream to read Clob data InputStream is = myClob.getAsciiStream(); // read Clob data using the InputStream byte[] myClobData = new byte[length]; int readLen = is.
When the Clob objects differ, the Clob object in the updateClob method behaves as if the setClob method was issued. See “Inserting a Clob Object by Using the setClob Method” (page 46). Replacing Clob Objects You can replace Clob objects in the following ways: • Use the EMPTY_CLOB() function to replace the Clob object with the empty Clob object, then insert new data as described under “Inserting CLOB Columns by Using the Clob Interface” (page 45). • Use the PreparedStatement.
Then, you must obtain the handle to the empty BLOB column by selecting the BLOB column for update. The following code illustrates how to obtain the handle to an empty BLOB column: Blob myBlob = null; Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("Select myBlobColumn from myTable where …For update"); if (rs.next()) myBlob = rs.getBlob(1); You can now write data to the BLOB column. See “Writing Binary Data to a BLOB Column” (page 49).
Reading Binary Data from a BLOB Column You can read binary data from the BLOB column by using the Blob interface or InputStream. The following code illustrates how to read the binary data from the BLOB column by using the Blob interface: // Obtain the Blob from ResultSet Blob myBlob = rs.getBlob("myBlobColumn"); // Obtain the input stream to read Blob data InputStream is = myBlob.getBinaryStream(); // read Blob data using the InputStream byte[] myBlobData = new byte[length]; is.
When the row containing the BLOB column is deleted by the application, the corresponding BLOB data is automatically deleted by the delete trigger associated with the base table. For information about triggers, see “Using SQL/MX Triggers to Delete LOB Data” (page 56). See also “NULL and Empty BLOB or CLOB Value” (page 51). NULL and Empty BLOB or CLOB Value The data locator can have a NULL value if the BLOB or CLOB column is omitted in the insert statement.
Only one InputStream or Reader and one OutputStream or Writer can be associated with the current LOB object. 52 • When the application obtains the InputStream or Reader from the LOB object, the JDBC/MX driver closes the InputStream or Reader that is already associated with the LOB object. • Similarly, when the application obtains the OutputStream or Writer from the LOB object, the JDBC/MX driver closes the OutputStream or Writer that is already associated with the LOB object.
5 Managing the SQL/MX Tables for BLOB and CLOB Data BLOB and CLOB are not native data types in an SQL/MX database. But, database administrators can create SQL/MX tables that have BLOB and CLOB columns by using the JDBC/MX driver or special SQL syntax in MXCI as described in this section. For management purposes, CLOB and BLOB data is referred to as large object (LOB) data, which can represent either data type. NOTE: Support for BLOB and CLOB data requires SQL/MX tables.
1. At the OSS prompt, invoke the SQL/MX utility MXCI. Type: MXCI 2. Type the following command to enable creating tables that have LOB columns: CONTROL QUERY DEFAULT JDBC_PROCESS 'TRUE' 3. Type the CREATE TABLE statement; for example, you might use the following simple form of the statement: CREATE TABLE table1 (c1 INTEGER NOT NULL, c2 CLOB, c3 BLOB, PRIMARY KEY(c1)) where; table1 The name of the base table. c1 Column 1, defined as the INTEGER data type with the NOT NULL constraint.
java_options The java_options are properties that can be specified on the java command line in the -D option. Property Specification Description jdbcmx.blobTableName Specifies the LOB table for using BLOB columns. Required if BLOB columns are involved. See “LOB Table Name Properties” (page 25). jdbcmx.clobTableName Specifies the LOB table for using CLOB columns. Required if CLOB columns are involved. See “LOB Table Name Properties” (page 25). jdbcmx.catalog Sets the default catalog.
-out - Write the SQL statements to . -bigblock - Generates SQL statement to create LOB column size of 24K bytes and attribute block size of 32K. | is: .. is: [.][.] is the table that contains LOB column(s). is the table that contains the LOB data.
6 Module File Caching The Module File Caching (MFC) feature shares the SQL/MX prepared statement plans among the JDBC/MX T2 database connections and JVM processes. It helps in reducing the SQL/MX compilation time during the steady state of the JDBC/MX T2 application, thereby reducing resource consumption. NOTE: Module File Caching is supported only on systems running J06.07 and later J-series RVUs, and H06.18 and later H-series RVUs.
• Combining external statement cache with MFC does not yield memory benefits. The WebLogic Server (WLS) statement cache is an example of external statement cache. It is recommended that you use the JDBC/MX T2 statement cache. • MFC is not supported for BLOB and CLOB data types. Troubleshooting MFC The troubleshooting of MFC includes: • “Benefits of MFC” (page 58) • “Setting an Environment for MFC” (page 58) • “.lock Files” (page 58) • “.
and then enter: assume $zpmon 2. At an SCF prompt, enter the SCF command: add server #zpns1,cpu 1,backupcpu 2 3. Add a fileset: add fileset mxc1,nameserver #zpns1,catalog $oss, pool mxcpool, mntpoint "/usr/tandem/sqlmx/USERMODULES" 4. Verify the status of the fileset: info fileset mxc1,detail 5. Start the fileset: start fileset mxc1 To enable OSS caching, perform the following steps: 1. At a TACL prompt, enter: SCF and then enter: assume $zpmon 2.
Known Issues Scenario 1 MFC plans become obsolete when the base table is altered or dropped. The following sequence of operations illustrates the issue. Operation Expected Result Actual Result Remarks Create table testing(info int); Success Success Table testing is created. Stmt1 = Prepare("select * from testing") Success Success Stmt1 is prepared with MXCMP. Stmt1.execute() Success Success Stmt1 is executed. Stmt1.fetch() Success Success Data in the table testing is retrieved.
7 JDBC/MX Compliance The JDBC/MX Driver for NonStop SQL/MX conforms where applicable to the Oracle JDBC 3.0 API specification. However, the JDBC/MX driver differs from the JDBC standard in some ways because of limitations of NonStop SQL/MX and the JDBC/MX driver. This subsection describes the JDBC methods that are not supported, the methods and features that deviate from the specification, and features that are HP extensions to the JDBC standard.
The following methods in the java.sql package throw an SQLException with the message "Unsupported feature - method-name": Method Comments CallableStatement.getArray(int parameterIndex) CallableStatement.getArray(String parameterName) CallableStatement.getBlob(int parameterIndex) CallableStatement.getBlob(String parameterName) CallableStatement.getClob(int parameterIndex) CallableStatement.getClob(String parameterName) CallableStatement.getObject(int parameterIndex, Map map) CallableStatement.
Method Comments ResultSet.updateBlob(int columnIndex) ResultSet.updateBlob(String columnName) ResultSet.updateClob(int columnIndex) ResultSet.updateClob(String columnName) The following methods in the java.sql package throw an SQLException with the message "Auto generated keys not supported": Method Comments Connection.prepareStatement(String sql, intautoGeneratedKeys) Connection.prepareStatement(String sql, int[]columnIndexes) Connection.
The following optional interfaces in the javax.sql package are not implemented in the JDBC/MX driver: Method Comments javax.sql.XAConnection javax.sql.XADataSource Distributed Transactions, as described in the JDBC 3.0 API specification, are not yet implemented. javax.sql.RowSet javax.sql.RowSetInternal javax.sql.RowSetListener javax.sql.RowSetMetaData javax.sql.RowSetReader javax.sql.RowSetWriter RowSet is not implemented in the JDBC/MX driver.
Method Comments SCOPE_SCHEMA, SCOPE_TABLE, and SOURCE_DATA_TYPE. nl nl java.sql.DatabaseMetaData.getSchemas() TABLE_CATALOG is added to the column data and returns the catalog name. java.sql.DatabaseMetaData.getTables(Stringcatalog, String schemaPattern, String[] types) The column is added to the column data, but its value is set to NULL because SQL/MX does not support the column type for types as follows: TYPE_CAT, TYPE_SCHEMA, TYPE_NAME, SELF_REFERENCING_COL_NAME, and REF_GENERATION.
Method Comments javax.sql.JdbcRowSet.setUsername(String username) javax.sql.JdbcRowSet.setPassword(String password) javax.sql.JdbcRowSet(String url, String username,String password) User name and password arguments are ignored. Security privileges are the same as for the user who invoked the Java VM. javax.sql.JdbcRowSet.setReadOnly(...) The read-only attribute is ignored. javax.sql.JdbcRowSet.setEscapeProcessing(...) Disabling escape processing has no effect because SQL/MX parses the escape syntax.
on an exception. By this, re-execution of entire jobs is avoided. However, for any TMF errors, that results in transaction failure, cannot be addressed by this enhancement. DatabaseMetaData Calls For the DatabaseMetaData calls, specify the catalog and schema values.
Table 9 Numeric Functions (continued) JDBC Function SQL/MX Equivalent Function ATAN2 ATAN2 CEILING CEILING COS COS DEGREES DEGREES EXP EXP FLOOR FLOOR LOG LOG LOG10 LOG10 MOD MOD PI PI POWER POWER RADIANS RADIANS SIGN SIGN SIN SIN SINH SINH SQRT SQRT TAN TAN Table 10 String Functions 68 JDBC Function SQL/MX Equivalent Function ASCII ASCII CHAR CHAR CHAR_LENGTH CHAR_LENGTH CONCAT CONCAT INSERT INSERT LCASE LOWER LEFT SUBSTRING LENGTH LENGTH LOCATE LOCA
Table 10 String Functions (continued) JDBC Function SQL/MX Equivalent Function RTRIM TRIM...TRAILING SPACE SPACE SUBSTRING SUBSTRING UCASE UPPER | UPSHIFT NOTE: JDBC string functions in queries can return unexpected results for fixed-length ( CHAR ) column names because SQL/MX pads a fixed-length string with blanks up to the length of the definition, so the results from some JDBC string functions can include trailing blanks at the end of the string.
Table 12 System Functions JDBC Function SQL/MX Equivalent Function CURRENT_USER CURRENT_USER SYSTEM_USER SYSTEM_USER USER USER CONVERT Function JDBC/MX uses the SQL/MX CAST function to support the JDBC CONVERT function.
JDBC Data Type Supported by JDBC/MX SQL/MX Data Type Types.REF No Types.SMALLINT Yes Types.STRUCT No Types.TIME Yes TIME Types.TIMESTAMP Yes TIMESTAMP Types.TINYINT No Types.VARBINARY No Types.VARCHAR Yes SMALLINT VARCHAR(n) * See “Floating-Point Support” (page 71). ** For details about maximum length, see the SQL/MX Reference Manual. The JDBC/MX driver maps the following SQL/MX data types to the JDBC data type Types.
the conversion from the IEEE 754 format to the Tandem format when storing the values and from the Tandem format to the IEEE 754 format when retrieving and passing the values. Since SQL/MX tables store IEEE 754 floating-point values, JDBC applications accessing floating-point data do not receive floating-point exceptions. The JDBC applications should check for plus (+) or minus (-) infinity conditions to determine if an overflow or underflow has occurred.
8 JDBC Trace Facility The JDBC trace facility traces the entry point of all JDBC methods called from the Java applications. To make this facility generic, it is implemented as a JDBC driver wrapper.
The following table describes the set of properties that are required for a trace data source object: Property Name Type Description dataSourceName String The data source name. description String Description of this data source. traceDataSource String The name of the DataSource object to be traced. The traceDataSource object is used to obtain the JDBC connection to the database. Java applications should turn on tracing using the setLogWriter method of the DataSource interface.
Enabling Tracing for Application Servers Typically, tracing output is written to the PrintWriter object that the application sets by using either the DataSource.setLogWriter() method or DriverManager.setLogWriter() method. User-written Java applications can use these methods with the JDBC Trace Facility. Application servers, however, might not enable the JDBC tracing with the setLogWriter() method.
jdbcTrace:[timestamp] [thread-id]:[object-id] :className.method(param...) returns [return-object] [return-object-id] where timestamp is the day and time representation in the form: mm/dd/yy hr:min:sec where mm is month; dd , day; yy , year; hr , hour; min , minute; sec , seconds. thread-id is the String representation of the current thread. object-id is the hashcode of the JDBC object. classname is the JDBC implementation class name. return-object is the object returned by the traced method.
Mapping statement-IDs to SQL statements applies to any interface that prepares or executes a statement, for example, PreparedStatement, Connection, ResultSet, JdbcRowSet, and Statement. • “Specifying Statement-ID Logging” (page 77) • “Properties for Statement-ID Logging” (page 77) • “Statement-ID Log Output” (page 78) Specifying Statement-ID Logging To specify supplemental logging: • Set the enableLog property to on to enable logging. • Set the idMapFile property to specify the log file.
To enable logging, use the enableLog property. For more information, see “Logging SQL Statement IDs and Corresponding JDBC SQL Statements” (page 76). Statement-ID Log Output The format of a statement-ID log output entry is: [timestamp] STMTobject-id (sql-statement) where timestamp is the day and time representation in the form: mm/dd/yy hr:min:sec where mm is month; dd , day; yy , year; hr , hour; min , minute; sec , seconds. object-id is the hashcode of the JDBC object.
9 Messages JDBC/MX returns sqlcode and file-system error codes as error codes for the getErrorCode() `method of SQLException. Messages from the Java Portion of the JDBC Driver (range Messages from the Native-interface Portion of the JDBC Driver 29000 through 29079) (range 29250 through 29499) 29001-29009 29050-29059 29010-29019 29060-29069 29020-29029 29070-29079 29030-29039 29080-29089 29040-29049 nl 29251-29259 29260-29267 nl nl nl nl Messages are listed in numerical SQLCODE order.
29005 HY024 Invalid ResultSet type Cause: An attempt was made to set an invalid ResultSet Type value. Effect: The SQLMX Statement call with the resultSetType parameter fails. Recovery: Valid ResultSet types are: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. nl 29006 HY000 Invalid Result Set concurrency Cause: An attempt was made to set an invalid result-set concurrency value. Effect: The SQLMXStatement call with resultSetConcurrency fails.
29012 07006 Restricted data type attribute violation Cause: An attempt was made to execute a method either while an invalid data type was set or the data type did not match the SQL column type. Effect: The interface method is not executed. Recovery: Make sure the correct method and Java data type is used for the column type. nl 29013 HY024 Fetch size is less than 0 Cause: The size set for ResultSet.setFetchSize rows to fetch is less than zero.
29019 07002 Parameter {0, number, integer} for {1, number, integer} set of parameters is not set Cause: An input descriptor contains a parameter that does not have a value set. Effect: The method checkIfAllParamsSet() reports the parameter that is not set. Recovery: Set a value for the listed parameter. nl 29020 07009 Invalid parameter index Cause: A getter or setter method parameter count index is outside of the valid input-descriptor range, or the input-descriptor range is null.
29026 HY000 Transaction can't be committed or rolled back when AutoCommit mode is on Cause: An attempt was made to commit a transaction while AutoCommit mode is enabled. Effect: The transaction is not committed. Recovery: Disable AutoCommit. Use the method only when the AutoCommit mode is disabled. nl 29027 HY011 SetAutoCommit not possible, since a transaction is active Cause: An attempt was made to call the setAutoCommit() mode while a transaction was active.
29035 HY000 IO Exception occurred {0} Cause: An ASCII or Binary or Character stream setter or an updater method resulted in a java.io.IOException. Effect: The designated setter or updater method does not modify the ASCII or Binary or Character stream. Recovery: Informational message only; no corrective action is needed. nl 29036 HY000 Unsupported encoding {0} Cause: The character encoding is not supported. Effect: An exception is thrown when the requested character encoding is not supported.
29042 HY000 Fetch size value is not valid Cause: An attempt was made to set the fetch-row size to a value that is less than 0. Effect: The number of rows that are fetched from the database when more rows are needed is not set. Recovery: For the setFetchSize() method, supply a valid row value that is greater than or equal to 0. nl 29043 HY000 Max rows value is not valid Cause: An attempt was made to set a limit of less than 0 for the maximum number of rows that any ResultSet object can contain.
Recovery: Before calling the begintransaction() method, validate whether other transactions are currently started. nl 29050 HY107 Row value out of range Cause: A call to getCurrentRow retrieved is outside the first and last row range. Effect: The current row is not retrieved. Recovery: It is an informational message only; no recovery is needed. Report the entire message to your service provider. nl 29051 01S02 ResultSet type changed to TYPE_SCROLL_INSENSITIVE Cause: The Result Set Type was changed.
Recovery: Use the validateSetInvocation() or validateExecuteInvocation method prior to the statement close. nl 29057 HY000 Auto generated keys not supported Cause: An attempt was made to use the Auto-generated keys feature. Effect: The attempt does not succeed. Recovery: The Auto-generated keys feature is not supported. nl 29058 HY000 Connection is not associated with a PooledConnection object Cause: The getPooledConnection() method was invoked before the PooledConnection object was established.
29063 HY000 Transaction error {0} - {1} while obtaining start data locator Cause: A transaction error occurred when the JDBC/MX driver attempted to reserve the data locators for the given process while inserting or updating a LOB column. Effect: The application cannot insert or update the LOB columns. Recovery: Check the file-system error in the message and take recovery action accordingly.
29070 HY000 Transaction error {0} - {1} while updating LOB tables Cause: An SQL or file system (FS) exception occurred during insert or update operations on the base and LOB tables within an internal transaction. Effect: An exception is reported; the internal transaction is rolled back. Recovery: See the SQL or FS error message.
Recovery: An SQL warning condition. Use the maxStatements property (or -Djdbcmx.maxStatements command-line property) to increase the number of statements allowed. nl 29077 HY000 HY000 Max rows value cannot be less than the fetch size Cause: The row value passed to the JdbcRowSet.setMaxRows method is less than the current fetch-size setting. Effect: The maximum number of rows that the JdbcRowSet object can contain is not set. Recovery: Increase the fetch-size value by using the JdbcRowSet.
Recovery: Use the setMatchColumn() method to set the designated column as a match column. nl 29084 HY000 Use column name as argument to unsetMatchColumn Cause: A column-name string value is passed to the unsetMatchColumn(integer i) method. Effect: An exception is thrown. The designated match column for this JdbcRowSet object is not unset. Recovery: Call the unsetMatchColumn(integer i) method with an integer column ID value, or use the unsetMatchColumn(String s) method.
Recovery: For SQL parameter errors, the exception-message text usually identifies the problem to be corrected. For unhandled SQL errors, the Error Code of the exception identifies the SQL error that was caught. Refer to the SQL error-message documentation for details about the error code. nl 29252 HY008 Operation Cancelled Cause: An SQL operation was cancelled by a break. Effect: An exception is reported; the operation is not completed. Recovery: This message is application-specific.
29259 HY000 Module Error Cause: An invalid parameter was detected when attempting to get catalog information or attempting to prepare a statement from a module. Effect: An exception is reported; the operation is not completed. Recovery: See the exception message for recovery details. nl 29260 HY000 Invalid Statement/Connection Handle Cause: An invalid SQL statement handle was detected. Effect: An exception is reported; the operation is not completed.
Recovery: None. BIT, BITVAR, BPINT_UNSIGNED, SQLTYPECODE_FLOAT, SQLTYPECODE_REAL, and SQLTYPECODE_DOUBLE data types are not expected to be returned from SQL/MX. The JDBC/MX driver does not support these data types. nl 29267 HY000 Exceeded JVM allocated memory Cause: JDBC attempted to internally allocate JVM memory after it has been exhausted. Effect: The condition is a function of the JVM heap size. An exception is thrown. Recovery: Configure the maximum JVM heap size accordingly.
A Sample Programs Accessing CLOB and BLOB Data This appendix shows two working programs: • Sample Program Accessing CLOB Data • Sample Program Accessing BLOB Data Sample Program Accessing CLOB Data This sample program shows operations that can be performed through the CLOB interface or through the PreparedStatement interface. The sample program shows examples of both interfaces taking a variable and putting the variable's value into a base table that has a CLOB column.
FileInputStream clobFs = new FileInputStream(args[1]); int clobFsLen = clobFs.available(); if (args.length == 3) length = Integer.parseInt(args[2]); recKey = Integer.parseInt(args[0]); System.out.println("Key: " + recKey +"; Using " + length + " of file " + args[1]); try { Class.forName("com.tandem.sqlmx.SQLMXDriver"); start = System.currentTimeMillis(); conn1 = DriverManager.getConnection("jdbc:sqlmx:"); System.out.println("Cleaning up test tables..."); Statement stmt0 = conn1.createStatement(); stmt0.
Sample Program Accessing BLOB Data This sample program shows the use of both the Blob interface and the PreparedStatement interface to take a byte variable and put the variable's value into a base table that has a BLOB column. // LOB operations may be performed through the Blob, or // PreparedStatement interface. This program shows examples of // using both interfaces taking a byte[] variable and putting // it into the cat.sch.blobtiff table.
stmt0.execute("delete from blobtiff"); conn1.setAutoCommit(false); } catch (Exception e1) { e1.printStackTrace(); } // PreparedStatement interface example - This technique is // suitable if the LOB data is already on the // NonStop system disk. try { System.out.println("PreparedStatement interface LOB insert..."); String stmtSource1 = "insert into blobtiff values (?,?)"; PreparedStatement stmt1 = conn1.prepareStatement(stmtSource1); stmt1.setInt(1,recKey); stmt1.setBinaryStream(2,iXstream,numBytes); stmt1.
Glossary A abstract class In Java, a class designed only as a parent from which subclasses can be derived, which is not itself suitable for instantiation. An abstract class is often used to "abstract out" incomplete sets of features, which can then be shared by a group of sibling subclasses that add different variations of the missing pieces.
CLOB Short for Character Large OBject, text data stored as a single entity in a database management system. A Java Clob object (Java type, java.sql.Clob) corresponds to the SQL CLOB data type. command The operation demanded by an operator or program; a demand for action by, or information from, a subsystem. A command is typically conveyed as an interprocess message from a program to a subsystem.
H Hotspot virtual machine See Java Hotspot virtual machine. HP JDBC Driver for SQL/MP (JDBC/MP) The product that provides access to SQL/MP and conforms to the JDBC API. HP JDBC Driver for SQL/MX (JDBC/MX) The product that provides access to SQL/MX and conforms to the JDBC API. HP NonStop ODBC Server The HP implementation of ODBC for NonStop systems. HP NonStop operating system The operating system for NonStop systems.
I IEC See International Electrotechnical Commission (IEC). IEEE Institute for Electrical and Electronic Engineers (IEEE). inlining Replacing a method call with the code for the called method, eliminating the call. interactive Question-and-answer exchange between a user and a computer system. interface In general, the point of communication or interconnection between one person, program, or device and another, or a set of rules for that interaction. See also API .
Java Database Connectivity (JDBC) An industry standard for database-independent connectivity between the Java platform and relational databases such as NonStop SQL/MP or NonStop SQL/MX . JDBC provides a call-level API for SQL-based database access. Java HotSpot virtual machine The Java virtual machine implementation designed to produce maximum program-execution speed for applications running in a server environment.
javap The Java class file disassembler, which disassembles compiled Java files and prints a representation of the Java bytecode . Also, the command to run the Java class file disassembler. JCK See Java Conformance Kit (JCK). jdb The Java Debugger, which helps you find and fix errors in Java programs. Also, the command to run the Java Debugger .jdb uses the Java Debugger API. JDBC See Java Database Connectivity (JDBC). JDBC/MP See HP JDBC Driver for SQL/MP (JDBC/MP).
NonStop Technical Library The browser-based interface to NonStop computing technical information. NonStop Technical Library replaces HP Total Information Manager (TIM). NSK See HP NonStop operating system. NSKCOM A program management tool for swap space. O Object Management Group (OMG) The standards body that defined CORBA.
Pathway CGI An extension to iTP Secure WebServer that provides CGI -like access to Pathway server classes. Extended in the NonStop Server for Java so that Java servlets can be invoked from a ServletServerClass, a special Pathway CGI server. Pathway/TS An HP product that provides tools for developing and interpreting screen programs to support OLTP programs in the Guardian environment on NonStop servers . Pathway/TS screen programs communicate with terminals and intelligent devices.
serialized object An object that has undergone object serialization. serialver The Serial Version Command, which returns the serialVersionUID of one or more classes. Also, the command to run the Serial Version Command. server 1. 2. 3. servlet An implementation of a system used as a stand-alone system or as a node in an Expand network. The hardware component of a computer system designed to provide services in response to requests received from clients across a network.
thread A task that is separately dispatched and that represents a sequential flow of control within a process. threads The nonnative thread package that is shipped with Sun Microsystems Java SE 6.0. throw Java keyword used to raise an exception. throws Java keyword used to define the exceptions that a method can raise.
