I'm running into a few problems when I try to use OQL to generate SQL for an Oracle db, and I'm hoping someone can point me to simple solutions.
_Problem 1_
We're using a length-1 numeric column for our boolean values. When I use "... where mci.relateBool=true" in the OQL, Castor accepts it fine; however, it generates the SQL as "... "ITEM_META_INFO"."CAN_BE_RELATED_TO" = true", which freaks Oracle out. I've tried adding a "type='[integer | numeric]' " attribute to the <sql> entry in the mapping file, to no effect. Seems to have the same thing happen when I use a variable ("$boolean1") and oq.bind(true). Is this type of setup supported? So far, the workaround has been to use "... where mci.relateBool=1", or oq.bind(1).
_Problem 2_
A little trickier. Oracle expects dates in the format dd-MMM-yyyy (13-Nov-2002). Using the "... mci.endDate > date '13-11-2002' " syntax (or the minute-inclusive timestamp format), OQL generates the SQL statement with date similarly formatted, and Oracle throws an error about not recognizing the month. Trying the MMM month format in the OQL of course generates a parsing error. Is there a fix for this, or any way of configuring returned SQL formates for types?
Hope someone can help.
_Relevant_Code_Snippets_
Platform: Resin 2.1.5, running on Windows NT sp6
- entry in resin.conf file for oracle DB:
<resource-ref>
<res-ref-name>jdbc/content</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<init-param driver-name="oracle.jdbc.driver.OracleDriver"/>
<init-param url=""jdbc:oracle:thin:@********:****:*****"/>
<init-param user="******"/>
<init-param password="******"/>
</resource-ref>
- db mapping file:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE database PUBLIC "-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN" "http://castor.exolab.org/jdo-conf.dtd">
<database name="content" engine="oracle" >
<jndi name="java:comp/env/jdbc/content"/>
<mapping href=""content_mapping.xml"/>
<mapping href=""meta_content_mapping.xml"/>
</database>
- object mapping file:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapping PUBLIC "-//EXOLAB/Castor Object Mapping DTD Version 1.0//EN" "http://castor.exolab.org/mapping.dtd">
<mapping>
<class name="clubmom.cms.MetaContentItem" identity="metaInfoId" key-generator="META_IDS">
<map-to table="ITEM_META_INFO"/>
[...]
<field name="startDate" type="date">
<sql name="START_DATE" type="timestamp"/>
</field>
<field name="endDate" type="date">
<sql name="END_DATE" type="timestamp"/>
</field>
<field name="relateBool" type="boolean">
<sql name="CAN_BE_RELATED_TO" type="integer"/>
</field>
[...]
</class>
</mapping>
Ben Cochran
Senior Interface Engineer
ClubMom, Inc.
200 Madison Avenue, 6th Floor
New York, New York 10016
tel 646.435.6508
fax 646.435.6500
