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

Question

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.

The attachment datatype contains a recordset that can be read with ADO or DAO. There are notes here that may give you some clues http://stackoverflow.com/questions/14223266/how-to-copy-the-contents-of-an-attached-file-from-an-ms-access-db-into-a-vba-var/14235764#14235764 — Fionnuala, Mar 22 '13 at 20:23

score 1 · Accepted Answer · edited May 23 '17 at 12:06

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

score 0 · Answer 2 · answered Mar 28 '13 at 06:57

0

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

answered Mar 28 '13 at 06:57

daiwei

1

score 0 · Answer 3 · edited May 23 '17 at 12:22

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

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

3 Answers3