HP NonStop SQL/MX Programming Manual for Java Abstract This manual explains how to use embedded SQL in Java (SQLJ) programs in HP NonStop™ SQL/MX. In SQL/MX, an SQLJ program uses embedded SQL/MX statements to access HP NonStop SQL/MP and SQL/MX databases. Note. HP NonStop SQL/MX SQLJ (product T1232) has reached MATURE support status, meaning that requests for enhancements (RFEs) are not considered and that only critical defects are repaired.
Document History Part Number Product Version Published 425979-001 NonStop SQL/MX Release 1.8 December 2002 425979-002 NonStop SQL/MX Release 1.8 February 2003 523726-001 NonStop SQL/MX Release 2.0 April 2004 523726-002 NonStop SQL/MX Release 2.0 August 2004 523726-003 NonStop SQL/MX Releases 2.0 and 2.
HP NonStop SQL/MX Programming Manual for Java Index Examples What’s New in This Manual xiii Manual Information xiii New and Changed Information Figures Tables xiii About This Manual xv Audience xv Organization xv Examples in This Manual xvi Related Documentation xvii Notation Conventions xxi 1.
2. Getting Started Contents 2. Getting Started Software Requirements 2-1 SQLJ Product File 2-1 NonStop Server for Java 2-2 JDBC/MX Driver 2-2 Verifying Software Versions 2-2 Verifying the SQLJ Product 2-2 Verifying the NonStop Server for Java 2-3 Verifying the JDBC/MX Driver 2-3 Configuring the Java Environment 2-4 Setting the JREHOME Environment Variable Setting the Class Path 2-4 Establishing Java Security 2-6 2-4 3.
3. SQLJ Programming (continued) Contents 3.
Contents 4. SQL/MX Programming Considerations (continued) 4.
4. SQL/MX Programming Considerations (continued) Contents 4.
Contents 5. Processing SQLJ Programs (continued) 5.
6. Program and Module Management Contents 6.
B. SQLJ Runtime Package (continued) Contents B. SQLJ Runtime Package (continued) sqlj.runtime.ExecutionContext Class B-12 ExecutionContext Constructor Details B-13 ExecutionContext Method Details B-13 sqlj.runtime.CharacterStream Class B-16 CharacterStream Constructor Details B-16 sqlj.runtime.ref.DefaultContext Class B-18 DefaultContext Method Details B-18 C. Sample Programs Using SQLJ Source Files C-1 Installing the sampleSQLJ.jar File C-1 Installing the SQL/MX Sample Database C-1 SampleDDL.
C. Sample Programs (continued) Contents C. Sample Programs (continued) StreamTimeout.sqlj—Stream Timeout Setting C-22 SQLJ Source File C-22 Translating the Program C-24 Running the Program C-24 Results of Running the Program C-24 EmbeddedDelete.sqlj—Embedded DELETE C-25 SQLJ Source File C-25 Translating the Program C-26 Running the Program C-26 Results of Running the Program C-27 EmbeddedUpdate.
Examples Contents Examples Example 4-1. Example C-1. Example C-2. Example C-3. Example C-4. Example C-5. Example C-6. Example C-7. Example C-8. Example C-9. Coding Transaction Control Statements in an SQLJ Program 4-42 SampleDDL.sqlj—Creating Tables From an SQLJ Program C-2 SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data C-9 MultiThread1.sqlj—Threads With Explicit Execution Contexts C-13 MultiThread2.sqlj—Threads With Different Connection Contexts C-16 StreamParcels.
Tables (continued) Contents Tables (continued) Table B-3. Table B-4. Table B-5. Table B-6. Table B-7. Table B-8. Table B-9.
Contents HP NonStop SQL/MX Programming Manual for Java—523726-003 xii
What’s New in This Manual Manual Information HP NonStop SQL/MX Programming Manual for Java Abstract This manual explains how to use embedded SQL in Java (SQLJ) programs in HP NonStop™ SQL/MX. In SQL/MX, an SQLJ program uses embedded SQL/MX statements to access HP NonStop SQL/MP and SQL/MX databases. Note. HP NonStop SQL/MX SQLJ (product T1232) has reached MATURE support status, meaning that requests for enhancements (RFEs) are not considered and that only critical defects are repaired.
New and Changed Information What’s New in This Manual Section New or Changed Information Section 4, SQL/MX Programming Considerations Updated the MODULE Directive on page 4-12. Added a consideration for SQL/MP floating-point columns to the Similarity Check Criteria on page 4-28. Explained the effect of the jdbcmx.transactionMode property on transactions under Transaction Management on page 4-41.
About This Manual This manual explains how to develop Java applications that contain embedded SQL statements, otherwise known as SQLJ programs, for the NonStop SQL/MX environment. SQLJ is defined in Part 10 of the ANSI/ISO/IEC 9075:2003 SQL standard (SQL:2003), otherwise known as SQLJ Part 0. Use SQLJ to embed static SQL statements in a Java program that accesses a database. In SQL/MX, an SQLJ program uses embedded SQL/MX statements to access SQL/MP and SQL/MX databases. Note.
Examples in This Manual About This Manual Section 4, SQL/MX Programming Considerations Describes advanced programming constructs, such as static and dynamic SQL, and the behavior of various SQL statements in an SQLJ program. Section 5, Processing SQLJ Programs Describes how to prepare an SQLJ program for execution. Section 6, Program and Module Management Describes how to manage the files of an SQLJ program and discusses the targeting, versioning, and grouping techniques of module management.
Related Documentation About This Manual Related Documentation This manual is part of the HP NonStop SQL/MX library of manuals, which includes: Introductory Guides SQL/MX Comparison Guide for SQL/MP Users Describes SQL differences between SQL/MP and SQL/MX. SQL/MX Quick Start Describes basic techniques for using SQL in the SQL/MX conversational interface (MXCI). Includes information about installing the sample database.
Related Documentation About This Manual SQL/MX Report Writer Guide Describes how to produce formatted reports using data from a NonStop SQL/MX database. SQL/MX Connectivity Service Manual Describes how to install and manage the SQL/MX Connectivity Service (MXCS), which enables applications developed for the Microsoft Open Database Connectivity (ODBC) application programming interface (API) and other connectivity APIs to use SQL/MX.
Related Documentation About This Manual This documentation is helpful for understanding the concepts and terminology of this manual: Other Related Documentation NonStop Server for Java Programmer’s Reference Describes the HP NonStop Server for Java, a Java environment that supports programs for the enterprise server.
Related Documentation About This Manual This figure shows the manuals in the SQL/MX library: Programming Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start SQL/MX Programming Manual for C and COBOL SQL/MX Programming Manual for Java Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual SQL/MX Glossary SQL/MX Connectivity Service Administrative Command Reference DataLoader/MX Reference Manual Specialized Guides SQL/MX Installation and Management G
Notation Conventions About This Manual Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2. General Syntax Notation The following list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
General Syntax Notation About This Manual each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. For example: FC [ num ] [ -num ] [ text ] K [ X | D ] address { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list may be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
Notation for Messages About This Manual Line Spacing. If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections. For example: ALTER [ / OUT file-spec / ] LINE [ , attribute-spec ]… !i and !o.
Notation for Messages About This Manual Nonitalic text. Nonitalic letters, numbers, and punctuation indicate text that is displayed or returned exactly as shown. For example: Backup Up. lowercase italic letters. Lowercase italic letters indicate variable items whose values are displayed or returned. For example: p-register process-name [ ] Brackets. Brackets enclose items that are sometimes, but not always, displayed.
Notation for Management Programming Interfaces About This Manual Notation for Management Programming Interfaces The following list summarizes the notation conventions used in the boxed descriptions of programmatic commands, event messages, and error lists in this manual. UPPERCASE LETTERS. Uppercase letters indicate names from definition files; enter these names exactly as shown. For example: ZCOM-TKN-SUBJ-SERV lowercase letters.
About This Manual Notation for Management Programming Interfaces HP NonStop SQL/MX Programming Manual for Java—523726-003 xxvi
1 Introduction This manual explains how to develop Java applications that contain embedded SQL statements, otherwise known as SQLJ programs, for the NonStop SQL/MX environment. Note. HP NonStop SQL/MX SQLJ (product T1232) has reached MATURE support status, meaning that requests for enhancements (RFEs) are not considered and that only critical defects are repaired. To develop Java applications that access NonStop SQL/MP and SQL/MX databases, use JDBC instead of SQLJ.
SQLJ Compared With Embedded SQL in C or COBOL Introduction • • • • Strong data typing of queries Online checking of syntactic and semantic correctness of SQL statements at development time rather than at run time Portability of binary code across different vendors’ databases Customization of an SQLJ program for optimal performance on an SQL/MX database SQLJ Compared With Embedded SQL in C or COBOL SQLJ offers the following benefits as compared with embedded SQL statements in a C or COBOL program.
SQLJ Compared With Embedded SQL in C or COBOL Introduction incompatible types in a C or COBOL program but are compatible types in an SQLJ program because Java defines those type conventions. Java handles a greater variety of conversions between SQL column data types and host variables than does C or COBOL. For example, SQLJ converts a fetched string value to a host variable typed as integer, as long as the string represents a valid integer.
SQLJ Compared With JDBC Introduction SQLJ Compared With JDBC SQLJ offers the following benefits as compared with JDBC programs. Statically Compiled SQL SQLJ programs typically use embedded SQL statements in SQLJ clauses for static SQL and JDBC methods for dynamic SQL. SQLJ is intended to yield the performance benefits of statically compiled SQL. Static SQL statements do not incur the cost of compilation at execution time because you compile them during development.
SQLJ and JDBC Interoperability Introduction Development-Time Validation The SQLJ translator provides online checking of the syntactic and semantic correctness at the time of development, thus enabling you to validate the SQL statements in an SQLJ program based on an actual database.
SQLJ Versus JDBC Performance Introduction SQLJ Versus JDBC Performance In addition to the advantages that SQLJ offers, as described in Benefits of SQLJ on page 1-1, you should consider performance when choosing between SQLJ or JDBC. A choice of SQLJ versus JDBC for a Java program depends on the importance of startup versus steady-state performance, how the application is written, and the functionality of the application. Be aware that you can use both SQLJ and JDBC in the same program.
Steady-State Performance Introduction Steady-State Performance Steady-state performance is based on how long it takes a long-running server to complete its operation after it starts and reaches a steady-state mode. Steady-State Performance of JDBC/MX Programs A well-written JDBC program compiles each of its SQL statements only once for each process instance (or thread). After the statements are compiled for that process instance (or thread), the performance penalty disappears.
Introduction Steps of Developing and Executing an SQLJ Program Steps of Developing and Executing an SQLJ Program The steps of developing and executing an SQLJ program for the NonStop SQL/MX environment include: 1. Configuring the system environment for developing and executing SQLJ programs. See Section 2, Getting Started. 2. Writing the SQLJ source file (the .sqlj file). See Section 3, SQLJ Programming and Section 4, SQL/MX Programming Considerations. 3. Using the SQLJ translator program, sqlj.tools.
Processing an SQLJ Program Introduction Processing an SQLJ Program There are two methods of processing an SQLJ program: • • Default Processing Without Module Definition Files on page 1-9 Processing With Module Definition Files on page 1-17 The first method is the default method of SQLJ processing in SQL/MX Release 2.x and later product versions. The second method is the only method of SQLJ processing in SQL/MX Release 1.8 and can be activated in SQL/MX Release 2.
Default Processing Without Module Definition Files Introduction Figure 1-1. SQLJ Translation and Customization Without Module Definition Files 1 SQLJ Source File MyProg.sqlj 2 SQLJ Translator Extended Profile with Embedded Module Definition Java Source File Profile MyProg.java MyProg_SJProfile0.ser MyProg_SJProfile0.ser 3 Keys File Java Compiler MyProg_SJProfile Keys.class 4 SQLJ Customizer Iterator Class MyProg$Iter. class Connection Context Class SQLMXCtx.
Default Processing Without Module Definition Files Introduction 1 Creation of the SQLJ Source File Write the source code of an SQLJ program in an SQLJ source file, which must have the .sqlj extension. See Section 3, SQLJ Programming and Section 4, SQL/MX Programming Considerations. 2 Translation The processing of an SQLJ source file starts when you invoke the SQLJ translator program from the command line. The SQLJ translator program, sqlj.tools.
Default Processing Without Module Definition Files Introduction Profile The profile (for example, MyProg_SJProfile0.ser file) is a serialized Java object that includes information about the SQL-specific parts of the translated SQLJ program. The profile contains the SQL/MX statements from the SQLJ source file, information about how to execute these statements, and host variable attributes.
Default Processing Without Module Definition Files Introduction 4 Customization Without Module Definition Files After successful translation and Java compilation, the SQLJ translator program automatically invokes customization of the profiles (.ser) for SQL/MX. The process of customization adds vendor-specific optimizations to each profile.
Introduction Default Processing Without Module Definition Files (.ser file) in an SQL/MX-specific section, keeping the original, default customization separate as required by the SQLJ standard. 5 Compilation by mxCompileUserModule After translating, customizing, and optionally packaging an SQLJ program, use the mxCompileUserModule utility to produce the compiled SQL/MX module or modules for the application. (To package an SQLJ program in a JAR file, see Packaging the Program in a JAR File on page 6-5.
Default Processing Without Module Definition Files Introduction Figure 1-2. SQL Compilation of an Embedded Module Definition Extended Profile with Embedded Module Definition MyProg_SJProfile0.ser SQLJ Application with Extended Profiles or MyApp.jar 5 mxCompileUserModule Temporary File SQL/MX Compiler SQL/MX Compiler Module CAT.SCH.GRP1^MOD1^ TABLESET1^VER1 Modules or CAT.SCH.GRP1^MOD1^ TABLESET1^VER1 /usr/tandem/sqlmx/USERMODULES directory VST007.
Default Processing Without Module Definition Files Introduction SQL Compilation The SQL/MX compiler (MXCMP) performs these specific functions during SQL compilation: • • • • • • Checks database object references to verify their existence Expands view definitions (that is, replaces a view name with a SELECT statement) Determines an optimized execution plan and access path for each DML statement if the database objects in the statement are present at SQL compile time Generates executable code for the exec
Processing With Module Definition Files Introduction Processing With Module Definition Files This method of SQLJ processing, which can be activated in SQL/MX Release 2.x by using the -createMDF option on the command line or in a properties file, generates module definition files (.m files) in addition to extended profiles. The SQLJ translator program, sqlj.tools.
Processing With Module Definition Files Introduction Figure 1-3. SQLJ Translation and Customization With Module Definition Files 1 SQLJ Source File MyProg.sqlj 2 SQLJ Translator Java Source File Profile Extended Profile MyProg.java MyProg_SJProfile0.ser MyProg_SJProfile0.ser 3 Keys File Java Compiler MyProg_SJProfile Keys.class 4 SQLJ Customizer Iterator Class Module Definition File MyProg$Iter. class MyProg_SJProfile0.m Connection Context Class 5 SQL/MX Compiler SQLMXCtx.
Processing With Module Definition Files Introduction 4 Customization With Module Definition Files After successful translation and Java compilation, the SQLJ translator program automatically invokes customization of the profiles (.ser) for SQL/MX. Specifying -createMDF=true (or -SQLMXCustomizerVersion=800) on the SQLJ command line or in a properties file causes the SQLJ customizer to produce a module definition file (.m) in addition to storing the embedded module definition in the extended profile.
Executing an SQLJ Program Introduction Executing an SQLJ Program When you execute an SQLJ program, the program class file (for example, MyProg.class) runs like any Java program except that it calls the SQLJ run time to execute the embedded SQL/MX statements. The SQLJ run time connects to an SQL/MX database by means of a JDBC connection.
Dynamic SQLJ Execution Introduction Dynamic SQLJ Execution If the SQLJ run time fails to load an SQL statement from the module, it calls JDBC to prepare and execute the statement dynamically. This process is called the default run time. Figure 1-5 shows the dynamic SQLJ execution environment. Figure 1-5. Dynamic SQLJ Execution Environment JAR File of the SQLJ Application MyApp.jar Keys File Connection Context Class Iterator Class SQLMXCtx.class MyProg$Iter.
Execution Scenarios Introduction Execution Scenarios After translating and customizing an SQLJ program, you have several options for executing it. Because SQLJ programs are Java programs, you have the same options for executing them as you do for other NonStop Java applications. Stand-Alone Execution You can run an SQLJ program from the OSS command line.
Execution Scenarios Introduction Web-Based Execution You can use an SQLJ program as a NonStop Servelet for JavaServer Pages (NSJSP). The servelets are platform-independent server-side programs that programmatically extend the functionality of Web-based applications. They provide dynamic content from a WebServer to a client browser over the HTTP protocol. For more information, see the iTP Secure WebServer System Administrator’s Guide.
Execution Scenarios Introduction HP NonStop SQL/MX Programming Manual for Java—523726-003 1- 24
2 Getting Started Before you can develop and execute SQLJ programs in SQL/MX: • • • Verify that your system meets software requirements. Install necessary software products. Configure the Java environment for SQLJ programs.
NonStop Server for Java Getting Started NonStop Server for Java To develop and execute SQLJ programs in SQL/MX, you must install the NonStop Server for Java on a NonStop system. Use the NonStop Server for Java to compile and execute Java applications. For details about this product, see the NonStop Server for Java Programmer’s Reference. For installation instructions, see the README file on the product CD.
Verifying the NonStop Server for Java Getting Started Verifying the NonStop Server for Java To display the product version of the NonStop Server for Java in the Java environment of a NonStop system, enter this vproc command at an OSS prompt: vproc /usr/tandem/java/bin/java If you have set the JREHOME environment variable for a nonstandard location of the NonStop Server for Java, enter: vproc $JREHOME/..
Configuring the Java Environment Getting Started The command displays output similar to: HP JDBC driver for NonStop(TM) SQL/MX Version T1225V31_08APR2004_JDBCMX_20040326 The JDBCMX information indicates the product version of the JDBC/MX driver that is installed on the system in the specified location. Compare the output of the command with the product version in the README or SOFTDOC file of the JDBC/MX driver.
Setting the Class Path Getting Started Setting the CLASSPATH Environment Variable Use the CLASSPATH environment variable to set the class path for a session during which you process or execute SQLJ programs. The setting of the CLASSPATH environment variable persists for the current session. To set the CLASSPATH environment variable in the OSS environment, enter this command at an OSS prompt: export CLASSPATH=["][$CLASSPATH:]path1[{:path2}...
Establishing Java Security Getting Started For more information about the java -cp option, see the NonStop Server for Java Tools Reference Pages. Establishing Java Security Java has built-in security features, such as the Java security manager, that protect against unauthorized use of or access to the system. With Java security enabled, the Java security manager protects the Java environment by restricting access to system resources.
Establishing Java Security Getting Started At run time, you must also have these required permissions for the directory that contains the SQLJ program: grant codeBase "file:path-to-my-SQLJ-program" { permission java.security.AllPermission; }; For example, if an SQLJ program exists in the /usr/mydir/production directory, the policy file must have these permissions: grant codeBase "file:/usr/mydir/production" { permission java.security.
Getting Started Establishing Java Security HP NonStop SQL/MX Programming Manual for Java—523726-003 2 -8
3 SQLJ Programming This section explains how to write an SQLJ program and covers basic SQLJ programming constructs: • • • • • • • • • • • Basic Elements of an SQLJ Program on page 3-1 Code Initialization on page 3-4 Connection Contexts on page 3-8 Execution Contexts on page 3-20 Multithreading on page 3-23 Host Variables and Expressions on page 3-27 Data Type Support on page 3-32 Character String Literals on page 3-45 Iterators and Result Sets on page 3-48 Calling Stored Procedures in Java on page 3-59
SQLJ Clause SQLJ Programming Declaration Clauses An SQLJ declaration clause can be a connection declaration clause or an iterator declaration clause. An SQLJ declaration clause causes the SQLJ translator to generate a Java class, and the scope and visibility of the generated Java class depend on the placement of the SQLJ declaration clause.
Comments SQLJ Programming example, SELECT, FROM, WHERE, table and view names, and so on) are caseinsensitive. For the syntax of an executable clause, see Executable Clause on page A-4. For a list of SQL statements that are allowed in SQLJ executable clauses, see Supported SQL Statements on page A-8. Example This DELETE statement is coded as an SQLJ executable clause: #sql {DELETE FROM employee}; Comments You can include SQL and Java comments in an SQLJ program.
Naming Restrictions and Reserved Words SQLJ Programming Naming Restrictions and Reserved Words The SQLJ translator generates variables that use this six-character prefix: __sJT_. Do not use this prefix for any fields, variables, or parameters in an SQLJ source file. The SQLJ translator generates internal classes and files that use this prefix: filename_SJ, where filename is the name of the SQLJ source file. Do not use this prefix for the class or file names.
Importing Java Packages SQLJ Programming The SQLJ translator program generates class files and other program files in this directory structure: /usr/pkg/subpkg/MyProg.class Importing Java Packages All classes, including the class files of an SQLJ program, belong to a package. When a program refers explicitly to a visible class outside the package of the program, you must do one of the following: • • • Use the fully qualified name of the class. Import the class. Import the entire package.
Registering and Loading the JDBC/MX Driver SQLJ Programming sqlj.runtime.ExecutionContext). To import this package, code this import statement at the beginning of an SQLJ source file: import sqlj.runtime.*; For more information about the sqlj.runtime package, see Appendix B, SQLJ Runtime Package. Registering and Loading the JDBC/MX Driver The JDBC Driver for SQL/MX (JDBC/MX) enables a Java program to use SQL/MX to access an SQL/MP or SQL/MX database.
SQLJ Programming Registering and Loading the JDBC/MX Driver Explicitly Loading the JDBC/MX Driver You must explicitly load the JDBC/MX driver for these types of connection context constructors: • Connection context constructor that accepts a JDBC connection object: CtxClassName(java.sql.Connection conn) • URL forms of the connection context constructor: CtxClassName(String url, java.util.
Connection Contexts SQLJ Programming Connection Contexts A connection context associates the execution of each embedded SQL statement with a particular JDBC connection to the database. Each SQL statement in an SQLJ executable clause is implicitly or explicitly associated with a connection context. For a particular SQLJ program, all SQL statements that are associated with the same connection context class, including an implicit connection context, are included in the same profile. See Profile on page 1-12.
Explicit Connection Contexts SQLJ Programming Declaring a Connection Context Class Declare the connection context class by specifying a connection declaration clause. For the syntax, see the Connection Declaration Clause on page A-1. Place the connection declaration clause where an SQLJ declaration clause is allowed in an SQLJ program. The connection declaration clauses usually appear at the beginning (or the top-level scope) of an SQLJ source file.
Explicit Connection Contexts SQLJ Programming There are several ways of instantiating the connection context object. See Connection Context Constructors and Methods on page 3-10. Connection Context Constructors and Methods The connection context class includes several constructors for instantiating the connection context object: Constructor Description public CtxClassName(java.sql.Connection conn) throws java.sql.
Explicit Connection Contexts SQLJ Programming Constructor Description public CtxClassName(String url, java.util.Properties info, boolean autoCommit) throws java.sql.SQLException Constructs a connection context object based on a JDBC URL and sets a Properties object and an autocommit setting for the connection. See Using JDBC URL Constructors on page 3-14. public CtxClassName(String url, boolean autoCommit) throws java.sql.
Explicit Connection Contexts SQLJ Programming returns null, the program should load the JDBC/MX driver, create a JDBC connection, and pass the JDBC connection object to the constructor of an explicit connection context, as this example shows: ctx = SQLMXCtx.getDefaultContext(); if (ctx == null) { String driver = "jdbc-driver"; Class.forName(driver); Connection jdbc = DriverManager.
Explicit Connection Contexts SQLJ Programming Set the default connection context to the connection supplied for ctx by passing the connection context ctx to the setDefaultContext() method: SQLMXCtx.setDefaultContext(ctx); Intantiate another connection context ctx1 to use the nondefault connection by invoking the getDefaultContext() method: SQLMXCtx ctx1 = SQLMXCtx.getDefaultContext(); Both connection contexts ctx and ctx1 share the same JDBC connection and underlying database session.
Explicit Connection Contexts SQLJ Programming Using JDBC URL Constructors The URL forms of the connection context constructor accept a JDBC URL and other properties for the connection: • • • • • url is a JDBC URL. The JDBC URL for the JDBC/MX driver is jdbc:sqlmx:. user is the name of a user. password is a string that represents a password. autoCommit is a Boolean value that turns the autocommit setting on or off. info is the name of a Properties object.
Explicit Connection Contexts SQLJ Programming For other types of connection context constructors and methods that you can use to instantiate a connection context object, see Connection Context Constructors and Methods on page 3-10.
Explicit Connection Contexts SQLJ Programming WHERE partnum = :partNum}; #sql [ctx1] {DELETE FROM samdbcat.invent.partloc WHERE partnum = :partNum}; ctx1.close(); #sql [ctx2] {DELETE FROM samdbcat.sales.parts WHERE partnum = :partNum}; ctx2.close(); } catch(Exception e) { System.err.println("Exception: " + e); } } } In the previous example, the ctx1.close() and ctx2.close() methods close all resources used by the connection contexts, ctx1 and ctx2.
Explicit Connection Contexts SQLJ Programming Generating a Profile for Each Connection Context Class All SQL statements that are associated with the same connection context class are included in the same profile. Because separate profiles exist for each connection context class, you can use explicit connection contexts to store sets of database objects in separate profiles.
Explicit Connection Contexts SQLJ Programming 3. Associate each connection context with a particular set of SQL statements. In this example, the ctx1 connection context is associated with database objects in the SALES schema, ctx2 is associated with database objects in the PERSNL schema, and ctx3 is associated with database objects in the INVENT schema. Notice that the table names are unqualified: #sql [ctx1] salesiter = {SELECT o.ordernum, o.salesrep, d.partnum FROM orders o, odetail d WHERE o.
Explicit Connection Contexts SQLJ Programming The previous example queries the SQL/MX sample database. For more information, see the entity-relationship diagram in the SQL/MX Reference Manual. Customizing Each Profile With Different Options When you process the SQLJ program, the SQLJ translator program generates separate profiles (and modules) for the database objects that are associated with each connection context class.
Execution Contexts SQLJ Programming Execution Contexts An execution context allows you some control over the execution of an SQL statement and enables you to retrieve information about the execution upon completion. Each SQL statement in an SQLJ executable clause is implicitly or explicitly associated with an execution context. Caution. Execution contexts should not be shared among threads in a multithreaded application. For more information, see Multithreading on page 3-23.
Explicit Execution Contexts SQLJ Programming Associating an SQLJ Executable Clause with an Execution Context To associate an embedded SQL statement with an explicit execution context, place the name of the execution context object inside square brackets [] (also known as a context clause) within the SQLJ executable clause, after #sql and before the SQL statement clause. For the syntax of the SQLJ executable clause, see Executable Clause on page A-4.
Execution Context Methods SQLJ Programming Execution Context Methods Use these methods to retrieve information about the results of executing an SQL statement in an implicit or explicit execution context, or use these methods to set attributes of an implicit or explicit execution context: cancel() Causes one thread to cancel an SQL operation being executed by another thread within the specified execution context instance.
Multithreading SQLJ Programming Multithreading A multithreaded Java application can perform tasks in parallel by using Java threads. A thread is a block of code that executes in a single process and performs a particular task. In an HP NonStop operating system, the JVM schedules threads with the CPU in a nonpreemptive manner and schedules a different thread only when the currently running thread performs a nonblocking I/O operation. See Nonblocking JDBC/MX (Nowait SQL) on page 3-26.
Coding a Multithreaded SQLJ Program SQLJ Programming This example uses different execution contexts for each thread: public class MultiThread1 implements Runnable { ... //main() method public static void main (String [] args) { try { #sql { MODULE samdbcat.sales.
Coding a Multithreaded SQLJ Program SQLJ Programming Using Different Connection Contexts for Each Thread Each connection context object has its own default execution context. If you do not specify explicit execution contexts in an SQLJ program, the program uses the same default execution context for all statements that use the same connection context object. If you explicitly specify different connection contexts for each thread, the threads will use different execution contexts by default. Caution.
Nonblocking JDBC/MX (Nowait SQL) SQLJ Programming WHERE partnum = :partNum }; System.out.println("Updated the price of " + partNum + " by " + percent + " percent"); ctx.close(); } ... For the entire program, see MultiThread2.sqlj—Threads With Explicit Connection Contexts on page C-16. Nonblocking JDBC/MX (Nowait SQL) Because SQLJ programs connect to a NonStop SQL database by using the JDBC/MX driver, these programs use the multithreading and nonblocking functionality of the JDBC/MX environment.
Host Variables and Expressions SQLJ Programming Host Variables and Expressions To pass data between an SQLJ program and SQL/MX or SQL/MP tables, use Java variables and expressions as host variables and host expressions in an SQLJ executable clause. Host variables and host expressions begin with a colon (:). For the syntax of host variables and expressions, see Host Expression on page A-6. Input and Output Modes There are two modes of host variables and expressions: input and output.
Host Variables SQLJ Programming Host Variables Unlike embedded variable declarations in C or COBOL programs, Java host variables are not declared within a BEGIN and END block. You can declare them anywhere Java variable declarations are allowed in a Java program. The following examples show how you can use host variables, which are highlighted in bold, in basic SELECT INTO, DELETE, INSERT, and UPDATE statements.
Host Variables SQLJ Programming VALUES (:empNum, :firstName, :lastName, :deptNum, :jobCode, :salary) }; ... Example—UPDATE Statement This UPDATE statement updates the salary of employee number 300 with the value of the host variable: java.math.BigDecimal Salary = new java.math.BigDecimal("60000.00"); ... #sql {UPDATE employee SET salary = :Salary WHERE empnum = 300 }; ... Case Sensitivity The host variables and expressions are processed according to the syntactical rules of Java.
Host Expressions SQLJ Programming You can test the value of the Java variable for a null value (instead of using NULL indicators as in embedded programs in C or COBOL). See this example: String empname = null; ... #sql {SELECT last_name INTO :empname FROM employee WHERE empnum = 65 }; ... if (empname == null) { // Handle null value } Host Expressions You can use complex host expressions in addition to using simple host variables.
Host Expressions SQLJ Programming Examples Given the order of assignment, if the value of the index i is 1 at the time of execution, then SQLJ uses Arry[1] and Arry[2] in the statement: ... #sql { SELECT INTO FROM WHERE AND }; last_name :empname employee jobcode = :(EmpArry[i++]) deptnum = :(EmpArry[i++]) In this example, the host expression, comp * 0.1 + comp, evaluates according to the precedence of the arithmetic operators: ...
Data Type Support SQLJ Programming Data Type Support The subsection discusses the data types that are supported in SQLJ programs and covers these topics: • • • • • • • Input Assignability on page 3-32 Output Assignability on page 3-35 Optimal Data Type Mappings on page 3-37 Date-Time Data Types on page 3-38 Interval Data Types on page 3-38 Floating-Point Data Types on page 3-39 Character String Data Types on page 3-40 For more information about SQL data types, see the SQL/MX Reference Manual and the SQ
Input Assignability SQLJ Programming Table 3-1. Input Assignability of Data Types (page 2 of 3) Java Wrapper Class Java Primitive Data Type* Maps to SQL Data Type... java.lang.Byte byte CHAR[ACTER], CHAR[ACTER] VARYING, VARCHAR, PIC[TURE] X, NATIONAL CHAR[ACTER], NATIONAL CHAR[ACTER] VARYING, NCHAR, NCHAR VARYING java.lang.String SMALLINT, INT[EGER], LARGEINT, NUMERIC, DEC[IMAL], PIC[TURE] S9, REAL, FLOAT, DOUBLE PRECISION java.sql.
Input Assignability SQLJ Programming Table 3-1. Input Assignability of Data Types (page 3 of 3) Java Wrapper Class Java Primitive Data Type* Maps to SQL Data Type... java.lang.Short short java.lang.Integer int java.lang.Long long SMALLINT, INT[EGER], LARGEINT, NUMERIC, DEC[IMAL], PIC[TURE] S9, REAL, FLOAT, DOUBLE PRECISION java.math.BigDecimal java.lang.Float float java.lang.Double double sqlj.runtime. AsciiStream sqlj.runtime. CharacterStream** sqlj.runtime.
Output Assignability SQLJ Programming Output Assignability The output assignability table shows the Java data types of output host variables and expressions that accept data from SQL column types. Note. For better performance in OLTP environments, use the data type mappings shown in Optimal Data Type Mappings on page 3-37. Table 3-2. Output Assignability of Data Types (page 1 of 2) SQL Data Type Maps to Java Wrapper Class or Primitive Data Type*...
Output Assignability SQLJ Programming Table 3-2. Output Assignability of Data Types (page 2 of 2) SQL Data Type Maps to Java Wrapper Class or Primitive Data Type*... SMALLINT INT[EGER] LARGEINT NUMERIC DEC[IMAL] PIC[TURE] S9 REAL FLOAT DOUBLE PRECISION java.lang.Boolean (or boolean) java.lang.Byte (or byte), java.lang.String java.lang.Short (or short), java.lang.Integer (or int), java.lang.Long (or long), java.math.BigDecimal, java.lang.Float (or float), java.lang.Double (or double) sqlj.runtime.
Optimal Data Type Mappings SQLJ Programming Optimal Data Type Mappings The following table shows optimal mappings between a Java data type that you specify for a Java host variable or iterator declaration and its equivalent SQL data type. Although Java provides conversions for combinations of data types, such as those listed in Input Assignability on page 3-32 and in Output Assignability on page 3-35, an SQLJ program will perform more efficiently if it uses these mappings. Note.
Date-Time Data Types SQLJ Programming Table 3-3. Optimal Data Type Mappings (page 2 of 2) Java Wrapper Class or Primitive Data Type* SQL Data Type SQL Column Description java.sql.Timestamp TIMESTAMP Timestamp in form yyyy-mm-dd hh:mm:ss.msssss java.lang.Short (or short) SMALLINT 16-bit signed binary integer java.lang.Integer (or int) INT[EGER] 32-bit signed binary integer java.lang.Long (or long) LARGEINT 64-bit signed binary integer java.math.
Floating-Point Data Types SQLJ Programming Floating-Point Data Types Like JDBC/MX and other Java programs, SQLJ programs handle all floating-point data in IEEE floating-point format. SQL/MX statements embedded in an SQLJ program can query both SQL/MX and SQL/MP format tables. Each type of table stores floating-point data in a different format. SQL/MX Floating-Point Format In SQL/MX tables, floating-point values are stored in IEEE floating-point format.
Character String Data Types SQLJ Programming Character String Data Types This subsection describes: • • • • Character String Truncation on page 3-40 Character Sets on page 3-40 Assigning Character String Data to an Output Host Variable on page 3-41 Assigning (or Comparing) Character String Data to an SQL Column on page 3-42 Character String Truncation Host variables declared with the java.lang.
Character String Data Types SQLJ Programming Assigning Character String Data to an Output Host Variable You must declare a Java host variable that handles character string data as a String or Byte data type in an SQLJ program. You cannot specify a character set in the Java variable declaration: String empname = null; Java host variables accept character string data of any character set.
Character String Data Types SQLJ Programming Assigning (or Comparing) Character String Data to an SQL Column This subsection describes: • • • Assigning Character String Data to an SQL/MP KANJI or KSC5601 Column on page 3-42 Assigning Character String Data to an SQL/MX UCS2 Column on page 3-43 Comparing Character String Data With an SQL/MX ISO88591 Column on page 3-44 For more information about input host variables, see Host Variables and Expressions on page 3-27.
Character String Data Types SQLJ Programming Assigning Character String Data to an SQL/MX UCS2 Column Consider an SQLJ program that modifies an SQL/MX table with a column named LOCATION that stores characters in UCS2 character-set format: CREATE TABLE samdbcat.persnl.dept_internatl ( deptum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ... ,location VARCHAR (18) CHARACTER SET UCS2 ...
Character String Data Types SQLJ Programming Comparing Character String Data With an SQL/MX ISO88591 Column Consider an SQLJ program that modifies an SQL/MX table with a column named LOCATION that stores characters in ISO88591 character-set format: CREATE TABLE samdbcat.persnl.dept ( deptum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ... ,location VARCHAR (18) CHARACTER SET ISO88591 ...
Character String Literals SQLJ Programming Character String Literals A character string literal is a series of characters enclosed in single quotes within an SQL statement. Use a character string literal in an SQL statement anywhere you need to supply a column value that has a character string data type.
Using Hexadecimal Values in Character String Literals SQLJ Programming Using Hexadecimal Values in Character String Literals Another way of specifying a character string literal is to use the hexadecimal form of the characters. This method is useful if your keyboard and operating system do not support the desired character set. Use hexadecimal values that correspond to the characters of a particular character set.
SQLJ Programming Specifying National Character String Literals Specifying National Character String Literals Specify national character string literals by using the N prefix: #sql {INSERT INTO dept_internatl (deptnum, deptname, manager, rptdept, location) VALUES (:deptNum, :deptName, :manager, :rptDept, N' ')}; These string literals are associated with the NATIONAL_CHARSET default attribute. The NATIONAL_CHARSET setting must match the character set of the corresponding SQL column.
Iterators and Result Sets SQLJ Programming Iterators and Result Sets In an SQLJ program, iterators enable you to access multiple rows of data in the result set of a query. An iterator is a Java object that serves the same purpose as a cursor in an embedded program in C or COBOL and as a result set in JDBC. Unlike a cursor, you can pass an iterator as a parameter to a method. Iterators are either positioned or named. You cannot use both types of iterators for a single result set.
Positioned Iterator SQLJ Programming SQLJ PositionedIterator interface and inherits its method signatures. See sqlj.runtime.PositionedIterator Interface on page B-6. Declaring an Iterator Variable After coding the iterator declaration clause, declare an iterator variable by using the iterator class definition. Place this declaration among other Java variable declarations within the class of the SQLJ source file.
Positioned Iterator SQLJ Programming PosIter iter; // Other Java variable declarations and initializations String empname = null; BigDecimal salary = null; // Assign the result set of a query to an iterator object // named iter #sql iter = {SELECT last_name, salary FROM samdbcat.persnl.employee}; // Retrieve rows while endFetch() returns false do { empname = null; #sql {FETCH :iter INTO :empname, :salary}; if (empname != null) && (salary != null) System.out.
Named Iterator SQLJ Programming the next row of the result set. If the last attempt to fetch a row fails (that is, there are no more rows left in the result set), endFetch() returns true. Note that the end-of-data handling of iterators differs from testing for the end-of-data condition in embedded SQL programs in C or COBOL. When you reach the end of data in a cursor (or result set), SQLSTATE returns 02000.
Named Iterator SQLJ Programming SQLJ NamedIterator interface and inherits its method signatures. See sqlj.runtime.NamedIterator Interface on page B-5. Declaring an Iterator Variable After coding the iterator declaration clause, declare an iterator variable by using the iterator class definition. Place this declaration among other Java variable declarations within the class of the SQLJ source file.
Named Iterator SQLJ Programming Retrieving Rows From a Named Iterator Within a loop, retrieve rows from a named iterator by using the next() method and the accessor methods included in the named iterator class. This step is similar to reading a cursor in an embedded SQL program in C or COBOL. Using Accessor Methods Within a named iterator class, SQLJ creates an accessor method for each column name in the iterator declaration clause.
Positioned Iterator Versus Named Iterator SQLJ Programming Using the next() Method Use the next() method from the generated iterator class NamIter to determine the success of retrieving a row from an iterator result set. While next() returns true, the next row of the result set is retrieved and the accessor methods for retrieving the column values are invoked. The next() method returns false when the iterator fails to retrieve the next row.
SQLJ Programming Positioned UPDATE and DELETE Statements Positioned UPDATE and DELETE Statements In an SQLJ program, use a positioned UPDATE and DELETE statement with an iterator in a data retrieval loop to update or delete multiple rows in a table. Positioned UPDATE and DELETE statements specify the name of an iterator in the WHERE CURRENT OF clause. For the syntax, see the SQL/MX Reference Manual. In an iterator declaration clause, use the implements sqlj.runtime.
Passing Iterators as Parameters SQLJ Programming // Retrieve rows and assign columns while next() returns true while (iter.next()) { if (iter.Salary() < avgsalary) { #sql {UPDATE samdbcat.persnl.employee SET salary = 1.1 * salary WHERE CURRENT OF :iter}; } } iter.close(); // Reconstruct the iterator iter so that it contains // the updated salaries #sql namiter = {SELECT last_name, salary FROM samdbcat.persnl.employee}; // Retrieve rows while next() returns true while (iter.next()) { empname = iter.
Converting Between SQLJ Iterators and JDBC Result Sets SQLJ Programming Example Suppose that you have a result set and want to cast it to an iterator: // Declare named iterator class NamIter #sql public iterator NamIter (String Last_Name, BigDecimal Salary); // Declare an iterator variable of NamIter class NamIter iter; // Other Java variable declarations and initializations String empname = null; BigDecimal salary = null; String query = "SELECT salary, last_name" + " FROM samdbcat.persnl.
Converting Between SQLJ Iterators and JDBC Result Sets SQLJ Programming // Other Java variable declarations and initializations String empname = null; BigDecimal salary = null; // Assign the result set of a query to an iterator object // named iter #sql iter = {SELECT last_name, salary FROM samdbcat.persnl.employee WHERE deptnum = :(argv[2])}; // Get the result set corresponding to the iter object ResultSet rs = iter.getResultSet(); // Retrieve rows and assign columns while next() returns true while (rs.
Calling Stored Procedures in Java SQLJ Programming Calling Stored Procedures in Java You can invoke stored procedures in Java (SPJs) in an SQLJ program by using a CALL statement in an SQLJ executable clause. However, it is more efficient to call the underlying Java method directly from an SQLJ or Java program instead of using a CALL statement. The host variables and expressions in the CALL statement default to the IN parameter mode.
Handling Exception Conditions SQLJ Programming Handling Exception Conditions In SQLJ, error handling is integrated with Java exception handling. Unlike embedded SQL programs in C or COBOL, which require SQLSTATE to be checked after each SQL statement, SQLJ programs use error handling code outside of the main code path in Java try and catch blocks. By using try and catch blocks, you enable methods in an SQLJ program to anticipate, recover from, and report certain errors.
SQL Exceptions SQLJ Programming This code handles SQL exceptions and prints an error message if an exception is thrown during the SELECT operation: import java.sql.*; import sqlj.runtime.*; public class SQLExceptionExample { public SQLExceptionExample() { } public static void main (String [] args) { int sqlCode = 0; String sqlState = null; String empname = null; // Holds SQLCODE // Holds SQLSTATE try { #sql {SELECT last_name INTO :empname FROM samdbcat.persnl.
Java Exceptions SQLJ Programming Java Exceptions If you code a catch block to handle SQL exceptions and the try block includes Java methods that throw other Java exceptions, you should code one or more catch blocks to handle those exceptions. For example, you might handle all Java exceptions by catching java.lang.Exception, or you might handle specific Java exceptions by catching subclasses of java.lang.Exception.
SQL Warnings SQLJ Programming SQL Warnings SQL warnings are stored in a java.sql.SQLWarnings object and do not generate SQL exceptions. To check for an SQL warning, you must invoke the getWarnings() method after you execute an SQLJ clause. Because the getWarnings() method is part of an execution context class, you must invoke it by using the execution context, explicit or implicit, that is associated with the SQLJ clause. See Execution Contexts on page 3-20.
Chained Exceptions and Warnings SQLJ Programming } // end of main() method } // end class SQLWarningExample SQL Warnings in an Implicit Execution Context This example is the same as the previous one, except that it calls getWarnings() by using an implicit execution context: import java.sql.*; import sqlj.runtime.*; public class SQLWarningExample { public SQLWarningExample() { } public static void main (String [] args) { SQLWarning warn; Float floatQty = new Float(37.
Chained Exceptions and Warnings SQLJ Programming next warning in the chain. The setNextWarning() method accepts an SQL warning and adds it to the end of the chain. This example shows how an SQLJ program, which uses a positioned UPDATE statement to update multiple rows, returns an SQL exception or warning from a chain. An exception or warning is added to the chain each time an exception or warning occurs during the execution of the UPDATE statement in the while loop: import java.sql.*; import sqlj.
Lost Open Error (8574) SQLJ Programming iter.close(); } // End of try block // Handles chained SQL exceptions from the try block catch (SQLException se) { while (se != null) { System.out.println("SQL exception: " + se.getMessage()); se = se.
Lost Open Error (8574) SQLJ Programming Recovering From the Lost Open Error If DML or iterator operations in a program enable concurrent access to tables or views, or if you anticipate network or hardware interruptions, add code to the program to catch and handle the Lost Open Error (8574). The way that you handle occurrences of the Lost Open Error (8574) depends on what you are trying to accomplish with the DML statement or iterator.
SQLJ Programming Lost Open Error (8574) HP NonStop SQL/MX Programming Manual for Java—523726-003 3- 68
4 SQL/MX Programming Considerations This section explains the behavior of various SQL statements in an SQLJ program and covers these topics: • • • • • • • • • • • Static and Dynamic SQL on page 4-1 CONTROL Statements on page 4-6 SET TABLE TIMEOUT Statement on page 4-11 MODULE Directive on page 4-12 DECLARE and SET Statements for Object Name Qualification on page 4-16 Name Resolution on page 4-20 Similarity Checks and Automatic Recompilation on page 4-26 Data Definition Language (DDL) Statements on page 4-
SQL/MX Programming Considerations Static and Dynamic Execution Scenarios If the SQLJ run time cannot load an SQL statement from the module, the SQLJ run time typically calls JDBC to prepare and execute the statement dynamically. The SQLJ standard refers to this dynamic process as the default run time. The next subsections explain why the default run time might occur instead of the customized run time.
Causes of Dynamic Execution SQL/MX Programming Considerations Figure 4-1. Effect of the -missingSQLObject Option Start Did you try to customize the profile? No Yes Did you set the -missingSQLObject option to true? Yes No Were all referenced database objects available at customization? Customization Yes succeeds. All SQL statements run statically. No Customization succeeds for SQL statements that refer to existing database objects. The customized statements run statically.
SQL/MX Programming Considerations Causes of Dynamic Execution profile and module, all the SQL statements in the program run dynamically in the default run time. The -missingSQLObject Set to True If -missingSQLObject is true, the referenced database objects are not required to exist at the time of customization for customization to succeed.
SQL/MX Programming Considerations Causes of Dynamic Execution SQL/MX Compiler Errors The SQL/MX compiler (mxcmp) compiles the SQL statements in the module definition, which is either an embedded module definition in the extended profile or a module definition file (.m), and generates a compiled module that contains the execution plans of the SQL statements. Sometimes SQL compilation might fail, you might forget to run mxCompileUserModule or the SQL/MX compiler, or you might remove the module accidentally.
SQL/MX Programming Considerations CONTROL Statements CONTROL Statements CONTROL statements are SQL/MX compiler directives that affect the execution of SQL statements in a program and that enable you to override the system-level default settings for the current process.
SQL/MX Programming Considerations Static CONTROL Statements If you set -missingSQLObject to true during customization, the CONTROL statements are applied statically and also execute dynamically at run time by calling JDBC. When the CONTROL statements execute dynamically, they affect other dynamic SQL statements within their control flow scope and JDBC connection. You might not always know when an embedded SQL statement in an SQLJ program executes dynamically.
SQL/MX Programming Considerations Dynamic CONTROL Statements Connection Context of Static CONTROL Statements Static CONTROL statements affect only those static SQL statements with the same connection context class because such statements are stored in the same profile.
SQL/MX Programming Considerations Dynamic CONTROL Statements during customization because it occurs immediately before the statically compiled statements in line order: try { if (deptnum == 3100) { #sql [ctx] {CONTROL QUERY DEFAULT CATALOG 'CAT1'}; } else { #sql [ctx] {CONTROL QUERY DEFAULT CATALOG 'CAT2'}; } #sql [ctx] { /* Static SELECT statement */ }; #sql [ctx] { /* Dynamic UPDATE statement */ }; #sql [ctx] { /* Static UPDATE statement */ }; #sql [ctx] { /* Dynamic SELECT statement */ }; } Connectio
SQL/MX Programming Considerations Placement of the CONTROL Statements Placement of the CONTROL Statements Place CONTROL statements anywhere an executable statement is allowed in an SQLJ program. You cannot place these statements at the top-level scope of a program where you code class declarations. If you associate a connection context with a CONTROL statement, place the CONTROL statement after the instantiation of the connection context object.
SQL/MX Programming Considerations SET TABLE TIMEOUT Statement SET TABLE TIMEOUT Statement The SET TABLE TIMEOUT statement sets a dynamic value for a lock timeout or a stream timeout in the environment of the current session. A SET TABLE TIMEOUT statement with a particular connection context affects only those statements that use the same connection context class as SET TABLE TIMEOUT. For the syntax of the SET TABLE TIMEOUT statement, see the SQL/MX Reference Manual.
SQL/MX Programming Considerations MODULE Directive MODULE Directive The MODULE directive specifies a name to be used for a module, which contains the compiled SQL plans of the application. Naming each module enables you to manage modules on a system more efficiently. For information about managing modules, see the SQL/MX Installation and Management Guide. If you do not specify the MODULE directive, the system determines a module name for you. See System-Defined Module Name on page 4-15.
SQL/MX Programming Considerations Connection Context of the MODULE Directive Connection Context of the MODULE Directive Specify only one MODULE directive per connection context. Otherwise, the SQLJ translator generates an error. For example, this code is disallowed and returns an error: #sql [ctx1, exCtx1] {MODULE cat.sch.mod NAMES ARE ISO88591}; #sql [ctx1, exCtx2] {MODULE catlg.schm.modl}; You can specify an execution context for the MODULE directive, but SQLJ ignores it.
SQL/MX Programming Considerations External Qualification of the Module Name External Qualification of the Module Name When you customize the profile, you can externally set the target (or table set), version, or group to create an externally qualified module name (for example, CAT.SCH.GROUP^MODULE^TABLESET^VERSION). For more information, see Module Management Naming on page 6-12. Delimited Identifiers in the Module Name Note.
SQL/MX Programming Considerations System-Defined Module Name Use of Dots and Trailing Spaces Caution. Avoid using delimited identifiers that contain dots (.) and trailing spaces in the names of the catalog, schema, and module. Dots and trailing spaces in delimited identifiers might cause the three-part module name to clash with an unrelated module name, which causes the overwriting of the query execution plans of the unrelated module. Example of Dots A MODULE directive that specifies a module named CAT.
SQL/MX Programming Considerations DECLARE and SET Statements for Object Name Qualification DECLARE and SET Statements for Object Name Qualification DECLARE and SET statements qualify the unqualified database object names in an SQLJ program and include: • • • • DECLARE CATALOG and SET CATALOG, which set the default catalog for unqualified schema names in SQL statements DECLARE MPLOC and SET MPLOC, which set the default volume and subvolume for unqualified Guardian physical names in SQL statements DECLARE
SQL/MX Programming Considerations DECLARE Statements for Static SQL Line Order Scope of the DECLARE Statements DECLARE statements have line order scope for a particular connection context. That is, the scope of a DECLARE statement is based on the order of the SQL statements in the SQLJ program.
SQL/MX Programming Considerations SET Statements for Dynamic SQL #sql [ctx2] { /* Static SQL statement 2 */ }; ctx2.close(); SET Statements for Dynamic SQL The SET statements are executable statements that affect only dynamic SQL statements with the same JDBC connection in an SQLJ program. Placement of the SET Statements Place the SET statements anywhere executable statements are allowed in an SQLJ program.
SQL/MX Programming Considerations Precedence of Object Name Qualification Connection Context of the SET Statements The SET statements affect only dynamic SQL statements that share the same JDBC connection. The JDBC/MX driver creates a different SQL context for each JDBC connection. Therefore, each instance of a connection context class will have only those dynamic SQL statements associated with it. Connection contexts that share a JDBC connection share the same SET statements.
SQL/MX Programming Considerations Name Resolution Name Resolution In an SQLJ program, you can use SQL/MX statements to query both SQL/MP and SQL/MX database objects. This subsection explains how to refer to SQL/MP and SQL/MX database objects in an SQLJ source file and how the object names are resolved during customization and SQLJ run time.
SQL/MX Programming Considerations Table and View Name References Guardian Names for SQL/MP Objects You can use the Guardian physical name for SQL/MP tables or views in an SQL statement: #sql {DELETE FROM $samdb.persnl.employee}; When you hard code a table name in the Guardian format, the physical name is tightly bound to the SQL statement when the SQL statement is customized.
SQL/MX Programming Considerations Precedence of Object Name Qualification name resolution. For more information, see Compile-Time Name Resolution for SQL/MP Objects on page 4-23. The use of DEFINEs in SQL statements also enables late name resolution. By using late name resolution, you can customize a statement to use one table and then, without recustomizing the statement, process a different table when the statement is executed by using a different value for the statement’s DEFINE.
SQL/MX Programming Considerations Compile-Time Name Resolution for SQL/MP Objects For more information about the SQLJ command-line options, see SQLJ Translator Command Line on page 5-10. For more information about the SYSTEM_DEFAULTS table, see the SQL/MX Reference Manual. Compile-Time Name Resolution for SQL/MP Objects Compile-time name resolution is an SQL/MX extension you use to customize and compile a module with statements that refer to SQL/MP tables or views with class MAP DEFINEs.
SQL/MX Programming Considerations Distributed Database Considerations the similarity check; otherwise, the plan is inoperable. See Similarity Checks and Automatic Recompilation on page 4-26. Distributed Database Considerations The SQL statements in an SQLJ program can refer to SQL/MX and SQL/MP database objects on remote nodes. Remote SQL/MX Objects To refer to remote SQL/MX database objects in an SQLJ program, you need not change the database object names in the source code.
SQL/MX Programming Considerations RDF Considerations To enable easier deployment of SQLJ applications in an RDF environment, follow these guidelines: • • SQL/MP Object Names for an RDF Environment on page 4-25 SQL/MX Object Names for an RDF Environment on page 4-25 SQL/MP Object Names for an RDF Environment When referring to SQL/MP objects in an SQLJ program, use class MAP DEFINEs or SQL/MP aliases. See DEFINE Names for SQL/MP Objects on page 4-21 and SQL/MP Aliases for SQL/MP Objects on page 4-21.
SQL/MX Programming Considerations Similarity Checks and Automatic Recompilation Similarity Checks and Automatic Recompilation This subsection explains what causes similarity checks and automatic recompilation to occur and how to control these operations by coding CONTROL QUERY DEFAULT statements in a program.
SQL/MX Programming Considerations Similarity Check Failed Timestamp Check The SQL/MX executor performs a similarity check if a timestamp check fails, which occurs if the table’s redefinition timestamp has changed since the referencing statement was compiled. The SQL/MX executor performs a timestamp check for each table referenced in an SQL statement at table open time (the first time the table is opened).
SQL/MX Programming Considerations Similarity Check Similarity Check Criteria During a similarity check, the SQL/MX executor compares the compile-time version of a table with its run-time version. For the similarity check to pass: • • • • Both tables must have the same table type (key-sequenced or entry-sequenced). Both tables must be either audited or nonaudited. Both tables must have the same number of columns.
SQL/MX Programming Considerations • Automatic Recompilation Both tables must have the same partitioning scheme (hash or range partitioned), the same number of partitions, and the same partitioning keys if the plan does not use OLT optimization. Note. If the plan uses OLT optimization, the number of partitions does not affect the similarity check. For more information about OLT optimization, see the SQL/MX Query Guide.
Recommended Recompilation Settings for OLTP Programs SQL/MX Programming Considerations Controlling Automatic Recompilation By default, automatic recompilation is enabled for all SQLJ programs.
SQL/MX Programming Considerations Recommended Recompilation Settings for OLTP Programs for warning events that indicate that an automatic recompilation has occurred. You can also turn on the RECOMPILATION_WARNINGS option to report recompilation warning messages directly to the program when they occur. For more information, see Similarity Check on page 4-26 and Automatic Recompilation on page 4-29.
SQL/MX Programming Considerations Data Definition Language (DDL) Statements Data Definition Language (DDL) Statements Data definition language (DDL) statements define, delete, or modify the definition of a catalog, schema, or database object, or the authorization to use a database object. You can embed some of the SQL/MX DDL statements in an SQLJ program. For a list of these statements, see Supported SQL Statements on page A-8. Note.
SQL/MX Programming Considerations Using Distributed Database Statements in an SQLJ Program For example, this INSERT statement fails to be customized because the referenced JOB table does not exist at the time of customization. When you execute the SQLJ program, the JOB table is created at run time, and the INSERT statement runs dynamically: #sql {CREATE TABLE samdbcat.persnl.
SQL/MX Programming Considerations Using Distributed Database Statements in an SQLJ Program If you embed a REGISTER CATALOG statement in an SQLJ program, you must run the program on the local node where the catalog is visible. Before executing the SQLJ program, verify that a catalog with an identical name does not already exist on the remote node. You cannot register a catalog on a node where an individual catalog with the same name already exists.
SQL/MX Programming Considerations Distributing SQL/MX Objects From an SQLJ Program For example, this SQLJ program removes a catalog reference from the target node, \remote: #sql {UNREGISTER CATALOG samdbcat FROM \remote.$samdb}; After running the SQLJ program, the SAMDBCAT catalog is no longer visible on the target node, \remote. Users can no longer query SQL/MX objects in the SAMDBCAT catalog from the remote node, \remote.
SQL/MX Programming Considerations Distributing SQL/MX Objects From an SQLJ Program Partitioning an SQL/MX Object on Remote Nodes To partition an SQL/MX object across remote nodes, the catalog of that object must be visible on all the remote nodes. That is, a catalog reference of the catalog must be registered on all the remote nodes.
SQL/MX Programming Considerations Automatic Hash Partitioning From an SQLJ Program information about maintaining a network-distributed SQL/MX database environment, see the SQL/MX Installation and Management Guide. Automatic Hash Partitioning From an SQLJ Program The Partition Overlay Specification (POS) enables you to create hash partitioned tables automatically from an SQLJ program without using partition syntax in the CREATE TABLE statement.
SQL/MX Programming Considerations Using SQL/MP Alias Statements in an SQLJ Program Using SQL/MP Alias Statements in an SQLJ Program The CREATE SQLMP ALIAS statement enables you to use logical names (for example, cat.sch.tab) for SQL/MP tables or views by mapping an SQL/MP alias to a Guardian physical file. The ALTER SQLMP ALIAS and DROP SQLMP ALIAS statements enable you to change the table mapping or drop the alias, respectively.
SQL/MX Programming Considerations UPDATE STATISTICS Statement UPDATE STATISTICS Statement The UPDATE STATISTICS statement updates the histogram statistics for one or more groups of columns within an SQL/MP or SQL/MX table to enable optimized access plans. For more information about the UPDATE STATISTICS statement, see the SQL/MX Reference Manual. Follow these guidelines when embedding an UPDATE STATISTICS statement in an SQLJ program.
SQL/MX Programming Considerations Long-Running UPDATE STATISTICS Operations and TMF Errors To ensure that DML statements use updated histogram statistics: • • Put embedded UPDATE STATISTICS statements in a separate program from dependent DML statements. Run the UPDATE STATISTICS statements before compiling an SQLJ program that refers to related tables.
SQL/MX Programming Considerations Transaction Management Transaction Management A transaction, which is a set of database changes that must be completed as a group, is the basic recoverable unit in case of a failure or transaction interruption. You can define transactions in an SQLJ program in much the same way you would define them in an embedded SQL program in C or COBOL. The typical order of events is: 1. Transaction is started. 2. Database changes are made. 3.
Steps for Ensuring Data Consistency SQL/MX Programming Considerations Steps for Ensuring Data Consistency Example 4-1 shows steps of coding transaction control statements in an SQLJ program: Example 4-1. Coding Transaction Control Statements in an SQLJ Program 1 ... /* Set attributes for transactions */ #sql {SET TRANSACTION AUTOCOMMIT OFF}; #sql {CONTROL QUERY DEFAULT ISOLATION_LEVEL 'READ COMMITTED'}; try { 2 #sql #sql #sql ... #sql 3 5 {BEGIN WORK}; {UPDATE...}; {INSERT...
SQL/MX Programming Considerations Setting Attributes for Transactions Setting Attributes for Transactions Use a SET TRANSACTION statement to set some of the attributes for subsequent transactions. When coding a SET TRANSACTION statement in an SQLJ program, consider that: • • • The READ ONLY access mode has no effect on the behavior of a transaction in an SQLJ program. The diagnostics area does not apply to SQLJ programs and is disallowed in a SET TRANSACTION statement in an SQLJ program.
SQL/MX Programming Considerations • Setting Attributes for Transactions Calling the setAutoCommit() method of the java.sql.Connection interface: For an implicit connection context: java.sql.Connection conn = DefaultContext.getDefaultContext().getConnection(); conn.setAutoCommit(false); For an explicit connection context named ctx: ctx.getConnection().setAutoCommit(false); For information about setAutoCommit(), see the JDBC Driver for SQL/MX Programmer’s Reference.
SQL/MX Programming Considerations Setting Attributes for Transactions isolation level of any containing transactions. See Precedence of Transaction Isolation Levels on page 4-45. If you set the isolation level to READ UNCOMMITTED, the access mode becomes READ ONLY by default. As a result, INSERT, UPDATE, and DELETE statements within the scope of a SET TRANSACTION or CONTROL QUERY DEFAULT statement fail to compile or execute. INSERT, UPDATE, and DELETE statements require the access mode to be READ WRITE.
SQL/MX Programming Considerations Starting a Transaction Starting a Transaction Use a BEGIN WORK statement to start a transaction explicitly: #sql {BEGIN WORK}; If you do not use the BEGIN WORK statement, SQL/MX automatically starts a transaction for a statement, provided that an active transaction does not already exist and that the statement supports implicit transactions. For information about implicit (or system-defined) transactions, see the SQL/MX Reference Manual.
SQL/MX Programming Considerations Committing Database Changes if No Errors Occur database changes are rolled back. For more information about handling exception conditions, see Handling Exception Conditions on page 3-60. Committing Database Changes if No Errors Occur The COMMIT WORK statement permanently commits changes made to the database within the current transaction and ends the transaction. It frees resources held by the transaction, such as row or table locks.
SQL/MX Programming Considerations Undoing Database Changes if an Error Occurs HP NonStop SQL/MX Programming Manual for Java—523726-003 4- 48
5 Processing SQLJ Programs This section explains how to prepare an SQLJ program for execution and assumes that you have already written the source code of an SQLJ program. For information about how to write SQLJ source code, see Section 3, SQLJ Programming and Section 4, SQL/MX Programming Considerations.
Processing SQLJ Programs Commands for Processing Without Module Definition Files Commands for Processing Without Module Definition Files The SQLJ translator program, sqlj.tools.Sqlj, is a Java program that automatically invokes translation, Java compilation, and customization of an SQLJ program, depending on the options that you pass to it. See the SQLJ Translator Command Line on page 5-10 and Using Command-Line Options on page 5-6.
Processing SQLJ Programs Commands for Processing Without Module Definition Files You can also invoke the Java compiler independently of the SQLJ translator program by running the javac command. For example, issue this OSS command to initiate Java compilation: javac MyProg.java For more information, see Java Compiler Command Line on page 5-33. Command for Customization If you specify a .ser file (profile) or .jar file containing profiles on the SQLJ command line, sqlj.tools.
Processing SQLJ Programs Commands for Processing With Module Definition Files Commands for Processing With Module Definition Files To enable SQL/MX-Release-1.8-style processing, which generates a module definition file, set -createMDF=true (or -SQLMXCustomizerVersion=800) in a properties file or on the command line. For more information, see -createMDF on page 5-28 (or -SQLMXCustomizerVersion on page 5-26). With SQL/MX-Release-1.
Processing SQLJ Programs Commands for Processing With Module Definition Files Commands for Customization With Module Definition Files If you specify a .ser file (profile) or .jar file containing profiles on the SQLJ command line, sqlj.tools.Sqlj performs customization on each profile. For example, issue this OSS command to initiate customization: java sqlj.tools.Sqlj -createMDF MyProg_SJProfile0.ser MyApp.
Processing SQLJ Programs Using Command-Line Options Using Command-Line Options To process an SQL source file, you must invoke the SQLJ translator program, sqlj.tools.Sqlj, on the OSS command line. This manual refers to this command line as the SQLJ command line. For more information about the SQLJ command-line syntax, see SQLJ Translator Command Line on page 5-10.
Processing SQLJ Programs Properties File Options The options that you specify on the SQLJ command line determine particular conditions or values to be used during SQLJ processing. Use the equals sign (=) to assign a value to an option or a flag setting. For example, to set the default catalog to cat for an SQLJ program, specify this argument on the SQLJ command line: -catalog=cat Be aware that the names of the options are case-sensitive.
Processing SQLJ Programs Properties File Syntax of the Properties File Follow these guidelines when creating a properties file and adding options to it: • • Place one option setting per line in the properties file. For each SQLJ translator option, use the sqlj. prefix: sqlj.status=true • For each Java compiler option, use the compile. prefix (instead of -C): compile.verbose • For each customizer option, use the profile. prefix (instead of -P) or the sqlj. prefix: profile.
Processing SQLJ Programs Precedence of Option Settings directory contains -status=false and -dir=/myjava, then the order of options would be: java sqlj.tools.Sqlj -status=true -status=false -dir=/myjava -dir=/mydir MyProg.sqlj In the previous example, the second -dir option takes precedence over the first -dir option. For more information, see Precedence of Option Settings on page 5-9.
Processing SQLJ Programs SQLJ Translator Command Line SQLJ Translator Command Line This subsection describes the SQLJ translator command line, or the SQLJ command line, and shows the command-line syntax, provides a summary of the SQLJ commandline options, and groups the command-line options in categories and describes them in more depth. Command-Line Syntax To process an SQLJ program, enter this command at an OSS prompt: java [Java-options] sqlj.tools.
Summary of SQLJ Command-Line Options Processing SQLJ Programs path. For more information about customizing JAR files, see Customizing Profiles in a JAR File on page 6-7. • • You cannot use a .m file with any other file type on the command line. You cannot mix .sqlj files with .ser, .jar, or .m files and cannot mix .java files with .ser, .jar, or .m files on the same command line. Use the wild-card character (*) in a file name to specify a set of similarly named files in the source directory.
Summary of SQLJ Command-Line Options Processing SQLJ Programs Table 5-1. SQLJ Command-Line Options (page 2 of 7) More Information Option Description Default Value -catalog Option that specifies the string of a default catalog to use for unqualified SQL/MX objects and modules (if not overridden by another setting) Default catalog. See Precedence of Object Name Qualification on page 4-22 and Catalog and Schema of the Module Name on page 4-13.
Summary of SQLJ Command-Line Options Processing SQLJ Programs Table 5-1. SQLJ Command-Line Options (page 3 of 7) More Information Option Description Default Value -encoding Option that specifies the Java input and output encoding for the Java source file. If you do not specify this option, the file encoding is based on the file.encoding system property file.
Summary of SQLJ Command-Line Options Processing SQLJ Programs Table 5-1. SQLJ Command-Line Options (page 4 of 7) More Information Option Description Default Value -missingSQLObject Flag that determines whether an unavailable database object during translation and customization is considered an error.
Summary of SQLJ Command-Line Options Processing SQLJ Programs Table 5-1. SQLJ Command-Line Options (page 5 of 7) More Information Option Description Default Value -moduleVersion (or -P-CmoduleVersion)* Option that specifies a Module Version Specification String (MVSS) for specifying the version of the modules of the application null Options for Module Management on page 5-28 -mploc Option that specifies the string of a default node, volume, and/or subvolume for SQL/MP objects.
Summary of SQLJ Command-Line Options Processing SQLJ Programs Table 5-1. SQLJ Command-Line Options (page 6 of 7) More Information Option Description Default Value -schema Option that specifies the string of a default schema to use for unqualified SQL/MX objects and modules (if not overridden by another setting) Default schema. See Precedence of Object Name Qualification on page 4-22 and Catalog and Schema of the Module Name on page 4-13.
Summary of SQLJ Command-Line Options Processing SQLJ Programs Table 5-1.
Processing SQLJ Programs Options for Output Files and Directories Options for Output Files and Directories Use these options to specify the locations of the output files during SQLJ processing. -d Note. To ensure successful customization, include the full directory path of the -d option in the class path. For more information, see Setting the Class Path on page 2-4. The -d option specifies the root output OSS directory for all the profiles (.
Processing SQLJ Programs Options for the Command Line Only If you specify a relative path, the full path is derived from the current directory. For example, if you run the command line from a current directory of /usr/dev and specify -dir=myjava, the full path of the Java source files is /usr/dev/myjava. If an SQLJ source file contains a package statement, the SQLJ translator program places all generated files in subdirectories based on the package name.
Processing SQLJ Programs Options for Reporting and Line Mapping -props The -props option specifies a properties file that contains option settings to be read by the SQLJ translator program. The properties file provides an alternate way of specifying option settings on the SQLJ command line. This example specifies a properties file named myprops.properties: java sqlj.tools.Sqlj -props=myprops.properties For more information, see Properties File on page 5-7.
Options for the SQLJ Translator Processing SQLJ Programs -warn The -warn option consists of a set of flags that specify which conditions should return warnings and which conditions should be ignored during translation. Combine the flags in a single, comma-separated string. Spaces are disallowed before and after the commas.
Options for the SQLJ Translator Processing SQLJ Programs -encoding The -encoding option specifies the input and output encoding of the Java source file. You can specify the alias -e instead of -encoding on the command line. To assign a value to -e, omit the = sign (for example, -eSJIS). By default, the encoding is based on the file.encoding system property. The file.encoding value in the OSS environment is ISO88591 by default.
Processing SQLJ Programs Option for Missing or Unavailable Database Objects Specify customizer-specific options with the -P prefix as shown in this example: java sqlj.tools.Sqlj -P-verbose -P-Ccatalog=cat -P-Cschema=sch MyProg.sqlj -ser2class The -ser2class flag directs the SQLJ translator program to convert the generated profiles (.ser files) to .class files. For example, this command converts the generated profiles to .class files: java sqlj.tools.Sqlj -ser2class=1 MyProg.
Processing SQLJ Programs Options for the Java Compiler customization. By default, this option is false, and an unavailable database object is considered an error. -missingSQLObject=false During translation, if a statement refers to an unavailable database object, the SQLJ translator program returns an error and does not generate a Java source file (.java file) or profile (.ser file).
Processing SQLJ Programs Options for the Java Compiler -compiler-encoding-flag The -compiler-encoding-flag passes the -encoding value to the Java compiler. For a Java compiler that does not support the -encoding option, set -compile-encoding-flag to false. For more information about the -encoding option, see -encoding on page 5-22. -compiler-executable The -compiler-executable option specifies a different version of the Java compiler, other than javac, for the SQLJ translator program to use.
Processing SQLJ Programs Options for the SQLJ Customizer Options for the SQLJ Customizer Use these options to influence how the SQLJ customizer processes the profiles of a program. The -P prefix before each option is optional. -backup The -backup option specifies whether to back up the profiles before customization, which extends the profile with customized run-time information. This option is helpful if you plan to customize a program and want keep the original profile or profiles.
Processing SQLJ Programs Options for the SQLJ Customizer definition file. For more information, see Processing With Module Definition Files on page 1-17. For example, this command generates and automatically SQL compiles a module definition file: java sqlj.tools.Sqlj -SQLMXCustomizerVersion=800 MyProg.sqlj If you specify 1200 for SQL/MX Release 2.0 or 2.
Processing SQLJ Programs Options for Module Management Options for Module Management Use these options to determine how the module definition is generated and to assign catalog, schema, group, target, and version to the module names of an application. The customizer adds the catalog, schema, group, target, and version options, if specified, to the externally qualified module name. The -P prefix before each option is optional.
Processing SQLJ Programs Options for Module Management If you do not specify an MGSS or if the string is null, the customizer does not add an MGSS to the externally qualified module name. If you specify more than one -moduleGroup option, the customizer uses last option on the command line, even if this option omits the MGSS or is null. For example, this command assigns an MGSS named MYGROUP to the group attribute of the externally qualified module name: java sqlj.tools.
Processing SQLJ Programs Options for Object Name Qualification -moduleVersion The -moduleVersion option specifies the Module Version Specification String (MVSS), which is a regular or delimited identifier. For the syntax of identifiers, see the SQL/MX Reference Manual. The maximum length of the MVSS is 31 characters, and whitespace is disallowed on either side of the equals sign (=). See Module Name Length on page 6-13.
Processing SQLJ Programs Options for Object Name Qualification -mploc value. By default the -nametype option is ANSI. To use the -mploc option, set -nametype to NSK. See -nametype on page 5-31. The customizer applies this setting to an unqualified object name only when another setting does not override this option. If you specify a three-part name, such as \node1.$vol1.subvol1, the customizer interprets the first part as the node, the second part as the volume, and the third part as the subvolume.
Processing SQLJ Programs Options for the SQL/MX Compiler For example, this command directs the customizer to use mycat.mysch as the catalog and schema for unqualified SQL/MX object names and modules: java sqlj.tools.Sqlj -schema=mycat.mysch MyProg.sqlj Options for the SQL/MX Compiler Use these options to influence how the SQL/MX compiler processes the module definition files of a program.
Processing SQLJ Programs Java Compiler Command Line Java Compiler Command Line The SQLJ translator program automatically invokes the Java compiler (javac). The Java compiler compiles the Java source file (.java file) into class files (.class files) that contain bytecode. You can also invoke the Java compiler independently of the SQLJ translator program by running the javac command. Command-Line Syntax To invoke the Java compiler, enter this command at an OSS prompt: javac [options] file1.java [file2.
Processing SQLJ Programs mxCompileUserModule Command Line mxCompileUserModule Command Line To invoke SQL compilation, you must run the mxCompileUserModule utility after you run the SQLJ translator program, sqlj.tools.Sqlj. Caution. If you do not run mxCompileUserModule, you will not generate module files, and the program will either run dynamically or return an SQL exception indicating that the module is missing or corrupted. For more information, see SQL/MX Compiler Errors on page 4-5.
Processing SQLJ Programs Command-Line Syntax -g moduleGlobal specifies that the module be placed globally in the /usr/tandem/sqlmx/ USERMODULES directory. Use this option to generate modules for an SQLJ program when the MXCMP_PLACES_LOCAL_MODULES attribute is ON in the SYSTEM_DEFAULTS table. If you do not specify this option when MXCMP_PLACES_LOCAL_MODULES is ON, mxCompileUserModule generates modules in the same directory where you invoke it. Note.
Processing SQLJ Programs MXCMP Environment Variable jar-file is the OSS path name of a JAR file that contains extended profiles (.ser file). The OSS directory: • • • Must exist and be accessible. Otherwise, an error is returned, and no module is created. Must not specify a Guardian subvolume (/G/...) or a remote directory in an Expand network (/E/...
Processing SQLJ Programs Examples—mxCompileUserModule For more information, see the Open System Services Shell and Utilities Reference Manual. Examples—mxCompileUserModule • This command compiles the embedded module definitions in all the extended profiles in the MyApp.jar file: mxCompileUserModule MyApp.jar • This command compiles the embedded module definition in the extended profile, MyProg_SJProfile2.ser: mxCompileUserModule MyProg_SJProfile2.
Processing SQLJ Programs SQL/MX Compiler Command Line SQL/MX Compiler Command Line The SQLJ translator program automatically invokes the SQL/MX compiler and produces a module if you choose to customize a program and if you choose to generate a module definition file (.m) by specifying the -createMDF=true (or -SQLMXCustomizerVersion=800) option. The SQL/MX compiler SQL compiles the embedded SQL statements in the module definition file.
Processing SQLJ Programs Example—mxcmp MXCMP_PLACES_LOCAL_MODULES is ON, mxcmp generates modules in the same directory where you invoke it. Note. Currently, SQL/MX does not support locally placed modules for SQLJ applications. The modules of an SQLJ application must be stored in the /usr/tandem/sqlmx/ USERMODULES directory. Otherwise, the program will either run dynamically or return an SQL exception indicating that the module is missing or corrupted.
Processing SQLJ Programs jar Tool Command Line jar Tool Command Line The jar tool combines multiple files into a single Java Archive (JAR) file. For more information about this tool, see the NonStop Server for Java Tools Reference Pages. Command-Line Syntax To invoke the jar tool, enter this command at an OSS prompt: jar [options] [manifest-file] destination-jar-file input-file [input-file]... jar is the jar tool. options are jar option settings, which must be separated by spaces.
Processing SQLJ Programs SQLJ Run-Time Command Line SQLJ Run-Time Command Line After processing an SQLJ program, you can execute it as stand-alone program by issuing the java command on the command line. For other ways of executing a program, see Execution Scenarios on page 1-22. Command-Line Syntax To run an SQLJ program, enter this command at an OSS prompt: java [Java-options] MyProg java is the Java application launcher, which executes Java bytecode.
Processing SQLJ Programs Example—SQLJ Run-Time Command Line Example—SQLJ Run-Time Command Line This example passes catalog value samdbcat and schema value persnl to the JVM at run time. At an OSS prompt, enter this command to run the SQLJ program: java -Dcatalog=samdbcat -Dschema=persnl SampleDML ProfilePrinter Tool Use the ProfilePrinter tool to print the contents of a profile (.ser file) in a readable format for diagnostic purposes.
Processing SQLJ Programs Interpreting the ProfilePrinter Output Profile Information Block The first block provides general information about the profile: • • • • • • • Name of the .ser file (SampleDML_SJProfile0) Date and time when profile was printed (11/7/03 3:53 PM) Name of the serialized connection context object (DefConCtx), Default loader used to load the profile Whether the profile is customized or not Name of the associated SQLJ source file (SampleDML.sqlj) Number of profile entries (12).
Processing SQLJ Programs Interpreting the ProfilePrinter Output associated with the SAMDBCAT.PERSNL.DMLMOD module whose timestamp is 1068249214683.
Processing SQLJ Programs Interpreting the ProfilePrinter Output The first three statement index entries, STATEMENT INDEX 0 to 2, describe an internal system default setting for the SQL/MX executor and the system default settings for the catalog and schema. The statement index entries after STATEMENT INDEX 2 show the ready-to-compile forms of the SQL/MX statements in the SQLJ program.
Processing SQLJ Programs Displaying Query Execution Plans Displaying Query Execution Plans The EXPLAIN function is an SQL/MX extension that generates a result table describing an access plan for a DML statement, otherwise known as a query execution plan. Use the EXPLAIN function only for customized SQLJ programs that have modules. For more information about the EXPLAIN function, see the SQL/MX Reference Manual and the SQL/MX Query Guide. Note.
Processing SQLJ Programs Displaying the Query Execution Plans of All Statements module definitions, see the ProfilePrinter Tool on page 5-42.
Processing SQLJ Programs Displaying the Query Execution Plans of All Statements HP NonStop SQL/MX Programming Manual for Java—523726-003 5- 48
6 Program and Module Management This section explains how to manage the files of an SQLJ application and covers these topics: • • • • • Program Files on page 6-1 Packaging the Program in a JAR File on page 6-5 Managing Program Files on page 6-11 Managing Modules on page 6-11 Module Management Naming on page 6-12 Program Files For effective program and module management, you should be familiar with the types of program files generated by the SQLJ translator program and know where the program files are sto
Customized SQLJ Program Files Program and Module Management Customized SQLJ Program Files If you customize the SQLJ program, the SQLJ translator program generates these files and stores them in the specified OSS directories: Program File File Name Convention* In an OSS directory specified by the user: SQLJ source file MyProg.sqlj In an OSS directory specified by the -dir command-line option and the package statement if present: Java source file MyProg.
Program and Module Management Customized SQLJ Program Files If you do not specify the -dir and -d command-line options during translation, the default directory of all programs files is the same as the OSS directory of the SQLJ source file (.sqlj file). For more information about the -dir and -d options, see Options for Output Files and Directories on page 5-18. By default, the SQLJ translator program does not generate module definition files (.m).
Noncustomized SQLJ Program Files Program and Module Management Noncustomized SQLJ Program Files If you do not customize the SQLJ program, the SQLJ translator program generates these files and stores them in the specified OSS directories: Program File File Name Convention* In an OSS directory specified by the user: SQLJ source file MyProg.sqlj In an OSS directory specified by the -dir command-line option and the package statement if present: Java source file MyProg.
Packaging the Program in a JAR File Program and Module Management Packaging the Program in a JAR File To use one program file for easier deployment and maintenance, consider packaging the SQLJ application in a JAR file. A JAR file of the SQLJ application must contain all the files necessary for execution, including all class files (.class) and profiles (.ser), as shown in Figure 6-1. Figure 6-1.
Program and Module Management Guidelines for Generating a JAR File Guidelines for Generating a JAR File Follow these guidelines for packaging an SQLJ application in a JAR file: • • • Listing Profiles in a Manifest File on page 6-6 Verifying the Program Files on page 6-6 Running the jar Tool on page 6-6 Listing Profiles in a Manifest File For the SQLJ run time and translator program to locate the profiles (.ser files) in a JAR file, the profiles must be listed in the manifest file of the JAR file.
Program and Module Management Customizing Profiles in a JAR File Customizing Profiles in a JAR File You can customize profiles (.ser files) in a JAR file—including those from another vendor—and optimize the profiles for the NonStop SQL/MX environment. The SQLJ customizer in SQL/MX adds SQL/MX customization to the profile and does not affect the customization of another vendor if it exists. For more information, see Customization Without Module Definition Files on page 1-13.
Customizing Profiles in a JAR File Program and Module Management Results of Customizing the Profiles in a JAR File If you customize profiles in a JAR file by using the default method of SQLJ processing, the customizer embeds the module definitions in the extended profiles within the JAR file. See Figure 6-2 on page 6-8. Figure 6-2. Customizing Profiles in a JAR File Without Producing Module Definition Files Uncustomized Profiles (.ser files) /usr/dev/myapps directory SQLJ Application with Profiles MyApp.
Program and Module Management Customizing Profiles in a JAR File Figure 6-3. Customizing Profiles in a JAR File and Producing Module Definition Files Uncustomized Profiles (.ser files) /usr/dev/myapps directory SQLJ Application with Profiles MyApp.jar META-INF/MANIFEST.MF pkg1/ pkg1/MyProg1.class pkg1/MyProg1_SJProfile0.ser pkg1/MyProg1_SJProfileKeys.class pkg2/ pkg2/MyProg2_SJProfile0.ser pkg2/MyProg2_SJProfileKeys.class java sqlj.tools.Sqlj -createMDF MyApp.
Program and Module Management Avoiding Possible Naming Conflicts Avoiding Possible Naming Conflicts Naming conflicts might occur when you customize an SQLJ program contained in a JAR file. If JAR files reside in the same directory and contain classes that share the same name, more than one profile within these JAR files will share the same name. When more than one profile has the same name, naming conflicts might occur among the module definition files and module files.
Program and Module Management Managing Program Files Managing Program Files You probably develop, test, and debug applications on a development or test system and then move the applications to a production system for actual use. On the development system, you would typically test and tune applications by using a database modeled after the database on the production system.
Program and Module Management Module Management Naming Module Management Naming Use module management naming to externally qualify the file names of modules to assist you with these development tasks: • • • Configuring applications to target different sets of database objects Managing different versions of an application Grouping the modules of an application To accomplish these tasks, you need not change the SQLJ source code or rely strictly on the MODULE directive or on module naming defaults.
Program and Module Management How Modules Are Named You specify these attributes externally on the SQLJ command line without changing the MODULE directive in the source file. The SQLJ customizer applies the catalog and schema and the target (or table set), version, and group attributes to the processed, three-part module name to create an externally qualified module name. For more information, see Options for Module Management on page 5-28 and Catalog and Schema of the Module Name on page 4-13.
How Modules Are Named Program and Module Management Figure 6-4. Module Name Length 1023-character maximum length 248-character maximum length 128-character maximum length /usr/tandem/sqlmx/USERMODULES/CatalogName.SchemaName.Group^ModuleName^Target^Version 128-character maximum length 128-character maximum length 31-character maximum length 128-character maximum length 31-character maximum length 31-character maximum length VST011.
Effect of Module Management Naming Program and Module Management Effect of Module Management Naming Table 6-1 lists the effects of combinations of group (MGSS), target or table set (MTSS), and version (MVSS) attributes on the module file name. Table 6-1. Module Management Naming Group Specified? TableSet Specified? Version Specified? Module Management Qualified Name Yes Yes Yes CAT.SCH.GRP^MOD^TABLESET^VER Yes Yes No CAT.SCH.GRP^MOD^TABLESET^ Yes No Yes CAT.SCH.
Program and Module Management Targeting If you specify an MTSS on the SQLJ command line, the customizer appends a leading circumflex (^) character and the MTSS to the processed, three-part module name. For example: CAT.SCH.GRP^MOD^TABLESET^VER If you do not specify an MTSS on the command line but do specify either a Module Version Specification String (MVSS) or a Module Group Specification String (MGSS), the customizer appends a trailing ^ to the module name to represent the unspecified MTSS.
Program and Module Management Versioning 3. The SQL/MX compiler produces modules with unique table-set names: /usr/tandem/sqlmx/USERMODULES/CAT.SCH.^MYPROGCTX1^TS1^ /usr/tandem/sqlmx/USERMODULES/CAT.SCH.^MYPROGCTX2^TS1^ To build another application from the same SQLJ source file that targets a different set of database objects: 1. Use the OSS add_define command to set up the class MAP DEFINEs for a different set of tables. For example: add_define =employee class=MAP file=\$SAMDB.PTPERSNL.EMPLOYEE 2.
Program and Module Management Versioning Effect of the Version Attribute The SQLJ customizer checks for the presence of a Module Version Specification String (MVSS), which is a regular or delimited identifier that you specify on the SQLJ command line. For information about how to specify an MVSS on the command line, see Options for Module Management on page 5-28. For information about identifiers, see the SQL/MX Reference Manual.
Program and Module Management Versioning Instructions To build a particular version of an application: 1. Use the OSS add_define command to set up any class MAP DEFINEs if they exist. For more information, see the SQL/MX Installation and Management Guide. 2. Run the SQLJ translator program and specify the -moduleVersion option on the command line: java sqlj.tools.Sqlj -d=mybin1 -dir=myjava1 -moduleVersion=VER1 MyProg.sqlj Note.
Program and Module Management Grouping Grouping All the modules that are generated on a particular NonStop system are stored in the /usr/tandem/sqlmx/USERMODULES directory, making it difficult to identify, or group, the modules that are associated with a particular application. By using the group attribute for module management, you can match module files to an application and perform basic file management tasks more easily.
Program and Module Management Grouping Instructions To group the modules of an application: 1. Run the SQLJ translator program and specify the -moduleGroup option on the command line: java sqlj.tools.Sqlj -d=mybin -dir=myjava -moduleGroup=MYGROUP MyProg.sqlj 2. The SQL/MX compiler produces modules with unique group names: /usr/tandem/sqlmx/USERMODULES/CAT.SCH.MYGROUP^MYPROGCTX1^^ /usr/tandem/sqlmx/USERMODULES/CAT.SCH.
Program and Module Management HP NonStop SQL/MX Programming Manual for Java—523726-003 6- 22 Grouping
A SQLJ Syntax This appendix provides a quick reference for the syntax of SQLJ as implemented in SQL/MX: • • • SQLJ Clause on page A-1 Host Expression on page A-6 Supported SQL Statements on page A-8 SQLJ Clause An SQLJ clause specifies an embedded SQLJ statement inside a Java application. An SQLJ clause begins with the characters #sql and is specified as one of these clauses: a connection declaration, an iterator declaration, or an executable clause.
Iterator Declaration Clause SQLJ Syntax Iterator Declaration Clause An iterator declaration clause generates an iterator class. An iterator is an object that contains the result of the execution of a query. An iterator is either positioned or named. An iterator class declaration can appear anywhere a Java class definition can appear. All iterator classes implement interface ResultSetIterator.
Iterator Declaration Clause SQLJ Syntax operation. For more information, see Positioned UPDATE and DELETE Statements on page 3-55 and sqlj.runtime.ForUpdate Interface on page B-5. with-clause determines the properties of the cursor, which underlies the iterator. sensitivity has no semantics in the current version of SQL/MX. If you specify the sensitivity, you must assign a with-value of ASENSITIVE, SENSITIVE, or INSENSITIVE.
Executable Clause SQLJ Syntax result set, and the Java types in the iterator declaration must be compatible with the positionally corresponding SQL column types in the result set. named-iterator specifies a list of Java identifiers preceded by their associated Java data types, where the pairs are separated by commas and the list is enclosed in parentheses: (Java-data-type Java-ID [,Java-data-type Java-ID]..) specifies the data types and names of the columns in the iterator.
Statement Clause SQLJ Syntax Statement Clause A statement clause contains an embedded SQL statement. statement-clause is: "{" SQL-statement "}" SQL-statement specifies one of the supported SQL/MX statements listed in Supported SQL Statements on page A-8. The SQL statement must be enclosed in curly braces ({}) and must not be terminated by a semicolon (;). For the syntax of the SQL statements, see the SQL/MX Reference Manual.
Host Expression SQLJ Syntax FOR UPDATE specifies that the iterator object to which the query expression is assigned is updatable. For more information, see Positioned UPDATE and DELETE Statements on page 3-55. Iterator Conversion Clause An iterator conversion clause converts a JDBC result set to an SQLJ iterator. iterator-conversion-clause is: CAST host-expression host-expression specifies a Java host variable or expression that contains a JDBC result set.
Host Expression SQLJ Syntax OUT specifies a host variable or expression that accepts data from an SQLJ clause. It is the default mode if the host expression is part of an INTO list or the lefthand part of an assignment expression. See Output Host Variables and Expressions (OUT or INOUT) on page 3-27. INOUT specifies a host variable or expression that accepts data from and passes data to an SQLJ clause. See Input and Output Modes on page 3-27. simple-variable is the identifier of a simple Java variable.
Supported SQL Statements SQLJ Syntax Supported SQL Statements These SQL/MX statements are allowed in SQLJ executable clauses. For the syntax of these statements, see the SQL/MX Reference Manual. You cannot embed SQL/MP statements in an SQLJ program. However, you can use SQL/MX statements to query SQL/MP database objects. For more information, see Name Resolution on page 4-20. If the portability of an SQLJ program is important, avoid embedding statements that are SQL/MX extensions to the ANSI standard.
Supported SQL Statements SQLJ Syntax Table A-1. Embedded SQL/MX Statements (page 2 of 7) SQL Statement Description More Information CONTROL QUERY DEFAULT statement Overrides the system-level default settings for the associated connection context. See CONTROL Statements on page 4-6. CONTROL QUERY SHAPE statement Forces execution plans by modifying the operator tree for a prepared statement. See CONTROL Statements on page 4-6.
Supported SQL Statements SQLJ Syntax Table A-1. Embedded SQL/MX Statements (page 3 of 7) ANSI ANSI SQL Statement Description More Information CREATE VIEW statement Creates an SQL/MX view. See Data Definition Language (DDL) Statements on page 4-32. DECLARE CATALOG statement Sets the default catalog for unqualified schema names in static SQL statements. See DECLARE and SET Statements for Object Name Qualification on page 4-16.
Supported SQL Statements SQLJ Syntax Table A-1. Embedded SQL/MX Statements (page 4 of 7) SQL Statement Description More Information ANSI DROP PROCEDURE statement Removes a stored procedure in Java (SPJ) from SQL/MX. See Data Definition Language (DDL) Statements on page 4-32. ANSI DROP SCHEMA statement Deletes an empty user schema. See Data Definition Language (DDL) Statements on page 4-32.
Supported SQL Statements SQLJ Syntax Table A-1. Embedded SQL/MX Statements (page 5 of 7) SQL Statement Description More Information LOCK TABLE statement Locks a table or underlying tables of a view and associated indexes. See the SQL/MX Reference Manual. Note: LOCK TABLE blocks the entire JVM process even when the Java environment is nonblocking (nowaited). MODULE Directive Specifies a module name to be used for the module file. See MODULE Directive on page 4-12.
Supported SQL Statements SQLJ Syntax Table A-1. Embedded SQL/MX Statements (page 6 of 7) ANSI ANSI SQL Statement Description More Information SET MPLOC statement Sets the default volume and subvolume for unqualified Guardian physical names of SQL/MP objects in dynamic SQL statements. See DECLARE and SET Statements for Object Name Qualification on page 4-16.
Supported SQL Statements SQLJ Syntax Table A-1. Embedded SQL/MX Statements (page 7 of 7) ANSI SQL Statement Description More Information UNREGISTER CATALOG statement Removes an empty catalog reference from a node. See Data Definition Language (DDL) Statements on page 4-32. UPDATE statement and positioned UPDATE statement Updates values in columns of a table or view. A positioned UPDATE statement with the WHERE CURRENT OF clause is used in a data retrieval loop for an iterator.
B SQLJ Runtime Package This appendix provides a quick reference for the interfaces and classes of the sqlj.runtime package, as implemented in SQL/MX. The sqlj.runtime package is based on the International Standard Database Language SQL—Part 10: Object Language Bindings (SQL/OLB) specification. This appendix describes only the interfaces and classes that SQL/MX supports and that you can code explicitly in an SQLJ application.
sqlj.runtime.ConnectionContext Interface SQLJ Runtime Package sqlj.runtime.ConnectionContext Interface public interface ConnectionContext The ConnectionContext interface includes a set of methods that manage SQL operations during a database session. For more information, see Connection Contexts on page 3-8. Table B-1. ConnectionContext Variables Summary Variable Description CLOSE_CONNECTION Specifies that the underlying JDBC Connection object be closed.
SQLJ Runtime Package ConnectionContext Method Details See Also • • KEEP_CONNECTION on page B-3 close(boolean) on page B-4 KEEP_CONNECTION public static final boolean KEEP_CONNECTION Specifies that the underlying JDBC Connection object remain open. For example, close(KEEP_CONNECTION) keeps the underlying JDBC Connection object open when it releases all resources for a specified connection context object.
SQLJ Runtime Package ConnectionContext Method Details close(boolean) public abstract void close(boolean closeConnection) throws SQLException Releases all resources for the specified connection context object and either closes the underlying JDBC Connection object or keeps the underlying JDBC Connection object open. Caution. Multiple connection context objects might share the same underlying JDBC Connection object.
SQLJ Runtime Package sqlj.runtime.ForUpdate Interface getExecutionContext() public abstract ExecutionContext getExecutionContext() Returns the default execution context object used by the specified connection context object. A connection context object uses a default, or implicit, execution context object if you do not specify an explicit execution context for a particular SQL statement. For more information, see Execution Contexts on page 3-20.
sqlj.runtime.PositionedIterator Interface SQLJ Runtime Package sqlj.runtime.PositionedIterator Interface public interface PositionedIterator extends ResultSetIterator Because the sqlj.runtime.PositionedIterator interface extends the sqlj.runtime.ResultSetIterator interface, it inherits all the methods in the ResultSetIterator interface. See sqlj.runtime.ResultSetIterator Interface on page B-7. Table B-3.
sqlj.runtime.ResultSetIterator Interface SQLJ Runtime Package sqlj.runtime.ResultSetIterator Interface public interface ResultSetIterator The ResultSetIterator interface includes a set of methods that manage the behavior of iterators and result sets in an SQLJ program. For more information, see Iterators and Result Sets on page 3-48. Table B-4.
SQLJ Runtime Package ResultSetIterator Method Details Throws • SQLException (if an error occurs while trying to close the iterator) See Also • isClosed() on page B-5 getResultSet() public abstract ResultSet getResultSet() throws SQLException Returns the JDBC ResultSet object associated with the specified iterator. For more information, see Creating a JDBC Result Set From an Iterator on page 3-57. For portability, invoke this method before invoking the first next() method on the iterator.
SQLJ Runtime Package ResultSetIterator Method Details isClosed() public abstract boolean isClosed() throws SQLException Determines if the iterator has been closed. Returns • Returns true if the iterator has been closed; otherwise, false Throws • SQLException (if an error occurs while determining the status of the iterator) See Also • close() on page B-7 next() public abstract boolean next() throws SQLException Advances the specified iterator to the next row.
sqlj.runtime.AsciiStream Class SQLJ Runtime Package sqlj.runtime.AsciiStream Class java.lang.Object | +----java.io.InputStream | +----java.io.FilterInputStream | +----sqlj.runtime.StreamWrapper | +----sqlj.runtime.AsciiStream public class AsciiStream extends StreamWrapper An InputStream-derived class that represents an input stream of bytes. The bytes of an AsciiStream object are interpreted as ASCII characters.
SQLJ Runtime Package AsciiStream Constructor Details AsciiStream Constructor Details AsciiStream(InputStream) public AsciiStream(InputStream in) Creates an ASCII-valued InputStream object with an uninitialized length. Set the length of the Java field by calling the setLength() method before passing an AsciiStream object to an IN or INOUT host variable of an SQL operation.
sqlj.runtime.ExecutionContext Class SQLJ Runtime Package sqlj.runtime.ExecutionContext Class java.lang.Object | +----sqlj.runtime.ExecutionContext public class ExecutionContext extends Object The ExecutionContext class includes a constructor to instantiate an ExecutionContext object, which you can associate with specific SQL operations. This class also provides methods to set the attributes of the execution context and to retrieve information about the results of executing an SQL statement.
SQLJ Runtime Package ExecutionContext Constructor Details ExecutionContext Constructor Details ExecutionContext() public ExecutionContext() Creates an ExecutionContext object, which you can associate with specific SQL operations. For more information, see Declaring and Instantiating an Execution Context Object on page 3-20.
SQLJ Runtime Package ExecutionContext Method Details getMaxRows() public synchronized int getMaxRows() Returns the maximum number of rows returned by a query using the specified ExecutionContext object. When the limit is exceeded, excess rows are dropped. By default, the maximum row limit is zero (unlimited).
SQLJ Runtime Package ExecutionContext Method Details setMaxFieldSize(int) public synchronized void setMaxFieldSize(int max) Sets the maximum Java field size, in bytes, to be returned for any column or output argument (OUT and INOUT parameters) of SQL operations using the specified ExecutionContext object. When the size limit is exceeded, excess data is discarded. By default, the maximum Java field size is zero (unlimited). For portability, use values greater than 256.
sqlj.runtime.CharacterStream Class SQLJ Runtime Package sqlj.runtime.CharacterStream Class java.lang.Object | +----java.io.Reader | +----java.io.FilterReader | +----sqlj.runtime.CharacterStream public class CharacterStream extends FilterReader A Reader-derived class that represents an input stream of bytes. The bytes of a CharacterStream object are interpreted as Unicode characters.
SQLJ Runtime Package CharacterStream Constructor Details CharacterStream(Reader, int) public CharacterStream(Reader in, int length) Creates a Unicode-valued CharacterStream object of a specified length.
SQLJ Runtime Package sqlj.runtime.ref.DefaultContext Class sqlj.runtime.ref.DefaultContext Class sqlj.runtime.ConnectionContext | +----sqlj.runtime.ref.ConnectionContextImpl | +----sqlj.runtime.ref.DefaultContext public class DefaultContext extends sqlj.runtime.ref.ConnectionContextImpl implements sqlj.runtime.ConnectionContext This class implements the ConnectionContext interface for a default connection context class.
SQLJ Runtime Package DefaultContext Method Details setDefaultContext(DefaultContext ctx) public static void setDefaultContext(DefaultContext ctx) Sets the default connection context object for the DefaultContext connection context class.
SQLJ Runtime Package DefaultContext Method Details HP NonStop SQL/MX Programming Manual for Java—523726-003 B -20
C Sample Programs This appendix presents sample programs that demonstrate basic SQLJ concepts, DDL and DML operations, multithreading, and publish/subscribe functionality. These programs include: • • • • • • • • • SampleDDL.sqlj—Creating Tables From an SQLJ Program on page C-2 SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data on page C-9 MultiThread1.sqlj—Threads With Explicit Execution Contexts on page C-13 MultiThread2.
SampleDDL.sqlj—Creating Tables From an SQLJ Program Sample Programs SQL/MX Release 1.8 sample database. To query SQL/MX tables, use the SQL/MX Release 2.x sample database. Note. The SQL/MX Release 2.x sample database uses SQL/MX format tables. To install the sample database, you must have a license to use SQL/MX DDL statements. To acquire this license, purchase product T0394. Without this product, you cannot install the sample database. An error message informs you that the system is not licensed.
SQLJ Source File Sample Programs Example C-1. SampleDDL.sqlj—Creating Tables From an SQLJ Program (page 2 of 7) /* The following constructor instantiates connection context objects * and establishes default database connections. * The try-catch block reports errors connecting to the database. */ public SampleDDL() { try { ctx1 ctx2 ctx3 #sql #sql #sql } = DefConCtx1.getDefaultContext(); = DefConCtx2.getDefaultContext(); = DefConCtx3.getDefaultContext(); [ctx1] { MODULE samdbcat2.persnl2.
SQLJ Source File Sample Programs Example C-1. SampleDDL.sqlj—Creating Tables From an SQLJ Program (page 3 of 7) /* The doCATSCH() method creates a catalog and schemas */ void doCATSCH() throws SQLException { // Creates a catalog #sql [ctx1] {CREATE CATALOG samdbcat2}; System.out.println("The SAMDBCAT2 catalog was created."); // Creates schemas #sql [ctx1] {CREATE SCHEMA samdbcat2.persnl2}; System.out.println("The PERSNL2 schema was created."); #sql [ctx2] {CREATE SCHEMA samdbcat2.sales2}; System.out.
SQLJ Source File Sample Programs Example C-1. SampleDDL.sqlj—Creating Tables From an SQLJ Program (page 4 of 7) #sql [ctx1] {ALTER TABLE employee_internatl ADD CONSTRAINT empnum_constrnt CHECK (empnum BETWEEN 0001 and 9999) }; System.out.println( "A constraint was added to the EMPLOYEE_INTERNATL table."); #sql [ctx1] {CREATE INDEX xempname_internatl ON employee_internatl ( last_name, first_name ) }; #sql [ctx1] {CREATE INDEX xempdept_internatl ON employee_internatl ( deptnum ) }; System.out.
SQLJ Source File Sample Programs Example C-1. SampleDDL.sqlj—Creating Tables From an SQLJ Program (page 5 of 7) #sql [ctx1] {ALTER TABLE dept_internatl ADD CONSTRAINT deptnum_constrnt CHECK (deptnum IN ( 1000, 1500, 2000, 2500, 3000, 3100, 3200, 3300, 3500, 4000, 4100, 9000 )) }; System.out.println( "Constraints were added to the DEPT_INTERNATL table.
SQLJ Source File Sample Programs Example C-1. SampleDDL.
Translating the Program Sample Programs Example C-1. SampleDDL.sqlj—Creating Tables From an SQLJ Program (page 7 of 7) /* * // // Set the default catalog and schema for a set of database objects to be created. */ #sql [ctx3] {DECLARE CATALOG 'SAMDBCAT2'}; #sql [ctx3] {DECLARE SCHEMA 'INVENT2'}; // Create tables in the schema // #sql [ctx3] {CREATE TABLE...}; // System.out.println("The ... table was created."); // #sql [ctx3] {CREATE TABLE...}; // System.out.println("The ... table was created.
SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data Sample Programs SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data The SampleDML program performs INSERT, DELETE, UPDATE, and SELECT operations. This SQLJ program uses tables in the SQL/MX sample database. To install the sample database, see the SQL/MX Quick Start. SQLJ Source File Example C-2. SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data (page 1 of 3) import java.sql.*; import sqlj.runtime.
SQLJ Source File Sample Programs Example C-2. SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data (page 2 of 3) // Throw an SQL exception if an error occurs // during execution catch (SQLException e) { System.err.
Translating the Program Sample Programs Example C-2. SampleDML.sqlj—Inserting, Deleting, Updating, and Selecting Data (page 3 of 3) /* Declare a positioned iterator class named PosIter. * The iterator declaration clause may not appear within * the method body.
Running the Program Sample Programs Running the Program At an OSS prompt, enter this command to run the SQLJ program: java SampleDML Results of Running the Program This output is returned to the screen when you run the SQLJ program: Sum of the salaries is 2838725.
MultiThread1.sqlj—Threads With Explicit Execution Contexts Sample Programs MultiThread1.sqlj—Threads With Explicit Execution Contexts The MultiThread1 program updates the price of a specified part in separate threads. To ensure the synchronization of the threads, the program uses explicit execution contexts. For more information, see Multithreading on page 3-23. This SQLJ program uses tables in the SQL/MX sample database. To install the sample database, see the SQL/MX Quick Start.
Translating the Program Sample Programs Example C-3. MultiThread1.sqlj—Threads With Explicit Execution Contexts (page 2 of 2) catch (SQLException se) { System.err.println("SQL exception: " + se); } catch (Exception e) { System.err.println("Exception: " + e); } } //Overriding the run() method public void run() { try { //Instantiating an explicit execution context object ExecutionContext execCtx = new ExecutionContext(); #sql [execCtx] { UPDATE samdbcat.sales.
Results of Running the Program Sample Programs Results of Running the Program This output is returned to the screen when you run the SQLJ program: Part number: 186 price: 186186.86 Part number: 212 price: 2500.0 Part number: 244 price: 3000.0 Part number: 255 price: 4000.0 Part number: 2001 price: 1100.0 Part number: 2002 price: 1500.0 Part number: 2003 price: 2000.0 Part number: 2402 price: 350.0 Part number: 2403 price: 650.0 Part number: 2405 price: 795.0 Part number: 3103 price: 4200.
MultiThread2.sqlj—Threads With Explicit Connection Contexts Sample Programs MultiThread2.sqlj—Threads With Explicit Connection Contexts The MultiThread2 program updates the price of a specified part in separate threads. To ensure the synchronization of the threads, the program uses explicit connection contexts. For more information, see Multithreading on page 3-23. This SQLJ program uses tables in the SQL/MX sample database. To install the sample database, see the SQL/MX Quick Start.
SQLJ Source File Sample Programs Example C-4. MultiThread2.sqlj—Threads With Different Connection Contexts (page 2 of 2) catch (SQLException se) { System.err.println("SQL exception: " + se); } catch (Exception e) { System.err.println("Exception: " + e); } } //Overriding the run() method public void run() { try { //Loading the JDBC/MX driver and creating a //JDBC connection Class.forName("com.tandem.sqlmx.SQLMXDriver"); Connection conn = DriverManager.
Translating the Program Sample Programs Translating the Program At an OSS prompt, enter this command to translate the SQLJ source file by running the SQLJ translator: java sqlj.tools.Sqlj -createMDF MultiThread2.sqlj Running the Program At an OSS prompt, enter this command to run the SQLJ program: java MultiThread2 Results of Running the Program This output is returned to the screen when you run the SQLJ program: Part number: 186 price: 186186.86 Part number: 212 price: 2500.
StreamParcels.sqlj—Stream Access Mode Sample Programs StreamParcels.sqlj—Stream Access Mode In this SQLJ program, an iterator with the holdability property accesses a regular database table as a continuous data stream. The iterator remains open when the transaction is committed and when a new transaction is started after each row is fetched. The stream access mode first does a regular scan of the table.
Translating the Program Sample Programs Example C-5. StreamParcels.sqlj—Stream Access Mode (page 2 of 2) // Wait for newly arrived parcels and notify receivers while (true) { #sql { FETCH :arrivals INTO :destination, :origin, :seqnbr }; System.out.println("Destination = " + destination); System.out.println("Origin = " + origin); System.out.println("Sequence number = " + seqnbr); System.out.
Results of Running the Program Sample Programs Results of Running the Program This output is returned to the screen when you run the SQLJ program: java StreamParcels Destination = 200 Origin = 100 Sequence number = 1 Destination = 233 Origin = 104 Sequence number = 6 Destination = 261 Origin = 103 Sequence number = 12 ... Destination = 201 Origin = 101 Sequence number = 3 Destination = 235 Origin = 102 Sequence number = 7 ...
StreamTimeout.sqlj—Stream Timeout Setting Sample Programs StreamTimeout.sqlj—Stream Timeout Setting In this SQLJ program, an iterator with the holdability property accesses a regular database table as a continuous data stream. The iterator remains open when the transaction is committed and when a new transaction is started after each row is fetched or after the stream times out. The stream access mode first does a regular scan of the table.
SQLJ Source File Sample Programs Example C-6. StreamTimeout.sqlj—Stream Timeout Setting (page 2 of 2) try { #sql { MODULE psdb.pubs.streamtimeoutmod }; #sql { BEGIN WORK }; /* Start a transaction */ // Set stream timeout to 5 seconds (5 hundredths // of a second) #sql { CONTROL QUERY DEFAULT STREAM_TIMEOUT '500' }; // Use an iterator to fetch parcels from a stream #sql arrivals = { SELECT destination, origin, seqnbr FROM STREAM(psdb.pubs.
Translating the Program Sample Programs Translating the Program At an OSS prompt, enter this command to translate the SQLJ source file by running the SQLJ translator: java sqlj.tools.Sqlj -createMDF StreamTimeout.
EmbeddedDelete.sqlj—Embedded DELETE Sample Programs EmbeddedDelete.sqlj—Embedded DELETE In this SQLJ program, an iterator with the holdability property dequeues rows from a stream using an embedded DELETE, which reads and deletes rows in a single operation. The program also uses a stream timeout setting of five seconds. For information about publish/subscribe functionality, see the SQL/MX Queuing and Publish/Subscribe Services manual. This SQLJ program uses tables in the shipping database.
Translating the Program Sample Programs Example C-7. EmbeddedDelete.sqlj—Embedded DELETE (page 2 of 2) catch (SQLException e) { // Continue after stream timeout error (-8006) // but handle other SQLExceptions in the outer // try-catch block. if (e.getErrorCode() != -8006) throw e; } // // // // if { Use the endFetch() method to see if a row was returned. endFetch() returns false when the last attempt to fetch a row succeeds. endFetch() returns true when the stream times out. (arrivals.
Results of Running the Program Sample Programs Results of Running the Program This output is returned to the screen when you run the SQLJ program: java StreamParcels Destination = 200 Origin = 100 Sequence number = 1 Destination = 233 Origin = 104 Sequence number = 6 Destination = 261 Origin = 103 Sequence number = 12 ... Destination = 201 Origin = 101 Sequence number = 3 Destination = 235 Origin = 102 Sequence number = 7 ...
EmbeddedUpdate.sqlj—Embedded UPDATE Sample Programs EmbeddedUpdate.sqlj—Embedded UPDATE In this SQLJ program, an iterator with the holdability property dequeues rows from a stream using an embedded UPDATE, which reads and updates rows in a single operation. Note that rows with the archive column set to ‘N’ are dequeued by setting the archive column to ‘Y’. The program also uses a stream timeout setting of five seconds.
Translating the Program Sample Programs Example C-8. EmbeddedUpdate.sqlj—Embedded UPDATE (page 2 of 2) catch { // // // if (SQLException e) Continue after stream timeout error (-8006) but handle other SQLExceptions in the outer try-catch block. (e.getErrorCode() != -8006) throw e; } // // // // if { Use the endFetch() method to see if a row was returned. endFetch() returns false when the last attempt to fetch a row succeeds. endFetch() returns true when the stream times out. (arrivals.
Results of Running the Program Sample Programs Results of Running the Program This output is returned to the screen when you run the SQLJ program: java StreamParcels Destination = 200 Origin = 100 Sequence number = 1 Destination = 233 Origin = 104 Sequence number = 6 Destination = 261 Origin = 103 Sequence number = 12 ... Destination = 201 Origin = 101 Sequence number = 3 Destination = 235 Origin = 102 Sequence number = 7 ...
SetOnRollback.sqlj—Setting Column Values on Rollback Sample Programs SetOnRollback.sqlj—Setting Column Values on Rollback In this SQLJ program, an iterator with the holdability property dequeues rows from a stream using an embedded DELETE. The program also uses a stream timeout setting of five seconds. The SET ON ROLLBACK clause causes the program to update a column on the rollback of the delete operation (that is, it marks rows that have been unsuccessfully dequeued).
Translating the Program Sample Programs Example C-9. SetOnRollback.sqlj—Setting Column Values on Rollback (page 2 of 2) // Wait for newly arrived parcels and notify receivers while (true) { try { #sql { FETCH :arrivals INTO :destination, :origin, :seqnbr }; } catch (SQLException e) { // Continue after stream timeout error (-8006) // but handle other SQLExceptions in the outer // try-catch block. if (e.
Running the Program Sample Programs Running the Program At an OSS prompt, enter this command to run the SQLJ program: java SetOnRollback Results of Running the Program This output is returned to the screen when you run the SQLJ program: java StreamParcels Destination = 200 Origin = 100 Sequence number = 1 Destination = 233 Origin = 104 Sequence number = 6 Destination = 261 Origin = 103 Sequence number = 12 ...
Sample Programs Results of Running the Program HP NonStop SQL/MX Programming Manual for Java—523726-003 C -34
D SQLJ Error Messages This appendix lists SQLJ error messages according to when the errors occur: • • SQLJ Processing Errors on page D-1 SQLJ Run-Time Errors on page D-90 SQLJ Processing Errors The SQLJ translator generates error messages during each phase of its operation.
SQLJ Error Messages Command-Line Options Processing (0000-000100— 0002-007100) 0000-000500 [0000-000500] unable to find input file name Cause. An error occurred while reading the input file name. Effect. SQLJ cannot translate the input file. Recovery. Report the entire message to your service provider. 0000-000800 [0000-000800] unknown option found in option-location: option-name Cause. You specified an unknown option option-name at option-location. Effect. SQLJ cannot process the option. Recovery.
SQLJ Error Messages Command-Line Options Processing (0000-000100— 0002-007100) Recovery. Report the entire message to your service provider. 0000-001200 [0000-001200] unexpected error occurred... Cause. An unexpected error occurred during translation. Effect. SQLJ cannot perform translation. Recovery. Report the entire message to your service provider. 0000-001300 [0000-001300] dir-name is not a directory Cause. An invalid directory name was set for the -dir or -d option. Effect.
SQLJ Error Messages Command-Line Options Processing (0000-000100— 0002-007100) 0000-001600 [0000-001600] use translator option -option-name rather than java compiler option: -C-option-name Cause. You entered either the -C-d or -C- encoding option. Effect. SQLJ does not support the processing of these options. See Disallowed Java Compiler Options on page 5-25. Recovery. Reissue the command with the translator option -d or -encoding. For details, see -d on page 5-18 and -encoding on page 5-22.
SQLJ Error Messages Command-Line Options Processing (0000-000100— 0002-007100) 0000-002000 [0000-002000] Unsupported file encoding Cause. You entered a file encoding that is not supported. Effect. SQLJ cannot perform translation. Recovery. Reissue the command with one of the supported encodings. For details, see -encoding on page 5-22. 0000-002100 [0000-002100] Exception caught: Cause. SQLJ received an exception. Effect. SQLJ cannot perform translation. Recovery.
SQLJ Error Messages SQLJ Syntax Checking (0100-000200—0100002800) 0002-007100 [0002-007100] SQL/MX compilation resulted in error verify sqlmx-compiler-executable name and path: string Cause. SQL compilation failed because SQLJ could not find the SQL/MX compiler or because there were problems during SQL compilation. Effect. The SQLJ translator fails during SQL compilation. Recovery.
SQLJ Error Messages SQLJ Syntax Checking (0100-000200—0100002800) Effect. SQLJ cannot parse the declaration clause. Recovery. Correct the syntax and resubmit. For details, see the Connection Declaration Clause on page A-1 or Iterator Declaration Clause on page A-2. 0100-000800 [0100-000800] Access modifiers mod1 and mod2 are not compatible. Cause. SQLJ encountered incompatible access modifiers in the connection or iterator declaration clause. Effect. SQLJ cannot parse the declaration clause. Recovery.
SQLJ Error Messages SQLJ Syntax Checking (0100-000200—0100002800) Recovery. Correct the syntax and resubmit. See the Iterator Declaration Clause on page A-2. 0100-001300 [0100-001300] Missing semicolon. Cause. A required semicolon (;) was missing in the SQLJ clause. Effect. SQLJ cannot parse the statement. Recovery. Correct the syntax and resubmit. See the SQLJ Clause on page A-1. 0100-001400 [0100-001400] Missing colon. Cause. A required colon (:) was missing in the SQLJ clause. Effect.
SQLJ Error Messages SQLJ Syntax Checking (0100-000200—0100002800) Effect. SQLJ cannot parse the statement. Recovery. Correct the syntax and resubmit. See the SQLJ Clause on page A-1. 0100-001800 [0100-001800] Unbalanced parenthesis. Cause. An unmatched parenthesis (( or )) was found in the SQLJ clause. Effect. SQLJ cannot parse the statement. Recovery. Correct the syntax and resubmit. See the SQLJ Clause on page A-1. 0100-001900 [0100-001900] Missing square bracket. Cause.
SQLJ Error Messages SQLJ Syntax Checking (0100-000200—0100002800) Effect. SQLJ cannot parse the statement. Recovery. Correct the syntax and resubmit. See the SQLJ Clause on page A-1. 0100-002400 [0100-002400] Illegal character in unicode escape sequence: 'character' Cause. SQLJ found an illegal Unicode character while reading the statement from SQLJ source file (.sqlj). Effect. SQLJ cannot read and parse the statement from the SQLJ source file. Recovery.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. SQLJ cannot parse the SQL statement. Recovery. Correct the syntax and resubmit. See the context-clause on page A-4. 0100-002800 [0100-002800] Invalid lvalue expression in assignment clause. Cause. SQLJ found an invalid expression on the left-hand side (LHS) of an assignment clause. For example, an iterator object was missing on the left-hand side of the equal sign (=). Effect. SQLJ cannot parse the SQL statement. Recovery.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-000700 [0201-000700] Duplicate method method. Cause. The SQLJ translator found a duplicate method method in an iterator class. Effect. The SQLJ translator fails during semantic analysis. Recovery. Report the entire message to your service provider. 0201-000702 [0201-000702] Duplicate methods method1 and method2. Cause. The SQLJ translator found duplicate methods in an iterator class. Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-000900 [0201-000900] Method name method is reserved by SQLJ. Cause. SQLJ encountered a method method that was the same as an iterator’s built-in method. Overloading an iterator’s built-in methods is disallowed. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit without overloading the iterator’s built-in methods. For iterator methods, see sqlj.runtime.ResultSetIterator Interface on page B-7 and sqlj.runtime.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-001306 [0201-001306] Type javatype of host item #num is not permitted in JDBC. This will not be portable. Cause. The data type of host variable #num was not a valid JDBC type. Effect. The SQLJ translator fails during semantic analysis. Recovery. Verify that the data type of host variable #num is a valid JDBC type and resubmit. For details, see the Data Type Support on page 3-32.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Verify that sqltype and javatype are compatible and resubmit. For details, see Data Type Support on page 3-32. If the error recurs, report the entire message to your service provider. 0201-001500 [0201-001500] Column javatype col is not compatible with database type sqltype Cause. The host variable type javatype and the database column type sqltype are incompatible.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-001702 [0201-001702] Unable to check SQL query. Error returned by database is: error-message Cause. SQLJ received an error message from the database while obtaining metadata information. Effect. The SQLJ translator fails during semantic analysis. Recovery. Examine the database error message. For details, see the SQL/MX Messages Manual. If the error recurs, report the entire message to your service provider.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Check the CAST cast-type and resubmit with the correct casting. For details, see Iterator Conversion Clause on page A-6 and Creating an Iterator From a JDBC Result Set on page 3-56. 0201-002100 [0201-002100] Unable to perform semantic analysis on connection connection by user username. Error returned by database is: error-message Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-002303 [0201-002303] No offline checker specified. Cause. The SQLJ translator could not find an offline checker. Effect. The SQLJ translator fails during semantic analysis. Recovery. Report the entire message to your service provider. 0201-002304 [0201-002304] No online checker specified for context context-name. Attempting to use offline checker instead. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Report the entire message to your service provider. 0201-002308 [0201-002308] Unable to obtain DatabaseMetaData to determine the online checker to use for context context-name. Attempting to use offline checker instead. Cause. The SQLJ translator received a JDBC error while trying to access information from a java.sql.DatabaseMetaData object.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Report the entire message to your service provider. 0201-005302 [0201-005302] Cannot load JDBC driver class driver-name. Cause. The SQLJ translator could not load the JDBC driver driver-name. Effect. The SQLJ translator fails during semantic analysis. Recovery. Verify that the JDBC driver exists. See Verifying the JDBC/MX Driver on page 2-3. If the error recurs, report the entire message to your service provider.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Check the modifier in the declaration clause and resubmit with the correct modifier. For details, see Connection Declaration Clause on page A-1 or Iterator Declaration Clause on page A-2. 0201-006500 [0201-006500] Illegal entry for option key-option Expected a boolean value, received: "string" Cause. You entered a non-Boolean value for a Boolean option. Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-006800 [0201-006800] Illegal INTO ... bind variable list: message. Cause. SQLJ encountered an illegal host variable in the INTO clause of a FETCH statement. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with the correct number and sequence of host variables in the INTO clause. For details, see Retrieving Rows From a Positioned Iterator on page 3-49.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Confirm that you have the correct JDBC driver and can retrieve metadata from JDBC properly and then resubmit. See Verifying the JDBC/MX Driver on page 2-3. If the error recurs, report the entire message to your service provider. 0201-006901 [0201-006901] Unable to obtain parameters and result columns description of stored function or procedure: routine-name. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-007002 [0201-007002] Syntax (, , ...) is illegal. Only two context descriptors are permitted. Cause. You specified more than one connection context or more than one execution context in a context clause. You must specify one connection context and one execution context in a context clause. For example, this statement is incorrect: #sql [ctx, execCtx, ctx1] {/* SQL statement */}; Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-007102 [0201-007102] Connection context must have been declared with #sql context ... It can not be declared as a ConnectionContext. Cause. A connection context in a context clause was not declared with a connection declaration clause. For example, you did not use a connection declaration clause, like this one, to generate a connection context class: #sql context SQLMXCtx; Effect. The SQLJ translator fails during semantic analysis.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis phase. Recovery. Verify that the host variable name is a valid Java type and resubmit. For details, see Data Type Support on page 3-32. 0201-007400 [0201-007400] Invalid Java type for host item #num: type. Cause. The Java type type of host variable #num was not a valid Java type Effect. The SQLJ translator fails during semantic analysis. Recovery.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Verify the access modifier of the class definition for type type, make any corrections, and resubmit. 0201-00740203Into [0201-00740203Into] Type type of INTO-list item name is not publicly accessible. Cause. The Java type type of host variable name in the INTO clause of a FETCH statement was not publicly accessible. Effect. The SQLJ translator fails during semantic analysis. Recovery.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-00740405Out [0201-00740405Out] This type is not legal as an OUT argument. Cause. You specified a type that was not a legal OUT argument. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with the correct OUT argument. For details, see Output Assignability on page 3-35. 0201-007405 [0201-007405] Unsupported Java type for host item name (at position #num): type. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-007410 [0201-007410] Invalid Java type for item #num of INTO-list: type. Cause. You specified an invalid Java type type for host variable #num in the INTO clause of a FETCH statement. Effect. The SQLJ translator fails during semantic analysis. Recovery. Declare the host variable with a valid data type and resubmit. For details, see Data Type Support on page 3-32.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with the correct FETCH syntax. For the syntax of a FETCH statement, see the SQL/MX Reference Manual. 0201-007415 [0201-007415] Type mismatch in argument #num of INTO-list. Expected: expected-type Found: specified-type Cause. There was a type mismatch between the Java type of the host variable in the INTO clause and the SQL type of the database column.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-007700 [0201-007700] Expected end of FETCH statement. Encountered: "specified-token" Cause. The syntax of the FETCH statement was incorrect, or there was no colon (:) before a host variable in the INTO clause of the FETCH statement. Effect. The SQLJ translator fails during semantic analysis. Recovery. Correct the syntax of the FETCH statement or prefix each host variable in the FETCH statement with a colon and resubmit.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Declare the iterator variable with a valid iterator type and resubmit. For details, see Iterators and Result Sets on page 3-48. For the syntax of a FETCH statement, see the SQL/MX Reference Manual. 0201-008100 [0201-008100] INTO-lists may only occur in SELECT and FETCH statements. Cause. You specified an INTO clause in an SQL statement other than SELECT and FETCH, which is disallowed. Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-008401 [0201-008401] SQL checking did not assign mode for host variable name (at position #num) - assuming IN. Cause. The SQL checker determined that the mode of the host variable name at position #num is UNKNOWN, so SQLJ assumes the IN mode. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with a host variable that has the proper mode associated with it. For details, see Host Expression on page A-6.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with either an iterator or host variable. For details, see the Assignment Clause on page A-5 or the SQL/MX Reference Manual for the SELECT INTO statement. 0201-008800 [0201-008800] SQL statement does not return a value. Cause. You tried to retrieve data from an SQL statement that does not return any values.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-009800 [0201-009800] No ";" permitted after stored procedure/function call. Cause. You specified a semicolon (;) after a stored procedure inside curly braces ({}), which is disallowed. For example: #sql {CALL foo(); }; Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with the correct CALL syntax. See Calling Stored Procedures in Java on page 3-59.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Check the argument modes of the stored procedure and resubmit with the correct modes. See Calling Stored Procedures in Java on page 3-59. 0201-010101 [0201-010101] Assuming IN mode for host item name (at position #num). Cause. The SQLJ translator determined that the DatabaseMetaData information for the stored procedure contains UNKNOWN mode for the host variable name at position #num, so SQLJ assumes the IN mode. Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Check the name of the stored procedure, verify that it exists in the database, and resubmit. For details, see the SQL/MX Guide to Stored Procedures in Java. 0201-010400 [0201-010400] Do not know how to analyze this SQL statement. Cause. SQLJ did not know the operation code for this SQL statement. Effect. The SQLJ translator fails during semantic analysis. Recovery.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Resubmit by using host variables for each OUT and INOUT parameter. See Calling Stored Procedures in Java on page 3-59. 0201-011000 [0201-011000] Argument #num of routine-name requires mode OUT. Cause. You used either the IN or INOUT mode for argument #num of routine-name, but the stored procedure requires the OUT mode. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with the OUT mode.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Verify that the stored procedure you are trying to call exists in the database and resubmit. See the SQL/MX Guide to Stored Procedures in Java. 0201-011500 [0201-011500] Did not find a stored procedure or function routine-name with num arguments. found-string Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-011600 [0201-011600] Did not find stored procedure name with num arguments. Cause. SQLJ could not find the stored procedure name with num arguments. Effect. The SQLJ translator fails during semantic analysis. Recovery. Verify that the stored procedure you are trying to call exists in the database and resubmit. See the SQL/MX Guide to Stored Procedures in Java.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-012300 [0201-012300] Unrecognized SET TRANSACTION syntax. Cause. The SQLJ translator encountered unsupported syntax in a SET TRANSACTION statement. For example, you tried to specify a diagnostics area in a SET TRANSACTION statement. Effect. The SQLJ translator fails during semantic analysis. Recovery. Correct the syntax and resubmit. For details, see Setting Attributes for Transactions on page 4-43.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Resubmit with valid syntax for the CALL statement. For details, see the Statement Clause on page A-5. 0201-012800 [0201-012800] No INTO variable for column #num: "col" type Cause. You did not specify a host variable in the INTO clause of a FETCH statement for column #num. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with a host variable for this column in the INTO clause of the FETCH statement.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-013100 [0201-013100] Select list has only total-num elements. Column col #num is not available. Cause. An iterator declaration clause or the INTO clause of a SELECT statement specified more column values than in the select list. Effect. The SQLJ translator fails during semantic analysis. Recovery.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-013600 [0201-013600] Expected host variable of type java.sql.ResultSet, found "string" ... Cause. You did not specify a host variable of type java.sqlj.ResultSet in the SQLJ CAST statement. The SQLJ translator found string instead. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit by providing a host variable of the type java.sql.ResultSet in the CAST statement.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit by providing a host variable of the type java.sql.ResultSet in the CAST statement. For details, see the Iterator Conversion Clause on page A-6 and Creating an Iterator From a JDBC Result Set on page 3-56. 0201-014000 [0201-014000] Expected cast to be assigned to an iterator. Cause. You did not assign the SQLJ CAST statement to an iterator variable. Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-015100 [0201-015100] Value of iterator attribute attrname must be a boolean. Cause. You assigned a non-Boolean value to the with attribute named attrname, which is disallowed in an iterator declaration clause. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with a Boolean value for the with attribute named attrname. For details, see the with-clause on page A-3.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Recovery. Verify the with attribute in the iterator declaration clause and resubmit with a valid attribute name. For details, see the Iterator Declaration Clause on page A-2. 0201-015402 [0201-015402] ConnectionContext attribute attrname is not defined in the SQLJ specification. Cause. You specified an unsupported with attribute named attrname in a connection declaration clause. Effect. The SQLJ translator fails during semantic analysis.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with an output host variable or expression for the host variable #num. For details, see Host Variables and Expressions on page 3-27 and Calling Stored Procedures in Java on page 3-59. 0201-015900 [0201-015900] Expected stored procedure name. Found: found-string Cause. You did not specify the stored procedure name after the CALL keyword. Effect.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-018000 [0201-018000] ConnectionContext class-name is an inner class of super-class and must be declared static. Cause. You declared the connection context class-name inside the super-class definition. If you want the connection context class-name to be the inner class of super-class, you must declare class-name as static. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit by adding the static modifier.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-018400 [0201-018400] identifier found after ROLLBACK [WORK]. Cause. SQLJ encountered an identifier after the ROLLBACK WORK statement. Effect. The SQLJ translator fails during semantic analysis. Recovery. Correct the syntax and resubmit. For details, see Transaction Control Statements on page 4-41. 0201-018500 [0201-018500] Compound Statements are not supported by SQLJ runtime. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-019000 [0201-019000] ShowShape is not supported by SQLJ. Cause. You embedded a SHOWSHAPE command, which SQLJ does not support. Effect. The SQLJ translator fails during semantic analysis. Recovery. Remove the SHOWSHAPE command from the SQLJ source file and resubmit. See Supported SQL Statements on page A-8. 0201-019200 [0201-019200] Argument position-num type is not compatible with database type data-type. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-019303 [0201-019303] Unexpected string found in MODULE statement. Cause. The SQLJ translator encountered an unexpected string in the MODULE directive. Effect. The SQLJ translator fails during semantic analysis. Recovery. Correct the syntax of the MODULE directive and resubmit. For details, see the SQL/MX Reference Manual and MODULE Directive on page 4-12.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-019403 [0201-019403] No value set for DECLARE declare-type. Cause. You did not specify a value for the DECLARE statement. Effect. The SQLJ translator fails during semantic analysis. Recovery. Resubmit with a valid value for the DECLARE statement. For details, see the SQL/MX Reference Manual. 0201-019404 [0201-019404] DECLARE requires SQL Identifier and value 'value' is not allowed. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-019700 [0201-019700] SQLJ does not support the BEGIN DECLARE statement. Cause. You embedded a BEGIN DECLARE SECTION statement, which SQLJ does not support. Effect. The SQLJ translator fails during semantic analysis. Recovery. Remove the BEGIN DECLARE SECTION statement from the SQLJ source file and resubmit. See Supported SQL Statements on page A-8. 0201-019800 [0201-019800] SQLJ does not support ALLOCATE statements. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-020100 [0201-020100] SQLJ does not support the DESCRIBE statement. Cause. You embedded a DESCRIBE statement, which SQLJ does not support. Effect. The SQLJ translator fails during semantic analysis. Recovery. Remove the DESCRIBE statement from the SQLJ source file and resubmit. See Supported SQL Statements on page A-8. 0201-020200 [0201-020200] SQLJ does not support the END statement. Cause.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-020500 [0201-020500] SQLJ does not support GET statements. Cause. You embedded a GET statement, which SQLJ does not support. Effect. The SQLJ translator fails during semantic analysis. Recovery. Remove the GET statement from the SQLJ source file and resubmit. See Supported SQL Statements on page A-8. 0201-020600 [0201-020600] SQLJ does not support the OPEN statement. Cause. You embedded an OPEN statement, which SQLJ does not support.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-020900 [0201-020900] Missing argument in procedure call. There are adjacent commas. Cause. SQLJ could not find an argument in the CALL statement. For example, you specified an extra comma in the argument list or forgot to specify the name of an argument: #sql {CALL foo(IN1,OUT1,)}; Effect. The SQLJ translator fails during semantic analysis. Recovery. Correct the syntax and resubmit with the proper arguments.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Use a supported character set in the MODULE directive. For details, see the SQL/MX Reference Manual and MODULE Directive on page 4-12. 0201-021300 [0201-021300] name is a reserved word and cannot be used as a module name Cause. The MODULE directive contained a reserved SQL keyword as part of the module name. For example, you specified: #sql {MODULE cat.sch.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) 0201-021600 [0201-021600] Invalid syntax in Module statement, missing character set specifier Cause. The MODULE directive was missing the character set specifier in the NAMES ARE clause. For example, you specified: #sql {MODULE cat.sch.mod NAMES ARE}; Effect. The SQLJ translator fails during semantic analysis. Recovery. Correct the syntax of the MODULE directive. For details, see the SQL/MX Reference Manual.
SQLJ Error Messages Semantic Analysis (0201-000100—0201-022306) Effect. The SQLJ translator fails during semantic analysis. Recovery. Specify the IN mode for the host variable and retranslate the program. See Host Expression on page A-6. 0201-022302 [0201-022302] Host item name (at position #num) requires mode IN. Cause. You did not specify the IN mode for the host variable name at position num. Effect. The SQLJ translator fails during semantic analysis. Recovery.
SQLJ Error Messages Profile-to-Class Conversion (0300-000100—0300001500) 0201-022306 [0201-022306] Host item name (at position #num) requires mode INOUT. Cause. You did not specify the INOUT mode for the host variable name at position num. Effect. The SQLJ translator fails during semantic analysis. Recovery. Specify the INOUT mode for the host variable at position num and retranslate the program. See Host Expression on page A-6.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0300-001500 [0300-001500] unable to move old-profile-name to new-profile-name Cause. SQLJ received an error while trying to rename the profile. Effect. SQLJ cannot rename the profile. Recovery. Report the entire message to your service provider. Java Syntax Checking (0500-000400—0500-005000) These messages occur during the verification of the Java syntax during translation.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) Effect. SQLJ cannot parse the statement. Recovery. Confirm that the data type is an array and resubmit. 0500-001100 [0500-001100] Ambiguous constructor invocation. Cause. You tried to invoke a constructor for which more than one constructor is available. Effect. SQLJ cannot parse the statement. Recovery. Report the entire message to your service provider. 0500-001200 [0500-001200] Ambiguous field access. Cause.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-001500 [0500-001500] Array index must be a numeric type. Cause. You tried to use a non-numeric value to index a context or iterator array, which is disallowed. Effect. SQLJ cannot parse the statement. Recovery. Verify that the array index is a numeric value and resubmit. 0500-001600 [0500-001600] Type cast operator requires non-void operand. Cause. You tried to cast using a void operand. Effect. SQLJ cannot parse the statement.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-001900 [0500-001900] Boolean operator requires boolean operands. Cause. You tried to use a Boolean operator with one or more non-Boolean operands. The operands for a Boolean operator must be the Boolean type. Effect. SQLJ cannot parse the statement. Recovery. Check the operands of Boolean operator and resubmit with the correct operand types. 0500-002000 [0500-002000] Comparison operator requires numeric operands. Cause.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-002300 [0500-002300] Conditional expression result types must match. Cause. The second and third operands in the ternary operator are not compatible. Effect. SQLJ cannot parse the statement. Recovery. Verify that the second and third operands are compatible and resubmit. 0500-002400 [0500-002400] Constructor not found. Cause. You tried to invoke a context or iterator constructor that does not exist. Effect.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-002700 [0500-002700] Instanceof operator requires an object reference operand. Cause. You tried to use an instanceof operator with a primitive data type. However, the instanceof operator requires an object reference operand. Effect. SQLJ cannot parse the statement. Recovery. Verify that the instanceof operator has an object reference operand and resubmit. 0500-002800 [0500-002800] Invalid type cast Cause.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-003100 [0500-003100] Name 'string' cannot be used as an identifier. Cause. The string used was not a valid identifier. Effect. SQLJ cannot parse the statement. Recovery. Resubmit with a valid identifier. 0500-003200 [0500-003200] Negation operator requires boolean operand. Cause. You tried to perform a Boolean negation operation on a non-Boolean operand. Effect. SQLJ cannot parse the statement. Recovery.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) Effect. SQLJ cannot parse the statement. Recovery. Report the entire message to your service provider. 0500-003600 [0500-003600] Unknown identifier 'string'. Cause. The field or method that you searched for could not be located. The method or field might not exist. Effect. SQLJ cannot parse the statement. Recovery. Report the entire message to your service provider 0500-003700 [0500-003700] Unknown identifier. Cause.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-004000 [0500-004000] Initialization lists are not allowed in bind expressions. Cause. You tried to use initialization lists, such as array initialization, which are disallowed in bind expressions. Effect. SQLJ cannot parse the statement. Recovery. Check the bind expression and resubmit without initialization lists. 0500-004100 [0500-004100] Anonymous classes are not allowed in bind expressions. Cause.
SQLJ Error Messages Java Syntax Checking (0500-000400—0500005000) 0500-004500 [0500-004500] illegal expression Cause. SQLJ encountered a NullPointerException while parsing an expression Effect. SQLJ cannot parse the statement. Recovery. Check the expression and resubmit. If the error recurs, report the entire message to your service provider. 0500-004600 [0500-004600] IN mode is not allowed for INTO-variables. Cause.
SQLJ Error Messages Customization (0600-001100—0600-004200) 0500-004900 [0500-004900] Jammed token - remove and reinsert to other slot. Cause. SQLJ encountered an unexpected jammed token in the statement. Effect. SQLJ cannot parse the statement. Recovery. Report the entire message to your service provider. 0500-005000 [0500-005000] Unterminated comment. Cause. You entered an unterminated comment, possibly a missing */ at the end of the comment. Effect. SQLJ cannot parse the statement. Recovery.
SQLJ Error Messages Customization (0600-001100—0600-004200) 0600-002000 [0600-002000] list item value may not be empty Cause. You specified incorrect syntax for the listed items. Effect. Customization might fail. Recovery. Correct the syntax of the listed items and resubmit. See the SQLJ Translator Command Line on page 5-10. 0600-002200 [0600-002200] no customizer specified Cause. The specified customizer could not be found. Effect. Customization fails. Recovery.
SQLJ Error Messages Customization (0600-001100—0600-004200) 0600-002500 [0600-002500] error loading customizer harness Cause. SQLJ could not load the SQL/MX customizer harness. Effect. Customization fails. Recovery. Report the entire message to your service provider. 0600-003100 [0600-003100] name: unknown option type Cause. You specified an unknown option type name. Effect. Customization fails. Recovery. Remove the option and resubmit.
SQLJ Error Messages Customization (0600-001100—0600-004200) Effect. Customization fails. Recovery. Remove or correct the option and resubmit. For details, see the Summary of SQLJ Command-Line Options on page 5-11. 0600-003600 [0600-003600] unable to remove file name Cause. The customizer could not remove the specified file, possibly because you do not have permission to delete the file. Effect. Customization fails. Recovery. Check the file permissions and resubmit.
SQLJ Error Messages Java Code Generation (0700-000100—0700001600) Recovery. Re-create or repair the JAR file. For details, see Packaging the Program in a JAR File on page 6-5. 0600-004000 [0600-004000] name: invalid profile name Cause. You specified a profile name that is invalid. Effect. Customization fails. Recovery. Correct the profile name on the command line, in a properties file, or in the manifest file of a JAR file that contains the profile.
SQLJ Error Messages Java Code Generation (0700-000100—0700001600) Recovery. Report the entire message to your service provider. 0700-000100 [0700-000100] name cannot be constructed as an iterator Cause. During code generation, SQLJ could not resolve an iterator constructor. Effect. The SQLJ translator fails to generate a Java source file (.java) during code generation. Recovery. Report the entire message to your service provider. 0700-000200 [0700-000200] iterator implements both sqlj.runtime.
SQLJ Error Messages Java Code Generation (0700-000100—0700001600) Effect. The SQLJ translator fails to generate a Java source file (.java) during code generation. Recovery. Report the entire message to your service provider. 0700-000500 [0700-000500] unable to generate execution code (ClassNotFound) Cause. The SQLJ translator could not generate code because it could not find information for one of these types of classes: context, iterator, or ProfileKeys. Effect.
SQLJ Error Messages Java Code Generation (0700-000100—0700001600) Effect. The SQLJ translator fails during code generation. Recovery. Report the entire message to your service provider. 0700-000900 [0700-000900] unable to generate code for FETCH statement: cannot fetch from non-cursor Cause. The SQLJ translator could not generate code for a FETCH statement because you specified a non-iterator object in the statement. Effect. The SQLJ translator fails during code generation. Recovery.
SQLJ Error Messages Java Code Generation (0700-000100—0700001600) Effect. The SQLJ translator fails during code generation. Recovery. Specify a host variable whose type is compatible with the data being selected. For details, see Data Type Support on page 3-32. 0700-001300 [0700-001300] unable to generate code for SELECT statement: incompatible lval found in exec Cause.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) 0700-001600 [0700-001600] unable to generate parameter info: no lval found in exec Cause. The SQLJ translator could not generate code for a SELECT statement because it could not find an output host variable in the statement. Effect. The SQLJ translator fails during code generation. Recovery. Report the entire message to your service provider.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) Effect. The customizer issues a warning and ignores the unsupported value. Customization completes successfully. Cause. Specify a supported value for the WITH clause element to avoid the warning. For details, see the Iterator Declaration Clause on page A-2. 0900-000400 [0900-000400] Iterator iterator-name: updateColumns apply to iterators implementing sqlj.runtime.ForUpdate; updateColumns ignored. Cause.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) Effect. Customization fails. Recovery. Report the entire message to your service provider. 0900-000800 [0900-000800] Cause. An SQL/MX error occurred. Effect. Customization fails. Recovery. Report the entire message to your service provider. 0900-000900 [0900-000900] No Corresponding SQL/MX type for non-SQLJ Core java type: java-type. Cause. You specified a Java data type java-type that has no corresponding SQL data type.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) Effect. The entry is not customized. Recovery. Use an appropriate Java type for conversion to the specified SQL data type. For details, see Data Type Support on page 3-32. 0900-001300 [0900-001300] No known SQL/MX type mapping to JDBC type javatype. Cause. The specified JDBC type java-type could not be mapped to an SQL data type. Effect. The entry is not customized. Recovery. Use a convertible JDBC type.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) 0900-001600 [0900-001600] Result column count: result-count; expected count: expected-count. Cause. The result column count result-count did not provide the expected number of columns expected-count. Effect. The entry is not customized. Recovery. Use the appropriate number of result set columns. For details, see Iterators and Result Sets on page 3-48. 0900-001700 [0900-001700] Unsupported Feature: SET Statements may only be SET TRANSACTION.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) 0900-002000 [0900-002000] Internal Error: removeSqlComments scan lockup @location in comment Cause. The comment was incorrectly formed, or there was an internal parsing error. Effect. The entry is not customized. Recovery. Correct any problems with the comment text and resubmit. See Comments on page 3-3. If the problem recurs, report the entire message to your service provider.
SQLJ Error Messages Data Customization (0900-000100—0900-003000) 0900-002600 [0900-002600] Module Version Specification String string given is not a Regular Identifier, or is longer than 31 characters Cause. You specified an invalid Module Version Specification String (MVSS). Effect. Customization fails. Recovery. Use an appropriate MVSS and resubmit. For details, see -moduleVersion on page 5-30 and Module Name Length on page 6-13.
SQLJ Error Messages SQLJ Customizer (1000-000200—1000-000700) 0900-003000 [0900-003000] OSS file name name cannot be longer than 248 characters. Cause. You specified an OSS path name name that is too long. Effect. Customization fails. Recovery. Use an appropriate OSS file name and resubmit. For more information, see the Open System Services User’s Guide. SQLJ Customizer (1000-000200—1000-000700) These messages occur during the customization of the profile and the creation of the module definition.
SQLJ Error Messages SQLJ Customizer (1000-000200—1000-000700) Effect. Customization fails. Recovery. Use the valid name of an existing catalog and translate and/or customize the program again. For details, see -catalog on page 5-30, -moduleCatalog on page 5-28, and Module Name Length on page 6-13. 1000-000500 [1000-000500] value: Invalid value for moduleSchema or schema option. Cause. You specified an invalid value for the -schema or -moduleSchema option on the command line or in a properties file.
SQLJ Run-Time Errors SQLJ Error Messages SQLJ Run-Time Errors Run-time errors occur when you execute an SQLJ program and include: • • • SQL/MX database errors returned through JDBC to the SQLJ run time JDBC/MX errors returned to the SQLJ run time SQLJ run-time errors as defined by the SQLJ standard This subsection describes only the SQLJ run-time error messages. For descriptions of SQL/MX error messages, see the SQL/MX Messages Manual.
SQLJ Error Messages SQLJ Run-Time Messages SQLSTATE 02000 no rows found for select into statement Cause. You tried to select a row into a host variable, but there is no row in the table. Effect. SQLJ cannot execute this statement. Recovery. Verify that a single row exists in the table for the INTO clause and resubmit. SQLSTATE 08000 found null execution context Cause. The SQLJ run time found a Null execution context. Effect. SQLJ cannot execute this statement. Recovery.
SQLJ Error Messages SQLJ Run-Time Messages SQLSTATE 08003 found null connection context Cause. The SQLJ run time found a Null connection context. Effect. SQLJ cannot execute this statement. Recovery. Report the entire message to your service provider. SQLSTATE 21000 multiple rows found for select into statement Cause. You tried to select a single row into a host variable using the INTO clause, but multiple rows fit the selection criteria. Effect. SQLJ cannot execute this statement. Recovery.
SQLJ Error Messages SQLJ Run-Time Messages SQLSTATE 46110 Unable to create CallableStatement for RTStatement Cause. A JDBC Callable statement was unavailable for this statement. Effect. SQLJ cannot execute this statement. Recovery. Report the entire message to your service provider. SQLSTATE 46110 Unable to create PreparedStatement for RTStatement Cause. A JDBC prepared statement was unavailable for this method. Effect. SQLJ cannot execute this statement. Recovery.
SQLJ Error Messages SQLJ Run-Time Messages Effect. SQLJ cannot execute this statement. Recovery. Verify that the iterator declaration clause implements ForUpdate. For details, see the Iterator Declaration Clause on page A-2. If the error recurs, report the entire message to your service provider. SQLSTATE 46130 expected statement 'statement-name' to be created using prepareCall Cause. The SQLJ run time expected this statement type to be CALLABLE_STATEMENT in the profile entry, but it was not.
SQLJ Error Messages SQLJ Run-Time Messages SQLSTATE 46130 expected statement 'statement-name' to use 0 (zero) parameters, found num-params Cause. The SQLJ run time expected this statement to contain no parameters in the profile entry but found parameters in the profile. Effect. SQLJ cannot execute this statement. Recovery. Report the entire message to your service provider. SQLSTATE 46130 profile profile-name not found: exception Cause. The SQLJ run time could not find the profile profile-name.
SQLJ Error Messages SQLJ Run-Time Messages Recovery. Verify that the number of columns in the iterator declaration clause is the same as the number of columns in the select list and resubmit. For details, see the Iterator Declaration Clause on page A-2. Message Without an SQLSTATE Value invalid descriptor: descriptor Cause. The SQLJ run time encountered an invalid descriptor in the profile. Effect. SQLJ cannot execute this statement. Recovery. Report the entire message to your service provider.
SQLJ Error Messages SQLJ Run-Time Messages Message Without an SQLSTATE Value invalid role: role Cause. The SQLJ run time encountered an invalid role in the profile. Effect. SQLJ cannot execute this statement. Recovery. Report the entire message to your service provider. Message Without an SQLSTATE Value invalid statement type: statement-type Cause. The SQLJ run time encountered an invalid statement type in the profile. Valid statement types are CALLABLE_STATEMENT and PREPARE_STATEMENT. Effect.
SQLJ Error Messages SQLJ Run-Time Messages HP NonStop SQL/MX Programming Manual for Java—523726-003 D -98
Index A Accessor methods 3-53 Alias name -e 5-22 -v 5-20 all flag 5-21 ALTER SQLMP ALIAS statement 4-38 ANSI names, SQL/MX objects 4-20 Application management 6-11 AS clause 3-52 ASENSITIVE 4-46, A-3 Assignment clause A-5 Autocommit 4-43 autoCommit property 3-14 Automatic hash partitioning 4-37 Automatic recompilation description of 4-29 OLTP settings 4-30 AUTOMATIC_RECOMPILATION default attribute 4-30 B BEGIN WORK statement 4-46 Binary portability 1-2 C CALL statement 3-59 cancel() method 3-22, B-13 CAST
D Index Command-line options (continued) SQLJ customizer 5-26 SQLJ translator 5-21 SQL/MX compiler 5-32 usage guidelines 5-6 Comments 3-3 COMMIT WORK statement 4-47 Comparison SQLJ and embedded SQL in C or COBOL 1-2 SQLJ and JDBC 1-4 Compilation See Java compilation See SQL compilation Compile-time name resolution 4-23 com.tandem.sqlmx.
E Index Database object names (continued) qualifying in the code 4-16 DataSource connection, support for 3-6 Date-time data types 3-38 DDL statements 4-32 Deadlocks, avoiding 3-23 Declaration clauses 3-2 DECLARE CATALOG statement 4-16 DECLARE MPLOC statement 4-16 DECLARE NAMETYPE statement 4-16 DECLARE SCHEMA statement 4-16 DECLARE statements, static behavior 4-16 Default connection context name 3-8 using 3-11 Default customization 1-12 Default run time 1-21, 4-2 DEFINE names description of 4-21 OLT optim
F Index Execution (continued) stand-alone approach 1-22 static environment 1-20 Web-based approach 1-23 Execution contexts explicit 3-20 implicit 3-20 instantiating 3-20 methods 3-22 specifying in SQLJ executable clauses 3-21 ExecutionContext() constructor B-13 EXPLAIN function 5-46 Extended profile 1-13, 1-19 F FETCH statement connection contexts 3-15, A-4 using 3-49 Files, specifying output directories for 5-18 Flags 5-7 Floating-point data types 3-39 FOR UPDATE clause 3-55 forName() method 3-7 ForUpda
J Index ISO8859_1 encoding 5-22 Isolation level guidelines for setting 4-44 precedence 4-45 Iterator conversion from a JDBC result set 3-56 description of 3-48 named iterator 3-51 passing as a parameter 3-56 positioned iterator 3-48 positioned versus named 3-54 sensitivity 4-46, A-3 Iterator class file description of 1-12 specifying an output directory for 5-18 Iterator conversion clause connection contexts 3-15, A-4 syntax A-6 Iterator declaration clause A-2 J Japanese characters assigning to a host var
K Index JDBC/MX driver (continued) minimum version requirement 2-2 required security permissions 2-6 setting the class path of 2-5 URL 3-7, 3-14 verifying the version 2-3 JREHOME environment variable 2-4 K KANJI character string assigning to a host variable 3-41 assigning to an SQL/MP column 3-42 KEEP_CONNECTION variable B-3 Keys file description of 1-12 specifying an output directory for 5-18 Korean characters 3-42 KSC5601 character string, assigning to an SQL/MP column 3-42 L Late name resolution 4-23
N Index N N literals 3-47 Name resolution compile-time 4-23 late (or run-time) 4-23 Named iterator 3-51 Names avoiding conflicts 6-10 restrictions 3-4 National character string literals 3-47 NATIONAL_CHARSET default attribute 3-47 next() method description of B-9 using 3-54 Nonblocking JDBC/MX description of 3-26 LOCK TABLE consideration A-12 none flag 5-21 NonStop Server for Java minimum version requirement 2-2 verifying the version 2-3 nonulls flag 5-21 noprecision flag 5-21 nostrict flag 5-21 noverbose
Q Index Profile (continued) description of 1-12 generating one for each connection context class 3-17 information block 5-43 printing the contents 5-42 specifying an output directory for 5-18 ProfilePrinter tool 5-42 Program development 1-8 execution 1-20 management 6-11 Program class file description of 1-12 specifying an output directory for 5-18 Program files list of 6-1 managing 6-11 specifying output directories for 5-18 Properties file description of 5-7 specifying on the command line 5-8, 5-20 synt
S Index SET CATALOG statement 4-16 SET MPLOC statement 4-16 SET NAMETYPE statement 4-16 SET SCHEMA statement 4-16 SET statements, dynamic behavior 4-18 SET TABLE TIMEOUT statement 4-11 SET TRANSACTION statement 4-43 setDefaultContext() method description of B-19 example 3-13 setMaxFieldSize() method 3-22, B-15 setMaxRows() method 3-22, B-15 setSecuritymanager() method 2-6 Shift-JIS encoding 3-42 Similarity check controlling 4-27 criteria 4-28 description of 4-26 OLT optimization 4-29 recommended OLTP sett
T Index sqlj.runtime.ConnectionContext interface B-2 sqlj.runtime.ExecutionContext class B-12 sqlj.runtime.ForUpdate interface A-2, B-5 sqlj.runtime.NamedIterator interface B-5 sqlj.runtime.PositionedIterator interface B-6 sqlj.runtime.ref.DefaultContext class B-18 sqlj.runtime.ResultSetIterator interface B-7 sqlj.tools.
W Index W Warnings (run time), handling 3-63 Warnings (SQLJ processing), setting display conditions 5-21 Web-based execution 1-23 WHERE CURRENT OF clause 3-55 WITH clause A-3 Special Characters -backup option 5-26 -catalog option 5-30 -CcreateMDF option See -createMDF option -classpath option See -cp option -CmoduleGroup option See -moduleGroup option -CmoduleTableSet option See -moduleTableSet option -CmoduleVersion option See -moduleVersion option -compile option 5-21 -compiler-encoding-flag option 5-2
Special Characters Index HP NonStop SQL/MX Programming Manual for Java—523726-003 Index -12
