|
|
|
|
|
|
Using the Mimer JDBC Driver
This chapter explains how to load the Mimer JDBC driver and how to connect to a Mimer SQL database. It also contains JDBC application examples and discusses driver characteristics.
Loading a Driver
To use the Mimer JDBC driver, it must be loaded into the Java environment. The Java environment locates a driver by a search along the class path, defined in the CLASSPATH environment variable.
The CLASSPATH environment variable informs the Java environment where to find Java class files, such as the Mimer JDBC drivers.
The Mimer JDBC driver jar file, including the directory specification, should be added to the Java class path, as can be seen in the following examples:
Besides defining the CLASSPATH environment variable explicitly, it can also be defined for a specific session when executing the application. For example:
java -classpath /usr/lib/mimjdbc3.jar JdbcApplicationConnecting the Traditional Way
The connection provides the link between the application and the Mimer SQL database server. To make a connection using the DriverManager class requires two operations, i.e. loading the driver and making the connection.
The class name of the Mimer JDBC Driver is:
com.mimer.jdbc.DriverThe class name of the Mimer JDBC Trace Driver is:
com.mimer.jtrace.Driver
The jar file referenced in the CLASSPATH determines which driver is loaded.
A driver can be explicitly loaded using the standard Class.forName method:
import java.io.*; import java.sql.*; ... try { Class.forName("com.mimer.jdbc.Driver"); } catch (java.lang.ClassNotFoundException cnf) { System.err.println("JDBC driver not found"); return; }Alternatively, DriverManager, when it initializes, looks for a jdbc.drivers property in the system properties. The jdbc.drivers property is a colon-separated list of drivers.
The DriverManager attempts to load each of the named drivers in this list of drivers. The jdbc.drivers property can be set like any other Java property, by using the -D option:
java -Djdbc.drivers=com.mimer.jdbc.Driver classThe property can also be set from within the Java application or applet:
Properties prp = System.getProperties(); prp.put("jdbc.drivers", "com.mimer.jdbc.Driver:com.mimer.jtrace.Driver"); System.setProperties(prp);Note: Neither of the mechanisms used to load the driver specify that the application will actually use the driver. The driver is merely loaded and registered with the DriverManager.
Connecting With URL
To make the actual database connection, a URL string is passed to the DriverManager.getConnection method in the JDBC management layer.
The URL defines the data source to connect to. The JDBC management layer locates a registered driver that can connect to the database represented by the URL.
URL Syntax
The Mimer JDBC drivers support the following URL syntax:
jdbc:mimer:[protocol:][URL-field-list][property-list]URL-field-list options can be combined with property-list options.
Protocol
If a protocol is specified, the driver will load the mimcomm JNI library and use native routines to connect to the database. If the protocol is not specified (or is an empty string), no JNI library will be loaded and a TCP/IP connection will be made using standard Java network packages in you Java runtime.
URL-field-list
All fields in the URL-field-list are optional.
The database server host computer, with or without a user specification, is introduced by // and the database name is introduced by /, like:
[//[user[:password]@]serverName[:portNumber]] [/databaseName]A Connection object is returned from the getConnection method, for example:
String url = "jdbc:mimer://MIMER_ADM:admin@localhost/ExampleDB"; Connection con = DriverManager.getConnection(url);Alternatively, the getConnection method allows the user name and password to be passed as parameters:
url = "jdbc:mimer://localhost/ExampleDB"; con = DriverManager.getConnection(url, "MIMER_ADM", "admin");Property-list
The property-list for the Mimer JDBC Driver is optional. The list is introduced by a leading question mark ? and where there are several properties defined they are separated by ampersands &, like:
?property=value[&property=value[&property=value]]The following properties are supported:
The following example demonstrates a connection using the driver properties:
url = "jdbc:mimer:?databaseName=ExampleDB" + "&user=MIMER_ADM" + "&password=admin" + "&serverName=srv2.mimer.com"; con = DriverManager.getConnection(url);Alternatively a java.util.Properties object can be used:
Properties dbProp = new Properties(); dbProp.put("databaseName", "ExampleDB"); dbProp.put("user", "MIMER_ADM"); dbProp.put("password", "admin"); con = DriverManager.getConnection("jdbc:mimer:", dbProp);Elements from the URL-field-list and the property-list can be combined:
url = "jdbc:mimer:/ExampleDB" + "?user=MIMER_ADM" + "&password=admin";The DriverPropertyInfo class is available for programmers who need to interact with a driver to discover the properties that are required to make a connection. This enables a generic GUI tool to prompt the user for the Mimer SQL connection properties:
Driver drv; DriverPropertyInfo [] drvInfo; drv = DriverManager.getDriver("jdbc:mimer:"); drvInfo = drv.getPropertyInfo("jdbc:mimer:", null); for (int i = 0; i < drvInfo.length; i++) { System.out.println(drvInfo[i].name + ": " + drvInfo[i].value); }After connecting to the database, all sorts of information about the driver and database is available through the use of the getMetadata method:
DatabaseMetaData dbmd; dbmd = con.getMetaData(); System.out.println("Driver " + dbmd.getDriverName()); System.out.println(" Version " + dbmd.getDriverVersion()); System.out.println("Database " + dbmd.getDatabaseProductName()); System.out.println(" Version " + dbmd.getDatabaseProductVersion ()); con.close();The close method tells JDBC to disconnect from the Mimer SQL database server. JDBC resources are also released.
It is usual for connections to be explicitly closed when no longer required. The normal Java garbage collection has no way of freeing external resources, such as the Mimer SQL database server.
Connecting the J2EE Way
Along with J2EE came a new way for JDBC drivers to connect to database servers. Instead of requesting connections through the java.sql.DriverManager class, applications should connect using the javax.sql.DataSource, com.mimer.jdbc.MimerConnectionPoolDataSource or com.mimer.jdbc.MimerXADataSource interfaces.
Deploying Mimer JDBC in JNDI
The Mimer DataSource class is com.mimer.jdbc.MimerDataSource. When applications are deployed within the J2EE environment, a properly initiated MimerDataSource object should be stored in JNDI for the application server to retrieve at runtime. Application servers may use the JavaBean interface to obtain configuration parameters for MimerDataSource objects.
These are the DataSource attributes recognized by the Mimer JDBC drivers:
See sample programs further down for programming examples.
Deploying Mimer JDBC in a Connection Pool
Mimer JDBC may be deployed in J2EE compliant connection pools.
When deploying Mimer JDBC in a connection pool, the class com.mimer.jdbc.MimerConnectionPoolDataSource should be used. This class features the same attributes as described above for .MimerDataSource.
Deploying Mimer JDBC in Distributed Transaction Environments
Mimer JDBC may be used in J2EE compliant distributed transaction environments.
When deploying Mimer JDBC to be used in distributed transactions, the class com.mimer.jdbc.MimerXADataSource should be used. Whenever connections are created using this factory class, Mimer SQL may cooperate in transactions with any other XA compliant database server.
Read more about Mimer SQL and distributed transactions in Mimer SQL Programmer's Manual.
Mimer JDBC/CDC Optional Package
The Mimer SQL product contains a Mimer JDBC driver suitable for CDC/FP environments. This driver follows a specification laid out by the Java Community Process (JCP) 169. The detailed specification, JDBC for CDC/FP Optional Package specification, can be found at the Sun web site (http://java.sun.com).
This driver is targeted at more powerful handheld environments, such as PDA's, handheld computers and high-end mobile telephones.
The Mimer JDBC/CDC driver is located in the Mimer installation directory under the name minjdbc3.jar. The steps required to use the driver in a project varies somewhat depending on the target platform.
Sony Ericsson CDC Platform
The Sony Ericsson CDC Platform is targeted at their high-end Symbian telephones, for example M600, P990 and W950. This package is available as a download from their developer site.
In order to use the Mimer JDBC driver in a project with the Sony Ericsson CDC Platform, two things must be done:
- Make sure the Mimer JDBC driver is installed in the private directory of the application in question. If the UID of the application is FF000000, this directory would be \private\FF000000 on any drive. To make the driver available to your application in the Symbian emulator, you need to copy the minjdbc3.jar file to the application private folder. For example:
copy "c:\program files\mimer sql 9.3\minjdbc3.jar" c:\symbian\uiq3sdk\epoc32\winscw\c\private\FF000000\minjdbc3.jarTo make the driver available for deployment on the actual telephone, a line similar to the following need to be included in the application package specification file (.PKG):
"c:\program files\mimer sql 9.3\minjdbc3.jar" "!:\private\FF000000\minjdb3.jar"
- Specify the Mimer JDBC driver in the classpath. This is done in an invocation specification file, with the extension .j9. For example, an application named HelloMimerCDC with the UID FF000000 may use this invocation file:
-cp c:\private\FF000000\HelloMimerCDC.jar;c:\private\FF000000\minjdbc3.jar HelloMimerCDCA complete programming example is available for download at the Mimer SQL Developer site.
Error Handling
Error handling is taken care of by using the classes SQLException and SQLWarning.
The Class SQLException
The SQLException class provides information relating to database errors. Details include a textual description of the error, an SQLState string, and an error code. There may be a number of SQLException objects for a failure.
try { ... } catch(SQLException sqe) { SQLException stk; stk = sqe; // Save initial exception for stack trace System.err.println("\n*** SQLException:\n"); while (sqe != null) { System.err.println("Message: " + sqe.getMessage()); System.err.println("SQLState: " + sqe.getSQLState()); System.err.println("NativeError: " + sqe.getErrorCode()); System.err.println(); sqe = sqe.getNextException(); } stk.printStackTrace(System.err); }The Class SQLWarning
The SQLWarning class provides information relating to database warnings. The difference between warnings and exceptions is that warnings, unlike exceptions, are not thrown.
The getWarnings method of the appropriate object (Connection, Statement or ResultSet) is used to determine whether warnings exist.
Warning information can be retrieved using the same mechanisms as in the SQLException example above but with the method getNextWarning retrieving the next warning in the chain:
con = DriverManager.getConnection(url); checkSQLWarning(con.getWarnings()); ... private static boolean checkSQLWarning( SQLWarning sqw ) throws SQLException { boolean rc = false; if (sqw != null) { rc = true; System.err.println("\n*** SQLWarning:\n"); while (sqw != null) { System.err.println("Message: " + sqw.getMessage()); System.err.println("SQLState: " + sqw.getSQLState()); System.err.println("NativeError: " + sqw.getErrorCode()); System.err.println(); sqw = sqw.getNextWarning(); } } return rc; }Viewing Driver Characteristics
By using the java com.mimer.jdbc.Driver command, you can view characteristics of a specific driver and the current environment:
java com.mimer.jdbc.Driver options:
The following is an example that uses the -version option:
java com.mimer.jdbc.Driver -version Mimer JDBC driver version 3.15Used without any arguments, the command will display usage information.
Note: This functionality is only supported for the mimjdbc1, mimjdbc2, and mimjdbc3 drivers. Other drivers must use the getMetadata method.
The mimcomm JNI library
The Mimer JDBC driver can be used in a 100% native Java environment. In this case, the connection to a Mimer database server is done by the TCP/IP support included in the Java platform.
However, it is also possible to load an external library called mimcomm that includes support for all the communication protocols available on the particular platform. Please note that the mimcomm library may not be available on platforms that don't have a recent version of Mimer SQL installed.
The name of the mimcomm library varies between platforms. It is called mimcomm.dll on Windows, libmimcomm.so on Unix and MIMCOMM.EXE on VMS.
When you install a Mimer SQL distribution, the mimcomm library will normally be installed in a place where the Java environment can find it. You can test this by using the -mimcomm switch as a command line argument to the JDBC driver:
unix $ java -cp mimjdbc3.jar com.mimer.jdbc.Driver -mimcomm System.getProperty("java.library.path"): /usr/lib/SunJava2-1.4.2/jre/lib/i386/client:/usr/lib/SunJava2 -1.4.2/jre/lib/i386:/usr/lib/SunJava2-1.4.2/jre/../lib/i386:/ mimer/v925/dist/lib System.loadLibrary("mimcomm"): mimcomm library Version: V925B JNI parameter method: JNI_COPYWhen the JDBC driver loads the mimcomm library, it looks for the library in the path specified by the java.library.path system property. If the JDBC driver cannot find the library in the path listed, you should either move the mimcomm library to a directory listed in the path or consult your Java manual for instructions on how to change the java.library.path system property.
Java Program Examples
Below are a collection of small basic Java programs for different environments, showing a database connection and a simple database operation with some error handling.
JDBC Application Example
The example Java program below creates a result set containing all rows of the data dictionary view INFORMATION_SCHEMA.TABLES, then each row is fetched and displayed on standard output.
In this example, the user name and password are given separately using the DriverManager.getConnection method, i.e. not given in the URL specification.
The below example will work with the mimjdbc drivers.
import java.sql.*; class Example { public static void main(String[] args) { try { Class.forName("com.mimer.jdbc.Driver"); String url = "jdbc:mimer://my_node.mimer.se/customers"; Connection con = DriverManager.getConnection(url, "SYSADM","SYSPW"); Statement stmt = con.createStatement(); String sql = "select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE from INFORMATION_SCHEMA.TABLES"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { String schema = rs.getString(1); String name = rs.getString(2); String type = rs.getString(3); System.out.println(schema + " " + name + " " + type); } rs.close(); stmt.close(); con.close(); } catch (SQLException e) { System.out.println("SQLException!"); while (e != null) { System.out.println("SQLState : " + e.getSQLState()); System.out.println("Message : " + e.getMessage()); System.out.println("ErrorCode : " + e.getErrorCode()); e = e.getNextException(); System.out.println(""); } } catch (Exception e) { System.out.println("Other Exception"); e.printStackTrace(); } } }Another way to provide connection properties is to supply a java.util.Properties object to the DriverManager.getConnection method.
JDBC Application Example for J2EE
This example Java program deploys a com.mimer.jdbc.MimerDataSource in a file system JNDI repository. Note that the file system JNDI repository have to be downloaded from Sun. It is available for download at http://java.sun.com/products/jndi/serviceproviders.html. At this site, several other service providers may be downloaded as well.
Examples provided in this section will only work with the mimjdbc2 and mimjdbc3 drivers.
import javax.sql.*; import java.sql.*; import javax.naming.*; import javax.naming.directory.*; import java.util.Hashtable; public class RegisterJNDI { public static void main(String argv[]) { try { com.mimer.jdbc.MimerDataSource ds = new com.mimer.jdbc.MimerDataSource(); ds.setDescription("Our Mimer data source"); ds.setServerName("my_node.mimer.se"); ds.setDatabaseName("customers"); ds.setPortNumber("1360"); ds.setUser("SYSADM"); ds.setPassword("SYSPW"); // Set up environment for creating initial context Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); env.put(Context.PROVIDER_URL, "file:."); Context ctx = new InitialContext(env); // Register the data source to JNDI naming service ctx.bind("jdbc/customers", ds); } catch (Exception e) { System.out.println(e); return; } } }Once the data source is deployed, applications may connect using the deployed DataSource object. For instance like the below code snippet:
Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory"); env.put(Context.PROVIDER_URL, "file:."); Context ctx = new InitialContext(env); DataSource ds = (DataSource)ctx.lookup("jdbc/customers"); return ds.getConnection();Using the Driver from Applets
The example Java applet below creates a result set containing all rows of the data dictionary view INFORMATION_SCHEMA.TABLES, then each row is fetched and displayed on standard output.
In this example, the user name and password are given separately using the DriverManager.getConnection method, i.e. not given in the URL specification.
The example will work with the mimjdbc drivers.
import java.sql.*; import java.applet.*; import java.awt.*; public class ExampleApplet extends java.applet.Applet { public void init() { resize(1200, 600); } public void paint(Graphics g) { int row = 1; g.drawString("Listing tables:", 20, 10 * row++); try { Class.forName("com.mimer.jdbc.Driver"); String url = "jdbc:mimer://my_node.mimer.se/customers"; Connection con = DriverManager.getConnection(url, "SYSADM", "SYSPW"); Statement stmt = con.createStatement(); String sql = "select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE from INFORMATION_SCHEMA.TABLES"; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { String schema = rs.getString(1); String name = rs.getString(2); String type = rs.getString(3); g.drawString(schema + " " + name + " " + type, 50, 10 * row++); } rs.close(); stmt.close(); con.close(); } catch (SQLException e) { g.drawString("SQLException!", 20, 10 * row++); while (e != null) { g.drawString("SQLState : " + e.getSQLState(), 20, 10 * row++); g.drawString("Message : " + e.getMessage(), 20, 10 * row++); g.drawString("ErrorCode : " + e.getErrorCode(), 20, 10 * row++); e = e.getNextException(); g.drawString("", 20, 10*row++); } } catch (Exception e) { g.drawString("Other Exception!", 20, 10 * row++); g.drawString(e.toString(), 20, 10 * row++); } } }Executing the Java Applet Example
To use a Mimer JDBC Driver in a Java applet, copy the driver jar file to the directory containing the applet's Java classes.
This directory must be accessible to the Web server. The driver jar file name should be given as the applet tag's ARCHIVE parameter in the HTML file. For example:
<html> <head> <title> The Example Applet </head> <body> Example Applet: <applet archive="mimjdbc2.jar" code="ExampleApplet.class" width=800 height=600> </applet> </body> </html>You execute the applet by accessing the HTML file from a browser, for example:
http://my_node/ExampleApplet.htmlNote: There is a security restriction for Java applets, which states that a network connection can only be opened to the host from which the applet itself was downloaded. This means that both the Web server distributing the applet code and the database server must reside on the same host computer.
Mimer JDBC Midlet Example
This example midlet connects to a Mimer SQL database db on the host my_node.mimer.se using TCP/IP port 4711. Instructions on compiling and executing the example is found in the article `Java programming for mobile phones with Mimer SQL', found at http://developer.mimer.com/howto/howto_43.htm. The example uses the environment described in the article.
Example program:
import com.mimer.jdbc.*; import java.lang.*; import javax.microedition.midlet.*; import javax.microedition.lcdui.*; // A MIDlet which browse the corporate telephone directory public class BrowsePhoneNumbers extends MIDlet implements CommandListener,Runnable { private Command exitCommand,browseCommand,backCommand; private Display display; private Form mainForm; private List resultList; private boolean firstTime; private MimerDataSource mds; private TextField namField,grpField,phnField; private Thread dbthread; public BrowsePhoneNumbers() { display = Display.getDisplay(this); exitCommand = new Command("Exit", Command.EXIT, 1); browseCommand = new Command("Browse", Command.ITEM, 1); backCommand = new Command("Back", Command.BACK, 1); mainForm = new Form("Corporate phone dictionary"); mds = new MimerDataSource(); mds.setServerName("my_node.mimer.se"); mds.setDatabaseName("db"); mds.setPortNumber(4711); namField = new TextField("Name", "", 25, TextField.ANY); grpField = new TextField("Group", "", 15, TextField.ANY); phnField = new TextField("Phone", "", 15, TextField.ANY); resultList = new List("Found numbers", Choice.IMPLICIT); resultList.addCommand(backCommand); resultList.setCommandListener(this); firstTime = true; } // Start the MIDlet by creating the TextBox and // associating the exit command and listener. public void startApp() { if (firstTime) { mainForm.append(namField); mainForm.append(grpField); mainForm.append(phnField); firstTime = false; } mainForm.addCommand(exitCommand); mainForm.addCommand(browseCommand); mainForm.setCommandListener(this); display.setCurrent(mainForm); } // Pause is a no-op because there are no background // activities or record stores to be closed. public void pauseApp() { } // Destroy must cleanup everything not handled // by the garbage collector. // In this case there is nothing to cleanup. public void destroyApp(boolean unconditional) { } public void commandAction(Command c, Displayable s) { if (c == exitCommand) { destroyApp(false); notifyDestroyed(); } if (c == browseCommand) { // resultList.deleteAll(); This may be uncommented on MIDP 2.0 display.setCurrent(resultList); Thread thread = new Thread(this); thread.start(); } if (c == backCommand) { display.setCurrent(mainForm); } } public void run() { Connection con; try { con = mds.getConnection("SYSADM","SYSADM"); try { PreparedStatement ps = con.prepareStatement("call PHNQRY(?,?,?)"); ps.setString(1,namField.getString()); ps.setString(2,grpField.getString()); ps.setString(3,phnField.getString()); ps.execute(); ResultSet rs = ps.getResultSet(); while (rs.next()) { resultList.append(rs.getString(1)+", "+rs.getString(2)+", "+rs.getString(3),null); } } finally { con.close(); } } catch (SQLException se) { resultList.append(se.getMessage()+ " SQLCODE "+se.getErrorCode(),null); } } }
 Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se |
|
|
|
|
|
|