A Simple Embedded SQLJ Program

This sample program, Demo03.sqlj, demonstrates the use of a named iterator to retrieve data from a database. This simple program outlines a standard sequence for many Embedded SQLJ programs:

1. Import necessary Java classes.
2. Declare an iterator class.
3. Define the main() method.

   All Java applications have a method called main, which is the entry point for the application (where the interpreter starts executing the program).

4. Connect to the database.

   The constructor of the application makes the connection to the database by calling the initContext() method of the ConnectionManager class.

5. Run queries.
6. Create an iterator object and populate it by running a query.
7. Handle the results.
8. Close the iterator.

/***************************************************************************
 *
 *                         IBM CORPORATION
 *
 *                         PROPRIETARY DATA
 *
 *      THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF
 *     IBM CORPORATION.  THIS DOCUMENT IS SUBMITTED TO RECIPIENT IN
 *      CONFIDENCE.  INFORMATION CONTAINED HEREIN MAY NOT BE USED, COPIED OR
 *      DISCLOSED IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN AGREEMENT
 *      SIGNED BY AN OFFICER OF IBM CORPORATION.
 *
 *      THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED WORK UNDER
 *      SECTIONS 104 AND 408 OF TITLE 17 OF THE UNITED STATES CODE.
 *      UNAUTHORIZED USE, COPYING OR OTHER REPRODUCTION IS PROHIBITED BY LAW.
 *
 *
 *  Title:        Demo03.sqlj 
 *
 *  Description:  This demonstrates simple iterator use 
 *
 *
 ***************************************************************************
*/
import java.sql.*;
import sqlj.runtime.*; //SQLJ runtime classes

#sql iterator CustRec(
    int    customer_num, 
    String fname, 
    String lname ,
    String company ,
    String address1 ,
    String address2 ,
    String city ,
    String state ,
    String zipcode ,
    String phone 
    );

public class Demo03
{
    public static void main (String args[]) throws SQLException
    {
        Demo03 demo03 = new Demo03();
        try
        {
            demo03.runDemo();
        }
        catch (SQLException s)
        {
            System.err.println( "Error running demo program: " + s );
            System.err.println( "Error Code                : " + 
                                 s.getErrorCode());
            System.err.println( "Error Message             : " + 
                                 s.getMessage());
        }
    }
     

    // Initialize database connection thru Connection Manager 
    Demo03()
    {
        ConnectionManager.initContext();
    }
    void runDemo() throws SQLException
    {
        drop_db();
      
        #sql { CREATE DATABASE demo_sqlj WITH LOG MODE ANSI };

        #sql 
        { 
            create table customer
            (
            customer_num            serial(101),
            fname                   char(15),
            lname                   char(15),
            company                 char(20),
            address1                char(20),
            address2                char(20),
            city                    char(15),
            state                   char(2),
            zipcode                 char(5),
            phone                   char(18),
            primary key (customer_num)
            )
        };

        // Insert 4 Records in a try block
        try
        {
            #sql 
            { 
            INSERT INTO customer VALUES
                ( 101, "Ludwig", "Pauli", "All Sports Supplies", 
                  "213 Erstwild Court", "", "Sunnyvale", "CA", 
                  "94086", "408-789-8075"
                )
            };  
    
            #sql 
            {
            INSERT INTO customer VALUES
                ( 102, "Carole", "Sadler", "Sports Spot", 
                  "785 Geary St", "", "San Francisco", "CA", 
                  "94117", "415-822-1289"
                )
            };

            #sql 
            {
            INSERT INTO customer VALUES
                ( 103, "Philip", "Currie", "Phil's Sports", 
                  "654 Poplar", "P. O. Box 3498", "Palo Alto", 
                  "CA", "94303", "415-328-4543"
                )
            };

            #sql 
            {
            INSERT INTO customer VALUES
                ( 104, "Anthony", "Higgins", "Play Ball!", 
                  "East Shopping Cntr.", "422 Bay Road", "Redwood City", 
                  "CA", "94026", "415-368-1100"
                )  
            };

        }
        catch (SQLException e)
        {
            System.out.println("INSERT Exception: " + e + "\n");
            System.out.println("Error Code                : " + 
                               e.getErrorCode());
            System.err.println("Error Message             : " + 
                               e.getMessage());
            
        }

        System.out.println();
        System.out.println( "Running demo program Demo03...." );
        System.out.println();

        // Declare Iterator of type CustRec
        CustRec cust_rec;

        #sql cust_rec = { SELECT *  FROM customer };

        int row_cnt = 0;
        while ( cust_rec.next() )
            {
            System.out.println("===================================");
            System.out.println("CUSTOMER NUMBER :" + cust_rec.customer_num());
            System.out.println("FIRST NAME      :" + cust_rec.fname());
            System.out.println("LAST NAME       :" + cust_rec.lname());
            System.out.println("COMPANY         :" + cust_rec.company());
            System.out.println("ADDRESS         :" + cust_rec.address1() +"\n" +
                               "                 " + cust_rec.address2());
            System.out.println("CITY            :" + cust_rec.city());
            System.out.println("STATE           :" + cust_rec.state());
            System.out.println("ZIPCODE         :" + cust_rec.zipcode());
            System.out.println("PHONE           :" + cust_rec.phone());
            System.out.println("===================================");
            System.out.println("\n\n");
            row_cnt++;
            }
        System.out.println("Total No Of rows Selected :" + row_cnt);
        cust_rec.close() ;
        System.out.println("\n\n\n\n\n");

        drop_db();
    }
    void drop_db() throws SQLException
    {
        try 
        {
            #sql { drop database demo_sqlj };
        }
        catch (SQLException s) { }
    }
}