Contents HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.1 Abstract 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. Product Version JDBC/MX driver for NonStop SQL/MX R 3.
Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein. Export of this documentation may require authorization from the U.S. Department of Commerce. Microsoft, Windows, and Windows NT are U.S. registered trademarks of Microsoft Corporation. Intel, Itanium, Pentium, and Celeron are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.
Home HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.
minPoolSize Property maxPoolSize Property transactionMode Property Setting Properties in the Command Line Transactions Autocommit Mode and Transaction Boundaries Disabling Autocommit Mode Stored Procedures Limitations SQL Context Management Holdable Cursors Connection Pooling Connection Pooling by an Application Server Connection Pooling Using the Basic DataSource API Connection Pooling with the DriverManager Class Statement Pooling Guidelines for Statement Pooling Controlling the Performance of ResultSet P
Deleting BLOB Data NULL and Empty BLOB or CLOB Value Transactions Involving Blob and Clob Access Access Considerations for Clob and Blob Objects Managing the SQL/MX Tables for BLOB and CLOB Data Creating Base Tables that Have LOB Columns Data Types for LOB Columns Using MXCI to Create Base Tables that Have LOB Columns Using JDBC Programs to Create Base Tables that Have LOB Columns Managing LOB Data by Using the JDBC/MX Lob Admin Utility Running the JDBC/MX Lob Admin Utility Help Listing from the JDBC/MX Lob
Messages Messages from the Java Side of the JDBC/MX Driver Messages from the JNI Side of the JDBC/MX Driver Appendix A. Sample Programs Accessing CLOB and BLOB Data Sample Program Accessing CLOB Data Sample Program Accessing BLOB Data Glossary Index List of Examples List of Figures List of Tables Home HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.1 (663859-001) © 2011 Hewlett-Packard Development Company L.P. All rights reserved.
Home | Contents | Index | Glossary | Prev | Next About This Document This section explains these subjects: New and Changed Information Is This Document for You? Document Structure Printing This Document Related Reading Notation Conventions Abbreviations New and Changed Information Changes added to 663859-001 manual: Added a new program option -bigblock in Running the JDBC/MX Lob Admin Utility . Added a new program option -bigblock in Help Listing from the JDBC/MX Lob Admin Utility.
This document also assumes that you are already familiar with the JDBC API from reading literature in the field. Document Structure This document is a set of linked HTML files (Web pages). Each file corresponds to one of the sections listed and described in the following table. Document Sections Section Description Table of Contents Shows the structure of this document in outline form. Each section and subsection name is a link to that section or subsection.
Appendix A. Sample Programs Accessing CLOB and BLOB Data Shows sample program accessing CLOB and BLOB data. Glossary Defines many terms that this document uses. Index Lists this document's subjects alphabetically. Each index entry is a link to the appropriate text. List of Examples Lists the examples in this document. Each example name is a link to that example. List of Figures Lists the figures in this document. Each figure name is a link to that figure.
NonStop Server for Java Tools Reference Page This documentation consists of a title page, a table of contents, and the Tools Reference Pages for NonStop Server for Java 5. NonStop Server for Java API and Reference This documentation contains the documentation for these packages: com.tandem.os com.tandem.tmf com.tandem.
SQL/MX Installation and Management Guide Describes how to install and manage SQL/MX on a NonStop server. SQL/MX Glossary Explains the terminology used in SQL/MX documentation. SQL/MX Query Guide Explains query execution plans and how to write optimal queries for SQL/MX. SQL/MX Reference Manual Describes SQL/MX language elements (such as expressions, predicates, and functions) and the SQL statements that can be run in MXCI or in embedded programs. Also describes MXCI commands and utilities.
more extensive documentation than JDK 5.0. HP cannot guarantee the availability of the JDK 5.0 documentation on the Oracle websites. Also, HP is not responsible for the links or content in the documentation from Oracle. JDBC 3.0 Specification, available for downloading from Oracle ( http://www.oracle.com/technetwork/java/download-141179.html). JDBC API Documentation, includes links to APIs and Tutorials ( http://download.oracle.com/javase/1.5.0/docs/guide/jdbc/index.
| Vertical Line A vertical line separates alternatives in a list that is enclosed in brackets or braces. For example: where [threadID|all] ... Ellipsis An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: 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 ...
DDL. Data Definition Language DML. Data Manipulation Language HTML. Hypertext Markup Language HTTP. Hypertext Transfer Protocol IEC. International Electrotechnical Committee ISO. International Organization for Standardization JAR. Java Archive JCK. Java Conformance Kit JFC. Java Foundation Classes JDBC. Java Database Connectivity JDBC/MP. JDBC Driver for SQL/MP JDBC/MX. JDBC Driver for NonStop SQL/MX JNDI. Java Naming and Directory Interface JNI. Java Native Interface JRE. Java Run-time Environment LAN.
TMF. Transaction Management Facility URL. uniform resource locator VM. virtual machine WWW. World Wide Web Home | Contents | Index | Glossary | Prev | Next HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.1 (663859-001) © 2011 Hewlett-Packard Development Company L.P. All rights reserved.
Home | Contents | Index | Glossary | Prev | Next Introduction to JDBC/MX Driver The HP JDBC/MX Driver 3.1 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 earlier in this document.
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.
run concurrently. This program displays the thread ID and status of the SQL operation before and after each operation. When the program runs in blocking mode, you observe only one thread switch because the begin-transaction operation starts a transaction in SQL nowait mode. When the program runs in nonblocking mode, you can observe many thread switches. holdJdbcMx.java Illustrates the holdable cursor support in the JDBC/MX driver. The program creates a subscriber thread that subscribes to a message queue.
Home | Contents | Index | Glossary | Prev | Next Installing and Verifying JDBC/MX The Softdoc file contains the instructions on how to install the Type 2 driver on NonStop SQL/MX 3.0 or later versions.
.../demo 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 ...
Setting the _RLD_LIB_PATH For running JDBC applications, ensure the _RLD_LIB_PATH environment variable path is set to TNS/E jdbcMx PIC file. Given the default installation, the path is: /usr/tandem/jdbcMx/T1275R31/lib Home | Contents | Index | Glossary | Prev | Next HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.1 (663859-001) © 2011 Hewlett-Packard Development Company L.P. All rights reserved.
Home | Contents | Index | Glossary | Prev | Next Accessing SQL Databases with SQL/MX This section describes the following subjects: Connection to SQL/MX JdbcRowSet Implementation JDBC/MX Properties Transactions Stored Procedures SQL Context Management Holdable Cursors Connection Pooling Statement Pooling Using Additional JDBC/MX Properties Supported Character Set Encodings Connection to SQL/MX A Java application can obtain a JDBC connection to SQL/MX in two ways: Using the DriverManager class Using the Da
JDBC/MX Driver Properties Used with the DriverManager Class JDBC/MX driver defines the following set of properties that you can use to configure the driver: Property Name contBatchOnError Type String Value on or off Description Communicates with JDBC driver to continue the remaining jobs in the batch even after any BatchUpdateExceptions. See contBatchOnError Property. catalog String See Default Catalog and Schema.
minPoolSize int Property. can be in the free connection pool. See minPoolSize Property. maxPoolSize int See maxPoolSize Property. Sets maximum number of physical connections that the pool should contain. This number includes both free connections and connections in use. See maxPoolSize Property. blobTableName String See LOB Table Name Properties. Specifies the LOB table for using BLOB columns. clobTableName String See LOB Table Name Properties. Specifies the LOB table for using CLOB columns.
schema combination. As in the basic DataSource object implementation, a Java application can enable statement pooling by setting the property to a non-zero positive value. Connection Using the DataSource Interface The DataSource interface, introduced in the JDBC 2.0 optional package, is the preferred way to establish a connection to the database because it enhances the application portability.
enableLog boolean on or off statement IDs and the corresponding JDBC SQL statements. See enableLog Property. idMapFile String A valid OSS filename Specifies the file to which the trace facility logs SQL statement IDs and the corresponding JDBC SQL statements. See idMapFile Property. ISO88591 String See ISO88591 Property. Specifies the Java encoding used when accessing and writing to ISO88591 columns. See maxPoolSize Property.
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. JdbcRowSet Implementation An implementation of the JdbcRowSet interface, SQLMXJdbcRowSet , is provided within the com.tandem.sqlmx package. A JdbcRowSet object maintains a connection to the database, similar to a ResultSet object.
Additional JDBC/MX Properties. Default Catalog and Schema The default catalog and schema are used to access SQL objects referenced in SQL statements if the SQL objects are not fully qualified. The three-part fully qualified name for SQL/MX objects is of the form: [[catalog.]schema.]object-name The catalog and schema names can be any arbitrary strings that conform to SQL identifiers. These names conform to ANSI SQL:99 catalog and schema names.
(http://download.oracle.com/javase/1.5.0/docs/guide/intl/encoding.doc.html). For example, if KANJI data has been stored in an ISO88591 column in an SQL/MP table (accessed through SQL/MX) and has been read and written to the database using the column character set, you can specify the following property to ensure the correct encoding: -Djdbcmx.ISO88591=SJIS mploc Property The property mploc specifies the Guardian location in which SQL tables are created. The format for mploc is: [\node].$volume.
when the connection is closed. maxPoolSize Property Sets the maximum number of physical connections that the pool can contain. These connections include both free connections and connections in use. When the maximum number of physical connections is reached, the JDBC/MX driver throws an SQLException with the message, "Maximum pool size is reached." Specify the maxPoolSize property as: int The integer can be -1, 0 through 2147483647, but greater than minPoolSize.
results in an SQL exception. Therefore, do not specify external transactionMode under these conditions. Considerations: If any other string is specified for the value of transaction mode, mixed is used. Using the external or mixed transaction mode can improve performance. Using the internal transaction mode can affect performance for applications because of the overhead of TMF transactions under a heavy load.
jdbcmx. enableLog and the corresponding JDBC SQL statements. See enableLog Property. idMapFile Specifies the file to which the trace facility logs SQL statement IDs and the corresponding JDBC SQL statements. See idMapFile Property. jdbcmx. ISO88591 Specifies the encoding to be used when accessing or writing to data stored in IS088591 columns. See ISO88591 Property. jdbcmx. maxPoolSize Sets the maximum pool size. See maxPoolSize Property. jdbcmx.
Transactions The JDBC/MX driver provides transaction support to maintain data integrity and consistency. To allow the application to interleave transactions between SQL/MX objects and the traditional file system, the JDBC/MX driver checks if a transaction is active whenever it needs to interact with SQL/MX. The transactionMode property determines transaction processing behavior.
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 .
schema SET SCHEMA default-schema-name mploc SET MPLOC default-location transaction isolation SET TRANSACTION isolation-level 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.
Connection Pooling with the DriverManager Class Connection Pooling by an Application Server Usually, in a three-tier environment, the application server implements the connection pooling component.
propertyCycle int The interval, in seconds, that the pool should wait before enforcing the current policy defined by the values of the above connection pool properties.
Connections with the same catalog-schema combination are pooled together and managed by the JDBC/MX driver. The connection-pooling property values that the application process uses when it obtains the first connection for a given catalog-schema combination are effective for that combination through the life of the application process.
Setting the fetch size greater than 2 for statements that return fewer than two rows causes a slight performance degradation, as compared to using the default fetch size. Setting the fetch size to a value greater than the number of rows returned by a statement causes the JDBC/MX driver to use more memory, but does not affect the API's functionality.
In addition to these topics, also see Enabling Tracing for Application Servers. BatchUpdate Exception handling Improvements When a command in the batch fails, the remaining commands of the batch are not executed resulting in reexecution of entire batch. But, with this Batch Update Exception handling support, the remaining elements of the batch after the error prone statement can be executed and hence re-execution of the entire batch jobs can be avoided.
Note: This functionality is already available in JDBC/MX H10AAB and V32AAU versions. Managing Nonblocking JDBC/MX Blocking mode with the JDBC/MX driver causes the whole JVM process to be blocked when an SQL operation occurs. Nonblocking mode causes the JDBC/MX driver to block only the thread that invokes the SQL operation and not the whole JVM process.
You can improve the performance of batch processing when using the PreparedStatement.executeBatch() method by setting the batchBinding property. When the batchBinding property is set, the statements are batched in the executeBatch() operation. When a JDBC application sets the batchBinding property, the JDBC/MX driver allocates resources relative to the specified binding size. To set the batchBinding size, specify the batchBinding property in the command line. The syntax is: -Djdbcmx.
Java applications using the JDBC/MX driver can specify the Java file.encoding property to set the default encoding to any character set supported by Java if no SQL literals exist in the program. If the program has SQL literals, the program should use only the Java encoding sets that correspond to SQL/MX supported sets.
KSC5601 EUC-KR (Code Set 1) EUC_KR Double-character character set required on systems used by government and banking within Korea. Its encoding is big endian. NOTE: KSC5601 is supported in SQL/MP tables only. For complete information on character sets supported by SQL/MX and any additional limitations on support for SQL/MP tables, see the SQL/MX Reference Manual. For complete information about NonStop Server for Java 5 support for encodings see Supported Encodings (http://download.oracle.com/javase/1.5.
Home | Contents | Index | Glossary | Prev | Next 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. and CLOB are not native data types in an SQL/MX database.
Architecture for LOB Support The tables that support LOB data are: Base table Referenced by JDBC applications to insert, store, read, and update BLOB and CLOB data. In the base table, the JDBC/MX driver maps the BLOB and CLOB columns into a data-locator column. The data-locator column points to the actual LOB data that is stored in a separate user table called the LOB table. LOB table Actually contains the BLOB and CLOB data in chunks. A Clob or Blob object is identified by a data locator.
object by using one of the following properties, depending on the LOB column type: LOB Column Type Property name BLOB blobTableName CLOB clobTableName For more information about using these properties, see LOB Table Name Properties. Reserving Data Locators A data locator is the reference pointer value (SQL LARGEINT data type) that is substituted for the BLOB or CLOB column in the base table definition. Each object stored into the LOB table is assigned a unique data locator value.
You can now write data to the CLOB column. See Writing ASCII or Unicode Data to a CLOB Column. Writing ASCII or Unicode Data to a CLOB Column You can write ASCII or Unicode data to a CLOB column as follows. ASCII Data Unicode Data ASCII Data You can write ASCII or Unicode data to the CLOB column by using the Clob interface. The following code illustrates using the setAsciiStream method of the Clob interface to write CLOB data.
into myTable (myClobColumn) values (?)"); ps.setAsciiStream(1, inputAsciiStream, clobLen); ps.executeUpdate(); The JDBC/MX driver reads the data from FileInputSteam and writes the data to the LOB table. The JDBC/MX driver substitutes the next-available data locator for the parameter of the CLOB column in the table. Unicode Data You can insert a CLOB column with Unicode data from a FileReader . You must use the PreparedStatement interface to insert the CLOB column.
Reading Unicode Data from a CLOB Column You can read Unicode data from the CLOB column by using the Clob interface or Reader. The following code illustrates how to read the Unicode 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 Reader cs = myClob.getCharacterStream(); // read Clob data using Reader char[] myClobData = new char[length]; int readLen = cs.
Deleting CLOB Data To delete CLOB data, the JDBC application uses the SQL DELETE statement to delete the row in the base table. When the row containing the CLOB column is deleted by the JDBC application, the corresponding CLOB data is automatically deleted by the delete trigger associated with the base table. For information about triggers, see Using an SQL/MX Trigger to Delete LOB Data. See also NULL and Empty BLOB or CLOB Value.
Writing Binary Data to a BLOB Column You can write data to the BLOB column by using Blob interfaces. The following code illustrates using the setBinaryStream method of the Blob interface to write BLOB data. Blob myBlob = null // Stream begins at position 1 long pos = 1; // Example string containing binary data String s = "BINARY_DATA"; for (int i=0; i<5000; i++) s = s + "DATA"; // Obtain the output stream to write Blob data OutputStream os = myBlob.
// obtain the InputStream from ResultSet InputStream is = rs.getBinaryStream("myBlobColumn"); // read Blob data using the InputStream byte[] myBlobData = new byte[length]; is.read(myBlobData, offset, length); Updating BLOB Data You can update BLOB data by using the methods in the Blob interface or by using the updateClob method of the ResultSet interface. The JDBC/MX driver makes changes to the BLOB data directly.
See also NULL and Empty BLOB or CLOB Value. 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. The JDBC/MX driver returns NULL when the application retrieves the value for such a column. When the application uses the EMPTY_BLOB() method or EMPTY_CLOB() method to insert empty BLOB or CLOB data into the BLOB or CLOB column, JDBC/MX driver returns the Blob or Clob object with no data.
throws an SQLException, issuing the following message, when the application attempts to perform operations on a LOB object that is not current: Lob object {object-id} is not current Only one InputStream or Reader and one OutputStream or Writer can be associated with the current LOB object. 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.
Home | Contents | Index | Glossary | Prev | Next Managing the SQL/MX Tables for BLOB and CLOB Data 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.
Using MXCI To Create Base Tables that Have LOB Columns Before using the procedure to create the tables, note that when using MXCI to create base tables, you must enter the following special command in the MXCI session to enable the base table creation of tables that have LOB ( BLOB or CLOB ) columns: CONTROL QUERY DEFAULT JDBC_PROCESS 'TRUE' Follow these steps to create a base table that has LOB columns: 1. At the OSS prompt, invoke the SQL/MX utility MXCI. Type: MXCI 2.
Creating the SQL/MX triggers for the LOB columns in the base tables to ensure that orphan LOB data does not occur in a LOB table. Information about using the JDBC/MX Lob Admin Utility is provided in these topics. Running the JDBC/MX Lob Admin Utility Help Listing from the JDBC/MX Lob Admin Utility Using SQL/MX Triggers to Delete LOB Data Running the JDBC/MX Lob Admin Utility Run the JDBC/MX Lob Admin utility in the OSS environment.
-drop must exist. -out Writes the SQL statements to a specified file in OSS file space. -bigblock Generates SQL statements to create LOB column size of 24K bytes and attribute block size of 32K. table_name The table_name represents a base table that contains LOB columns. The table_name is of the form: [catalogName.][schemaName.]baseTableName For information about catalog, schema, and table names, see the SQL/MX Reference Manual.
Generates SQL statements to create triggers. -drop Generates SQL statements drop triggers. For example, the following command (typed on one line) generates the SQL statements to create the triggers for the base table sales.paris.pictures, which contains a BLOB column, and executes those statements. java -Djdbcmx.blobTableName=sales.paris.lobTable4pictures JdbcMxLobAdmin -trigger -exec sales.paris.
Home | Contents | Index | Glossary | Prev | Next Module File Caching (MFC) 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.
It is recommended to set the CQD in SQL/MX for disabling the auto-recompilation feature of the SQL/MX while using with MFC. This ensures that automatic recompilations are avoided due to changes in SQL/MX objects because the plans are generated in the module file. The application will receive an SQL/MX exception if there is an auto-recompilation required for the query. You must clean the stale module files before continuing with the application.
These temporary files are generated during preprocessing. These .mdf files are retained for easier support and troubleshooting. Disk Activity The MFC access plans, stored in the disk OSH location ( /usr/tandem/sqlmx/USERMODULE ), increases the processor utilization of the disks. To overcome this problem, use the fileset for that directory. It is beneficial to have OSS caching on data volumes. To enable fileset and OSS caching, see Enable Fileset and OSS Caching.
This command begins with the last fileset mounted and stops the filesets in the reverse order in which they were last started. 3. Stop the OSS Monitor process: If the OSS Monitor is running as a standard process, enter the STOP command at a TACL prompt: STOP $ZPMON If the OSS Monitor is running as a persistent process, enter the ABORT command at an SCF prompt: ABORT PROCESS $ZZKRN.#ZPMON 4.
Success Stmt1.execute() plan is created. MXOSRVR turned the SQL/MX CQD recompilation_warnings ON. SQL/MX throws SQL exception upon similarity Failure check failure and MXOSRVR drops the invalid module file from the /usr/tandem/sqlmx/USERMODULES Stmt1 = Prepare("select * from testing") Success Stmt1.execute() Success location. Success A new plan is created in the MFC location. Success MFC statement works as expected.
Home | Contents | Index | Glossary | Prev | Next 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.
CallableStatement.getRef(int parameterIndex) CallableStatement.getRef(String parameterName) CallableStatement.getURL(int parameterIndex) CallableStatement.getURL(String parameterName) CallableStatement.executeBatch() Connection.releaseSavepoint(Savepoint savepoint) Connection.rollback(Savepoint savepoint) Connection.setSavepoint() Connection.setSavepoint(String name) The particular Connection methods are not supported. PreparedStatement.setArray(int parameterIndex, Array x) PreparedStatement.
The following methods in the java.sql package throw an SQLException with the message "Data type not supported:" Method Comments CallableStatement.getBytes(int parameterIndex) CallableStatement.getBytes(String parameterName) The particular data type is not supported. CallableStatement.setBytes(String parameterIndex, bytes[] x) Supports only BLOB, VARCHAR, BINARY, LONGVARCHAR, VARBINARY, and LONGVARBINARY; otherwise, the particular data type is not supported. PreparedStatement.
columnIndex) javax.sql.JdbcRowSet.getURL(String columnName) javax.sql.JdbcRowSet.rollback(Savepoint savepoint) javax.sql.JdbcRowSet.setArray(int parameterIndex, Array x) javax.sql.JdbcRowSet.setRef(int parameterIndex, Ref x) javax.sql.JdbcRowSet.updateArray(int columnIndex) javax.sql.JdbcRowSet.updateArray(String columnName) javax.sql.JdbcRowSet.updateRef(int columnIndex) javax.sql.JdbcRowSet.updateRef(int columnIndex) For additional information about deviations for some methods, see Deviations.
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. java.sql.DatabaseMetaData.getUDTs(String catalog, String schemaPattern, String tableNamePattern, int[] types) BASE_TYPE is added to the column data, but its value is set to NULL because SQL/MX does not support the base type. java.sql.DatabaseMetaData.getVersionColumns() Mimics the DatabaseMetaData.
java.sql.Statement.setFetchDirection(...) The fetch direction attribute is ignored. java.sql.Statement.setQueryTimeout(...) The query time-out value is ignored. The JDBC/MX driver does not abort execution when the query time-out period has expired. javax.sql.JdbcRowSet.setUsername(String username) User name and password arguments are ignored. Security privileges are the same as for the user who invoked the Java VM. javax.sql.JdbcRowSet.setPassword(String password) javax.sql.
An inserted row is added to the result set at the current cursor position. The method databaseMetaData.insertsAreDetected() returns true . Batch Updates The batch update facility allows a Statement object to submit a set of heterogeneous update, insert, or delete commands together as a single unit to the database. This facility also allows multiple sets of parameters to be associated with a PreparedStatement object.
SQL Conformance JDBC/MX conforms to the SQL language entry level of SQL:1999.
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 LOCATE (JDBC LOCATE start parameter is not supported) LOWER LOWER LPAD LPAD LTRIM LTRIM OCTET_LENGTH OCTET_LENGTH POSITION POSITION REPEAT REPEAT REPLACE REPLACE RIGHT RIGHT RTRIM TRIM...
CURTIME , CURRENT_TIME CURRENT_TIME DATEFORMAT DATEFORMAT DAY DAY DAYNAME DAYNAME DAYOFMONTH DAYOFMONTH DAYOFWEEK DAYOFWEEK DAYOFYEAR DAYOFYEAR EXTRACT EXTRACT HOUR HOUR JULIANTIMESTAMP JULIANTIMESTAMP MINUTE MINUTE MONTH MONTH MONTHNAME MONTHNAME QUARTER QUARTER SECOND SECOND WEEK WEEK YEAR YEAR 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
SQL/MX data types: Supported by JDBC/MX JDBC Data Type SQL/MX Data Type Types.Array No Types.BIGINT Yes Types.BINARY No Types.BIT No Types.BLOB Yes Types.CHAR Yes Types.CLOB Yes Types.DATE Yes DATE Types.DECIMAL Yes DECIMAL(p,s) Types.DISTINCT No Types.DOUBLE Yes DOUBLE PRECISION Types.FLOAT Yes FLOAT(p) Types.INTEGER Yes INTEGER Types.JAVA_OBJECT No Types.LONGVARBINARY No Types.LONGVARCHAR Yes** Types.NULL No Types.NUMERIC Yes NUMERIC(p,s) Types.
Types.VARCHAR Yes VARCHAR(n) * See Floating Point Support. ** 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.
see "Data Types" in the SQL/MX Reference Manual.
Home | Contents | Index | Glossary | Prev | Next 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 JDBC trace facility can be enabled in any of the following ways in which a JDBC connection to a database can be obtained: Tracing using the DriverManager Class Tracing using the DataSource Implementation Tracing using the java command Tracing using the system.
name is mapped to a trace data source object by means of a naming service that uses the Java Naming and Directory Interface (JNDI).
Set the system property before the program makes any JDBC API calls. Tracing by Loading the Trace Driver Within the Program Enable tracing by loading the JDBC trace driver within the program by using the Class.forName("com.tandem.jdbc.TDriver") method. This method also requires that you set the DriverManager.setLogWriter method. Tracing Using a Wrapper Data Source Enable tracing by creating a wrapper data source around the data source to be traced.
where n is an integer that specifies the tracing level. The value can be 0, 1, or 2. The default level is 0. Any value greater than 2 is treated like 2. The tracing levels are: Level Meaning 0 No tracing. 1 Traces connection and statement pooling information. 2 Traces the LOB-code path only. 3 Traces the entry point of all JDBC methods. Note: Only one traceFlag value can be in effect at a time.
return-object-id is the hashcode of the object returned by the traced method. Trace output is sent to the PrintWriter specified in the setLogWriter method. Example 1 jdbcTrace:[10/12/05 10:04:39] [Thread[main,5,main]]:[5256233]:com.tandem.sqlmx.SQLMXPreparedStatement.executeQuery() Example 2 Some traced methods will have two trace statements, one for the method entry point and the other for return object mapping.
To specify supplemental logging: 1. Set the enableLog property to on to enable logging. 2. Set the idMapFile property to specify the log file. By default, the log is written to the screen. For additional information about these properties, see enableLog Property and idMapFile Property. You can specify these properties either in the command line or in the program similar to setting tracing described earlier under Tracing Using the java Command and Tracing Using the system.setProperty Method.
Corresponding JDBC SQL Statements. 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. sql-statement is the actual SQL statement mapped to the statement ID.
Home | Contents | Index | Glossary | Prev | Next Messages JDBC/MX returns sqlcode and file-system error codes as error codes for the getErrorCode() method of SQLException.
29003 HY000 Statement does not exist Cause: A validation attempt was made on the getter or exec invocation on a closed statement. Effect: The getter or exec invocation validation fails. Recovery: Issue validateGetInvocation() or validateExecDirectInvocation when the statement is open. [back to the top] 29004 HY024 Invalid transaction isolation value Cause: An attempt was made to set the transaction isolation level to an invalid value. Effect: SQLMXConnection.
29009 HY109 Invalid cursor position Cause: An attempt was made to perform a deleteRow() method or updateRow() method or cancelRowUpdates method when the ResultSet row cursor was on the insert row. Or an attempt was made to perform the insertRow() method when the ResultSet row cursor was not on the insert row. Effect: The row changes and cursor manipulation do not succeed. Recovery: To insert a row, move the cursor to the insert row. To delete, cancel, or update a row, move the cursor from the insert row.
[back to the top] 29015 HY024 Invalid fetch direction Cause: The setFetchDirection() method direction parameter is set to an invalid value. Effect: The direction in which the rows in this ResultSet object are processed is not set. Recovery: Valid fetch directions are: ResultSet.FETCH_FORWARD , ResultSet.FETCH_REVERSE , and ResultSet.FETCH_UNKNOWN .
[back to the top] 29021 HY004 Object type not supported Cause: A prepared-statement setObject() method call contains an unsupported Object Type. Effect: The setObject() method does not set a value for the designated parameter. Recovery: Informational message only; no corrective action is needed. Valid Object Types are: null , BigDecimal , Date , Time , Timestamp , Double, Float , Long , Short , Byte , Boolean , String, byte[], Clob , and Blob .
Recovery: Disable AutoCommit . Use the method only when the AutoCommit mode is disabled. [back to the top] 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. Effect: The current AutoCommit mode is not modified. Recovery: Complete the transaction, then attempt to set the AutoCommit mode.
the NOT NULL clause in the column definition. [back to the top] 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. [back to the top] 29036 HY000 Unsupported encoding {0} Cause: The character encoding is not supported.
Effect: The ResultSet row information retrieval does not succeed. Recovery: To retrieve row information, move the ResultSet object cursor away from the insert row. [back to the top] 29041 HY000 Operation invalid. No primary key for the table Cause: The getKeyColumns() method failed on a table that was created without a primary-key column defined. Effect: No primary-key data is returned for the table. Recovery: Change the table to include a primary-key column.
[back to the top] 29047 HY000 Batch update failed. See next exception for details Cause: One of the commands in a batch update failed to execute properly. Effect: Not all the batch-update commands succeed. See the subsequent exception for more information. Recovery: View the subsequent exception for possible recovery actions. [back to the top] 29048 HY009 Invalid use of null Cause: A parameter that has an expected table name is set to null.
29053 HY000 SQL SELECT statement is invalid in executeUpdate() method Cause: A select SQL statement was used in the executeUpdate() method. Effect: The SQL query not performed exception is reported. Recovery: Use the executeQuery() method to issue the select SQL statement. [back to the top] 29054 HY000 Only SQL SELECT statements are valid in executeQuery() method Cause: A non-select SQL statement was used in the executeQuery() method. Effect: The exception reported is " SQL query not performed ".
Cause: Attempted to access a BLOB column without setting the property blobTableName , or the property is set to an invalid value. Effect: The application cannot access BLOB columns. Recovery: Set the blobTableName property to a valid LOB table name. The LOB table name is of format catalog.schema.lobTableName .
29065 22018 Java data type cannot be converted to the specified SQL data type Cause: A PreparedStatement setter method Java object conversion to the given SQL data type is invalid. Effect: An exception is thrown; data is not updated. Recovery: Make sure that the column data type is valid for the PreparedStatement setter method. [back to the top] 29066 22018 The String data {0} cannot be converted to a numeric value Cause: A PreparedStatement setter method could not convert a string to an integer.
29071 HY000 Internal programming error - {0} Cause: The JNI layer (get Object method) always returns a byte array and, therefore, any other instance is considered a programming error. Effect: An exception is reported. Recovery: None. The error is internal to the JDBC/MX driver. [back to the top] 29072 HY000 Attempting to exceed the maximum connection pool size ({0,number,integer}) Cause: An attempt was made to obtain a connection outside the set connection pool size limit.
Effect: An SQL warning condition. Statements continue to be added to the internal cache. Recovery: An SQL warning condition. Use the maxStatements property (or -Djdbcmx.maxStatements command-line property) to increase the number of statements allowed. [back to the top] 29077 HY000 HY000 Max rows value cannot be less than the fetch size Cause: The row value passed to the JdbcRowSet.
Recovery: Call the setMatchColumn() method with a valid non-null column-name string. [back to the top] 29083 HY000 Columns being unset are not the same as those set Cause: The designated column passed to the unsetMatchColumn() method was not previously set as a match column. Effect: The designated column is not unset for this JdbcRowSet object. Recovery: Use the setMatchColumn() method to set the designated column as a match column.
[back to the top] 29089 HY000 JdbcRowSet connect error - {0} {1} Cause: The driver encountered an internal error when attempting to establish a connection. Effect: An exception is reported. Recovery: None. The error is internal to the JDBC/MX driver.
29255 HY000 TMF error has occurred : [tmf-error] Cause: An internal transaction request failed. Effect: An exception is reported; the operation is not completed. Recovery: Refer to the TMF error message tmf-error. [back to the top] 29256 HY000 Error while obtaining the system catalog name : [error] Cause: During initialization of the JDBC driver, an error occurred when attempting to determine a system catalog name. Effect: The JDBC driver is not registered with the Driver Manager.
29261 HY000 No error message in SQL/MX diagnostics area, but sqlcode is non-zero Cause: An SQL error was detected but no error message was reported by SQL/MX. Effect: An SQL exception or warning is thrown without a diagnostic message. Recovery: Unknown. [back to the top] 29262 HY090 Invalid or null sql string Cause: A stored-procedure or prepared-statement call contains an invalid SQL string. Effect: The stored procedure or prepared statement is not executed.
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.
Home | Contents | Index | Glossary | Prev | Next Appendix 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.
java.io.IOException { int int long long Connection length = 500; recKey; start; end; conn1 = null; // Set jdbcmx.clobTableName System Property. This property // can also be added to the command line through // "-Djdbcmx.clobTableName=...", or a // java.util.Properties object can be used and passed to // getConnection. System.setProperty( "jdbcmx.clobTableName","cat.sch.clobdatatbl" ); if (args.length < 2) { System.out.
// insert a second base table row with an empty LOB column System.out.println("CLOB interface EMPTY LOB insert..."); String stmtSource2 = "insert into clobbase values (?,EMPTY_CLOB())"; PreparedStatement stmt2 = conn1.prepareStatement(stmtSource2); stmt2.setInt(1,recKey+1); stmt2.executeUpdate(); Clob clob = null; // System.out.println("Obtaining CLOB data to update (EMPTY in this case)..."); PreparedStatement stmt3 = conn1.prepareStatement("select col2 from clobbase where col1 = ? for update"); stmt3.
// - need an Inputstream object that already has data // - need a PreparedStatement object that contains the // 'insert...' DML of the base table // - ps.setAsciiStream() for the lob data // - ps.executeupdate(); for the DML // // To run this example, issue the following: // # java TestBLOB 1 TestBLOB.class 1000 // import java.sql.*; import java.io.*; public class TestBLOB { public static void main (String[] args) throws java.io.FileNotFoundException, java.io.
stmt1.executeUpdate(); conn1.commit(); } catch (SQLException e) { e.printStackTrace(); SQLException next = e; do { System.out.println("Messge : " + e.getMessage()); System.out.println("Error Code : " + e.getErrorCode()); System.out.println("SQLState : " + e.getSQLState()); } while ((next = next.getNextException()) != null); } // Blob interface example - This technique is suitable when // the LOB data is already in the app, such as having been // transfered in a msgbuf.
Home | Contents | Index | Prev | Next Glossary ABCDEFGHIJLMNOPQRSTUVWXYZ 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.
base table A table that has physical existence: that is, a table stored in a file. BLOB Short for Binary Large OBject, a collection of binary data stored as a single entity in a database management system. These entities are primarily used to hold multimedia objects such as images, videos, and sound. They can also be used to store programs or even fragments of code. A Java Blob object (Java type, java.sql.Blob ) corresponds to the SQL BLOB data type.
dynamically locking and unlocking affected records to ensure that only one transaction at a time accesses those records. connection pooling A framework for pooling JDBC connections. Core Packages The required set of APIs in a Java platform edition which must be supported in any and all compatible implementations. D Data Control Language (DCL) The set of data control statements within the SQL/MP language. Data Manipulation Language (DML) The set of data-manipulation statements within the SQL/MP language.
fault tolerance The ability of a computer system to continue processing during and after a single fault (the failure of a system component) without the loss of data or function. G get() method A method used to read a data item. For example, the SQLMPConnection.getAutoCommit() method returns the transaction mode of the JDBC driver's connection to an SQL/MP or SQL/MX database. Compare to set() method. Guardian An environment available for interactive and programmatic use with the NonStop operating system.
hyperlink A reference (link) from a point in one hypertext document to a point in another document or another point in the same document. A browser usually displays a hyperlink in a different color, font, or style. When the user activates the link (usually by clicking on it with the mouse), the browser displays the target of the link.
vendor. HP documents often use the term connectivity in this context, while other vendors use connectivity to mean hardware compatibility. Within a NonStop system node, the ability to use the features or facilities of one environment from another. For example, the gtacl command in the OSS environment allows an interactive user to start and use a Guardian tool in the Guardian environment. interpreter The component of the Java VM that interprets bytecode into native machine code.
Java virtual machine (JVM) The process that loads, links, verifies, and interprets Java bytecode. The NonStop Server for Java 5 implements the Java HotSpot virtual machine. JDBC See Java Database Connectivity (JDBC). JDBC API The programmatic API in Java to access relational databases. JDBC Trace Facility A utility designed to trace the entry point of all the JDBC methods called from the Java applications. JDBC/MP See HP JDBC Driver for SQL/MP (JDBC/MP). JDBC/MX See HP JDBC Driver for SQL/MX (JDBC/MX).
In the context of Java programming, something written in a language other than Java (such as C or C++) for a specific platform. node One of the following: An addressable device attached to a computer network. A hypertext document. NonStop Server for Java 5 The informal name of the NonStop Server for Java, based on the Java 2 Platform Standard Edition 5.0 products. This product is a Java environment that supports compact, concurrent, dynamic, and portable programs for the enterprise server.
POSIX See Portable Operating System Interface X (POSIX). protocol A set of formal rules for transmitting data, especially across a network. Low-level protocols define electrical and physical standards, bit-ordering, byte-ordering, and the transmission, error detection, and error correction of the bit stream. High-level protocols define data formatting, including the syntax of messages, the terminal-tocomputer dialogue, character sets, sequencing of messages, and so on.
SQL context An instantiation of the SQL executor with its own environment. SQLJ Also referred to as SQLJ Part 0, the "Database Language SQL—Part 10: Object Language Bindings (SQL/OLB)" part of the ANSI SQL-2002 standard that allows static SQL statements to be embedded directly in a Java program. SQL/MP See HP NonStop SQL/MP. SQL/MX See HP NonStop SQL/MX. statement pooling A framework for pooling PreparedStatement objects.
A user-defined action that a client program (usually running on a workstation) requests from a server. Transaction Management Facility (TMF) A set of HP software products for NonStop systems that assures database integrity by preventing incomplete updates to a database. It can continuously save the changes that are made to a database (in real time) and back out these changes when necessary. It can also take online "snapshot" backups of the database and restore the database from these backups.
Home | Contents | Index | Prev | Next HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.1 (663859-001) © 2011 Hewlett-Packard Development Company L.P. All rights reserved.
Home | Contents | Glossary | Prev Index ABCDEFGHIJKLMNOPQRSTUVWXYZ A Abbreviations used in this document Accessing NonStop SQL databases with JDBC/MX driver Admin Utility See JDBC/MX Lob Admin Utility ANSI API packages Application servers connection pooling tracing Architecture JDBC/MX driver LOB support ASCII data inserting by using the PreparedStatement interface reading from a CLOB column writing by using the Clob interface Audience for this document Autocommit m
using Blob objects access considerations replacing blobTableName property Blocking Java VM process See Nonblocking JDBC/MX C Catalog, default Character set encodings ISO88591 property CLASSPATH environment variable CLOB (Character Large Object) accessing, sample program creating tables for data type deleting data limitations managing tables for reading data storing data support architecture updating data working with Clob inter
CreateDataSource sample program enabling tracing dataSourceName property Data types for LOB columns limitations of CLOB and BLOB support of Default catalog and schema Deleting BLOB data CLOB data Demonstration programs of JDBC trace facility summary Deviations from JDBC in JDBC/MX 3.
G method See Connecting programs to databases getConnection H Help listing, JDBC/MX Lob Admin Utility Holdable cursors JDBC/MX support sample program HP extensions, JDBC 3.
running table_name JDBC Trace Facility demonstration programs output format for application servers by loading the trace driver within the program tracing using a wrapper data source tracing using the DataSource implementation tracing using the DriverManager class tracing using the java command tracing using the system.setProperty method jdbcmx.
minPoolSize property mploc property Module File Caching Benefits Known Issues Troubleshooting Multibyte character set (MBCS) data character set encodings inserting by using the PreparedStatement interface reading from a CLOB column writing by using the Clob interface supported character sets Multithreaded Java application sample program MXCI, using N Nonblocking JDBC/MX NonStop SQL/MX documents Notation conventions NULL value O Objects See SQL object
R _RLD_LIB_PATH environment variable Reader Blob and Clob access considerations reading Unicode data from a CLOB column Reading binary data CLOB data Related reading JDBC/MX Driver for NonStop SQL/MX API Reference NonStop system computing documents Oracle documents Replacing Blob objects Clob objects reserveDataLocators property Result sets in holdable cursors ResultSet processing, controlling performance of S Sample programs accessing BLOB data access
creating Guardian location managing for LOB support specifying to JDBC/MX Lob Admin Utility Tandem floating point Threads, blocking TNS floating point traceFile property traceFlag property Tracing See JDBC Trace Facility transactionMode property Transactions autocommit mode and transaction boundaries Blob and Clob access disabling autocommit mode modes support of Triggers creating dropping example creating using Troubleshooting c
Writing See also Storing ASCII or Unicode data to CLOB columns ABCDEFGHIJKLMNOPQRSTUVWXYZ Home | Contents | Glossary | Prev HP NonStop JDBC Type 2 Driver Programmer's Reference for SQL/MX Release 3.1 (663859-001) © 2011 Hewlett-Packard Development Company L.P. All rights reserved.
Home | Contents | Index | Glossary | Prev | Next List of Examples Section Title Example Title(s) Installing and Verifying JDBC/MX Verifying the JDBC/MX Driver Working with BLOB and CLOB Data Inserting CLOB Columns by Using the Clob Interface Writing ASCII or Unicode Data to a CLOB Column Inserting CLOB Data by Using the PreparedStatement Interface Reading ASCII Data from a CLOB Column Reading Unicode Data from a CLOB Column Inserting a BLOB Column by Using the Blob Interface Writing Binary Data to a B
Home | Contents | Index | Glossary | Prev | Next List of Figures Section Title Figure Title(s) Introduction to JDBC/MX Architecture of the JDBC/MX Driver Working with BLOB and CLOB Data LOB Architecture: Tables for LOB Data Support HP JDBC Driver for SQL/MX Programmer's Reference for H50 (640330-002) © 2011 Hewlett-Packard Development Company L.P. All rights reserved.
Home | Contents | Index | Glossary | Prev List of Tables Section Title Table Title(s) About This Manual Document Sections Introduction to JDBC/MX Driver Sample Programs Summary Accessing SQL Databases with SQL/MX JDBC/MX Driver Properties Used with the DriverManager Class DataSource Object Properties JDBC/MX Driver Properties Allowed in the Command Line Connection Attributes Passed to SQL/MX Context Standard ConnectionPoolDataSource Object Properties Corresponding SQL/MX Character Sets and Java Enco
