arrow_back history picture_as_pdf This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. ~~NOTRANS~~ ~~Title: Functions and Procedures With IN and OUT Parameters~~ If you use database procedures and/or functions in your application, wouldn't it be great to call them with one simple call. Normally a function or procedure call is very complex because you have to define a CallableStatement, set the output parameters, execute the statement, and read the result. And don't forget that the parameter index starts with 1 instead of 0! Suppose we have a function (e.g. Oracle): <file plsql> create or replace function execFunction(pNumber in out number, pInText in varchar2, pOutText out varchar2) return varchar2 is res varchar2(200); nr number := pNumber; begin pOutText := 'Out: '|| pOutText ||' In: '|| pInText; pNumber := pNumber + pNumber; return 'IN-Param Nr: '|| nr; end execFunction; </file> The function is simple but uses one in/out and one out parameter. A standard JDBC call looks like: <file java> Connection con; //create a DB connection CallableStatement cstmt = con.prepareCall("{ ? = call EXECFUNCTION(?, ?, ?) }"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.registerOutParameter(2, Types.DECIMAL); cstmt.registerOutParameter(4, Types.VARCHAR); cstmt.setObject(2, BigDecimal.valueOf(1), Types.DECIMAL); cstmt.setObject(3, "ABC", Types.VARCHAR); cstmt.execute(); Object oResult = cstmt.getObject(1); </file> The execution has more lines of code than the function in the database! Now we call the same function through JVx: <file java> Object oResult = dba.executeFunction("execFunction", Types.VARCHAR, BigDecimal.valueOf(1), "ABC", null); </file> You are right, we have not support for in/out and out parameters! To support this kind of parameters, we have some special classes: com.sibvisions.rad.persist.jdbc.param.InParam \\ com.sibvisions.rad.persist.jdbc.param.OutParam \\ com.sibvisions.rad.persist.jdbc.param.InOutParam \\ Use it as with the following code: <file java> OutParam ouTextParam = new OutParam(InOutParam.SQLTYPE_VARCHAR); InOutParam ioNumberParam = new InOutParam(InOutParam.SQLTYPE_DECIMAL, BigDecimal.valueOf(1)); Object oResult = dba.executeFunction("execFunction", Types.VARCHAR, ioNumberParam, "ABC", ouTextParam); Object oNumber = ioNumberParam.getValue(); Object oText = ouTextParam.getValue(); </file> The ''executeFunction'' method supports standard Java objects and our special Param objects, as used in the previous example. If you want to call a database procedure instead of a function, use the same classes and call ''executeProcedure''.