cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Сурнин Евгений <surni...@gmail.com>
Subject Stored Procedure&Function(Oracle parse results, SQL server - TVF)
Date Mon, 06 Aug 2012 13:31:41 GMT
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?

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message