Stored procedure with SQLXML parameter

[lfoster]

I am using spring 3.0 and MSSQL database, and am having a problem calling a stored procedure using SimpleJdbcCall. The stored procedure requires a parameter of type 'XML'.

I've tried this:

public void setDataSource(DataSource dataSource)
{
   this.updateDataProc = new SimpleJdbcCall(dataSource).
      withProcedureName("spUpdateProc");
   this.createUserProc.declareParameters( 
      new SqlParameter("ID", Types.INTEGER),
      new SqlParameter("XmlData", Types.SQLXML) );
...


public void updateData(int id, Document xmlDoc)
{
   Map<String,Object> in = new HashMap<String,Object>();
   in.put("ID", id);
   in.put("XmlData", xmlDoc );
        
   this.updateGameConfigProc.execute(in);
}

I always wind up with [#document: null] in the database.

There is existing code (not using spring) that works fine, so I know the stored procedure itself is ok.

Help!

[ShaunakDesai]

I am trying to pass value for XML type input parameter into a stored procedure in SQL Server 2005 using Spring 2.5.6.

When I supply a String value in the parameter, I get the following error:
"com.microsoft.sqlserver.jdbc.SQLServerExcepti on: The conversion from CHAR to UNKNOWN-9 is unsupported."

When I supply a dom4j Document type object in the parameter, I get the following error:
"com.microsoft.sqlserver.jdbc.SQLServerExcepti on: The Java type org.dom4j.tree.DefaultDocument is not a supported type."

Can somebody please enlighten me on how to pass XML data into a stored procedure?

[ShaunakDesai]

I tried using Jdbc4SqlXmlHandler to create a SqlXmlValue type object to pass into the Stored procedure with XML type input parameter.

Below are some snippets from my DAO:


public class MyDAOImpl extends SimpleJdbcDaoSupport
{
private Jdbc4SqlXmlHandler sqlXmlHandler; // setter injected

public int insertMyData() {

return new MyStoredProcedure().executeMyProcedure();
}

private class MyStoredProcedure extends StoredProcedure {

//constructor
private MyStoredProcedure() {

super(getDataSource(), STORED_PROC_NAME);

// input parameters
declareParameter(new SqlParameter("myXML", Types.SQLXML));

//output parameters
declareParameter(new SqlOutParameter("rowId", Types.INTEGER));

compile();
}

private int executeMyProcedure() throws DataAccessException {

String xmlString = "<eventDetail><key>pageName</key><value>FirstPage</value></eventDetail>"

SqlXmlValue eventDtlSqlXml = sqlXmlHandler.newSqlXmlValue( xmlString );

// set up input parameters
Map inputMap = new HashMap();
inputMap.put( "myXML", eventDtlSqlXml );

// execute stored procedure
Map outputMap = super.execute(inputMap);

int rowId = (Integer)outputMap.get("rowId");

return rowId;
}//end of method executeMyProcedure()
}//end of inner class MyStoredProcedure
}//end of class MyDAOImpl

I am getting the following error while trying to execute the stored procedure:
java.lang.NullPointerException
at org.springframework.jdbc.support.xml.Jdbc4SqlXmlHa ndler$AbstractJdbc4SqlXmlValue.cleanup(Jdbc4SqlXml Handler.java:152)
at org.springframework.jdbc.core.StatementCreatorUtil s.cleanupParameters(StatementCreatorUtils.java:403 )
at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.cleanupPa rameters(CallableStatementCreatorFactory.java:228)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:956)
at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:985)
at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:117)

Any ideas on what caused the NPE?

Thanks.

[ShaunakDesai]

The solution is as simple as creating the SQLXML object this way.

SQLXML xmlParam = getConnection().createSQLXML().setString(XML_STRIN G);