Announcement Announcement Module
Collapse
No announcement yet.
Stored procedure with SQLXML parameter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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:
    Code:
    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!

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

    Comment


    • #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 {

      //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.

      Comment


      • #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);

        Comment

        Working...
        X