getting Blob from DB throws "invalid transaction handle"

Question

I have a problem with fetching results from a database. I'm using firebird, c3p0, JDBCTemplate, SpringMVC.

public class InvoiceDaoImpl implements InvoiceDao {
...
    public Invoice getInvoice(int id) {
    List<Invoice> invoice = new ArrayList<Invoice>();
    String sql = "SELECT ID,FILENAME, FILEBODY FROM T_FILES WHERE id=" + id;
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    invoice = jdbcTemplate.query(sql, new InvoiceRowMapper());
    return invoice.get(0);
}....}    

Used Model:

public class Invoice {  
private int ID;
private Blob FILEBODY;
private String FILENAME;
getters and setters ...
}

RowMapper and Extractor are standard.

In the JSP I'm getting a FileStream and return as file for download:

@RequestMapping("admin/file/GetFile/{id}")
public void invoiceGetFile(@PathVariable("id") Integer id, HttpServletResponse response) {

    Invoice invoice = invoiceService.getInvoice(id);
    try {
        response.setHeader("Content-Disposition", "inline;filename=\"" + invoice.getFILENAME() + "\"");
        OutputStream out = response.getOutputStream();
        response.setContentType("application/x-ms-excel");
        IOUtils.copy(invoice.getFILEBODY().getBinaryStream(), out);
        out.flush();
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

catalina.out:

datasource.DataSourceTransactionManager - Releasing JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@566b1836] after transaction
http-nio-8443-exec-29 DEBUG datasource.DataSourceUtils - Returning JDBC Connection to DataSource
http-nio-8443-exec-29 DEBUG resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@4d2dbc65 [managed: 2, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@4ca5c225)
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544332. 
**invalid transaction handle (expecting explicit transaction start)**
at org.firebirdsql.jdbc.FBBlobInputStream.<init>(FBBlobInputStream.java:38)
at org.firebirdsql.jdbc.FBBlob.getBinaryStream(FBBlob.java:404)

I don't understand why do I need to use transactions handling when I use SELECT, and not UPDATE or INSERT?

Answer 1

Firebird (and JDBC for that matter) does everything in a transaction, because the transaction determines the visibility of data.

In this specific case the select query was executed within a transaction (presumably an auto-commit), but the blob access is done after the transaction has been committed.

This triggers this specific exception because Jaybird knows it requires a transaction to retrieve the blob, but even if Jaybird had a new transaction accessing the blob wouldn't work as the blob handle is only valid inside the transaction that queried for the blob handle.

You will either need to disable auto commit and only commit after retrieving the blob (which would require extensive changes to your DAO by the looks of it), or your row mapper needs to explicitly load the blob (for example into a byte array).

Another option is to ensure this query is executed using a holdable result set (in which case Jaybird will materialize the blob into a byte array within the Blob instance for you), but I am unsure if JdbcTemplate allows you to specify use of holdable result sets.