Hi,
Thanks for your comments.
It turns out that the java-JDBC-Oracle mapping combination of
long-BIGINT-INTEGER is fine. The problem I was experiencing was
actually caused by a mismatch between query and Oracle types. It was
quite clear that this was the problem when I used the PB API. In the
following example, the id field is of the long-BIGINT-INTEGER variety.
// criteria.addEqualTo("id", new Integer(-1));
criteria.addEqualTo("id", new Long(-1));
Query query = new QueryByCriteria(X.class, criteria);
Using the Long criteria does work. But if the Integer criteria is used,
the Oracle thin driver complains (see stack trace below) presumably
expecting a Long argument.
However with the ODMG API and a string literal query, the problem is
trickier and the workaround is less clear:
query.create("select p from " + X.class.getName() + " where id <100");
DList pNodes = (DList) query.execute();
This gives the same Oracle driver exception as I saw with the PB API
example (see stack trace below). I noticed that
org.apache.ojb.odmg.oql.OQLParser.literal() parses 100 as an Integer
rather than Long (and writing ..."where id < 100L" didn't help). This
later causes the Oracle driver to complain about a type mismatch.
A brute force hack to OQLParser.literal() fixed the problem for my
particular example, but I think there must be a better approach:
...
case TOK_UNSIGNED_INTEGER:
{
tokInt = LT(1);
match(TOK_UNSIGNED_INTEGER);
try
{
// value = Integer.valueOf(tokInt.getText());
value = Long.valueOf(tokInt.getText()); // My hack
}
catch (NumberFormatException ignored)
{
value = Long.valueOf(tokInt.getText());
}
break;
}
Can anyone shed some light on this?
Thanks very much,
Phil
*******begin Oracle thin driver exception*************
java.lang.ClassCastException: java.lang.Integer at
oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2024)
at
oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2102)
at
org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectForStatement(PlatformDefaultImpl.java:181)
at
org.apache.ojb.broker.platforms.PlatformOracleImpl.setObjectForStatement(PlatformOracleImpl.java:130)
at
org.apache.ojb.broker.accesslayer.StatementManager.bindStatementValue(StatementManager.java:239)
at
org.apache.ojb.broker.accesslayer.StatementManager.bindStatement(StatementManager.java:279)
at
org.apache.ojb.broker.accesslayer.StatementManager.bindStatement(StatementManager.java:441)
at
org.apache.ojb.broker.accesslayer.JdbcAccess.executeQuery(JdbcAccess.java:240)
at org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:245)
at
org.apache.ojb.broker.singlevm.RsIteratorFactoryImpl.createRsIterator(RsIteratorFactoryImpl.java:95)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:1995)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getIteratorFromQuery(PersistenceBrokerImpl.java:1423)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1092)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1239)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1265)End
time: Thu Oct 17 00:11:13 EDT 2002
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1252)
*******end Oracle thin driver exception*************
lacht wrote:
> Just a comment on your table below. Oracle's jdbc driver returns
> NUMBER(38) in the data types metadata for BIGINT. I agree that
> number(20) should be big enough.
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 16, 2002 12:12 PM
> To: [EMAIL PROTECTED]
> Subject: AW: AW: Oracle JDBC Types
>
>
> Hello,
>
> i don't konw if i understand your problem exactly, but let's give it a try:
>
> As far as i know the JDBC type BIGINT represents a 64-bit signed integer
> value, thus it needs at most 19 digits to be represented in the decimal
> system. Accordingly NUMBER(20) should be large enough. The corresponding
> Java mapping should be a Java 'long'.
>
> We use this mapping and it seems to work.
> However, it should be no problem to store a BIGINT number in a
> NUMBER(38) field, as this is larger. The different direction of the
> implication would not hold. If your database is filled by some tool and
> should be read by an ojb application you may run into trouble as your
> database may contain a number that is to large to be stored as a BIGINT.
>
> Maybe the following mapping table which also includes the Java datatypes
> helps you:
>
> JAVA JDBC ORACLE
> --------------------- -----------------------------
> --------------------------------
> java.lang.String java.sql.Types.CHAR
> CHAR(<approptiate_lenght>)
> java.lang.String java.sql.Types.VARCHAR
> VARCHAR2(<approptiate_lenght>)
> java.lang.String java.sql.Types.LONGVARCHAR LONG
>
> java.sql.Date java.sql.Types.DATE DATE
> java.sql.Time java.sql.Types.TIME DATE
> javal.sql.Timestamp java.sql.Types.TIMESTAMP DATE
>
> boolean java.sql.Types.BIT NUMBER(1)
> byte java.sql.Types.TINYINT NUMBER(3)
> short java.sql.Types.SMALLINT NUMBER(5)
> int java.sql.Types.INTEGER NUMBER(10)
> long java.sql.Types.BIGINT NUMBER(20)
> double java.sql.Types.DOUBLE NUMBER
> double java.sql.Types.FLOAT NUMBER
> float java.sql.Types.REAL NUMBER
> java.math.BigDecimal java.sql.Types.NUMERIC NUMBER
> java.math.BigDecimal java.sql.Types.DECIMAL NUMBER
>
> byte[] java.sql.Types.BINARY RAW
> byte[] java.sql.Types.VARBINARY RAW
> byte[] java.sql.Types.LONGVARBINARY LONG RAW
>
> java.sql.Clob java.sql.Types.CLOB CLOB
> java.sql.Blob java.sql.Types.BLOB BLOB
>
> So far my understanding, i hope my answer meets your question.
>
> So long.
> Max
>
>
> > -----Urspr�ngliche Nachricht-----
> > Von: Phil Warrick [mailto:[EMAIL PROTECTED]]
> > Gesendet: Mittwoch, 16. Oktober 2002 15:50
> > An: OJB Users List
> > Betreff: Re: AW: Oracle JDBC Types
> >
> >
> > Hi,
> >
> > Thanks Max, this is very useful.
> >
> > I have a related question: My database uses Oracle type
> > "INTEGER" which
> > supposedly is a synonym for NUMBER(38). Yet the only JDBC type that
> > seems to work is "INTEGER", not "BIGINT". I'd like to use
> > JDBC BIGINT
> > and "long" java types in the code so is there a way to do
> > this without
> > changing the Oracle type?
> >
> > Thanks,
> >
> > Phil
> >
> > [EMAIL PROTECTED] wrote:
> > > Hi Graham,
> > >
> > > i posted some questions on this a couple of weeks ago, but
> > got no reply.
> > > Anyway, we are currently using the following mapping
> > (partially according to
> > > the Oracle documentation):
> > >
> > > JDBC --> ORACLE (8.1.7)
> > > ----------- --------------
> > > BIT --> NUMBER(1)
> > > TINYINT --> NUMBER(3)
> > > SMALLINT --> NUMBER(5)
> > > INTEGER --> NUMBER(10)
> > > BIGINT --> NUMBER(20)
> > > DOUBLE --> NUMBER
> > > REAL --> NUMBER
> > > FLOAT --> NUMBER
> > > NUMERIC --> NUMBER
> > > DECIMAL --> NUMBER
> > > CHAR --> CHAR
> > > VARCHAR --> VARCHAR2
> > > LONGVARCHAR --> LONG
> > > DATE --> DATE
> > > TIME --> DATE
> > > TIMESTAMP --> DATE
> > > BINARY --> RAW
> > > VARBINARY --> RAW
> > > LONGVARBINARY --> LONG RAW
> > > CLOB --> CLOB
> > > BLOB --> BLOB
> > >
> > > Note that you cannot have zwo LONG or LONG RAW fields in a
> > single table. So
> > > you have to change ojb table OJB_DMAP_ENTRIES: both columns
> > KEY_OID and
> > > VALUE_OID are originally "LONGVARBINARY". I left KEY_OID as
> > LONGVARBINARY,
> > > thus LONG RAW, and changed VALUE_OID zu VARCHAR/2048, thus
> > VARCHAR2(2048).
> > > We use these JDBC types: VARCHAR, CHAR, BIGINT, TIME, DATE,
> > LONGVARCHAR,
> > > INTEGER, LONGVARBINARY with the mapping mentioned above. So
> > far everything
> > > seems to work properly.
> > >
> > > "veryfiymappings" also keeps on giving me errors, but i
> > could not figure out
> > > why. I don't know what is happening behind the sceenes
> > (other mapping tools
> > > [crossDB] are used) and where the mapping are configured.
> > >
> > > I don't know the proper way to generate DDL statements from the
> > > repository.xml file, but would be interested in learning
> > (currently we're
> > > using a simple xsl file to generate the statements for
> > creating tables an
> > > primary keys).
> > >
> > > Hope it was some useful info for you.
> > >
> > > Regards
> > > Max
> > >
> > >
> > >
> > >>-----Urspr�ngliche Nachricht-----
> > >>Von: Graham Lounder [mailto:[EMAIL PROTECTED]]
> > >>Gesendet: Dienstag, 15. Oktober 2002 13:18
> > >>An: OJB Users List
> > >>Betreff: Oracle JDBC Types
> > >>
> > >>
> > >>Hey all,
> > >>
> > >>I'm currently setting up my second OJB project, this time
> > >>with Oracle. In
> > >>the database there are types such as NUMBER and VARCHAR2.
> > >>What types are
> > >>these mapped to? Currently I'm using INTEGER/DOUBLE and
> > >>LONGVARCHAR. I'm
> > >>asking this question because I'm using the Ant task
> > >>'verifiymappings' and it
> > >>is giving me lots of warnings that the types are not the same.
> > >>
> > >>Any Ideas?
> > >>
> > >>Thanks in advance,
> > >>Graham
> > >>
> > >>============================================
> > >> Graham Lounder
> > >> Java Developer
> > >> Spatial Components Division
> > >> CARIS
> > >> 264 Rookwood Ave
> > >> Fredericton NB E3B-2M2
> > >> Office 506 462-4263
> > >> Fax 506 459-3849
> > >> [EMAIL PROTECTED]
> > >> http://www.spatialcomponents.com
> > >>============================================
> > >>
> > >>
> > >>--
> > >>To unsubscribe, e-mail:
> > >><mailto:[EMAIL PROTECTED]>
> > >>For additional commands, e-mail:
> > >><mailto:[EMAIL PROTECTED]>
> > >>
> > >
> > > --
> > > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > > For additional commands, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> >
> >
> >
> > --
> > To unsubscribe, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> > For additional commands, e-mail:
> > <mailto:[EMAIL PROTECTED]>
> >
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
>
>
> ------------------------------------------------------------------------
>
> --
> To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
