CODEWITHSUNDEEP
javams-accessjdbcodbcblob
3ndelebu
3ndelebu

Reputation: 53

How to retrieve files from an MS Access "Attachment" field via JDBC?

I wrote a small Java application to read data from an Access 2007 database. I can read all fields but not the one containing the photo. The field type is "Attachment".

When I call the .getBlob() method of the ResultSet object I catch an "UnsupportedOperationException".

As suggested on several websites I tried to call .getBytes() and .getBinaryStream() methods but both only return the filename of the attachment but not it's content.

Upvotes: 2

Views: 2341

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123549

The UCanAccess JDBC driver can extract files from an Attachment field in an Access database. For a table named [AttachmentsTable]

ID - AutoNumber, Primary Key
Description - Text(255)
Attachments - Attachment

The following code will extract all of the files in the [Attachments] field for the record where ID=1:

import java.io.File;
import java.sql.*;
import net.ucanaccess.complex.Attachment;

...

String dbFileSpec = "C:/Users/Public/AttachmentsDB.accdb";
String connStr = "jdbc:ucanaccess://" + dbFileSpec;
try (Connection conn = DriverManager.getConnection(connStr)) {
    try (Statement s = conn.createStatement()) {
        try (ResultSet rs = s.executeQuery(
                "SELECT Attachments FROM AttachmentsTable WHERE ID=1")) {
            rs.next();
            // retrieve array of net.ucanaccess.complex.Attachment objects
            Attachment[] atts = (Attachment[]) rs.getObject(1);
            for (Attachment att : atts) {
                System.out.println(att.getName());
                org.apache.commons.io.FileUtils.writeByteArrayToFile(
                        new File("C:/Users/Gord/Desktop/" + att.getName()), 
                        att.getData());
            }
        }
    }
}

For more information on using the UCanAccess JDBC driver see

Manipulating an Access database from Java without ODBC

Upvotes: 1

3ndelebu
3ndelebu

Reputation: 53

I found a dirty workaround for certain situations...

The XML export of a table contains the pictures base64 encoded with some extra data. A java application using stax XML API can handle this data easily. It's almost straight forward but a 20 byte overhead in the beginning of the binary data has to be truncated. Read this!

If you are in the compfortable situation to do it only ones you are finished. If you have to do this on a regular basis but not in real time you can try the .ExportXML API from access described here.

cheers

Upvotes: 0

daiwei
daiwei

Reputation: 1

HXTT Access can get uncompressed Attachment data, but doesn't support compressed Attachment data.

Upvotes: 0

Related Questions