The conversion from UNKNOWN to UNKNOWN is unsupported

Question

I get the following exception running a stored procedure:

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.

The procedure is defined like this:

CREATE PROCEDURE spTest (
@p1 varchar(1024) ,
@p2 varchar(1024) ,
@p3 char(1) ,
@p4 varchar(254),
@p5 varchar(254),
@debug bit )

My parameters in Java are defined like this:

Object[] params = {"1,2,3", "d", '2', "", "", 1};

I think it's caused by the character. Any ideas why?

Answer 1

I found it. Clockwork-Muse put me on the path. The char type does not convert to an Object when you set the parameters. The following will work:

     try (PreparedStatement st = con.prepareStatement(query)) {
        int n = 1;
        for (Object o : params) {

            if (o instanceof Character) {
                o = "" + o;
            }

            st.setObject(n, o);
            n++;
        }
        st.executeQuery();
    }

Answer 2

This error can occur, if you use jdbcTemplate.update(sql, List<Object[]>, int[]), but instead you wanted to use jdbcTemplate.batchUpdate(sql, List<Object[]>, int[]).

It compiles, because there is a jdbcTemplate.update(sql, Object...) method in jdbcTemplate, but it will yield "The conversion from UNKNOWN to UNKNOWN is unsupported.", because it cannot use List<Object[]> as parameter.