Announcement Announcement Module
No announcement yet.
Stored procedure with SQLXML parameter Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored procedure with SQLXML parameter

    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).
          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 );
    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.


  • #2
    Pass XML input parameter into Stored Procedure

    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:
    " 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:
    " 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?


    • #3
      Attempted solution with Jdbc4SqlXmlHandler

      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 {

      private MyStoredProcedure() {

      super(getDataSource(), STORED_PROC_NAME);

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

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


      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:
      at ndler$AbstractJdbc4SqlXmlValue.cleanup(Jdbc4SqlXml
      at org.springframework.jdbc.core.StatementCreatorUtil s.cleanupParameters( )
      at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.cleanupPa rameters(
      at org.springframework.jdbc.core.JdbcTemplate.execute (
      at org.springframework.jdbc.object.StoredProcedure.ex ecute(

      Any ideas on what caused the NPE?



      • #4
        Pass SQLXML object into Stored Procedure parameter

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

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