helpinghand
search
needassistance
 
How To
Get the Most Out of BLOBs and JDBC
Categories: JDBC, Programming Examples
Introduction

In Mimer SQL Engine V9, we introduced the Binary Large OBject (BLOB) and Character Large OBject (CLOB) data types. (Big up to us!) BLOBs are used to store arbitrary data, such as images or sound, while CLOBs are used to store large character strings.

This article describes how to use BLOBs in Mimer SQL Engine via JDBC. We’ve even included a program that can exchange data between a BLOB column and a file on your host platform.

In an effort to be as efficient as possible ;-) the table used in this article is identical to the table used by the BlobDemo program, see Working with BLOBs and Mimer SQL Engine in Windows. And BLOBs stored by one program can be read by the other, and vice versa.

The Example Program

You can review the source code here here and download the compiled code here. It has a simple command-line interface.

By using the –import switch, files can be read from the host platform into a BLOB column in a table called BLOBTABLE.

By using the –export switch, the program can read the BLOB column and recreate the files.

Environment

You need to have a Java2 environment installed.

The example program uses the Mimer JDBC 2.5 driver.

You also need to use Mimer SQL Engine V9.

Running the Example Program
C:\Java>java BlobExample
Usage: BlobExample [-url url] {action...}
{action}: -init Create BLOBTABLE
-import path... Copy file at path into a BLOB
-export name [path]
Read BLOBs from database that match name
name can include LIKE wildcards (%_)
Create file(s) with BLOB contents
in current directory (or in specified path)
-list List what's stored in the database
You control the program by using switches on the command line.

We will first try the –init switch which creates the database table (BLOBTABLE) used by this example:
C:\Java>java BlobExample -url jdbc:mimer://SYSADM:SYSADM@gere/pesc91 -init

Creating the Example Table

We use the –url switch to specify the database, user name and password to use. If you don’t specify a URL, the program will use the default URL.

This default URL is, in fact, the same one as was specified above, so all of the examples that follow will omit the –url switch, for brevity.

If you want to change the default URL, you can edit the example program and recompile it.

The –init switch used above assumes that the user, in this case SYSADM, has access to a databank that can be used to store the table in. If you don’t have a databank, you can create one by starting BSQL and issuing the command:
SQL> CREATE DATABANK MYDB;
Importing Files
C:\images>dir *.jpg

Volume in drive C has no label
Volume Serial Number is 3929-4375
Directory of C:\images


YOGA JPG 16 090 00-07-26 12.44 yoga.jpg
REMOTE JPG 109 712 00-09-28 11.32 remote.jpg
BOMBSQ~1 JPG 222 415 01-02-05 9.56 Bombsquad.jpg
PC130145 JPG 234 668 01-12-14 0.02 Pc130145.jpg
PC130152 JPG 250 414 01-12-14 0.03 Pc130152.jpg
WATERP~1 JPG 141 141 02-03-20 14.28 waterpark_1.jpg
WOM1 JPG 169 371 02-05-31 11.39 wom1.jpg
CAT JPG 41 026 02-06-09 17.16 cat.jpg
8 file(s) 1 184 837 bytes
0 dir(s) 967.69 MB free


C:\images>java BlobExample -import *.jpg
Copy to database: Bombsquad.jpg 222415 bytes
Copy to database: cat.jpg 41026 bytes
Copy to database: Pc130145.jpg 234668 bytes
Copy to database: Pc130152.jpg 250414 bytes
Copy to database: remote.jpg 109712 bytes
Copy to database: waterpark_1.jpg 141141 bytes
Copy to database: wom1.jpg 169371 bytes
Copy to database: yoga.jpg 16090 bytes
You can use the –import switch to import any file to the BLOBTABLE table.

Each file will create a new row in the table. The file contents will be copied to a BLOB column.

Listing Table Contents
C:\images>java BlobExample -list
List example databank contents
Size Name
========== ========================================
222415 Bombsquad.jpg
234668 Pc130145.jpg
250414 Pc130152.jpg
41026 cat.jpg
109712 remote.jpg
141141 waterpark_1.jpg
169371 wom1.jpg
16090 yoga.jpg
The –list switch lists the contents of the BLOBTABLE table.

The program executes the SQL statement:
SELECT name, OCTET_LENGTH(data) FROM BLOBTABLE …
to get the information.

The SQL function OCTET_LENGTH returns the current size (in bytes) of a BLOB value.

Exporting BLOBs to Files
C:\images>mkdir temp
C:\images>cd temp
C:\images\temp>java BlobExample -export w%
Read BLOBs with name like w% to directory C:\images\temp
Write 141141 bytes to C:\images\temp\waterpark_1.jpg
Write 169371 bytes to C:\images\temp\wom1.jpg
The –export switch searches BLOBTABLE for records that matches the specified name.

Note You can use LIKE patterns, see the Mimer SQL Reference Manual (use % for wildcards), when selecting names.

The program will not write to a file if it already exists. That’s why an empty directory was created for the example above.

How the Program Works

Reading BLOBs

Reading data from a BLOB column is similar to reading data from any other column type in JDBC.

You must first execute a select statement where the BLOB column is selected. This creates a ResultSet object. Since the BLOB may be arbitrarily large, you should use the ResultSet.getBinaryStream() method to access the BLOB. This method will return a java.io.InputStream object which can be read as any other stream.

Note You must read the entire stream and close it before you access another column in the ResultSet.

From exportFiles() in BlobExample.java:
pssel.setString(1, name);		// Set search value of PreparedStatement
rs = pssel.executeQuery(); // Execute PreparedStatement
while (rs.next()) { // Loop through records
bs = rs.getBinaryStream(3); // Open a InputStream to the BLOB
bytes = bs.read(buf); // Read bytes from BLOB stream
Storing BLOBs

To store the contents of a file in a database record, the program first tries to INSERT the record. If this fails, it assumes that a record with that name already exists, and tries to UPDATE the record instead.

In both cases, a PreparedStatement is used. For example, the PreparedStatement for the INSERT statement is created by the following code (method crepsins()):
psins = con.prepareStatement("insert into BLOBTABLE(name,type,cre_date,data)"+
" values (?,'',CURRENT_TIMESTAMP,?)");
The PreparedStatement has a parameter marker (?) for the BLOB column (data).

Storing the contents of a file is very simple. First open the file. This produces a java.io.InputStream object. Then store the entire file by calling the setBinaryStream() method. From the importFile() method in BlobExample.java:
File file = new File(path);		// Create File object for the file
long filesize = file.length(); // Get size of file
fs = new FileInputStream(file); // Open the file
bs = new BufferedInputStream(fs, 16384); // Use file buffering
psins.setBinaryStream(2, bs, (int)filesize);
First note that this example program creates a BufferedInputStream object for the stream. Using buffered I/O when reading files can increase performance.

Also note that the JDBC method setBinaryStream() needs to have the total size of the stream before it starts reading it. Luckily, the File.length() method provides this information.


Links

The example program source: BlobExample.java

The compiled code class: BlobExample.class

For performance issues when using JDBC, please read the article Use Mimer JDBC and Fetch Sizes.

Last updated: 2002-06-18

 

Powered by Mimer SQL

Powered by Mimer SQL