6 Replies Latest reply on Mar 5, 2014 9:52 AM by Tomohisa igarashi

    Switchyard and stored procedures (IN/OUT params)

    Krzysztof Stelmach Newbie



      I try to move one of my project to switchyard, but I don't know how to call stored procedures with IN/OUT params:

      CallableStatement apiStmt = apiSqlCon.prepareCall("{call myApi.createItem(?,?,?,?,?)}");
      apiStmt.setString(i++, "ID:12");
      apiStmt.setString(i++, "Item Name");
      apiStmt.setTimestamp(i++, java.sql.Timestamp.valueOf("2014-02-28 00:00:00"));
      apiStmt.registerOutParameter(i++, java.sql.Types.INTEGER);
      apiStmt.registerOutParameter(i++, java.sql.Types.VARCHAR);
      result = apiStmt.getInt(4);
      message = apiStmt.getString(5);

      Above code work well and add item to a databese.


      To move it to switchyard I do:


      1. I add to switchyard.xml:

      <sca:reference name="MyItemReference" multiplicity="0..1" promote="MyItemServiceBean/MyItemReference">
      <sca:interface.java interface="com.example.proj.MyItemReference"/>
      <sql:binding.sql name="sql1">
      <sql:query>call myApi.createItem(#, #, #, #, #)</sql:query>

      2. Create a reference interface:

      public interface MyItemReference {
           public Object retrive(final List<Object> params);

      3. Add a method into java bean:

      @Inject @Reference("MyItemReference")
      private MyItemReference myItemReference;
      public Object addItem() {
           List<Object> o = new ArrayList<Object>();
           o.add("Item Name");
           o.add(java.sql.Timestamp.valueOf("2014-02-28 00:00:00"));
           return myItemReference.addItem(o);


      When I call addItem() method it return NULL value without error.

      My stored procedure should add item to a database, but it  doesn't (in this case).


      What do I wrong? Or what can I do to find place of an error ?