Fundamentals of Embedded SQLJ
This chapter introduces simple Embedded SQLJ statements; see Chapter 4, "The Embedded SQLJ Language," for detailed information about the language.
SQLJ Statement Identifier
Each SQLJ statement in an Embedded SQLJ program is identified by #sql at the beginning of the statement. The SQLJ translator recognizes #sql and translates the rest of the statement into Java code using JDBC calls.
Connecting to a Database
You can use a class called ConnectionManager (located in a file in the /demo/sqlj directory) to initiate a JDBC connection. The ConnectionManager class uses a JDBC driver and a database URL to connect to a database. Database URLs are described in "Database URLs".
To enable your Embedded SQLJ program to connect to a database, you assign the values to the following data members of the ConnectionManager class in the file /demo/sqlj/ConnectionManager.java:
You must include the directory that contains your ConnectionManager.class file (produced when you compile ConnectionManager.java) in your CLASSPATH environment variable definition.
Your Embedded SQLJ program connects to the database by calling the initContext() method of the ConnectionManager class, as follows:
"The ConnectionManager Class" provides details about the functionality of the initContext() method.
As an alternative to using the ConnectionManager class, you can write your own input methods to read the values of user name, password, driver, and database URL from a file or from the command line.
The connection context that you set up is the default connection context; all #sql statements execute within this context, unless you specify a different context. For information about using nondefault connection contexts, see "Using Nondefault Connection Contexts".
Embedding SQL Statements
Embedded SQL statements can appear anywhere that Java statements can legally appear. SQL statements must appear within curly braces, as follows:
You can use the SELECT...INTO statement to retrieve data into Java variables (host variables). Host variables within SQL statements are designated by a preceding colon ( : ). For example, the following query places values in the variables customer_num, fname, lname, company, address1, address2, city, state, zipcode, and phone:
SQL statements are case insensitive and can be written in uppercase, lowercase, or mixed-case letters. Java statements are case sensitive (and so are host variables).
You use SELECT...INTO statements for queries that return a single record; for queries that return multiple rows (a result set), you use an iterator object, described in the next section.
Handling Result Sets
Embedded SQLJ uses result-set iterator objects rather than cursors to manage result sets (cursors are used by languages such as INFORMIX-ESQL/C). A result-set iterator is a Java object from which you can retrieve the data returned by a SELECT statement. Unlike cursors, iterator objects can be passed as parameters to a method.
When you declare an iterator class, you specify a set of Java variables to match the SQL columns that your SELECT statement returns. There are two types of iterators: positional and named.
Positional Iterators
The order of declaration of the Java variables of a positional iterator must match the order in which the SQL columns are returned. You use a FETCH...INTO statement to retrieve data from a positional iterator.
For example, the following statement generates a positional iterator class with five columns, called CustIter:
This iterator can hold the result set from the following SELECT statement:
Named Iterators
The name of each Java variable of a named iterator must match the name of a column returned by your SELECT statement; order is irrelevant. The matching of SQL column name and iterator column name is case insensitive.
You use accessor methods of the same name as each iterator column to obtain the returned data, as shown in the example in "A Simple Embedded SQLJ Program". The SQLJ translator uses the iterator column names to create accessor methods. Iterator column names are case sensitive; therefore, you must use the correct case when you specify an accessor method.
You cannot use the FETCH...INTO statement with named iterators.
For example, the following statement generates a named iterator class called CustRec:
This iterator class can hold the result set of any query that returns the columns defined in the iterator class. The result set from the query can have more columns than the iterator class, but the iterator class cannot have more columns than the result set. For example, this iterator class can hold the result set of the following query because the iterator columns include all of the columns in the customer table:
|