A dbproc with that returns CURSOR and has at least one OUT param always blows up with an Error -4024 during a call from jdbc,
If I remove the RETURNS CURSOR or OUT params, it works. Is the mix not supported ?
I must admit that I would find *very very* usefull
if a proc could have CUSROR type for OUT params (it would solve the above problem). I know from reading earlyer posts that you guys at sapdb find this unelegant as you consider it to be weak encapsulation.
Without going into a philosophic debate about this, please consider
the the advantages of CURSORs as OUT params :
i) For 3 tier applications, a stored proc layer is sometimes used
as an 'interface' to the DB, kind of a pattern where the code
in the middle tier doesn't know the tables, juste the procs,
so tables can always be changed, provided that the proc is
retrofited to maintain the 'contract' between the DB and the
middle tier. The proc layer can be viewed as the 'contract'
between the middle tier and the DB. ii) Again in 3 tier apps, the most efficient scheme is often
a 'coarse grained' O2R mapping, i.e. minimize round trips to
the DB by getting bigger graphs of object, ex.: instead of fetching
students one by one fetch the classroom, all students, the teacher
and the course topic all at once in a stored proc... Of course the
example might or might not make sense depending on the usage of the
data, hence all the difficulty lies in defining this
'coarse grained' access and mapping.############### The proc : ###############
CREATE DBPROC testProc ( IN p1 varchar(64), IN p2 fixed(12), OUT o1 varchar(64), OUT o2 fixed(12)) RETURNS CURSOR AS BEGIN
SET o1 = p1; SET o2 = p2;
DECLARE :$CURSOR CURSOR FOR
select :p1, :p2 from dba.dual;
END;############### The CallableStatement : ###############
public void testStoredProc() throws Exception {
Connection connection = JDBCUtil.createConnection();
CallableStatement cs =
connection.prepareCall("{call testProc(?,?,?,?)}"); cs.setString(1, "1stParam");
cs.setLong(2, 1212);
cs.registerOutParameter(3, Types.VARCHAR);
cs.registerOutParameter(4, Types.NUMERIC);cs.execute();
assertEquals("1stParam",cs.getString(3));
assertEquals(1212,cs.getLong(4));ResultSet rs = cs.getResultSet();
assertTrue(rs.next());
assertEquals("1stParam", rs.getString(1));
assertEquals(1212, rs.getLong(2));
}############### The Exception : ###############
testStoredProc(com.map.jdbcimpl.SapDBTests)com.sap.dbtech.jdbc.exceptions.DatabaseException:
[-4024]: Error
at
com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPacket.java:69)
at
com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.java:763)
at
com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:429)
at
com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:320)
at
com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:380)
at
com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:289)
at
com.map.jdbcimpl.SapDBTests.testStoredProc(SapDBTests.java:32)
at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit.TextTestRunner.main(TextTestRunner.java:12)
_______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
