Insert byte[] into blob field with spring's jdbcTemplate and stored procedure

I'm try to insert byte[] into blob field with stored procedure, and get an Exception :

Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT ID FROM sp_NEWFILE(?,?,?)]; nested exception is org.firebirdsql.jdbc.field.TypeConversionException: Error converting to object.

Model:

public class fileBody { 
private int ID;
private byte[] BODY;
private String FILENAME; //getters an setters}

Insert it to database

public class FileBodyDaoImpl implements FileBodyDao {

public int insertData(final FileBody fileBody) throws IOException {     
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    LobHandler lobHandler = new DefaultLobHandler();        
    final InputStream in = new ByteArrayInputStream(fileBody.getBODY());
    final int fileSize = in.available();
    Map<String, Object> out = jdbcTemplate.queryForMap("SELECT ID FROM  sp_NEWFILE(?,?,?)",
            new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
                protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException,
                        DataAccessException {
                    ps.setString(1, fileBody.getFILENAME());
                    lobCreator.setBlobAsBinaryStream(ps, 2, in, fileSize);
                    ps.setNull(3, java.sql.Types.INTEGER);
                }
            });     
    int last_inserted = Integer.parseInt(String.valueOf(out.get("ID")));
    return last_inserted;
}

And my stored procedure

create or alter procedure sp_NEWFILE (
FILENAME varchar(255),
BODY blob sub_type 0 segment size 80,
USEID integer)
returns (
ID integer)
as
begin
  if (useid is not null) then ID=USEID;
  else ID=GEN_ID(gen_filebody_id,1);
  if ((FILENAME is NULL) or (FILENAME=''))  then FILENAME='UNDEFINED';
  INSERT INTO  t_filebody(ID,BODY,FILENAME) VALUES(:ID,:BODY,:FILENAME);
  suspend;
end^

and i get an Exception:

Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
PreparedStatementCallback; 
bad SQL grammar [SELECT ID FROM sp_NEWFILE(?,?,?)]; nested exception is org.firebirdsql.jdbc.field.TypeConversionException: Error converting to object.

Versions: jaybird-jdk17-2.2.5; Source: firebird2.5 Version: 2.5.1.26351.ds4-2ubuntu0.1;

Solution

The problem is that queryForMap does not support a PreparedStatementCallback (contrary to for example execute), instead your anonymous object is considered a normal parameter for the query to execute, and Jaybird does not support this object type. And if Jaybird had supported it, you would have received an error for missing parameters 2 and 3.

Your code can be greatly simplified by passing the byte array:

Map<String, Object> out = jdbcTemplate.queryForMap("SELECT ID FROM sp_NEWFILE(?,?,?)",
        fileBody.getFILENAME(), fileBody.getBODY(), null);

This works as Jaybird considers a BLOB SUB_TYPE 0 as a java.sql.Types.LONGVARBINARY and JDBC 4.2 appendix B declares that byte[] is the default type for that (although you can also use it as a java.sql.Types.BLOB).

As a side note, your stored procedure does not need to be selectable (removing SUSPEND makes it executable), and the procedure could also be replaced by using a TRIGGER to generate the primary key and retrieving the value either by using INSERT .. RETURNING .. or through the JDBC generated keys facility (which in turn is implemented in Jaybird through INSERT .. RETURNING ..).