IBM Informix Embedded SQLJ User's Guide

######

IBM Informix Embedded SQLJ User's Guide Chapter 3: Building an Embedded SQLJ Program

Contents | Index |  |

## 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:
UID | The user name
PWD | The password for the user name
DRIVER | The JDBC driver
DBURL | The URL for the database
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:

    ConnectionManager.initContext();
"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:

    #sql
    {
    INSERT INTO customer VALUES
    ( 101, "Ludwig", "Pauli", "All Sports Supplies",
    "213 Erstwild Court", "", "Sunnyvale", "CA",
    "94086", "408-789-8075"
    )
    };
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
    {
    SELECT *  INTO :customer_num, :fname, :lname, :company,
    :address1, :address2, :city, :state, :zipcode,
    :phone
    FROM customer
    WHERE customer_num = 101
    };
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.
| Important: Names of iterator classes must be unique within an application.
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:

    #sql iterator CustIter( int , String, String, String, String,
    String );
This iterator can hold the result set from the following SELECT statement:

    SELECT customer_num, fname, lname,  address1,
    address2, phone
    FROM   customer
#### 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:

    #sql iterator CustRec(
    int    customer_num,
    String fname,
    String lname ,
    String company ,
    String address1 ,
    String address2 ,
    String city ,
    String state ,
    String zipcode ,
    String phone
    );
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:

    SELECT *  FROM customer

IBM Informix Embedded SQLJ User's Guide, Version 1.0
Copyright © 1999, IBM Corporation. All rights reserved.