You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by David Balažic <da...@comtrade.com> on 2011/01/17 18:27:21 UTC
Stored procedures - more examples?
Hi!
Are there any more examples of using stored procedures besides the ones
in the guide*?
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?
How is the result set (cursor) support for Oracle?
In the Modeller there is no CURSOR type, should OTHER be used?
One working example would be really helpful (including the stored
procedure definition, with several in and out parameters).
* http://cayenne.apache.org/doc/stored-procedures.html
Regards,
David Balažic
Re: Stored procedures - more examples?
Posted by Andrus Adamchik <an...@objectstyle.org>.
>
> Is there anything special I should look out for?
The scope of the change (and possibly thread safety) are the things to watch for. Cayenne mapping is usually application-scoped and shared by all contexts and all threads. So your tweak should probably have the same scope.
Andrus
On Jan 19, 2011, at 6:23 PM, David Balažic wrote:
> Hi!
>
> I tried putting the fix between
> Procedure proc = context.getEntityResolver().getProcedure("cayenne_tst_select_proc");
> and
> ProcedureQuery query = new ProcedureQuery(proc);
>
>
> This way I don't need any special context setup.
> It appears to work. I just copied over the content
> of OracleStackAdapter.tweakProcedure().
>
> Is there anything special I should look out for?
>
> Regards,
> David
>
>
>> -----Original Message-----
>> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
>> Sent: Tuesday, January 18, 2011 9:33 PM
>> To: user@cayenne.apache.org
>> Subject: Re: Stored procedures - more examples?
>> Importance: Low
>>
>>
>> On Jan 18, 2011, at 8:28 PM, David Balažic wrote:
>>
>>> So, does that testcase really work on Oracle?
>>
>> Yes. Now revisiting the tests, we are cheating to make the
>> generic mapping work with Oracle. The reason for this is
>> Oracle driver's insistence on circumventing basic JDBC
>> abstractions. We take a generic mapping [1] and alter the
>> procedure object in the test code [2]. Notice how we install
>> Oracle-specific type as an OUT parameter type:
>>
>> // the type used below is "oracle.jdbc.OracleTypes.CURSOR"
>> proc.addCallParameter(new ProcedureParameter("result",
>> OracleAdapter.getOracleCursorType(),
>> ProcedureParameter.OUT_PARAMETER));
>>
>> For now the above (creating or tweaking procedure mapping
>> metadata in the code) is one possible way for dealing with
>> Oracle. Going forward, we may either support numeric values
>> for column types (so that a user could map this column as
>> "-10", same as "oracle.jdbc.driver.OracleTypes.CURSOR"). Or
>> maybe we should dynamically rewrite Cayenne-mapped stored
>> procedures, adding CURSOR parameter inside Cayenne (not yet
>> sure how what part of the mapping should trigger such behavior).\
>>
>> And unfortunately there's little hope that after all these
>> years Oracle will finally fix their driver (or at least
>> implement ParameterMetaData JDBC interface so that
>> Oracle-specific types could be used dynamically).
>>
>> Andrus
>>
>>
>> [1]
>>
>> <procedure name="cayenne_tst_select_proc">
>> <procedure-parameter name="aName" type="VARCHAR"
>> length="254" direction="in"/>
>> <procedure-parameter name="paintingPrice" type="INTEGER"
>> direction="in"/>
>> </procedure>
>>
>>
>> [2] OracleStackAdapter.java:
>>
>> public void tweakProcedure(Procedure proc) {
>> if
>> (DataContextProcedureQueryTest.SELECT_STORED_PROCEDURE.equals(
>> proc.getName())
>> && proc.getCallParameters().size() == 2) {
>> List params = new ArrayList(proc.getCallParameters());
>>
>> proc.clearCallParameters();
>> proc.addCallParameter(new
>> ProcedureParameter("result", OracleAdapter
>> .getOracleCursorType(),
>> ProcedureParameter.OUT_PARAMETER));
>> Iterator it = params.iterator();
>> while (it.hasNext()) {
>> ProcedureParameter param =
>> (ProcedureParameter) it.next();
>> proc.addCallParameter(param);
>> }
>>
>> proc.setReturningValue(true);
>> }
>> }
>>
>
RE: Stored procedures - more examples?
Posted by David Balažic <da...@comtrade.com>.
Hi!
I tried putting the fix between
Procedure proc = context.getEntityResolver().getProcedure("cayenne_tst_select_proc");
and
ProcedureQuery query = new ProcedureQuery(proc);
This way I don't need any special context setup.
It appears to work. I just copied over the content
of OracleStackAdapter.tweakProcedure().
Is there anything special I should look out for?
Regards,
David
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Tuesday, January 18, 2011 9:33 PM
> To: user@cayenne.apache.org
> Subject: Re: Stored procedures - more examples?
> Importance: Low
>
>
> On Jan 18, 2011, at 8:28 PM, David Balažic wrote:
>
> > So, does that testcase really work on Oracle?
>
> Yes. Now revisiting the tests, we are cheating to make the
> generic mapping work with Oracle. The reason for this is
> Oracle driver's insistence on circumventing basic JDBC
> abstractions. We take a generic mapping [1] and alter the
> procedure object in the test code [2]. Notice how we install
> Oracle-specific type as an OUT parameter type:
>
> // the type used below is "oracle.jdbc.OracleTypes.CURSOR"
> proc.addCallParameter(new ProcedureParameter("result",
> OracleAdapter.getOracleCursorType(),
> ProcedureParameter.OUT_PARAMETER));
>
> For now the above (creating or tweaking procedure mapping
> metadata in the code) is one possible way for dealing with
> Oracle. Going forward, we may either support numeric values
> for column types (so that a user could map this column as
> "-10", same as "oracle.jdbc.driver.OracleTypes.CURSOR"). Or
> maybe we should dynamically rewrite Cayenne-mapped stored
> procedures, adding CURSOR parameter inside Cayenne (not yet
> sure how what part of the mapping should trigger such behavior).\
>
> And unfortunately there's little hope that after all these
> years Oracle will finally fix their driver (or at least
> implement ParameterMetaData JDBC interface so that
> Oracle-specific types could be used dynamically).
>
> Andrus
>
>
> [1]
>
> <procedure name="cayenne_tst_select_proc">
> <procedure-parameter name="aName" type="VARCHAR"
> length="254" direction="in"/>
> <procedure-parameter name="paintingPrice" type="INTEGER"
> direction="in"/>
> </procedure>
>
>
> [2] OracleStackAdapter.java:
>
> public void tweakProcedure(Procedure proc) {
> if
> (DataContextProcedureQueryTest.SELECT_STORED_PROCEDURE.equals(
> proc.getName())
> && proc.getCallParameters().size() == 2) {
> List params = new ArrayList(proc.getCallParameters());
>
> proc.clearCallParameters();
> proc.addCallParameter(new
> ProcedureParameter("result", OracleAdapter
> .getOracleCursorType(),
> ProcedureParameter.OUT_PARAMETER));
> Iterator it = params.iterator();
> while (it.hasNext()) {
> ProcedureParameter param =
> (ProcedureParameter) it.next();
> proc.addCallParameter(param);
> }
>
> proc.setReturningValue(true);
> }
> }
>
Re: Stored procedures - more examples?
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jan 18, 2011, at 8:28 PM, David Balažic wrote:
> So, does that testcase really work on Oracle?
Yes. Now revisiting the tests, we are cheating to make the generic mapping work with Oracle. The reason for this is Oracle driver's insistence on circumventing basic JDBC abstractions. We take a generic mapping [1] and alter the procedure object in the test code [2]. Notice how we install Oracle-specific type as an OUT parameter type:
// the type used below is "oracle.jdbc.OracleTypes.CURSOR"
proc.addCallParameter(new ProcedureParameter("result", OracleAdapter.getOracleCursorType(), ProcedureParameter.OUT_PARAMETER));
For now the above (creating or tweaking procedure mapping metadata in the code) is one possible way for dealing with Oracle. Going forward, we may either support numeric values for column types (so that a user could map this column as "-10", same as "oracle.jdbc.driver.OracleTypes.CURSOR"). Or maybe we should dynamically rewrite Cayenne-mapped stored procedures, adding CURSOR parameter inside Cayenne (not yet sure how what part of the mapping should trigger such behavior).\
And unfortunately there's little hope that after all these years Oracle will finally fix their driver (or at least implement ParameterMetaData JDBC interface so that Oracle-specific types could be used dynamically).
Andrus
[1]
<procedure name="cayenne_tst_select_proc">
<procedure-parameter name="aName" type="VARCHAR" length="254" direction="in"/>
<procedure-parameter name="paintingPrice" type="INTEGER" direction="in"/>
</procedure>
[2] OracleStackAdapter.java:
public void tweakProcedure(Procedure proc) {
if (DataContextProcedureQueryTest.SELECT_STORED_PROCEDURE.equals(proc.getName())
&& proc.getCallParameters().size() == 2) {
List params = new ArrayList(proc.getCallParameters());
proc.clearCallParameters();
proc.addCallParameter(new ProcedureParameter("result", OracleAdapter
.getOracleCursorType(), ProcedureParameter.OUT_PARAMETER));
Iterator it = params.iterator();
while (it.hasNext()) {
ProcedureParameter param = (ProcedureParameter) it.next();
proc.addCallParameter(param);
}
proc.setReturningValue(true);
}
}
RE: Stored procedures - more examples?
Posted by David Balažic <da...@comtrade.com>.
Hi!
Thanks for the info. I tried "cayenne_tst_out_proc" and "cayenne_tst_select_proc".
The first works, but the second fails (with Oracle 10g).
This is what I get:
INFO: {call cayenne_tst_select_proc(?, ?)} [bind: 1:'An Artist', 2:3000]
18.1.2011 19:15:02 org.apache.cayenne.access.QueryLogger logQueryError
INFO: *** error.
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00221: 'CAYENNE_TST_SELECT_PROC' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I think the problem is that for Oracle a function must be called like this:
{? = call cayenne_tst_select_proc(?, ?)}
If I change the mapping to :
<procedure name="cayenne_tst_select_proc" returningValue="true">
<procedure-parameter name="OUT" type="OTHER" direction="out"/>
<procedure-parameter name="aName" type="VARCHAR" length="254" direction="in"/>
<procedure-parameter name="paintingPrice" type="INTEGER" direction="in"/>
</procedure>
Then I get the correct call, but the type gives an error:
INFO: {? = call cayenne_tst_select_proc(?, ?)} [bind: 1:'[OUT]', 2:'An Artist', 3:3000]
18.1.2011 19:23:44 org.apache.cayenne.access.QueryLogger logQueryError
INFO: *** error.
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:209)
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3462)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:126)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:285)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:371)
at org.apache.cayenne.access.trans.ProcedureTranslator.setOutParam(ProcedureTranslator.java:228)
at org.apache.cayenne.access.trans.ProcedureTranslator.initStatement(ProcedureTranslator.java:176)
at org.apache.cayenne.access.trans.ProcedureTranslator.createStatement(ProcedureTranslator.java:150)
at org.apache.cayenne.access.jdbc.ProcedureAction.performAction(ProcedureAction.java:70)
at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:269)
at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:422)
at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:69)
at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:395)
at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:850)
at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:392)
at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:121)
at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:743)
at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:333)
at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:96)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1278)
at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1267)
at test.stein.Dummy1.callSP1(Dummy1.java:54)
So, does that testcase really work on Oracle?
If yes, what am I doing wrong?
Regards,
David
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Tuesday, January 18, 2011 2:12 PM
> To: user@cayenne.apache.org
> Subject: Re: Stored procedures - more examples?
> Importance: Low
>
>
>
> 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
>
Re: Stored procedures - more examples?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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