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.
/***************************************************************************
*
* 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: 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) { }
}
}
/***************************************************************************
*
* 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: 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 IBM 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;
}
}
Home |
[ Top of Page | Previous Page | Next Page | Contents |
Index ]