Warrick,
don't know if this helps but;
A while ago I upgraded from oracle 8.1.7 to 9.1. We did not use OJB but
a DAO pattern, so we had a lot of manually created SQL statements.
Our ID's were java Long's. After the upgrade our code broke with a
bizar error mentioning a Long could not be bound (bind) to a Integer
collumn. I was very confused and dove into the JAVA-JDBC-ORACLE
mappings. to no avail.
Opened a support case, and went to the bottom. In fact they could not
replicate the issue....
It turned out there was a driver problem. We used a type2 driver, after
we switched to the proper type4 driver everything went away.....
What makes things more confusing is that Long in Oracle is not a
numeric datatype, but intended to store character data...
not sure if this relates to your problem, just my two cents.
ilya
>>> [EMAIL PROTECTED] 10/17/02 06:40am >>>
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(Pl
atformDefaultImpl.java:181)
at
org.apache.ojb.broker.platforms.PlatformOracleImpl.setObjectForStatement(Pla
tformOracleImpl.java:130)
at
org.apache.ojb.broker.accesslayer.StatementManager.bindStatementValue(Statem
entManager.java:239)
at
org.apache.ojb.broker.accesslayer.StatementManager.bindStatement(StatementMa
nager.java:279)
at
org.apache.ojb.broker.accesslayer.StatementManager.bindStatement(StatementMa
nager.java:441)
at
org.apache.ojb.broker.accesslayer.JdbcAccess.executeQuery(JdbcAccess.java:24
0)
at
org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:245)
at
org.apache.ojb.broker.singlevm.RsIteratorFactoryImpl.createRsIterator(RsIter
atorFactoryImpl.java:95)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getRsIteratorFromQuery(
PersistenceBrokerImpl.java:1995)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getItera
torFromQuery(PersistenceBrokerImpl.java:1423)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Pe
rsistenceBrokerImpl.java:1092)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Pe
rsistenceBrokerImpl.java:1239)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Pe
rsistenceBrokerImpl.java:1265)End
time: Thu Oct 17 00:11:13 EDT 2002
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.getCollectionByQuery(Pe
rsistenceBrokerImpl.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 unsubscri
be, 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]>
--
****************************************************************************
This message contains information that may be privileged or confidential and
is the property of the Cap Gemini Ernst & Young Group. It is only intended
for the person to whom it is addressed. If you are not the intended
recipient, you are not authorized to read, print, retain, copy disseminate,
distribute, or use this message or any part thereof. If you receive this
message in error, please notify the sender immediately and delete all copies
of this message.
****************************************************************************
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
