Hi,
I looked at this finally and came up with the following patch for
org.apache.ojb.broker.platforms.PlatformOracleImpl.setObjectForStatement(...)
...
else if (value instanceof Double)
{
// workaround for the bug in Oracle thin driver
ps.setDouble(index, ((Double) value).doubleValue());
}
else if (sqlType == Types.BIGINT && value instanceof Integer)
{
// workaround: Oracle thin driver problem when expecting long
ps.setLong(index, ((Integer)value).intValue());
}
else
{
super.setObjectForStatement(ps, index, value, sqlType);
}
...
Phil
Jakob Braeuchi wrote:
hi phil,
imho it would be better to handle this problem in the oracle platform
instead of hacking the oql parser.
jakob
----- Original Message -----
From: "Phil Warrick" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Thursday, October 17, 2002 10:05 PM
Subject: OQLParser problem with Long and Oracle?
Hi again,
Sorry to repost, but the subject line wasn't very informative at the end
of the "Oracle JDBC Types" thread.
Phil Warrick wrote:
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.getIteratorFromQuery(Pe
rsistenceBrokerImpl.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:Geigl.Maximilian@;akdb.de]
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:warrick@;bmed.mcgill.ca]
> 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:lounder@;caris.com]
> >>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:ojb-user-unsubscribe@;jakarta.apache.org>
> >>For additional commands, e-mail:
> >><mailto:ojb-user-help@;jakarta.apache.org>
> >>
> >
> > --
> > To unsubscribe, e-mail: >
<mailto:ojb-user-unsubscribe@;jakarta.apache.org>
> > For additional commands, e-mail:
> <mailto:ojb-user-help@;jakarta.apache.org>
>
>
>
> --
> To unsubscribe, e-mail: >
<mailto:ojb-user-unsubscribe@;jakarta.apache.org>
> For additional commands, e-mail:
> <mailto:ojb-user-help@;jakarta.apache.org>
>
--
To unsubscribe, e-mail:
<mailto:ojb-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail:
<mailto:ojb-user-help@;jakarta.apache.org>
------------------------------------------------------------------------
--
To unsubscribe, e-mail:
<mailto:ojb-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail:
<mailto:ojb-user-help@;jakarta.apache.org>
--
To unsubscribe, e-mail: <mailto:ojb-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:ojb-user-help@;jakarta.apache.org>
--
To unsubscribe, e-mail: <mailto:ojb-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:ojb-user-help@;jakarta.apache.org>
--
To unsubscribe, e-mail: <mailto:ojb-user-unsubscribe@;jakarta.apache.org>
For additional commands, e-mail: <mailto:ojb-user-help@;jakarta.apache.org>
