cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Balažic <david.bala...@comtrade.com>
Subject RE: Stored procedures - more examples?
Date Tue, 18 Jan 2011 18:28:15 GMT
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
> 

Mime
View raw message