Java Exception Error - Sqlite preparedStatement.setBlob

Question

I'm placing and image into a databse, it could be eitehr an MYSQL database (the Server) or an SQLITE database (a Tablet PC for on the road). The Java application synchs with the server on a dialy basis, uploading new data and downloading any new data. Thats working terrirfic, the requirement is for it to be able to handle images as well.

Rather than implement a whole new system that relies on copying the images to the filesystem on each end, we are optiong 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, dependnet 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 utimataley an Image) no problem. So I'f fairly convinced that issue is in seting the Blob in the prepared statment, but I could be wrong.

The Excpetion 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

Try using the 'count' field to get the length of your ByteArrayInputStream object, bais, instead of the length of your decoded string.

Answer 2

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.

Answer 3

Thanks for the response