I've been upgrading an application to Hibernate 3.1 (from 2.1) over the last few days, and I've come across a strange problem with an "Invalid column name" that Oracle 8i is passing back. The query runs fine in TOAD and as a PreparedStatement (copying what Hibernate is generating) using the same mapping files and JDBC driver.
From my hibernate.cfg.xml file:
<property name="dialect">org.hibernate.dialect.OracleDialect</property>
I've searched the forums, and found this problem mentioned twice (once was an id-issue, and the other had no resolution).
Here's the log4j debugging info (I've added some blank lines for readability):
21-Mar-2006 11:56:09 111 unable to locate native-sql query plan in cache; generating (SELECT {bles.*} FROM bl_exchange_sites bles, boat_listing bl WHERE bl.boat_listing_id = bles.boat_listing_id AND ( bl.last_modified_date >= bles.last_Transmission_date OR (bles.last_transmission_date IS NULL AND bles.deleted_ind = 0)) AND bles.site_id = :pSiteID AND bles.last_transmission_failure_yn = 'N' AND rownum <= :pICount AND mod(bles.boat_listing_id,2) = 1 ORDER BY bl.last_modified_date ASC)
21-Mar-2006 11:56:10 1664 SQL query: SELECT bles.bl_exchange_site_id as bl1_7_0_, bles.DELETED_IND as DELETED2_7_0_, bles.LAST_TRANSMISSION_DATE as LAST3_7_0_, bles.EXTERNAL_LAST_MODIFIED_DATE as EXTERNAL4_7_0_, bles.EXTERNAL_UNIQUE_ID as EXTERNAL5_7_0_, bles.LAST_TRANSMISSION_FAILURE_YN as LAST6_7_0_, bles.pictures_processed_ind as pictures7_7_0_, bles.image_processed_date as image8_7_0_, bles.priority_ind as priority9_7_0_, bles.picture_priority_ind as picture10_7_0_, bles.master_site_id as master11_7_0_, bles.site_id as site12_7_0_, bles.boat_listing_id as boat13_7_0_ FROM bl_exchange_sites bles, boat_listing bl WHERE bl.boat_listing_id = bles.boat_listing_id AND ( bl.last_modified_date >= bles.last_Transmission_date OR (bles.last_transmission_date IS NULL AND bles.deleted_ind = 0)) AND bles.site_id = ? AND bles.last_transmission_failure_yn = 'N' AND rownum <= ? AND mod(bles.boat_listing_id,2) = 1 ORDER BY bl.last_modified_date ASC
21-Mar-2006 11:56:10 311 about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
21-Mar-2006 11:56:10 346 SELECT bles.bl_exchange_site_id as bl1_7_0_, bles.DELETED_IND as DELETED2_7_0_, bles.LAST_TRANSMISSION_DATE as LAST3_7_0_, bles.EXTERNAL_LAST_MODIFIED_DATE as EXTERNAL4_7_0_, bles.EXTERNAL_UNIQUE_ID as EXTERNAL5_7_0_, bles.LAST_TRANSMISSION_FAILURE_YN as LAST6_7_0_, bles.pictures_processed_ind as pictures7_7_0_, bles.image_processed_date as image8_7_0_, bles.priority_ind as priority9_7_0_, bles.picture_priority_ind as picture10_7_0_, bles.master_site_id as master11_7_0_, bles.site_id as site12_7_0_, bles.boat_listing_id as boat13_7_0_ FROM bl_exchange_sites bles, boat_listing bl WHERE bl.boat_listing_id = bles.boat_listing_id AND ( bl.last_modified_date >= bles.last_Transmission_date OR (bles.last_transmission_date IS NULL AND bles.deleted_ind = 0)) AND bles.site_id = ? AND bles.last_transmission_failure_yn = 'N' AND rownum <= ? AND mod(bles.boat_listing_id,2) = 1 ORDER BY bl.last_modified_date ASC
21-Mar-2006 11:56:10 424 preparing statement
21-Mar-2006 11:56:10 1742 bindNamedParameters() 3 -> pSiteID [1]
21-Mar-2006 11:56:10 79 binding '3' to parameter: 1
21-Mar-2006 11:56:10 1742 bindNamedParameters() 10 -> pICount [2]
21-Mar-2006 11:56:10 79 binding '10' to parameter: 2
21-Mar-2006 11:56:10 327 about to open ResultSet (open ResultSets: 0, globally: 0)
21-Mar-2006 11:56:10 682 processing result set
21-Mar-2006 11:56:10 687 result set row: 0
21-Mar-2006 11:56:10 123 returning '21405' as column: bl1_7_0_
21-Mar-2006 11:56:10 334 about to close ResultSet (open ResultSets: 1, globally: 1)
21-Mar-2006 11:56:10 319 about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
21-Mar-2006 11:56:10 470 closing statement
21-Mar-2006 11:56:10 63 could not execute query [SELECT bles.bl_exchange_site_id as bl1_7_0_, bles.DELETED_IND as DELETED2_7_0_, bles.LAST_TRANSMISSION_DATE as LAST3_7_0_, bles.EXTERNAL_LAST_MODIFIED_DATE as EXTERNAL4_7_0_, bles.EXTERNAL_UNIQUE_ID as EXTERNAL5_7_0_, bles.LAST_TRANSMISSION_FAILURE_YN as LAST6_7_0_, bles.pictures_processed_ind as pictures7_7_0_, bles.image_processed_date as image8_7_0_, bles.priority_ind as priority9_7_0_, bles.picture_priority_ind as picture10_7_0_, bles.master_site_id as master11_7_0_, bles.site_id as site12_7_0_, bles.boat_listing_id as boat13_7_0_ FROM bl_exchange_sites bles, boat_listing bl WHERE bl.boat_listing_id = bles.boat_listing_id AND ( bl.last_modified_date >= bles.last_Transmission_date OR (bles.last_transmission_date IS NULL AND bles.deleted_ind = 0)) AND bles.site_id = ? AND bles.last_transmission_failure_yn = 'N' AND rownum <= ? AND mod(bles.boat_listing_id,2) = 1 ORDER BY bl.last_modified_date ASC]
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:4383)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:667)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1432)
at org.hibernate.type.LongType.get(LongType.java:28)
I haven't attached the mapping documents, as they are quite large.
The java query is,
q = session.createSQLQuery("SELECT {bles.*} "
+ "FROM bl_exchange_sites bles, boat_listing bl "
+ "WHERE bl.boat_listing_id = bles.boat_listing_id "
+ "AND ( bl.last_modified_date >= bles.last_Transmission_date OR (bles.last_transmission_date IS NULL AND bles.deleted_ind = 0)) "
+ "AND bles.site_id = :pSiteID AND bles.last_transmission_failure_yn = 'N' AND rownum <= :pICount "
+ strOddOrEven
+ "ORDER BY bl.last_modified_date ASC")
.addEntity("bles", BLExchangeSites.class)
.addEntity("bl", BoatListing.class);
q.setInteger("pSiteID", 3);
q.setInteger("pICount", m_iSelectCount.intValue());
List listModifiedBoats = q.list();
Obviously, I'm using the new 3.0 syntax for native queries.
Here's the JDBC statement that works:
PreparedStatement stmt = m_oConn.prepareStatement("SELECT bles.bl_exchange_site_id as bl1_7_0_, bles.DELETED_IND as DELETED2_7_0_, bles.LAST_TRANSMISSION_DATE as LAST3_7_0_, bles.EXTERNAL_LAST_MODIFIED_DATE as EXTERNAL4_7_0_, bles.EXTERNAL_UNIQUE_ID as EXTERNAL5_7_0_, bles.LAST_TRANSMISSION_FAILURE_YN as LAST6_7_0_, bles.pictures_processed_ind as pictures7_7_0_, bles.image_processed_date as image8_7_0_, bles.priority_ind as priority9_7_0_, bles.picture_priority_ind as picture10_7_0_, bles.master_site_id as master11_7_0_, bles.site_id as site12_7_0_, bles.boat_listing_id as boat13_7_0_ "
+ "FROM bl_exchange_sites bles, boat_listing bl "
+ "WHERE bl.boat_listing_id = bles.boat_listing_id AND ( bl.last_modified_date >= bles.last_Transmission_date OR (bles.last_transmission_date IS NULL AND bles.deleted_ind = 0)) AND bles.site_id = 3 AND bles.last_transmission_failure_yn = 'N' AND rownum <= 5 AND mod(bles.boat_listing_id,2) = 1 ORDER BY bl.last_modified_date ASC");
ResultSet rset = stmt.executeQuery();
while (rset.next())
{
System.out.println(rset.getLong(1));
}
Here's the mapping file for bl_exchange_sites.hbm.xml:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="boats.hibernate">
<class name="BLExchangeSites" table="bl_exchange_sites">
<id name="Id" type="long" column="bl_exchange_site_id">
<generator class="sequence">
<param name="sequence">bl_exchange_site_id_seq</param>
</generator>
</id>
<property
name="DeletedInd"
column="DELETED_IND"
type="integer"
not-null="true"
length="1"
/>
<property
name="LastTransmissionDate"
column="LAST_TRANSMISSION_DATE"
type="timestamp"
not-null="false"
length="1"
/>
<property
name="ExternalLastModifiedDate"
column="EXTERNAL_LAST_MODIFIED_DATE"
type="timestamp"
not-null="false"
length="1"
/>
<property
name="ExternalUniqueID"
column="EXTERNAL_UNIQUE_ID"
type="string"
not-null="false"
length="64"
/>
<property
name="LastTransmissionFailureYN"
column="LAST_TRANSMISSION_FAILURE_YN"
type="string"
not-null="false"
/>
<property
name="PicturesProcessedInd"
column="pictures_processed_ind"
type="integer"
not-null="false"
/>
<property
name="ImageProcessedDate"
column="image_processed_date"
type="date"
not-null="false"
/>
<property
name="PriorityInd"
column="priority_ind"
type="integer"
not-null="true"
/>
<property
name="PicturePriorityInd"
column="picture_priority_ind"
type="integer"
not-null="true"
/>
<set name="ListingExchangeURLS" inverse="true" cascade="save-update" lazy="true" order-by="picture_order asc">
<key column="bl_exchange_site_id"/>
<one-to-many class="boats.hibernate.ListingExchangeURL"/>
</set>
<many-to-one name="MasterSite" column="master_site_id" class="boats.hibernate.ExchangeSites" not-null="true"/>
<many-to-one name="Site" column="site_id" class="boats.hibernate.ExchangeSites" not-null="true"/>
<many-to-one name="BoatListing" column="boat_listing_id" class="boats.hibernate.BoatListing" not-null="true"/>
</class>
</hibernate-mapping>
I found this really interesting:
21-Mar-2006 11:56:10 123 returning '21405' as column: bl1_7_0_
The query seems to be running, but then it throws an exception. "bl1_7_0" is the "id" for the table, so the problem might be when Hibernate hydrates the object using the id.
I noticed this in the migration guide:
"Note: there is a known bug affecting dialects with theta-style outer joins (eg. OracleDialect for Oracle 8i, TimesTen dialect, Sybase11Dialect). Try to use a dialect which supports ANSI-style joins (eg. Oracle9Dialect), or fall back to the old query parser if you experience problems."
Am I running into this bug?
Any help would be appreciated.
Login
FAQ
Search