Never mind the bug report. I used CAY-141 to track the fix. Patched
1.1 cayenne.jar (code that will go to 1.1.3 eventually) can be
downloaded from here:
http://objectstyle.org/confluence/download/attachments/134/
cayenne-1.1.3p1.jar?version=1
Patched version contains API additions to ProcedureQuery that allow
to specify exactly what Java types you want to see in the result. It
is backwards compatible (i.e. you don't have to use it if things
worked fine before). It supports one or more ResultSets per
ProcedureQuery. Here is some sample code from the unit tests:
ProcedureQuery q = ....
// Use ColumnDescriptors to manually describe the ResultSet:
ColumnDescriptor[] columns = new ColumnDescriptor[3];
columns[0] = new ColumnDescriptor("ARTIST_ID",
Types.INTEGER, Long.class
.getName());
columns[1] = new ColumnDescriptor("ARTIST_NAME", Types.CHAR,
String.class
.getName());
columns[2] = new ColumnDescriptor("DATE_OF_BIRTH",
Types.DATE, Date.class
.getName());
q.addResultDescriptor(columns);
List results = context.performQuery(q);
As there is no Modeler support for that, if you use queries stored in
the Modeler, you will need to do "result descriptor" configuration of
those queries in the code on startup.
Please let me know if you encounter any problems (preferrably by
posting a comment on CAY-141).
Andrus
On Jun 23, 2005, at 10:46 AM, Andrus Adamchik wrote:
> I see... Let me think how to patch this for the time being. The
> ultimate solution would be to extend procedure query to stop
> relying on metadata per http://objectstyle.org/jira/secure/
> ViewIssue.jspa?key=CAY-141
>
> Could you please submit a JIRA bug report, so that we can track it?
>
> Andrus
>
> On Jun 23, 2005, at 10:39 AM, Pirola Davide wrote:
>
>
>> Hi andrus,
>> The type of data that i use in a ref cursor is NUMBER(8)(but NUMBER
>> (x) where x is any number or NUMBER without precision
>> definition... give the same problem).
>>
>> here the info:
>>
>> - Oracle is 8.1.7.4:
>> ResultSetMetadata.getColumnClassName(int) -->
>> java.math.BigDecimal
>> ResultSetMetadata.getScale(int) --> 0
>> ResultSetMetadata.getPrecision(int) --> 0
>>
>> ResultSetMetadata.getColumnTypeName(int) --> NUMBER
>> ResultSetMetadata.getColumnType(int) --> 2
>>
>> - Oralce 9:
>> ResultSetMetadata.getColumnClassName(int) -->
>> java.math.BigDecimal
>> ResultSetMetadata.getScale(int) --> -127
>> ResultSetMetadata.getPrecision(int) --> 0
>>
>> ResultSetMetadata.getColumnTypeName(int) --> NUMBER
>> ResultSetMetadata.getColumnType(int) --> 2
>>
>> Thank's
>> Davide
>>
>> -----Original Message-----
>> From: Andrus Adamchik [mailto:andru..bjectstyle.org]
>> Sent: giovedì 23 giugno 2005 15.47
>> To: cayenne-use..bjectstyle.org
>> Subject: Re: Problem getting long value from stored procedure
>> (cursor)
>>
>>
>> Davide,
>>
>> Unfortunately I don't have Oracle 8i to test it (only 9i), so could
>> you obtain the following information from ResultSetMetadata for the
>> ref cursor (via debugger or good old System.out.println()) and send
>> it to me:
>>
>> ResultSetMetadata.getColumnClassName(int)
>> ResultSetMetadata.getScale(int) // this one you posted already
>> ResultSetMetadata.getPrecision(int)
>>
>> This information should help us to patch it in the Oracle adapter.
>>
>> Andrus
>>
>>
>> On Jun 23, 2005, at 8:59 AM, Pirola Davide wrote:
>>
>>
>>
>>> I have discovered the problem... i have 2 Oracle database 9i ,
>>> and 8i.
>>> The first return a precision -127 for a NUMBER (oracle type) colum,
>>> the second precision 0.
>>> So the behavior is different for the same code (stored procedure /
>>> java class) ...
>>> This means that if i have Oracle 8i, and i use a NUMER data type (i
>>> have tested only ref cursor returned by a stored procedure),
>>> Cayenne is not able to get the precision correctly and return by
>>> default an Integer type..
>>>
>>> public static String getJavaBySqlType(int type, int length, int
>>> precision) {
>>>
>>> if (type == Types.NUMERIC && precision == 0) {
>>> type = Types.INTEGER;
>>> }
>>> return (String) sqlEnumJava.get(new Integer(type));
>>> }
>>>
>>> also if the number returned maybe bigger than the max value of an
>>> java.lang.Integer.
>>>
>>>
>>> Davide
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Pirola Davide
>>> Sent: giovedì 23 giugno 2005 11.24
>>> To: 'cayenne-use..bjectstyle.org'
>>> Subject: RE: Problem getting long value from stored procedure
>>> (cursor)
>>>
>>>
>>> Yes.. i use Cayenne 1.1.2.
>>> I have investigate the problem.
>>> Cayenne try to get data type and precision of a column returned by
>>> Oracle.. and if the type returned is a NUMBER with precision = 0
>>> it's assume that the type is INTEGER.
>>> This is my ref cursor:
>>>
>>> TYPE ContainerType IS RECORD (ContainerId NUMBER(8),
>>> ContainerName VARCHAR2(255),
>>> FlgProtezione CHAR(1),
>>> ContentId NUMBER(8));
>>>
>>> but in the class "ResultDescriptor"... in a "createDescriptor"
>>> method, the type returned (for the first and last column) is 2
>>> (NUMBER), the precision is 0 and the length is 22.
>>>
>>> If i modify the query in the stored procedure associated to the ref
>>> cursor, in this manner
>>>
>>> Select 0,'','',0 from dual
>>>
>>> the result is the same.
>>>
>>> I'm not able to modify the type returned by a "ResultSetMetaData"...
>>>
>>> You have any suggestion?
>>>
>>> Thank's
>>> Davide
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Andrus Adamchik [mailto:andru..bjectstyle.org]
>>> Sent: mercoledì 22 giugno 2005 20.29
>>> To: cayenne-use..bjectstyle.org
>>> Subject: Re: Problem getting long value from stored procedure
>>> (cursor)
>>>
>>>
>>> From the stack trace look I presume you are using Cayenne 1.1.2.
>>> This line
>>> - OracleDataNode.readStoredProcedureOutParameters
>>> (OracleDataNode.java:145)
>>> - is actually inside the code block that reads the Oracle REF
>>> CURSOR. As
>>> procedure ref cursors are not mapped anywhere, Cayenne tries to
>>> guess the
>>> type of returned objects from JDBC ResultSetMetadata returned by
>>> Oracle.
>>>
>>> So I guess somehow the column type is assumed to be an integer.
>>> Could you
>>> post your REF CURSOR definition code, so that I could look at it?
>>>
>>> Andrus
>>>
>>>
>>>
>>>
>>>
>>>> Hi,
>>>> i have a problem.
>>>> i have a store procedure that return this kind of data:
>>>>
>>>> CONTAINERID CONTAINERNAME CONTENTID
>>>> 3 samsung -9999990000
>>>> 6385 samsungcalendari -9999990013
>>>>
>>>>
>>>> i have mapped this cursor of data (in datamap.xml) in that manner:
>>>>
>>>> <obj-entity name="Container" readOnly="true"
>>>> className="it.telecomitalia.rossoalice.cntman.types.ContainerImpl"
>>>> dbEntityName="CONTAINER">
>>>> <obj-attribute name="id" type="java.lang.Long"
>>>> db-attribute-path="CONTAINERID" />
>>>> <obj-attribute name="name" type="java.lang.String"
>>>> db-attribute-path="CONTAINERNAME" />
>>>> <obj-attribute name="contentId" type="java.lang.Long"
>>>> db-attribute-path="CONTENTID" />
>>>> </obj-entity>
>>>>
>>>> When i execute the stored procedure i obtain this exception:
>>>>
>>>> 6590 [main] INFO org.objectstyle.cayenne.access.QueryLogger - +++
>>>> Connecting: SUCCESS.
>>>> 6605 [main] INFO org.objectstyle.cayenne.access.QueryLogger -
>>>> {call
>>>> alwos01.UTL_SPECIALI.ContainerSearch(?, ?, ?, ?, ?)} [bind: NULL,
>>>> 'samsung', '[OUT]', '[OUT]', '[OUT]']
>>>> 7028 [main] INFO org.objectstyle.cayenne.access.QueryLogger - ***
>>>> error.
>>>> java.sql.SQLException: Overflow numerico
>>>> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:
>>>> 134)
>>>> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:
>>>> 179)
>>>> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:
>>>> 269)
>>>> at
>>>> oracle.jdbc.driver.OracleStatement.getIntValue
>>>> (OracleStatement.java:4489)
>>>> at
>>>> oracle.jdbc.driver.OracleResultSetImpl.getInt
>>>> (OracleResultSetImpl.java:536)
>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>> at
>>>> sun.reflect.NativeMethodAccessorImpl.invoke
>>>> (NativeMethodAccessorImpl.java:39)
>>>> at
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke
>>>> (DelegatingMethodAccessorImpl.java:25)
>>>> at java.lang.reflect.Method.invoke(Method.java:585)
>>>> at
>>>> org.objectstyle.cayenne.access.types.DefaultType.materializeObject
>>>> (DefaultType.java:243)
>>>> at
>>>> org.objectstyle.cayenne.access.DefaultResultIterator.readDataRow
>>>> (DefaultResultIterator.java:250)
>>>> at
>>>> org.objectstyle.cayenne.access.DefaultResultIterator.nextDataRow
>>>> (DefaultResultIterator.java:194)
>>>> at
>>>> org.objectstyle.cayenne.access.DefaultResultIterator.dataRows
>>>> (DefaultResultIterator.java:224)
>>>> at
>>>> org.objectstyle.cayenne.access.DataNode.readResultSet
>>>> (DataNode.java:730)
>>>> at
>>>> org.objectstyle.cayenne.dba.oracle.OracleDataNode.readStoredProcedu
>>>> re
>>>> OutParameters(OracleDataNode.java:145)
>>>> at
>>>> org.objectstyle.cayenne.access.DataNode.runStoredProcedure
>>>> (DataNode.java:644)
>>>> at
>>>> org.objectstyle.cayenne.access.DataNode.performQueries
>>>> (DataNode.java:345)
>>>> at
>>>> org.objectstyle.cayenne.access.DataDomain.performQueries
>>>> (DataDomain.java:654)
>>>> at
>>>> org.objectstyle.cayenne.access.DataContext.performQueries
>>>> (DataContext.java:1408)
>>>> at
>>>> org.objectstyle.cayenne.access.Transaction.performQueries
>>>> (Transaction.java:179)
>>>> at
>>>> org.objectstyle.cayenne.access.DataContext.performQueries
>>>> (DataContext.java:1366)
>>>> at
>>>> org.objectstyle.cayenne.access.DataContext.performQuery
>>>> (DataContext.java:1463)
>>>> at
>>>> it.telecomitalia.rossoalice.cntman.loaders.generic.GenericLoader.ge
>>>> tT
>>>> ypesFromCursor(GenericLoader.java:245)
>>>> ......
>>>> .....
>>>> ....
>>>>
>>>> The error is caused by a number contained in a last column
>>>> (CONTENTID).
>>>> This is greater than the max value of a Integer... buy i have
>>>> mapped this
>>>> field with java.lang.Long... so i don't understand why Cayenne try
>>>> to read
>>>> this in that manner "...OracleResultSetImpl.getInt...".
>>>>
>>>> Anyone can help me?
>>>>
>>>> Than's
>>>> Davide
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>
This archive was generated by hypermail 2.0.0 : Thu Jun 23 2005 - 20:18:45 EDT