Re: SQLExec query

[Andrus Adamchik <an...@objectstyle.org>]

> 
> On Jan 31, 2015, at 10:08 AM, Aristedes Maniatis <ar...@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;