Hello,
the the JDBC-driver (7.4.3.06) do you use has a known bug. DBprocedures which returns
a resultset fails if autocommit is on. The bug is already fixed in the newest
available JDBC 2.0 driver
(ftp://ftp.sap.com/pub/sapdb/bin/java/sapdb-jdbc-bin-7.3.00.29a.jar). If you don't
need the JDBC 3.0 features or javax.sql.* package please tryout these driver. Another
workaround is switching off autocommit.
The following code snippet gives an example on how you can handle dbprocedures
returning a resultset:
Statement stmt = connection.createStatement();
try {
stmt.executeUpdate("DROP DBPROC DBProcWithResult3");
}
catch (Exception ignore) {}
stmt.executeUpdate("CREATE DBPROC DBProcWithResult3 RETURNS CURSOR AS "
+"DECLARE :$CURSOR CURSOR FOR SELECT 1 FROM SYSDBA.DUAL;");
CallableStatement cs = this.connection.prepareCall("CALL DBProcWithResult3");
ResultSet rs0 = cs.executeQuery();
while(rs0.next()){
System.out.println("Value: "+rs0.getInt(1));
}
cs.close();
stmt.close();
Regards,
Marco Paskamp
> -----Original Message-----
> From: K. M. Kale [mailto:[EMAIL PROTECTED]]
> Sent: Mittwoch, 16. Oktober 2002 18:23
> To: Anhaus, Thomas
> Cc: [EMAIL PROTECTED]
> Subject: Re: NEWBIE DBPROC QUESTION
>
>
> On Wednesday 16 Oct 2002 10:51 am, you wrote:
> > K. M. Kale
> >
> > >I have created a simple procedure like ...
> > >create dbproc test_select as select * from dba.test;
> > >when i call the dbproc i get...
> > >Statements successfully executed, no result !
> > >Am i not suppose to get all rows in the table test?
> > >What am i doing wrong?
> > >Thanks in advance
> > >--
> >
> > You have to declare that your db-procedure returns a cursor :
> >
> > create dbproc test_select returns cursor as select :$cursor (*) from
> > dba.test;
> >
> > Please note, that at the moment not all tools and
> programming languages
> > are already able to handle db-procedures which return a cursor.
> > For example, SQLStudio will not display
> > the resultset after calling the db-procedure.
> >
> > Thomas
> Hi,
> I did try the above. Nither jdbc nor websql are displaying
> the resultset. Do i
> need to update my JDBC driver for SAPDB? i am using
> sapdb-jdbc-bin-7.4.03.06a.jar.
> --
> Regards,
> K. M. Kale
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
