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

From: Pirola Davide (davide.pirol..tin.it)
Date: Thu Jun 23 2005 - 10:39:17 EDT

  • Next message: Andrus Adamchik: "Re: Problem getting long value from stored procedure (cursor)"

    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.readStoredProcedure
    >> 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.getT
    >> 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 - 10:40:07 EDT