RE: Problem getting long value from stored procedure (cursor)

From: Pirola Davide (davide.pirol..tin.it)
Date: Thu Jun 23 2005 - 05:23:40 EDT

  • Next message: Pirola Davide: "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.readStoredProcedureOutParameters(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.getTypesFromCursor(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 - 05:24:33 EDT