cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: SQLExec query
Date Mon, 02 Feb 2015 12:12:22 GMT
> 
> On Jan 31, 2015, at 10:08 AM, Aristedes Maniatis <ari@maniatis.org> wrote:
> 
> Can you give an example of the type of multi-part query you have in mind and how the
code would be used.
> 
> Ari

Can't immediately provide an example of a multi-part raw SQL query (will need to see what
various DBs allow to put in a single Statement). But see below a PL/SQL function that can
be called as a stored procedure. It does an update and returns a ResultSet. So assuming we
spec a ProcedureExec query similar to SQLExec, a call might look like this (assuming we are
ok with hardcoding procedure result structure in the Java code):

List<QueryResult> rs = ProcedureExec.query("cayenne_tst_select_proc").paramsArray("A",
5000).execute(context);
int updatedRows = rs.get(0).getUpdateResult();
List<DataRow> data = rs.get(1).getSelectResult();

Andrus

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