Hi there,
Writing ORM code on top of stored procedures is not my strong suite. I've
tested a number of cases with all major DBs, discovering a bunch of JDBC driver
limitations and a few workarounds for those. That's the extent of my
knowledge...
1) Interesting... IIRC in the past we could only return the resultsets via
returned value of Oracle FUNCTIONs. Good to know it also works for the OUT
parameters (it did not for me). As for the order, Cayenne iterates over
procedure parameters in declared order and places the results in QueryResponse
in that order as well. Very odd that the order is mangled. May need to try it
(unless you beat us and run it in debugger yourself, putting a breakpoint in
OracleProcedureAction ;))
2)
> How to execute this function? Or I have to execute it like this: select *
> from test('some') via performQuery method?
If you have this option, I would certainly do the later. Or even better:
Class queryType = .. ; // init this dynamically
SelectQuery query = new SelectQuery(queryType); // very ORM code
Which is good ORM code.... Other than that... I know the following works on
SQLServer.
CREATE PROCEDURE cayenne_tst_select_proc @aName VARCHAR(255), @paintingPrice
INT AS
BEGIN
SELECT DISTINCT A.ARTIST_ID, A.ARTIST_NAME, A.DATE_OF_BIRTH
FROM ARTIST A, PAINTING P
WHERE A.ARTIST_ID = P.ARTIST_ID AND
A.ARTIST_NAME = @aName
ORDER BY A.ARTIST_ID
END
Haven't tried functions with it though. Have you checked 'Return Value'
checkbox when mapping the procedure and created a parameter for the return
value?
Andrus
On Aug 6, 2012, at 4:31 PM, Сурнин Евгений wrote:
> Hello, I use cayenne - 2.0.4.29, ojdbc14 - 10.2.0.4.0, jtds - 1.2
> I try to get response from stored functions/procedures on Oracle, SQL
> Server.
> 1) I write test oracle procedure:
>
> CREATE OR REPLACE PROCEDURE getSomething(w1 NUMERIC, w2 OUT SYS_REFCURSOR,
> w3 OUT NUMERIC, w5 OUT SYS_REFCURSOR) IS
>
> BEGIN
>
> w3:=777;
>
> OPEN w5 FOR
>
> select * from TABLE1;
>
> OPEN w2 FOR
>
> select * from TABLE2;
> END;
>
> {call getSomething (?, ?, ?, ?)} [bind: 4, '[OUT]', '[OUT]', '[OUT]']} -
> OUT parameters in procedure declare order
>
> Then I execute stored procedure like in documentation:
> QueryResponse result = context.performGenericQuery(query);
> List outList = result.firstList();
>
> I get first return value from result: value of w2.
> Next return value(result.next()) is value of w5.
> Last return value(result.next()) is value of w3.
>
> How correctly to parse result in the general case? I want to know param -
> result mapping.
>
> 2) Also I write SQL Server function:
>
> CREATE FUNCTION test(@table varchar)
>
> RETURNS TABLE
>
> AS
>
> RETURN
>
> (
>
> SELECT *
>
> FROM TABLE
>
> WHERE TABLE_NAME = @table
> );
>
> When I try to execute it, I get exception:
> Caused by: *java.sql.SQLException*: The request for procedure 'test' failed
> because 'test' is a table valued function object.
>
> at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(*
> SQLDiagnostic.java:365*)
>
> at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(*TdsCore.java:2781*
> )
>
> at net.sourceforge.jtds.jdbc.TdsCore.nextToken(*TdsCore.java:2224*)
>
> at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(*TdsCore.java:628*
> )
>
> at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(*
> JtdsStatement.java:525*)
>
> at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(*
> JtdsStatement.java:487*)
>
> at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(*
> JtdsPreparedStatement.java:478*)
>
> at
> org.apache.cayenne.dba.sqlserver.SQLServerProcedureAction.performAction(*
> SQLServerProcedureAction.java:70*)
>
> at org.apache.cayenne.access.DataNodeQueryAction.runQuery(*
> DataNodeQueryAction.java:59*)
>
> at org.apache.cayenne.access.DataNode.performQueries(*
> DataNode.java:279*)
> ... 48 more
>
> How to execute this function? Or I have to execute it like this: select *
> from test('some') via performQuery method?
