From: Maxime Levesque (maxime.levesquewebhelp.com)
Date: Fri Jul 04 2003 - 10:16:13 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

  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
sapdb.generallistserv.sap.com
http://listserv.sap.com/mailman/listinfo/sapdb.general

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]