Set - 2

Question 1 :

I would like to download the JDBC-ODBC Bridge for the Java 2 SDK, Standard Edition (formerly JDK 1.2). I'm a beginner with the JDBC API, and I would like to start with the Bridge. How do I do it?

Answer :

The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.

Question 2 :

If I use the JDBC API, do I have to use ODBC underneath?

Answer :

No, this is just one of many possible solutions. We recommend using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get all of the benefits of the Java programming language and the JDBC API.

Question 3 :

Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?

Answer :

You still need to get and install a JDBC technology-enabled driver that supports the database that you are using. There are many drivers available from a variety of sources. You can also try using the JDBC-ODBC Bridge if you have ODBC connectivity set up already. The Bridge comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it doesn't require any extra setup itself. The Bridge is a normal ODBC client. Note, however, that you should use the JDBC-ODBC Bridge only for experimental prototyping or when you have no other driver available.

Question 4 :

What is the best way to generate a universally unique object ID? Do I need to use an external resource like a file or database, or can I do it all in memory?

Answer :

1: Unique down to the millisecond. Digits 1-8 are the hex encoded lower 32 bits of the System.currentTimeMillis() call.
2: Unique across a cluster. Digits 9-16 are the encoded representation of the 32 bit integer of the underlying IP address.
3: Unique down to the object in a JVM. Digits 17-24 are the hex representation of the call to System.identityHashCode(), which is guaranteed to return distinct integers for distinct objects within a JVM.
4: Unique within an object within a millisecond. Finally digits 25-32 represent a random 32 bit integer generated on every method call using the cryptographically strong class.

There are two reasons to use the random number instead of incrementing your last. 1. The number would be predictable and, depending on what this is used for, you could be opening up a potential security issue. This is why ProcessIDs are randomized on some OSes (AIX for one). 2. You must synchronize on that counter to guarantee that your number isn't reused. Your random number generator need not be synchronized, (though its implementation may be).

1) If your using Oracle You can create a sequence ,by which you can generate unique primary key or universal primary key. 2) you can generate by using random numbers but you may have to check the range and check for unique id. ie random number generate 0.0 to 1.0 u may have to make some logic which suits your unique id 3) Set the maximum value into an XML file and read that file at the time of loading your application from xml .

Question 5 :

What happens when I close a Connection application obtained from a connection Pool? How does a connection pool maintain the Connections that I had closed through the application?

Answer :

It is the magic of polymorphism, and of Java interface vs. implementation types. Two objects can both be "instanceof" the same interface type, even though they are not of the same implementation type.
When you call "getConnection()" on a pooled connection cache manager object, you get a "logical" connection, something which implements the java.sql.Connection interface.
But it is not the same implementation type as you would get for your Connection, if you directly called getConnection() from a (non-pooled/non-cached) datasource.
So the "close()" that you invoke on the "logical" Connection is not the same "close()" method as the one on the actual underlying "physical" connection hidden by the pool cache manager.
The close() method of the "logical" connection object, while it satisfies the method signature of close() in the java.sql.Connection interface, does not actually close the underlying physical connection.

Typically a connection pool keeps the active/in-use connections in a hashtable or other Collection mechanism. I've seen some that use one stack for ready-for-use, one stack for in-use.
When close() is called, whatever the mechanism for indicating inuse/ready-for-use, that connection is either returned to the pool for ready-for-use or else physically closed. Connections pools should have a minimum number of connections open. Any that are closing where the minimum are already available should be physically closed.
Some connection pools periodically test their connections to see if queries work on the ready-for-use connections or they may test that on the close() method before returning to the ready-for-use pool.

Question 6 :

How do I insert a .jpg into a mySQL data base? I have tried inserting the file as byte[], but I recieve an error message stating that the syntax is incorrect?

Answer :

Binary data is stored and retrieved from the database using
streams in connection with prepared statements and resultsets.
This minimal application stores an image file in the database,
then it retrieves the binary data from the database and converts
it back to an image.

import java.sql.*;
import java.awt.*;
import java.awt.Image;

* Storing and retrieving images from a MySQL database
public class StoreBinary {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private Statement stmt = null;
private Connection conn = null;

public StoreBinary() {}
* Strips path prefix from filenames
* @param fileName
* @return the base filename
public static String getBaseName(String fileName) {
int ix=fileName.lastIndexOf("\\");
if (ix < 0) return fileName;
return fileName.substring(ix+1);
* Store a binary (image) file in the database using a
* prepared statement.
* @param fileName
* @return true if the operation succeeds
* @throws Exception
public boolean storeImageFile(String fileName) throws Exception {
if (!connect("test", "root", "")) {
return false;

FileInputStream in = new FileInputStream(fileName);
int len=in.available();
String baseName=StoreBinary.getBaseName(fileName);
PreparedStatement pStmt = conn.prepareStatement
("insert into image_tab values (?,?,?)");
pStmt.setString(1, baseName);
pStmt.setBinaryStream(3, in, len);
System.out.println("Stored: "+baseName+", length: "+len);
return true;
* Retrieve the biary file data from the DB and convert it to an image
* @param fileName
* @return
* @throws Exception
public Image getImageFile(String fileName) throws Exception {
String baseName=StoreBinary.getBaseName(fileName);

ResultSet rs=stmt.executeQuery("select * from image_tab
where image_name='"+baseName+"'");

if (! {
System.out.println("Image:"+baseName+" not found");
return null;
int len=rs.getInt(2);

byte [] b=new byte[len];
InputStream in = rs.getBinaryStream(3);
System.out.println("n: "+n);
Image img=Toolkit.getDefaultToolkit().createImage(b);
System.out.println("Image: "+baseName+" retrieved ok, size: "+len);
return img;
* Establish database connection
* @param dbName
* @param dbUser
* @param dbPassword
* @return true if the operation succeeds
public boolean connect(String dbName, String dbUser, String dbPassword) {
try {
catch (ClassNotFoundException ex) {
return false;
try {
conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
stmt = conn.createStatement();
catch (SQLException ex1) {
return false;
return true;

* MAIN stub driver for testing the class.
public static void main(String[] args) {
String fileName="c:\\tmp\\f128.jpg";
StoreBinary sb = new StoreBinary();
try {
if (sb.storeImageFile(fileName)) {
// stored ok, now get it back again
Image img=sb.getImageFile(fileName);
catch (Exception ex) {

Question 7 :

How can I know when I reach the last record in a table, since JDBC doesn't provide an EOF method?

Answer :

You can use last() method of java.sql.ResultSet, if you make it scrollable.
You can also use isLast() as you are reading the ResultSet.
One thing to keep in mind, though, is that both methods tell you that you have reached the end of the current ResultSet, not necessarily the end of the table. SQL and RDBMSes make no guarantees about the order of rows, even from sequential SELECTs, unless you specifically use ORDER BY. Even then, that doesn't necessarily tell you the order of data in the table.

Assuming you mean ResultSet instead of Table, the usual idiom for iterating over a forward only resultset is:
ResultSet rs=statement.executeQuery(...);
while ( {
// Manipulate row here

Question 8 :

Where can I find info, frameworks and example source for writing a JDBC driver?

Answer :

There a several drivers with source available, like MM.MySQL, SimpleText Database, FreeTDS, and RmiJdbc. There is at least one free framework, the jxDBCon-Open Source JDBC driver framework. Any driver writer should also review For Driver Writers.

Question 9 :

How can I create a custom RowSetMetaData object from scratch?

Answer :

One unfortunate aspect of RowSetMetaData for custom versions is that it is an interface. This means that implementations almost have to be proprietary. The JDBC RowSet package is the most commonly available and offers the sun.jdbc.rowset.RowSetMetaDataImpl class. After instantiation, any of the RowSetMetaData setter methods may be used. The bare minimum needed for a RowSet to function is to set the Column Count for a row and the Column Types for each column in the row. For a working code example that includes a custom RowSetMetaData,

Question 10 :

How does a custom RowSetReader get called from a CachedRowSet?

Answer :

The Reader must be registered with the CachedRowSet using CachedRowSet.setReader(javax.sql.RowSetReader reader). Once that is done, a call to CachedRowSet.execute() will, among other things, invoke the readData method.

Question 11 :

How do I implement a RowSetReader? I want to populate a CachedRowSet myself and the documents specify that a RowSetReader should be used. The single method accepts a RowSetInternal caller and returns void. What can I do in the readData method?

Answer :

"It can be implemented in a wide variety of ways..." and is pretty vague about what can actually be done. In general, readData() would obtain or create the data to be loaded, then use CachedRowSet methods to do the actual loading. This would usually mean inserting rows, so the code would move to the insert row, set the column data and insert rows. Then the cursor must be set to to the appropriate position.

Question 12 :

How can I instantiate and load a new CachedRowSet object from a non-JDBC source?

Answer :

The basics are:
* Create an object that implements javax.sql.RowSetReader, which loads the data.
* Instantiate a CachedRowset object.
* Set the CachedRowset's reader to the reader object previously created.
* Invoke CachedRowset.execute().

Note that a RowSetMetaData object must be created, set up with a description of the data, and attached to the CachedRowset before loading the actual data.
The following code works with the Early Access JDBC RowSet download available from the Java Developer Connection and is an expansion of one of the examples:
// Independent data source CachedRowSet Example
import java.sql.*;
import javax.sql.*;
import sun.jdbc.rowset.*;

public class RowSetEx1 implements RowSetReader
CachedRowSet crs;
int iCol2;
RowSetMetaDataImpl rsmdi;
String sCol1,

public RowSetEx1()
crs = new CachedRowSet();
crs.execute(); // load from reader

"Fetching from RowSet...");
} // end while next

if(crs.isAfterLast() == true)
"We have reached the end");
System.out.println("crs row: " +

"And now backwards...");

} // end while previous

if(crs.isBeforeFirst() == true)
{ System.out.println(
"We have reached the start");

if(crs.isFirst() == true)
{ System.out.println(
"We have moved to first");

System.out.println("crs row: " +

if(crs.isBeforeFirst() == false)
{ System.out.println(
"We aren't before the first row."); }

if(crs.isLast() == true)
{ System.out.println(
"...and now we have moved to the last");

System.out.println("crs row: " +

if(crs.isAfterLast() == false)
"we aren't after the last.");

} // end try
catch (SQLException ex)
System.err.println("SQLException: " +

} // end constructor

public void showTheData() throws SQLException
sCol1 = crs.getString(1);
if(crs.wasNull() == false)
{ System.out.println("sCol1: " + sCol1); }
else { System.out.println("sCol1 is null"); }

iCol2 = crs.getInt(2);
if (crs.wasNull() == false)
{ System.out.println("iCol2: " + iCol2); }
else { System.out.println("iCol2 is null"); }

sCol3 = crs.getString(3);
if (crs.wasNull() == false)
System.out.println("sCol3: " +
sCol3 + "\n" );
{ System.out.println("sCol3 is null\n"); }

} // end showTheData

// RowSetReader implementation
public void readData(RowSetInternal caller)
throws SQLException
rsmdi = new RowSetMetaDataImpl();
rsmdi.setColumnType(1, Types.VARCHAR);
rsmdi.setColumnType(2, Types.INTEGER);
rsmdi.setColumnType(3, Types.VARCHAR);
crs.setMetaData( rsmdi );


crs.updateString( 1, "StringCol11" );
crs.updateInt( 2, 1 );
crs.updateString( 3, "StringCol31" );

crs.updateString( 1, "StringCol12" );
crs.updateInt( 2, 2 );
crs.updateString( 3, "StringCol32" );


} // end readData

public static void main(String args[])
new RowSetEx1();

} // end class RowSetEx1

Question 13 :

Can I set up a connection pool with multiple user IDs? The single ID we are forced to use causes problems when debugging the DBMS?

Answer :

Since the Connection interface ( and the underlying DBMS ) requires a specific user and password, there's not much of a way around this in a pool. While you could create a different Connection for each user, most of the rationale for a pool would then be gone. Debugging is only one of several issues that arise when using pools.
However, for debugging, at least a couple of other methods come to mind. One is to log executed statements and times, which should allow you to backtrack to the user. Another method that also maintains a trail of modifications is to include user and timestamp as standard columns in your tables. In this last case, you would collect a separate user value in your program.

Question 14 :

How can I protect my database password ? I'm writing a client-side java application that will access a database over the internet. I have concerns about the security of the database passwords. The client will have access in one way or another to the class files, where the connection string to the database, including user and password, is stored in as plain text. What can I do to protect my passwords?

Answer :

This is a very common question.
Conclusion: JAD decompiles things easily and obfuscation would not help you. But you'd have the same problem with C/C++ because the connect string would still be visible in the executable.
SSL JDBC network drivers fix the password sniffing problem (in MySQL 4.0), but not the decompile problem. If you have a servlet container on the web server, I would go that route (see other discussion above) then you could at least keep people from reading/destroying your mysql database.
Make sure you use database security to limit that app user to the minimum tables that they need, then at least hackers will not be able to reconfigure your DBMS engine.
Aside from encryption issues over the internet, it seems to me that it is bad practice to embed user ID and password into program code. One could generally see the text even without decompilation in almost any language. This would be appropriate only to a read-only database meant to be open to the world. Normally one would either force the user to enter the information or keep it in a properties file.

Question 15 :

Detecting Duplicate Keys I have a program that inserts rows in a table. My table has a column 'Name' that has a unique constraint. If the user attempts to insert a duplicate name into the table, I want to display an error message by processing the error code from the database. How can I capture this error code in a Java program?

Answer :

A solution that is perfectly portable to all databases, is to execute a query for checking if that unique value is present before inserting the row. The big advantage is that you can handle your error message in a very simple way, and the obvious downside is that you are going to use more time for inserting the record, but since you're working on a PK field, performance should not be so bad.
You can also get this information in a portable way, and potentially avoid another database access, by capturing SQLState messages. Some databases get more specific than others, but the general code portion is 23 - "Constraint Violations". UDB2, for example, gives a specific such as 23505, while others will only give 23000.

Question 16 :

What driver should I use for scalable Oracle JDBC applications?

Answer :

Sun recommends using the thin ( type 4 ) driver.
* On single processor machines to avoid JNI overhead.
* On multiple processor machines, especially running Solaris, to avoid synchronization bottlenecks.

Question 17 :

Can you scroll a result set returned from a stored procedure?

Answer :

I am returning a result set from a stored procedure with type SQLRPGLE but once I reach the end of the result set it does not allow repositioning. Is it possible to scroll this result set?

A CallableStatement is no different than other Statements in regard to whether related ResultSets are scrollable. You should create the CallableStatement using Connection.prepareCall(String sql, int resultSetType, int resultSetConcurrency).

Question 18 :

How do I write Greek ( or other non-ASCII/8859-1 ) characters to a database?

Answer :

From the standard JDBC perspective, there is no difference between ASCII/8859-1 characters and those above 255 ( hex FF ). The reason for that is that all Java characters are in Unicode ( unless you perform/request special encoding ). Implicit in that statement is the presumption that the data store can handle characters outside the hex FF range or interprets different character sets appropriately. That means either:

* The OS, application and database use the same code page and character set. For example, a Greek version of NT with the DBMS set to the default OS encoding.
* The DBMS has I18N support for Greek ( or other language ), regardless of OS encoding. This has been the most common for production quality databases, although support varies. Particular DBMSes may allow setting the encoding/code page/CCSID at the database, table or even column level. There is no particular standard for provided support or methods of setting the encoding. You have to check the DBMS documentation and set up the table properly.
* The DBMS has I18N support in the form of Unicode capability. This would handle any Unicode characters and therefore any language defined in the Unicode standard. Again, set up is proprietary.

Question 19 :

How can I insert images into a Mysql database?

Answer :

This code snippet shows the basics:

File file = new File(fPICTURE);
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps =
ConrsIn.prepareStatement("insert into dbPICTURE values (?,?)");

// ***use as many ??? as you need to insert in the exact order***

Question 20 :

Is possible to open a connection to a database with exclusive mode with JDBC?

Answer :

I think you mean "lock a table in exclusive mode". You cannot open a connection with exclusive mode. Depending on your database engine, you can lock tables or rows in exclusive mode.
In Oracle you would create a statement st and run
st.execute("lock table mytable in exclusive mode");
Then when you are finished with the table, execute the commit to unlock the table. Mysql, Informix and SQLServer all have a slightly different syntax for this function, so you'll have to change it depending on your database. But they can all be done with execute().

Question 21 :

What are the standard isolation levels defined by JDBC?

Answer :

The values are defined in the class java.sql.Connection and are:

Question 22 :

Update fails without blank padding. Although a particular row is present in the database for a given key, executeUpdate() shows 0 rows updated and, in fact, the table is not updated. If I pad the Key with spaces for the column length (e.g. if the key column is 20 characters long, and key is msgID, length 6, I pad it with 14 spaces), the update then works!!! Is there any solution to this problem without padding?

Answer :

In the SQL standard, CHAR is a fixed length data type. In many DBMSes ( but not all), that means that for a WHERE clause to match, every character must match, including size and trailing blanks. As Alessandro indicates, defining CHAR columns to be VARCHAR is the most general answer.

Question 23 :

What isolation level is used by the DBMS when inserting, updating and selecting rows from a database?

Answer :

The answer depends on both your code and the DBMS. If the program does not explicitly set the isolation level, the DBMS default is used. You can determine the default using DatabaseMetaData.getDefaultTransactionIsolation() and the level for the current Connection with Connection.getTransactionIsolation(). If the default is not appropriate for your transaction, change it with Connection.setTransactionIsolation(int level).

Question 24 :

How can I determine the isolation levels supported by my DBMS?

Answer :

Use DatabaseMetaData.supportsTransactionIsolationLevel(int level).

Question 25 :

Connecting to a database through the Proxy I want to connect to remote database using a program that is running in the local network behind the proxy. Is that possible?

Answer :

I assume that your proxy is set to accept http requests only on port 80. If you want to have a local class behind the proxy connect to the database for you, then you need a servlet/JSP to receive an HTTP request and use the local class to connect to the database and send the response back to the client.
You could also use RMI where your remote computer class that connects to the database acts as a remote server that talks RMI with the clients. if you implement this, then you will need to tunnel RMI through HTTP which is not that hard.
In summary, either have a servlet/JSP take HTTP requests, instantiate a class that handles database connections and send HTTP response back to the client or have the local class deployed as RMI server and send requests to it using RMI.

Question 26 :

How do I receive a ResultSet from a stored procedure?

Answer :

Stored procedures can return a result parameter, which can be a result set. For a discussion of standard JDBC syntax for dealing with result, IN, IN/OUT and OUT parameters, see Stored Procedures.

Question 27 :

How can I write to the log used by DriverManager and JDBC drivers?

Answer :

The simplest method is to use DriverManager.println(String message), which will write to the current log.

Question 28 :

How can I get or redirect the log used by DriverManager and JDBC drivers?

Answer :

As of JDBC 2.0, use DriverManager.getLogWriter() and DriverManager.setLogWriter(PrintWriter out). Prior to JDBC 2.0, the DriverManager methods getLogStream() and setLogStream(PrintStream out) were used. These are now deprecated.

Question 29 :

What does it mean to "materialize" data?

Answer :

This term generally refers to Array, Blob and Clob data which is referred to in the database via SQL locators "Materializing" the data means to return the actual data pointed to by the Locator.
For Arrays, use the various forms of getArray() and getResultSet().
For Blobs, use getBinaryStream() or getBytes(long pos, int length).
For Clobs, use getAsciiStream() or getCharacterStream().

Question 30 :

Why do I have to reaccess the database for Array, Blob, and Clob data?

Answer :

Most DBMS vendors have implemented these types via the SQL3 Locator type
Some rationales for using Locators rather than directly returning the data can be seen most clearly with the Blob type. By definition, a Blob is an arbitrary set of binary data. It could be anything; the DBMS has no knowledge of what the data represents. Notice that this effectively demolishes data independence, because applications must now be aware of what the Blob data actually represents. Let's assume an employee table that includes employee images as Blobs.
Say we have an inquiry program that presents multiple employees with department and identification information. To see all of the data for a specific employee, including the image, the summary row is selected and another screen appears. It is only at this pont that the application needs the specific image. It would be very wasteful and time consuming to bring down an entire employee page of images when only a few would ever be selected in a given run.
Now assume a general interactive SQL application. A query is issued against the employee table. Because the image is a Blob, the application has no idea what to do with the data, so why bring it down, killing performance along the way, in a long running operation?
Clearly this is not helpful in those applications that need the data everytime, but these and other considerations have made the most general sense to DBMS vendors.

Question 31 :

What is an SQL Locator?

Answer :

A Locator is an SQL3 data type that acts as a logical pointer to data that resides on a database server. Read "logical pointer" here as an identifier the DBMS can use to locate and manipulate the data. A Locator allows some manipulation of the data on the server. While the JDBC specification does not directly address Locators, JDBC drivers typically use Locators under the covers to handle Array, Blob, and Clob data types.

Question 32 :

How do I set properties for a JDBC driver and where are the properties stored?

Answer :

A JDBC driver may accept any number of properties to tune or optimize performance for the specific driver. There is no standard, other than user and password, for what these properties should be. Therefore, the developer is dependent on the driver documentation to automatically pass properties. For a standard dynamic method that can be used to solicit user input for properties, see What properties should I supply to a database driver in order to connect to a database?
In addition, a driver may specify its own method of accepting properties. Many do this via appending the property to the JDBC Database URL. However, a JDBC Compliant driver should implement the connect(String url, Properties info) method. This is generally invoked through DriverManager.getConnection(String url, Properties info).
The passed properties are ( probably ) stored in variables in the Driver instance. This, again, is up to the driver, but unless there is some sort of driver setup, which is unusual, only default values are remembered over multiple instantiations.

Question 33 :

What is the JDBC syntax for using a literal or variable in a standard Statement?

Answer :

First, it should be pointed out that PreparedStatement handles many issues for the developer and normally should be preferred over a standard Statement.
Otherwise, the JDBC syntax is really the same as SQL syntax. One problem that often affects newbies ( and others ) is that SQL, like many languages, requires quotes around character ( read "String" for Java ) values to distinguish from numerics. So the clause:
"WHERE myCol = " + myVal
is perfectly valid and works for numerics, but will fail when myVal is a String. Instead use:
"WHERE myCol = '" + myVal + "'"
if myVal equals "stringValue", the clause works out to:
WHERE myCol = 'stringValue'
You can still encounter problems when quotes are embedded in the value, which, again, a PreparedStatement will handle for you.

Question 34 :

How do I check in my code whether a maximum limit of database connections have been reached?

Answer :

Use DatabaseMetaData.getMaxConnections() and compare to the number of connections currently open. Note that a return value of zero can mean unlimited or, unfortunately, unknown. Of course, driverManager.getConnection() will throw an exception if a Connection can not be obtained.

Question 35 :

Why do I get UnsatisfiedLinkError when I try to use my JDBC driver?

Answer :

The first thing is to be sure that this does not occur when running non-JDBC apps. If so, there is a faulty JDK/JRE installation. If it happens only when using JDBC, then it's time to check the documentation that came with the driver or the driver/DBMS support. JDBC driver types 1 through 3 have some native code aspect and typically require some sort of client install. Along with the install, various environment variables and path or classpath settings must be in place. Because the requirements and installation procedures vary with the provider, there is no reasonable way to provide details here. A type 4 driver, on the other hand, is pure Java and should never exhibit this problem. The trade off is that a type 4 driver is usually slower.