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

    Switchyard and stored procedures (IN/OUT params)

    stelmik
    stelmik Feb 28, 2014 11:50 AM

      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 ?