1 Guy Bowerman
IBM Information Management Click here to Add Session Code
Day, Month 0, 2006 • 00:00 a.m. – 00:00 a.m.
Platform:
Java and IDS: Part 1 - JDBC
2
2
Agenda
• Informix JDBC Driver characteristics
• Implementing a simple select
• Informix JDBC Driver recent features
• Batch updates
• Implementing a connection pool
3
3
JDBC Review – Implementing a Simple Select
4
4
Informix JDBC Driver Characteristics
5
5
JDBC Review – Implementing a Simple Select
• Initialize JDBC Driver
• Connect to Database
• Create statement
• Execute Query
• Display ResultSet
6
6
JDBC Review – Initialize JDBC driver
import java.sql.*;
public class jdbc_select {
public static void main(String args[]) { try {
// Initialize Informix JDBC driver
Class.forName("com.informix.jdbc.IfxDriver");
}
catch (Exception e) {
System.out.println("Informix JDBC driver error: “ + e.getMessage());
}
…….
} }
7
7
JDBC Review – Connect to Database
String url =
"jdbc:informix-sqli://mymachine:1526/stores_demo:” +
“INFORMIXSERVER=ol_myserver;";
try {
// get database connection Connection conn =
DriverManager.getConnection(url, “informix”, “mypasswd”);
…..
// close connection conn.close();
}
catch (SQLException e) {
System.out.println(e + "Connect Error: " + e.getErrorCode() + “ “ + e.getMessage());
}
getConnection(String url)
getConnection(String url, Properties info)
getConnection(String url, String user, String password)
8
8
JDBC Review –Execute SQL, Display Results
try {
….
// create statement and execute query Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery("select city, company from customer order by 1,2");
// print header
System.out.printf("\n Customers by city...\n\n");
// process result set while (rs.next())
System.out.printf("\t%-15s\t%s\n", rs.getString(1).trim(), rs.getString(2));
rs.close();
stmt.close();
conn.close();
}
catch (SQLException e() { ….}
J2SE 5.0 introduced variable arguments – allowing multiple parameters to be passed as arguments to methods. It also added some PrintStream methods which use variable arguments such as printf().
9
9
JDBC Review – Compile to Bytecode
set PATH=c:\disks\java\5.0\bin;%PATH%
set CLASSPATH=.;c:\jdbc_30jc2\lib\ifxjdbc.jar
javac jdbc_select.java java jdbc_select
C:\work\docs\java_talk>java jdbc_select
java.sql.SQLException: com.informix.asf.IfxASFException: Attempt to connect to database server (ol_myserver) failed.
Error Code: -908
10
10
Informix JDBC Driver New Features
• 2.2 New Features
• 2.21.JC1 New Features
• 3.00.JC1 New Features
• 3.00.JC1 Examples
11
11
JDBC 2.2 New Features
• XML utility methods
• Connection Pool Manager
• UDT/UDR Manager (in ifxtools.jar )
• HTTP proxy enhancement: Added LDAP and sqlhosts file lookup capability
• New Smart blob methods: IfxLoLock(), IfxLoUnLock(), IfxLoAlter(), IfxLoFromFile(), IfxLoToFile()
Ifxtools.jar file contains new methods to assist with inserting and retrieving XML data. For more information about the XML utility methods see “Storing and retrieving XML documents” in the JDBC programmer’s manual:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ib m.jdbc.doc/jdbc102.htm
The Informix JDBC Driver provides a Connection Pool Manager as a transparent component of the ConnectionPoolDataSource object. See the JDBC Programmer’s Manual for information on deploying a
ConnectionPoolDataSource object and tuning the Connection Pool Manager.
The UDTManager and UDRManager classes provide an infrastructure for mapping client-side Java classes as opaque data types and UDRs and storing their instances in the database.
The LDAP and sqlhosts file lookup capability allow a client to use JNDI (Java naming and directory interface) for specifying a connection.
The new smart blob methods facilitate working with smart blobs. IfxLoLock and IfxLoLock are methods to support byte-range locking – locking part of a smart blob.
12
12
JDBC 2.21.JC1 New Features
• HDR Support
• SQLHOSTS file group keyword support.
• Return number of rows affected by Statement.executeBatch().
• Identify exact location of a syntax error using getSQLStatementOffset().
• Clean pooled connections before returning to the connection pool.
• Statement.cancel() and Statement.setQueryTimeout() methods (JDK 1.4).
IBM Informix JDBC Driver Programmer's Guide references:
HDR Support (for example specifying a secondary server in the connection URL), see “Using High-Availability Data Replication" section in Chapter 2 and "Tuning the Connection Pool" in Chapter 7.
SQLHOSTS group keyword: "Dynamically Reading the Informix sqlhosts File" in Chapter 2.
Ability to clean pooled connections before returning them to the connection pool: "Tuning the Connection Pool" in Chapter 7.
Support for the Statement.cancel() and Statement.setQueryTimeout() methods.
These methods need out-of-band data support from JDK and are available only in the JDK 1.4 runtime environment.
The executeBatch() statement is a JDBC standard method for executing a batch of statements at a time.
The Connection.getSQLStatementOffset() method is useful in a catch(Exception e) clause. E.g
The scrubConnection() method will close the resultsets in a connection pool.
setQueryTimeout() sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds.
Note: UDT support initially went into JDBC 2.0
13
13
JDBC 3.00.JC1 New Features
• Internally update BLOB and CLOB data types using all methods introduced in the JDBC 3.0 spec
• Specify and control ResultSet holdability using methods from the java.sql.Connection interface
• Retrieve auto-generated keys from the database server.
• Access multiple INOUT mode parameters in Dynamic Server through the CallableStatement interface
• Mapping for SQL BYTE data type as SPL OUT or INOUT parameters to JDBC binary data types.
The setHoldability() and getHoldability() Connection methods relate to whether to close or hold cursors at commit time.
The getGeneratedKeys() method allows a program to get the value of an auto- generated key (such as the value of a serial column following an insert).
Using the CallableStatement interface provides greater flexibility by allowing UDRs to return multiple parameters.
14
14
3.00.JC1 Informix Specific New Features
• IPV6 Aware Connection URLs.
• Scrollable ResultSet performance enhancement.
• Enable connect to servers using SQLHOSTS group name to connect only to the primary server in an HDR pair.
15
15
IPV6 Aware Connection URLs
• The JDBC Driver code the parses the connection URL supports
both IPV4 and 128 bit IPV6 addresses, e.g.:
jdbc:informix-
sqli://3ffe:ffff:ffff:ffff:0::12:8088:INFORMIXSERVER=X
• Connecting to an IDS 10.0 server IPv6 port may require the use of system property, e.g.:
java -Djava.net.preferIPv6Addresses=true
The current default for a Java application is to prefer IPV4 addresses for backward compatilibity.
16
16
Scrollable ResultSet Performance Enhancement
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs =
stmt.executeQuery(“select city, company from customer");
rs.setFetchSize(6);
rs.absolute(9); // one row will be fetched rs.next() // rs will contain rows 10-15
Previously the last rs.next() would cause one row to be fetched.
A performance enhancement for Scrollable ResultSet allows multiple rows to be fetched at one time.
17
17
Batch Updates
18
18
Implementing a Connection Pool
19
19
Guy Bowerman
IBM Information Management [email protected]
Session ####
Java and IDS: Part 1 - JDBC
