javax.imageio.ImageIO and JTDS JDBC Driver to the Rescue

A friend of mine back home in Nigeria, a VB developer hollered at me sometimes last month to ask how he can retrieve blob data containing images stored in a MS SQL database and save them to the local disk. The database contains over 6,000 records. I’m sure there is a way to achieve this in VB, apparently he does not know. . . I completed the task in minutes. . .

It’s been a long time I worked with MSSQL and I think I only used it once since I graduated from school in 2003. Don’t ask me why, ask MySQL. . .lol

First, I don’t have MSSQL installed on my laptop which means I have to write a database independent code which should work for both MySQL and MSSQL. .

I quickly setup a database, created a table and populated it some of my pictures. At the end, I had 30 records in my table. First, we run our select query. .

[sourcecode language=”java”]
//To store the image ID
String imageID;

// To store the binary stream retrieved from the database
InputStream in;

//Path to save Image on local disk
String imagePath="c:\pictures\";
File file;

//Will be used to store the image;
ImageIcon icon;
Image image;

PreparedStatement st=cn.prepareStatement("select ImageID, BlobImage from tblImages");
ResultSet rs=st.executQuery();
while(rs.next())
{
imageID=rs.getString(1);
in=rs.getBinaryStream(2);

//Store the retrieved image ina byte array
byte[] b= new byte[1024];

//Store the bytes in java.awt.Image object
image=Toolkit.getDefaultToolkit().createImage(b);

//Convert java.awt.Image to javax.swing.ImageIcon;

icon= new Imageicon(image);

//Convert the java.awt.Image to a BufferedImage
BufferedImage bi= new BufferedImage(image.getWidth(null), image.getHeight(null), BufferedImage.TYPE_INT_RGB);
Graphics2d g2d= bi.createGraphics(icon.getImage(),0,0,null);

//Create the file name. Our encoding is JPEG
imageName= imageID+".jpg";
file= new File(imagePath+imageName);

//Now lets write out the image to our file path
ImageIO.write(bi, "jpg", file);

}
[/sourcecode]

This worked perfectly well with mySQL but there was a problem when he used it with MS SQL. He complained that only one image is retrieved and saved, and then the ResultSet closes. I figured out that the relationship between JDBC and MSSQL is not a rosy one. I tried JTDS driver, repackaged the application and sent to him. It worked perfect and retrieved the 6000 images in the database in 25 minutes but there was another problem. he could not log in to MSSQL with windows authentication. I sent him a dll file that was shipped with the JTDS package to paste in c:\wondows\system32 and that fixed it.

Below is the UI