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

    Switchyard and stored procedures (IN/OUT params)

    Krzysztof Stelmach Newbie

      Hi,

       

      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);
      apiStmt.execute();
      
      result = apiStmt.getInt(4);
      message = apiStmt.getString(5);
      
      apiStmt.close();
      

      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>
      <sql:dataSourceRef>java:jboss/datasources/MyTestDS</sql:dataSourceRef>
      </sql:binding.sql>
      </sca:reference>

      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("ID:12");
           o.add("Item Name");
           o.add(java.sql.Timestamp.valueOf("2014-02-28 00:00:00"));
           o.add(0);
           o.add("");
           return myItemReference.addItem(o);
      }
      

      Then:

      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 ?