Home

Products
   IDS Server
   IDS JDBC Driver
   What's New

Download
   IDS Server Trial
   ODBC Drivers
   Other Tools

Pricing

Ordering

Support
   FAQ
   Articles
   JDBC Quick Start

Company
   About Us
   Our Customers
   Contact


 

FAQ - JDBC

J1. What are the major differences between the 4 types of JDBC driver?

The most important characteristic of a JDBC driver is whether it is 100% pure Java implementation of the JDBC API. This breaks the 4 types of JDBC driver into two groups. Type-3 and Type-4 drivers are written in 100% pure Java code while the Type-1 and Type-2 are not.  For example, the ODBC-JDBC bridge is a Type-1 driver, and the IDS JDBC Driver is a Type-3 driver.

The proper function of Type-1 and Type-2 drivers rely completely or partially on native binary modules. These binary modules are platform specific. As a result these types of driver do not support Java applets deployed over the internet because:

(a) Java does not support the distribution (download) of native binary modules.
(b) Normally downloaded applets are prohibited from accessing native modules.
(c) Java programs must be pre-installed on client machines before it can be run.
(d) It is unfeasible to deploy applets to anonymous clients.

A Type-3 JDBC driver like the IDS JDBC Driver or a Type-4 driver do not have all of the above limitations. They are truly platform independent and can be downloaded over the internet along with applets. Therefore, they are the types of JDBC driver suitable for deploying Java applets. See the next topic for a comparison of the two.

 

J2. Is a Type-4 JDBC driver superior than a Type-3 driver?

A Type-4 driver communicates directly to the database server using the database specific network protocol. It eliminated the middleware server used by a Type-3 driver. Thus, in terms of network architecture and performance, a Type-4 driver is more efficient and faster than a Type-3 driver. However, in the following aspects, a Type-3 driver is more favorable:

(a) The database network protocol (e.g. SQL*Net, TDS, etc.) usually is much more complex than one that is design just for a Type-3 driver to communicate with the middleware server. Therefore, the size of a Type-4 driver is almost always bigger than a Type-3 driver, making the latter more efficient during download.

(b) Many 2-tier database systems, like MS Access, dBase, FoxPro, Paradox, etc., do not have a network protocol. Therefore, it is not possible to create a Type-4 driver for these databases. Type-3 is the only choice.

(c) It is not possible to use one Type-4 JDBC driver to access two incompatible database systems (i.e. heterogeneous database access). This is not a problem with a Type-3 driver. For example, using two ODBC data sources with IDS Server, you can query an Oracle table alongside a MS Access query.

(d) It is not possible to encrypt the communication between a Type-4 driver and the database server, because the database network protocol, such as Oracle's SQL*Net and Sybase's TDS, usually does not have any cryptography ability. Transmitting data via the internet using these protocols is vulnerable to eavesdroppers. On the other hand, IDS JDBC Driver support strong encryption, because the network protocol between the driver and the IDS Server is designed just for this product and can be easily extended to include encryption, a feature we call Secure JDBC.

 

J3. Can I just get IDS JDBC Driver without IDS Server?

No. The IDS JDBC Driver cannot function without the IDS Server. The basic idea of a Type-3 JDBC driver like IDS JDBC Driver is that it connects exclusively to a middle-agent server (i.e. IDS Server), and the server in turn connects to the database. The driver all by itself cannot query the database.  Refer to the diagram in the IDS JDBC Driver page for a conceptual view.

If you are looking to download IDS JDBC Driver to use in your Java program, download one of the IDS Server Evaluations which includes the IDS JDBC Driver.  Note that although IDS Server is a platform specific server program, IDS JDBC Driver is platform independent and can be run in any Java environment.  For example, you can install IDS Server on Windows NT but run your pure Java client applications on PC, Unix, Apple, etc. along with IDS JDBC Driver which is also 100% pure Java.

 

J4. Does IDS JDBC Driver work with applets and JDK 1.0.2 browsers?

Yes. The Java package j102.sql.*  is a version of the IDS JDBC Driver that is designed specifically for deploying applets to JDK 1.0.2 browsers.

 

J5. Does IDS JDBC Driver support JDK 1.1 browsers and Java Plug-in?

Yes. The JDK 1.1 version of the IDS JDBC Driver (jdk11drv.zip) is designed to run in JDK 1.1 browser (IE4.x, IE5.x and Netscape 4.x), as well as all versions of JDK Plug-in.  While current IE and Netscape do not support JDK 1.2 or higher, a special version of IDS JDBC Driver (jdk12drv-v4browser.zip) can be used to run JDBC 2.0 applets on these browsers.  Refer to the JDBC Quick Start on how to select an IDS JDBC Driver for your Java applications.

 

J6. IDS JDBC Test froze after 'bogus'.

If the test program froze after the expected error caused by the erroneous query "select * from bogus", it indicates that you have an older version of the MS Access ODBC Driver. We strongly recommend that you download and upgrade to Microsoft Data Access Components. This upgrade pack, suitable for both Windows NT and Windows 95, contains important bug-fixes for the MS Jet Database Engine that will allow multi-threaded ODBC applications like IDS Server to function properly.

 

J7. My applet is loaded but nothing happens.

Most likely the browser could not load the IDS JDBC Driver from the Web server. The most important thing to remember when putting your applet on a Web server is that the IDS JDBC Driver has to be placed in the same directory. Further, this directory must be specified in the CODEBASE attribute of the <APPLET> tag. The following are some common mistakes when installing applets in a Web server:

(a) The JDBC driver package is in a subdirectory of the CODEBASE directory.
(b) The directory tree and directory names of the driver package was not preserved.
(c) The CODEBASE directory is a virtual path with no read permission.

 

J8. I can't connect to my Informix data source.

The problem is caused by the Informix ODBC Driver not able to find the proper settings in the registry, specifically, values in the HKEY_CURRENT_USER\Software\Informix\ENVIRONMENT key which are set by the SetNet32.exe program. On Windows NT, each login account has its own HKEY_CURRENT_USER registry hierarchy. 

A Windows NT service such as IDS Server is by default launched under the 'system' account. Since the HKEY_CURRENT_USER registry hierarchy for the 'system' account does not have those keys and values set by SetNet32.exe, the ODBC driver fails with that error message. 

 

The solution is to run the IDS Server using a specific Windows NT account such as administrator or informix. First, login to Windows NT as 'administrator' or 'informix' and run SetNet32.exe to set the proper environment variables (INFORMIXDIR, etc.)   Then under setup the IDS Server to run using this user account (See FAQ-G14).

 

J9. I can't connect to my data source when IDS Server is run as a service but OK when run in a console window.

This is similar to J8 such that the ODBC driver requires system parameters that is set only for a specific NT login account. Use Control Panel - Services - Startup to assign a user login account for running the IDS Server service.

 

J10. Can I have multiple concurrent Statements created from the same Connection?

This depends on the underlying database system and the ODBC driver. There seems to be no problem for MS Access to have multiple Statements on one Connection.

For a MS SQL Server data source, it will cause an exception saying "Connection is busy with results for another hstmt" if a new Statement is executed (or prepared) while there are more results left in another Statement executed earlier or concurrently. The solution is to use the new JDBC 2.0 method to create Statement instances with ResultSetType. Please refer to Section 4.6.4 of the IDS Server User's Guide.

 

J11. How do I store a JPEG or GIF file then later retrieve and display it?

To store a JPEG or GIF file into a database is straight forward. Open the file and get its InputStream. Create a table column of type LONGVARBINARY. Compose a insert SQL statement and create a PreparedStatement object for the SQL statement. Call the setBinaryStream(,,) method and finally the execute() method.

JDK 1.1 added the new method createImage(byte[]) to class java.awt.Toolkit. This made it easier to retrieve an image file store in the database. Create the query to select the column of the image. Device a loop to read the entire image into a byte array using the InputStream created by calling getBinaryStream(int) of ResultSet. Finally, simply call createImage(byte[]) to get the Image object.

You can get the Toolkit instance by calling the static method Toolkit.getDefaultToolkit(). Note that JDK 1.0.2 does not have the createImage(byte[]) method.

 

J12. Does IDS JDBC Driver work with Java servlets?

Yes.  IDS JDBC Driver is a pure Java component that works in all Java environments including servlets.  JDBC and Java servlet are two separate Java technologies that do not exclude each other.  To give an example, using the java.io.* package does not prevent you from using the java.net.* package.  Similarly, using the javax.servlet.* package does not prevent you from using java.sql.* package and the underlying JDBC drivers.  From the IDS JDBC Driver stand-point, running inside a servlet is very much like running inside a stand-alone Java application.  Finally, don't confuse this question with "Can IDS Server launch Java servlets?"

 

J13. Why is the OUT parameter of my stored procedure always zero or empty String?

A new requirement for ODBC 3.5 API is that stored procedure return values are not available until after all result sets and updates are processed, which correspond to JDBC condition getMoreResults() == false and getUpdateCount() == -1. Here are some related ODBC documents:

Processing Stored Procedure Results (2nd paragraph from the bottom) 
How to process return codes and output parameters (1st paragraph)
Procedure Parameters (2nd paragraph from the bottom)
SQLMoreResults (3rd paragraph of "Comments")

IDS Server 3.5.x uses ODBC 3.5 API and has the same requirements above. The usual routine to do this may look like this:

    PreparedStatement prep = conn.prepare(sql_statement);
    ...
    boolean query = prep.execute();
    int upd_count = prep.getUpdateCount();

    while (query || upd_count >= 0) {
        if (query) {
            ResultSet rs = prep.getResultSet();
            ...
        }
        else {
            // An UPDATE/INSERT/DELETE that modified upd_count rows
            ...
        }
        query = prep.getMoreResults();
        upd_count = prep.getUpdateCount();
    }
    // No more

If you know your stored procedure only return OUT parameters, simply loop through getMoreResults() and getUpdateCount(), then examine your output parameters:

    CallableStatement proc = conn.prepareCall(sql_statement);
    ...
    proc.execute();
    while (proc.getMoreResults() || proc.getUpdateCount() != -1);
    // No more
    number = call.getInt(1);

 

J14. Why my stored procedure with OUT parameter works in JDBC-ODBC Bridge and IDS Server 3.2.x but not in IDS Server 3.5.x?

Both JDBC-ODBC Bridge and IDS Server 3.2.x uses ODBC 2.x API, which makes OUT parameter values available once the stored procedure is executed.  The problem for this behavior is that some stored procedures contain multiple SQL statements, and the OUT parameter value may not be assigned until all statements are processed. IDS Server 3.5.x uses ODBC 3.5 API, which changes how OUT parameters values are assigned to address this problem. Refer to topic J13 for details.

 

J15. How do I use setDate() setTime() setTimestamp() and getDate() getTime() getTimestamp() methods with a Calendar parameter?

1. There is only one unique time value for one point in time regardless of time zone. This unique value is the long value returned by method java.util.Date.getTime(). This method is inherited by JDBC classes java.sql.Timestamp/Time/Date.

2. By design, java.util.Date and java.sql.Timestamp/Time/Date classes only represent local time. If you run the following lines through VMs on different time zone computers, they yield the same GMT value but different local time values.

    Date date = new Date(1002033911485L); 
    System.out.println(date.toGMTString()); 
    System.out.println(date.toString()); // different local time

With the exception of toGMTString(), UTC() and methods and constructors that take or return long, all other methods and constructors in these 4 classes are about local time.

3. All setXXX methods with a Calendar parameter in PreparedStatement are meant to send a local time value of the time zone in the Calendar parameter to the database. Therefore, if you want the database always store time value of a specific time zone (EST, GMT, etc.), you pass a Calendar instance of that time zone to these methods. For example, if a column must always stored GMT values, then the PrepareStatement for inserting new rows should have the following call:

    Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    prep.setTimestamp(1, timestamp, cal);

4. All getXXX methods with a Calendar parameter in CallableStatement and ResultSet are meant to create a local time of the VM computer from a local time value stored in the database, and the Calendar parameter indicates the time zone of the local time value from the database.  For example, if the column in the database stores GMT values, then the ResultSet used to retrieve this column should do the following:

    Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    Timestamp ts = rs.getTimestamp(column_name, cal);

5. If you want a unified view for this, then consider that all Calendar parameters in these get/set methods are about the time zone information of time values in the database.  The behavior of IDS JDBC Driver guarantees that you can consistently insert and retrieve time value of any time-zone regardless of the default time zone of the client's Java VM.  For example, assuming the following code refers to the same column and row of the same table which always store GMT values,

    Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    Timestamp ts = rs.getTimestamp(column_name, cal);
    System.out.println(ts.getTime());      // same long value
    System.out.println(ts.toGMTString());  // same GMT value
    System.out.println(ts.toString());     // different local time

then running it in New York (EST -05:00GMT) and in Los Angeles (PST -08:00GMT) will yield the same first two lines, and the third line is their respective local time with a 3 hour difference.

6. The JDBC API document is not very precise about the true role of the Calendar parameter. The descriptions in 3, 4 and 5 are based on our understanding of this subject. We have also explored other interpretations, but all have obvious flaws. We will be happy to elaborate if you are interested.

7. There is a bug in IDS JDBC Driver version 3.5.5 or older in these methods with a Calendar parameter. It is fixed in IDS Server version 3.5.6.

 

J16. How can I connect to an ODBC data source without setting up a DSN in ODBC Driver Manager (Control Panel)?

This depends on the ODBC driver. You can do it for both SQL Server and MS Access by specifying the ODBC Browse Connect string for the JDBC 'dsn=' parameter. Here are 2 examples:

// For SQL Server
String url = "jdbc:ids://michaelc:12/conn?dsn=" + 
    "'DRIVER={SQL Server};SERVER=x;UID=y;PWD=z'";

// For MS Access
String url = "jdbc:ids://michaelc:12/conn?dsn=" +
    "'DRIVER={Microsoft Access Driver (*.mdb)};" + 
    "DBQ=c:\idsserver\file\examples\IDSExamples.mdb'";

The entire value after dsn= is enclosed by a pair of single quotes.  The value of the DRIVER parameter is exactly what you see in Control Panel - ODBC - Drivers enclosed in a pair of {}. Parameter DRIVER, UID and PWD are common to all drivers, others are driver specific. Here SERVER is SQL Server specific parameter refers to the database server name.  DBQ is MS Access specific refers to the .mdb file of the database.  Please refer to the ODBC driver documentation for function SQLBrowseConnect() for the syntax of the connection string.