On Jan 17, 2011, at 7:27 PM, David Balažic wrote:
> Hi!
>
> Are there any more examples of using stored procedures besides the ones
> in the guide*?
I can't think of any except for unit tests in Cayenne under
DataContextProcedureQueryTest.java. Others may have better examples.
> What exactly is the meaning of the returningValue attribute?
> Is it needed if any parameter is of type OUT?
> Or when the procedure is actually a function?
Yes. This is for functions support (see example below). Not all DB's support
this. IIRC this was introduced for Oracle and PostgreSQL.
> How is the result set (cursor) support for Oracle?
> In the Modeller there is no CURSOR type, should OTHER be used?
Here is an Oracle example. To access ResultSet in Cayenne, "Return Value"
checkbox must be checked:
CREATE OR REPLACE PACKAGE cayenne_types
AS
TYPE ref_cursor IS REF CURSOR;
END;
CREATE OR REPLACE FUNCTION cayenne_tst_select_proc (a_name IN VARCHAR2,
painting_price IN NUMBER)
RETURN cayenne_types.ref_cursor
AS
artists cayenne_types.ref_cursor;
BEGIN
SET TRANSACTION READ WRITE;
UPDATE PAINTING SET ESTIMATED_PRICE = ESTIMATED_PRICE * 2
WHERE ESTIMATED_PRICE < painting_price;
COMMIT;
OPEN artists FOR
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
RTRIM(A.ARTIST_NAME) = a_name
ORDER BY A.ARTIST_ID;
RETURN artists;
END;
The same example in MySQL. MySQL returns ResultSet without a need for return
value mapping:
CREATE PROCEDURE cayenne_tst_select_proc (IN p1 varchar(200), IN p2 DECIMAL)
BEGIN
UPDATE PAINTING SET ESTIMATED_PRICE = ESTIMATED_PRICE * 2
WHERE ESTIMATED_PRICE < p2;
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
RTRIM(A.ARTIST_NAME) = p1
ORDER BY A.ARTIST_ID;
END
While the mapping would differ between the DB's, Java code is the same for both
Oracle and MySQL:
ProcedureQuery q = new ProcedureQuery("cayenne_tst_select_proc");
q.addParameter("aName", "An Artist");
q.addParameter("paintingPrice", new Integer(3000));
List artists = ctxt.performQuery(q);
Andrus
