cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Stored procedures - more examples?
Date Tue, 18 Jan 2011 13:12:06 GMT


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
Mime
View raw message