Search code examples
javasql-serverspringjdbcsqlxml

Spring JDBC for SQL Server - Using SQLXML datatypes yields SQLServerException: Implicit conversion from data type xml to nvarchar(max) is not allowed

I'm trying to make use of a SQL Server stored procedure that uses XML in/out parameters using a Spring SimpleJdbcCall as per this example from the Spring 3.2 documentation (http://static.springsource.org/spring/docs/3.2.x/spring-framework-reference/html/jdbc.html):

public class JdbcActorDao implements ActorDao {
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcCall procReadActor;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.procReadActor =
                new SimpleJdbcCall(dataSource)
                        .withProcedureName("read_actor");
    }

    public Actor readActor(Long id) {
        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("in_id", id);
        Map out = procReadActor.execute(in);
        Actor actor = new Actor();
        actor.setId(id);
        actor.setFirstName((String) out.get("out_first_name"));
        actor.setLastName((String) out.get("out_last_name"));
        actor.setBirthDate((Date) out.get("out_birth_date"));
        return actor;
    }

    //  ... additional methods
}

My implementation is as such:

@Repository
public class ObjectDao {

    private JdbcTemplate jdbcTemplate;

    @Value("${db.sp.getObject}")
    private String spName;

    private SimpleJdbcCall jdbcCall;


    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.jdbcCall=
                new SimpleJdbcCall(dataSource)
        .withProcedureName(spName);
    }

    public SQLXML getDbObjectById(Integer id) {

        SqlParameterSource in = new MapSqlParameterSource()
                .addValue("objectId", id, Types.INTEGER);


        Map<String, Object> out = jdbcCall.execute(in);

        return (SQLXML) out.get("OutputXML");
    }

}

I've tested the connection parameters out with other database transactions and these have been successful, but when attempting to read an output parameter that uses an XML variable, I'm presented with a BadSqlGrammarException as per the below

EXECEPTION

    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:948)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

ROOT CAUSE

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call spXML_Wrapper(?, ?, ?)}]; nested exception is  c om  .microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
    org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1036)
    org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1070)
    org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:387)
    org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:350)
    org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
    com.test.dao.ObjectDao.getDbObjectById(InvoiceDaoImpl.java:59)
    ........

ROOT CAUSE 

com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type xml to nvarchar(max) is not allowed. Use the CONVERT function to run this query.
    com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
    com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332)
    org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
    org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1072)
    org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1070)
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1020)
    org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1070)
    org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:387)
    org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:350)
    org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
    com.test.dao.ObjectDao.getDbObjectById(InvoiceDaoImpl.java:59)
    .......

I've checked that the stored procedure itself is up to scratch - executes fine. Does anyone have any experience with such an error or have any alternative suggestions to reading XML output from stored procedures using the Spring framework?

Other info:

Spring 3.2

Tomcat 7.0

Microsoft JDBC Driver 4.0 for SQL Server

Thanks for your help

Solution

  • I haven't looked this up so it's just a theory, but my guess is that the XML type used by the SQL Server JDBC driver is not using a standard datatype value used in java.sql.Types.

    What I would do is write a simple, standalone JDBC program that prints the ResultSetMetaData.getColumnType() value of the data type returned by your stored procedure. If that integer is not listed in java.sql.Types, then chances are that Spring is defaulting it to a string value, and you'll have to find a way to override how it is handling data type inference.

    I don't think you're going to be able to pull this off using a SimpleJdbcCall; I think you'll have to fall back to using JdbcTemplate, unfortunately. Spring's handling of data types in stored procedures appears to be pretty limited.