>
> On Jan 31, 2015, at 10:08 AM, Aristedes Maniatis <[email protected]> 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;
