SQL/MX Programming Manual for Java

SQL/MX Programming Considerations
HP NonStop SQL/MX Programming Manual for Java—523726-003
4-33
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.job
( jobcode NUMERIC (4) UNSIGNED
NO DEFAULT
NOT NULL NOT DROPPABLE
HEADING 'Job/Code'
,jobdesc VARCHAR (18)
DEFAULT 0
HEADING 'Job Description'
,PRIMARY KEY (jobcode) NOT DROPPABLE
)
};
#sql {INSERT INTO samdbcat.persnl.job
VALUES (:jobCode, :jobDesc)
};
To prevent customization problems and dynamic execution of DML statements:
•
Put embedded DDL statements in a separate program from dependent DML
statements.
•
Run DDL statements before compiling an SQLJ program that refers to related
database objects.
For an example of DDL statements in an SQLJ program, see SampleDDL.sqlj—
Creating Tables From an SQLJ Program on page C-2.
Using Distributed Database Statements in an SQLJ Program
The REGISTER CATALOG and UNREGISTER CATALOG statements enable you to
prepare and maintain a network-distributed SQL/MX database environment.
Required Permissions
To run the REGISTER CATALOG and UNREGISTER CATALOG statements, you must
be the catalog owner or the super ID. Therefore, a user who runs an SQLJ program
that contains one of those distributed database statements must be the catalog owner
or the super ID. Those statements also require the validation of a remote password.
Embedding a REGISTER CATALOG Statement
The REGISTER CATALOG statement establishes a catalog reference on a remote
node for a catalog that is visible on the local node. When a catalog reference or
catalog is visible on a node in an Expand network, you can query SQL/MX objects in
that catalog from the node regardless of the physical location of the objects.