IBM Informix Embedded SQLJ User's Guide

######

IBM Informix Embedded SQLJ User's Guide Appendix A: Connecting to Databases

Contents | Index |  |

## Connecting to Databases "Connecting to a Database" describes how Embedded SQLJ programs connect to databases. This appendix provides background information and information about using nondefault connection contexts. ## The ConnectionManager Class You use the ConnectionManager class to make a connection to a database, as described in "Connecting to a Database". The ConnectionManager class has two methods: newConnection() initContext() The newConnection() method creates and returns a new JDBC Connection object using the current values of the DRIVER, DBURL, UID, and PWD attributes. If any of the needed attributes is null, or a connection cannot be established, an error message is printed to System.out, and the program exits. The initContext() method returns the currently installed default context. If the current default context is null, a new default context instance is created and installed using a connection obtained from a call to getConnection. ## Database URLs The DBURL data member of the ConnectionManager class and the value for the -url option that you specify for on-line checking are database URLs (see "On-Line Checking" for information about on-line checking). Database URLs specify the subprotocol (the database connectivity mechanism), the database or server identifier, and a list of properties. Your Embedded SQLJ program uses Informix JDBC Driver to connect to an Informix database. Informix JDBC Driver supports database URLs of the following format:

    jdbc:informix-sqli://[{ip-address|host-name}:port-number][/dbname]:
    	INFORMIXSERVER=server-name;[user=user;password=password]
    	[;name=value[;name=value]...]
In the preceding syntax: curly brackets ( {} ) together with vertical lines ( | ) denote more than one choice of variable. italics denote a variable value. brackets ( [] ) denote an optional value. words or symbols not enclosed in brackets are required (`INFORMIXSERVER=`, for example). JHimportantJHImportant: Spaces are not allowed in the database URL. The following table describes the variable parts of the database URL.

Database URL Variable | Required? | Description
--- | --- | ---
ip-address or domain-name | Yes | The IP address or the domain name of the computer running the Informix database server. An example of an IP address is `123.45.67.89`. An example of a domain name is `myhost.com`.
port-number | Yes | The port number of the Informix database server.
dbname | No | The name of the Informix database to which you want to connect. If you do not specify the name of a database, a connection is made to the Informix database server.
server-name | Yes | The name of the Informix server to which you want to connect. This is the value of the INFORMIXSERVER environment variable. The INFORMIXSERVER environment variable is required in the database URL, unless it is included in the property list.
username | Yes | The name of the user you want to connect to the Informix database or database server as.
password | Yes | The password of the user specified by username.
name=value | No | A name-value pair that specifies a value for the Informix environment variable contained in the name variable, recognized by either Informix JDBC Driver or Informix database servers. The value of name is case insensitive. Refer to the Informix JDBC Driver Programmer's Guide for information about environment variables supported by Informix JDBC Driver and how to set them.
## Using Nondefault Connection Contexts This section explains how to use nondefault connection contexts. Embedded SQLJ uses a connection-context object to manage the connection to the database in which you want an SQL statement to execute. You can specify different connection-context objects for different SQL statements in the same Embedded SQLJ program, as shown in the sample program MultiConnect.sqlj included in this section. #### To use a nondefault connection context 1. Define the connection-context class by using an Embedded SQLJ connection statement. The syntax of the connection statement is as follows:

                #sql [modifiers] context java_class_name;
     modifiers
    A list of Java class modifiers: for example, public
    java_class_name
    The name of the Java class of the new connection context
2. Create a connection-context object for connecting to the database. 3. Specify the connection-context object in your Embedded SQLJ statement in parentheses following the #sql string. ### MultiConnect.sqlj The sample program MultiConnect.sqlj creates two databases with two tables, Orders and Items, and inserts two records in the Orders table with corresponding records in the Items table. The program prints the order line items for all the orders from both tables, which reside in different databases, by creating separate connection contexts for each database. MultiConnect.sqlj calls the methods executeSQLScript() and getConnect(). These methods are contained in demoUtil.java, which follows this program.

    /***************************************************************************
     *
     *                         INFORMIX SOFTWARE, INC.
     *
     *                            PROPRIETARY DATA
     *
     *      THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF
     *      INFORMIX SOFTWARE, INC.  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 INFORMIX SOFTWARE, INC.
     *
     *      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:        MultiConnect.sqlj
     *
     *  Description:  This demonstrates usage of 2 connection contexts using
     *                different URLs.
     *
     *
     ***************************************************************************
    */

    import java.sql.*;
    import java.math.*;
    import java.lang.*;
    import sqlj.runtime.*;  //SQLJ runtime classes
    import sqlj.runtime.ref.*;

    /* Declare  ConnectionContext classes OrdersCtx and ItemsCtx.
     * OrdersCtx is related to the orders table which is in orders_db database
     * ItemsCtx  is related to the items table which is in items_db database
     * Instances of these  classes are used to specify where SQL operations
     * on orders table or items table shld should execute.
     * We create the 2 databases using a default context using ConnectionManager
     *
     * For an order (from the orders table in the orders_db database), we try
     * to query the items table(in the items_db database) for the line items which
     * make up that order
     *
     */

    #sql context OrdersCtx;
    #sql context ItemsCtx;

    // Declare 2 named iterators for Items and Orders

    #sql iterator OrdersRec (
        Integer     order_num,
        Date        order_date,
        String      po_num,
        Date        paid_date
        );

    #sql iterator ItemsRec (
        Short       item_num,
        int         order_num,
        Short       stock_num,
        String      manu_code,
        Integer     quantity,
        BigDecimal  total_price
        );

    public class MultiConnect extends demoUtil
    {
        private OrdersCtx o_ctx = null;
        private ItemsCtx  i_ctx = null;
        private DefaultContext ctx = null;

        // The constructor sets up a default database context

        MultiConnect()
        {
            /* Initialize database connection thru Connection Manager
             * and create a default context
             */
            ctx = ConnectionManager.initContext();
        }

        public static void main (String args[]) throws SQLException
        {
            MultiConnect mc_ob = new MultiConnect();
            try
            {
                System.out.println( "Running demo program MultiConnect...." );
                mc_ob.runDemo();

                //Close the connection
                mc_ob.o_ctx.close() ;
                mc_ob.i_ctx.close() ;
            }
            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());
            }
        }
        void runDemo() throws SQLException
        {
            // We drop the 2 databases using the default context

            drop_db();

            /*
             * We create the 2 databases needed for the program using the
             * default Connection Context
             */

            #sql [ctx] { CREATE DATABASE orders_db WITH LOG MODE ANSI };
            #sql [ctx] { CREATE DATABASE items_db WITH LOG MODE ANSI };
            ctx.close();

            String driver = "com.informix.jdbc.IfxDriver";
            String url = "jdbc:158.58.9.121:1527:informixserver=tulua2";
            String user = "rdtest";
            String password = "1RDSRDS";
            set_driver(driver);
            set_url(url);
            set_user(user);
            set_passwd(password);
            getConnect();

            // Create the schema and the tables by running the SQL scripts
            executeSQLScript("./schema.sql");
            conn.close();

            // We now set up the Connection context OrdersCtx
            url = "jdbc:158.58.9.121:1527/orders_db:informixserver=tulua2";
            set_url(url);
            o_ctx = new OrdersCtx(getConnect());

            /* Change the url to reflect items database
             * Here we are changing the database name
             * the machine name and the port no could also be different
             */

            url = "jdbc:158.58.9.121:1527/items_db:informixserver=tulua2";
            set_url(url);
            i_ctx = new ItemsCtx(getConnect());

            // Declare orders_rec of type OrdersRec
            OrdersRec orders_rec;

            // Using context o_ctx query orders

            #sql [o_ctx] orders_rec =
            { SELECT order_num, order_date, po_num, paid_date
              FROM orders
            };
            while ( orders_rec.next() )
            {
            System.out.println("================================="+
                               "=====================");
            System.out.print("ORDER NUMBER:" + orders_rec.order_num() + "\t\t");
            System.out.println("ORDER DATE:" + orders_rec.order_date() );
            System.out.print("PURCHASE ORDER NUMBER:" +
                              orders_rec.po_num() + "\t");
            System.out.println("PAID DATE:" + orders_rec.paid_date() );
            System.out.println("================================="+
                               "=====================");
            System.out.print("\n");
            int ord_no = orders_rec.order_num().intValue();
            printItemRec( fetchItemRec(ord_no) ) ;
            }
            System.out.println("\n");
        }
        ItemsRec fetchItemRec(int ord_no) throws SQLException
        {

            ItemsRec  items_rec;
            #sql  [i_ctx] items_rec =
            { SELECT item_num, order_num, stock_num, manu_code, quantity,
                     total_price
              FROM   items
              WHERE  order_num = :ord_no
            };
            return items_rec;
        }
        void printItemRec(ItemsRec items_rec) throws SQLException
        {
            System.out.print("ITEM NUMBER   ");
            System.out.print("STOCK NUMBER   ");
            System.out.print("MANUFACTURER CODE   ");
            System.out.print("QUANTITY   ");
            System.out.print("TOTAL PRICE   ");
            System.out.println("\n---------------------------------"+
                               "---------------------------------------");
            while ( items_rec.next() )
            {
                System.out.print(items_rec.item_num() + "\t\t");
                System.out.print(items_rec.stock_num() + "\t\t" );
                System.out.print(items_rec.manu_code()+ "\t\t");
                System.out.print(items_rec.quantity() + "   " + "\t\t");
                System.out.print(items_rec.total_price() + "\t\t");
                System.out.print("\n");
            }
            System.out.println("\n");

        }
        void drop_db() throws SQLException
        {
            try
            {
                #sql [ctx]  { drop database orders_db };
                #sql [ctx] { drop database items_db };
            }
            catch (SQLException s) { }
        }
    }

    /***************************************************************************
     *
     *                         INFORMIX SOFTWARE, INC.
     *
     *                            PROPRIETARY DATA
     *
     *      THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF
     *      INFORMIX SOFTWARE, INC.  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 INFORMIX SOFTWARE, INC.
     *
     *      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:        demoUtil.java
     *
     *  Description:  Utilities used in the demo programs
     *
     *
     *
     ***************************************************************************
    */

    import java.io.*;
    import java.util.*;
    import java.lang.*;
    import java.sql.*;

    public class demoUtil
    {
        private String driver;
        private String URL;
        private String myURL;
        private String user;
        private String passwd;
        private int count = 0;
        private int lineno = 0;
        private int errors = 0;
        private boolean end_of_file = false;
        private FileInputStream fs = null;
        private DataInputStream in = null;
        private BufferedReader br = null;
        private String line = null;
        private StringBuffer read_line = null;
        public Connection conn;

        public void executeSQLScript(String SQLscript)
        {
            try
            {
                fs = new FileInputStream(SQLscript);
            }
            catch (Exception e)
            {
                System.out.println("Script File Not Found");
    	    e.printStackTrace();
            }

            in = new DataInputStream(fs);
            br = new BufferedReader(new InputStreamReader(in));
            line = getNextLine();
            read_line = (line==null) ? new StringBuffer() : new StringBuffer(line);
            while (!end_of_file)
            {
                if (line!=null && line.indexOf(';')==line.length()-1)
                {
                    tryExecute(read_line);
                    read_line = new StringBuffer();
                }
                line = getNextLine();
                if (line!=null)
                    read_line.append(line).append(" ");

            }
            if (read_line!=null && read_line.length()>0)
            {
                tryExecute(read_line);
            }
            System.out.println("\n");
        }
        private boolean isComment(String s)
        {
            if (s!=null)
                s.trim();
            return (
                   s==null || s.equals("")
                   || (s.length()>=2 && s.substring(0,2).equals("--"))
                   || (s.length()>=4 && s.substring(0,4).toUpperCase().equals(
                       "REM "))
                   );
        }

        private String getNextLine()
        {
            String line = null;
            lineno++;

            try
            {
                line = br.readLine();
                if (line==null)
                    end_of_file=true;
            }
            catch (IOException e)
            {
                line = null;
                end_of_file=true;
            }

            return ( (isComment(line)) ? null : line);
        }

        private String bufferToCommand(StringBuffer sb)
        {
            String s = sb.toString().trim();

            // chop off trailing semicolon
            if (s.substring(s.length()-1,s.length()).equals(";"))
                s = s.substring(0,s.length()-1);

            return s;
        }
        private void tryExecute(StringBuffer sb)
        {
            String cmd = bufferToCommand(sb);
            System.out.print(".");
            System.out.flush();

            try
            {
                count++;
                Statement stmt = conn.createStatement();
                stmt.executeUpdate(cmd);
                stmt.close();
            }
            catch (SQLException e)
            {
                errors++;
                System.out.println("SQL Error line "+lineno+": "+e.getMessage());
                System.out.println("SQLState: " + e.getSQLState());
                System.out.println("ErrorCode: " + e.getErrorCode());
                System.out.println("Offending statement: '"+cmd+"'");
                e.printStackTrace();
            }
        }
        public void set_driver(String driver)
        {
            this.driver = driver;
        }
        public void set_url(String url)
        {
            this.URL = url;
        }
        public void set_user(String userName)
        {
            this.user = userName;
        }
        public void set_passwd(String passwd)
        {
            this.passwd = passwd;
        }
        public void connSetup()
        {
            try
            {
                Class.forName(driver);
            }
            catch (Exception e)
            {
                System.out.println("Failed to load Informix JDBC driver.");
    	    e.printStackTrace();
            }
    	myURL = URL ;
    	myURL = myURL + ";user=" + user + ";password=" + passwd;
        }
        public Connection getConnect()
        {

            connSetup();
            try
    	{
                conn = DriverManager.getConnection(myURL);
            }
        	catch (SQLException e)
            {
                System.out.println("Connect Error : " + e.getErrorCode());
                System.out.println("Failed to connect: " + e.toString());
                e.printStackTrace();
            }
            return conn;
        }
        public Connection getConnect(Connection i_conn)
        {
            connSetup();
            try
    	{
                i_conn = DriverManager.getConnection(myURL);
            }
        	catch (SQLException e)
            {
                System.out.println("Connect Error : " + e.getErrorCode());
                System.out.println("Failed to connect: " + e.toString());
                e.printStackTrace();
            }
            return i_conn;
        }
    }

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