Java Exception Error - Sqlite preparedStatement.setBlob

Question

I'm placing and image into a database, it could be either an MYSQL database (the Server) or an SQLITE database (a Tablet PC for on the road). The Java application syncs with the server on a daily basis, uploading new data and downloading any new data. That part is working well. However, the requirement is for it to be able to handle images too.

Rather than implement a whole new system that relies on copying the images to the filesystem on each end, we are opting to use blobs in the database. Not really interested in responses that say don't do it ;) what I really need help with is that when I try to write the blob I get an exception in the dispatching.

We are constructing the input forms from the database, as the whole application is being used for different purposes, dependent on the database. The input form allows you to attach an image to the record, we store that as base64 string. Then decode it into a byte[].

My test program converts back and forth between string and byte array (and ultimately an Image) no problem. So I'm fairly convinced that issue is in setting the Blob in the prepared statement, but I could be wrong.

The Exception occurs after the Save button is clicked.

Exception occurred during event dispatching:
java.lang.AbstractMethodError: org.sqlite.PrepStmt.setBlob(ILjava/io/InputStream;J)V
    at tabletapp.database.DB.prepareStatement(DB.java:641)
    at tabletapp.database.DB.save(DB.java:743)
    at tabletapp.FormPanel.saveData(FormPanel.java:546)

The offending code block

public void prepareStatement(String table, String sql, LinkedHashMap<String, String> data) {
    try {
        String typeID = "";
        PreparedStatement ps = connection.prepareStatement(sql);
        log.debug("Preparing SQL: " + sql.replace("\n", ""));
        int parameterIndex = 1;

        //for (String columnName : getColumnNames(table)) {
        for (String columnName : data.keySet()) {
            typeID = getColumnType(table, columnName);

            if (data.containsKey(columnName)) {
                String value = data.get(columnName);
                if (value == null || "".equals(value)) {
                    //log.debug(columnName+":"+parameterIndex+" set to null");
                    ps.setNull(parameterIndex, Types.NULL);
                } else {
                    //log.debug(columnName+":"+parameterIndex+" set to "+value);
                    switch (getColumnTypeId(table, columnName)) {
                        case Types.VARCHAR:
                        case Types.CHAR:
                            ps.setString(parameterIndex, value);
                            break;

                        case Types.TIMESTAMP:
                            DateFormat timestampFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            java.util.Date timstamp = new java.util.Date();
                            ps.setString(parameterIndex, timestampFormat.format(timstamp));
                            break;

                        case Types.DATE:
                            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                            java.util.Date date = new java.util.Date();
                            ps.setString(parameterIndex, dateFormat.format(date));
                            break;

                        case Types.SMALLINT:
                        case Types.INTEGER:
                        case Types.NUMERIC:
                            ps.setInt(parameterIndex, new Integer(value));
                            break;

                        case Types.FLOAT:
                            ps.setFloat(parameterIndex, new Float(value));
                            break;

                        case Types.BLOB:
                            // convert string to byte array to blob
                            byte[] bData = null;
                            try {
                               bData = new BASE64Decoder().decodeBuffer(value);
                               log.info("I have Bytes[]");
                            }
                            catch (Exception e){
                                log.info("Something went Horribly, Horribly Wrong");
                            }

                            // Note tried the follwowing
                            //Blob blob=connection.createBlob();
                            // blob.setBytes(bData.length, bData);
                            // ps.setBlob(parameterIndex,blob);

                            ByteArrayInputStream bais = new ByteArrayInputStream(bData);
                            ps.setBlob(parameterIndex, bais,bData.length);


                            break;

                        case Types.BOOLEAN:
                        case Types.BIT:
                            //log.debug(table + "." + columnName + " (boolean) = " + value);
                            if ("1".equals(value) || "true".equals(value)) {
                                ps.setBoolean(parameterIndex, true);
                            } else {
                                ps.setBoolean(parameterIndex, false);
                            }
                            break;
                    }
                }
                parameterIndex++;
            }
        }
        ps.executeUpdate();
        connection.commit();
    } catch (SQLException e) {
        log.error("Error in sql: " + sql);
        e.printStackTrace();
    }

}

Any help greatly appreciated.

Answer 1

According to a recent post Xerial group the method setBlob is not implemented in sqlite-jdbc. For me, the proposed alternative

preparedStatement.setBytes(idx, data)

worked fine, if your binary data is small enough to load into memory as byte array.