Exception: conversion from UNKNOWN to UNKNOWN is unsupported

Question

I'm converting some jdbc code from MySql to SQL Server. When trying to

        query = "Update ReportSetup "
                    + "set N_ID=?, "
                    + "R_Default=?, "
                    + "R_Name=?, "
                    + "R_Module=? "
                    + " where R_ID = ?";
        }

        PreparedStatement stmt =
                (PreparedStatement) con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        stmt.setInt(1, ri.getNodeID());
        stmt.setInt(2, (ri.isDefault()) ? 1 : 0);
        stmt.setString(3, ri.getName());
        Object o = ri.getReportModule();
        stmt.setObject(4, o);

The last statement stmt.setObject(4,o) throws the exception.

ri.getReportModule returns an instance of a class which implements Externalizable.

The method writeExternal() of that class is implemented as

public final void writeExternal(final ObjectOutput objectOutput) throws IOException {
    for (int i=0; i<pdV.size(); i++) {
        PropertyDescriptor pd = pdV.elementAt(i);
        try {
            Method m = pd.getReadMethod();
            Object val = pd.getReadMethod().invoke(this);
            System.out.print("writing property " + i + ": " + pd.getName() + " = " + val);
            objectOutput.writeObject(val);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The database column in question is defined as varbinary(max), not null

The code works well using MySql but I can't figure out what to do to make it run with Sql Server.

Any suggestions would be very much appreciated

Answer 1

The problem was that sql server is not happy to save a serialization (as done when implementing externalizable). .setObject() fails. The solution is to use setBinaryStream().

        // Sql Server can't do an stmt.setObject(4,o) "Conversion from UNKNOWN to UNKNOWN not supported"
        // Serialize the object to an output stream and then read it in again into the stmt.
        Object o = ri.getReportModule();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        ObjectOutputStream objectOutput = new ObjectOutputStream(bos);
        objectOutput.writeObject(o);
        objectOutput.flush();
        InputStream objectInput = new ByteArrayInputStream(bos.toByteArray());
        stmt.setBinaryStream(4, objectInput);

Cheers Christian