Hi,

I'm implementing a new dialect for QED (http:www.quadcap.com)
in Hibernate 2.1.

Running 'ant eg' and getting a run-time exception for
Session.createQuery, which is used in

'viewAllAuctionsSlow' and 'viewAllAuctionsFast' methods of 'main'

If I comment out calls to those methods in the constructor for main,
ant eg succeeds, and so the current problem is localized to the
Session.createQuery call.

I've checked the qed database after running 'ant eg' and find well
constructed tables w/ appropriate column headings, and rows that
appropriately match the created data before the exception occurs.

The exception has to do with bad column id's. The SQL rendering
looks mangled to me. However, being a newbie, I'm not sure if my
eval is right, and so the questions. . .

What should the sql rendering look like?
Is the SQL rendering that I'm seeing in the log wrong?

If the sql rendering is wrong for createQuery, then

What might be the reason for this? i.e., Is it related
to the qed dialect?
If so, how?

Here's a summary of the qed dialect, environment, configs, and log . . .

------------
QEDDialect.java

package net.sf.hibernate.dialect;

import java.sql.Types;

import net.sf.hibernate.cfg.Environment;

/**
* A SQL dialect for QED.
* @author Jeffrey Franks
*/
public class QEDDialect extends Dialect {

public QEDDialect() {
super();
registerColumnType( Types.BIT, "BIT" );
registerColumnType( Types.BIGINT, "BIGINT" );
registerColumnType( Types.SMALLINT, "SMALLINT" );
registerColumnType( Types.TINYINT, "TINYINT" );
registerColumnType( Types.INTEGER, "INTEGER" );
registerColumnType( Types.CHAR, "CHAR(1)" );
registerColumnType( Types.VARCHAR, "VARCHAR($l)" );
registerColumnType( Types.FLOAT, "FLOAT" );
registerColumnType( Types.DOUBLE, "DOUBLE PRECISION" );
registerColumnType( Types.DATE, "DATE" );
registerColumnType( Types.TIME, "TIME" );
registerColumnType( Types.TIMESTAMP, "TIMESTAMP" );
registerColumnType( Types.VARBINARY, "VARBINARY($l)" );
registerColumnType( Types.NUMERIC, "NUMERIC(19, $l)" );
registerColumnType( Types.REAL, "REAL" );
registerColumnType( Types.BLOB, "BLOB($l)" );
registerColumnType( Types.CLOB, "CLOB($l)" );

getDefaultProperties().setProperty(Environment.USE_OUTER_JOIN, "true");
getDefaultProperties().setProperty(Environment.STATEMENT_BATCH_SIZE, NO_BATCH);
}

public String getAddColumnString() {
return "add column";
}

public boolean dropConstraints() {
return true;
}

}

------------

hibernate.config

* hsql stuff commented out,
* QED added as follows:

## QED

hibernate.dialect net.sf.hibernate.dialect.QEDDialect
hibernate.connection.driver_class com.quadcap.jdbc.JdbcDriver
hibernate.connection.username sa
hibernate.connection.password
hibernate.connection.url jdbc:qed:test
hibernate.show_sql=true
------------

log4j.properties modified, turned on SQL=debug

------------
Here is the log for:
exception occuring inside createQuery
called by viewAllAuctionsSlow
called by constructor for main

Note: database created from scratch during this run and so
drop commands fail because no tables exist yet.

Buildfile: build.xml

eg:
[echo] remember to place your JDBC driver in the lib directory
[java] 12:23:20,109 INFO Environment:432 - Hibernate 2.1.1
[java] 12:23:20,195 INFO Environment:466 - loaded properties from resource hibernate.properties: {hibernate.connection.driver_class=com.quadcap.jdbc.JdbcDriver, hibernate.cglib.use_reflection_optimizer=true, hibernate.cache.provider_class=net.sf.hibernate.cache.HashtableCacheProvider, hibernate.max_fetch_depth=1, hibernate.dialect=net.sf.hibernate.dialect.QEDDialect, hibernate.jdbc.use_streams_for_binary=true, hibernate.jdbc.batch_size=0, hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N', hibernate.proxool.pool_alias=pool1, hibernate.connection.username=sa, hibernate.connection.url=jdbc:qed:test, hibernate.show_sql=true, hibernate.connection.password=, hibernate.connection.pool_size=1}
[java] 12:23:20,206 INFO Environment:480 - using java.io streams to persist binary types
[java] 12:23:20,208 INFO Environment:481 - using CGLIB reflection optimizer
[java] 12:23:20,237 INFO Configuration:318 - Mapping resource: org/hibernate/auction/AuctionItem.hbm.xml
[java] 12:23:22,931 INFO Binder:225 - Mapping class: org.hibernate.auction.AuctionItem -> AuctionItem
[java] 12:23:23,419 INFO Configuration:318 - Mapping resource: org/hibernate/auction/Bid.hbm.xml
[java] 12:23:23,577 INFO Binder:225 - Mapping class: org.hibernate.auction.Bid -> Bid
[java] 12:23:23,789 INFO Binder:169 - Mapping subclass: org.hibernate.auction.BuyNow -> Bid
[java] 12:23:23,794 INFO Configuration:318 - Mapping resource: org/hibernate/auction/User.hbm.xml
[java] 12:23:24,030 INFO Binder:225 - Mapping class: org.hibernate.auction.User -> AuctionUser
[java] 12:23:24,868 INFO ReflectHelper:160 - reflection optimizer disabled for: org.hibernate.auction.Name, IllegalArgumentException: Cannot find matching method/constructor
[java] 12:23:24,880 INFO Configuration:584 - processing one-to-many association mappings
[java] 12:23:24,901 INFO Binder:1139 - Mapping collection: org.hibernate.auction.AuctionItem.bids -> Bid
[java] 12:23:24,905 INFO Binder:1139 - Mapping collection: org.hibernate.auction.User.bids -> Bid
[java] 12:23:24,906 INFO Binder:1139 - Mapping collection: org.hibernate.auction.User.auctions -> AuctionItem
[java] 12:23:24,907 INFO Configuration:593 - processing one-to-one association property references
[java] 12:23:24,909 INFO Configuration:618 - processing foreign key constraints
[java] 12:23:25,056 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.QEDDialect
[java] 12:23:25,064 INFO SettingsFactory:58 - Maximim outer join fetch depth: 1
[java] 12:23:25,065 INFO SettingsFactory:62 - Use outer join fetching: true
[java] 12:23:25,139 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[java] 12:23:25,144 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1
[java] 12:23:25,233 INFO DriverManagerConnectionProvider:71 - using driver: com.quadcap.jdbc.JdbcDriver at URL: jdbc:qed:test
[java] 12:23:25,236 INFO DriverManagerConnectionProvider:72 - connection properties: {user=sa, password=}
[java] 12:23:25,278 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
[java] 12:23:27,643 INFO SettingsFactory:89 - Use scrollable result sets: false
[java] 12:23:27,653 INFO SettingsFactory:96 - echoing all SQL to stdout
[java] 12:23:27,656 INFO SettingsFactory:99 - Query language substitutions: {no='N', true=1, yes='Y', false=0}
[java] 12:23:27,690 INFO SettingsFactory:110 - cache provider: net.sf.hibernate.cache.HashtableCacheProvider
[java] 12:23:27,726 INFO Configuration:1057 - instantiating and configuring caches
[java] 12:23:28,004 INFO SessionFactoryImpl:119 - building session factory
[java] 12:23:30,091 INFO SessionFactoryObjectFactory:82 - no JNDI name configured
[java] 12:23:30,147 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.QEDDialect
[java] 12:23:30,156 INFO Configuration:584 - processing one-to-many association mappings
[java] 12:23:30,158 INFO Configuration:593 - processing one-to-one association property references
[java] 12:23:30,159 INFO Configuration:618 - processing foreign key constraints
[java] 12:23:30,161 INFO Configuration:584 - processing one-to-many association mappings
[java] 12:23:30,189 INFO Configuration:593 - processing one-to-one association property references
[java] 12:23:30,190 INFO Configuration:618 - processing foreign key constraints
[java] 12:23:30,200 INFO SchemaExport:98 - Running hbm2ddl schema export
[java] 12:23:30,235 INFO SchemaExport:117 - exporting generated schema to database
[java] 12:23:30,237 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[java] 12:23:30,238 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1
[java] 12:23:30,240 INFO DriverManagerConnectionProvider:71 - using driver: com.quadcap.jdbc.JdbcDriver at URL: jdbc:qed:test
[java] 12:23:30,241 INFO DriverManagerConnectionProvider:72 - connection properties: {user=sa, password=}
[java] 12:23:30,324 DEBUG SchemaExport:132 - alter table AuctionItem drop constraint FK522A9BD6C9FF4F7F
[java] 12:23:30,969 DEBUG SchemaExport:137 - Unsuccessful: alter table AuctionItem drop constraint FK522A9BD6C9FF4F7F
[java] 12:23:30,976 DEBUG SchemaExport:138 - table not found: SA.AUCTIONITEM
[java] 12:23:30,977 DEBUG SchemaExport:132 - alter table AuctionItem drop constraint FK522A9BD657291C03
[java] 12:23:31,043 DEBUG SchemaExport:137 - Unsuccessful: alter table AuctionItem drop constraint FK522A9BD657291C03
[java] 12:23:31,046 DEBUG SchemaExport:138 - table not found: SA.AUCTIONITEM
[java] 12:23:31,077 DEBUG SchemaExport:132 - alter table Bid drop constraint FK104DD317B13
[java] 12:23:31,115 DEBUG SchemaExport:137 - Unsuccessful: alter table Bid drop constraint FK104DD317B13
[java] 12:23:31,118 DEBUG SchemaExport:138 - table not found: SA.BID
[java] 12:23:31,119 DEBUG SchemaExport:132 - alter table Bid drop constraint FK104DDAD3189F4
[java] 12:23:31,197 DEBUG SchemaExport:137 - Unsuccessful: alter table Bid drop constraint FK104DDAD3189F4
[java] 12:23:31,203 DEBUG SchemaExport:138 - table not found: SA.BID
[java] 12:23:31,204 DEBUG SchemaExport:132 - drop table AuctionItem
[java] 12:23:31,294 DEBUG SchemaExport:137 - Unsuccessful: drop table AuctionItem
[java] 12:23:31,296 DEBUG SchemaExport:138 - no such table: SA.AUCTIONITEM
[java] 12:23:31,298 DEBUG SchemaExport:132 - drop table AuctionUser
[java] 12:23:31,334 DEBUG SchemaExport:137 - Unsuccessful: drop table AuctionUser
[java] 12:23:31,337 DEBUG SchemaExport:138 - no such table: SA.AUCTIONUSER
[java] 12:23:31,338 DEBUG SchemaExport:132 - drop table Bid
[java] 12:23:31,424 DEBUG SchemaExport:137 - Unsuccessful: drop table Bid
[java] 12:23:31,427 DEBUG SchemaExport:138 - no such table: SA.BID
[java] 12:23:31,428 DEBUG SchemaExport:132 - drop table hibernate_unique_key
[java] 12:23:31,500 DEBUG SchemaExport:137 - Unsuccessful: drop table hibernate_unique_key
[java] 12:23:31,506 DEBUG SchemaExport:138 - no such table: SA.HIBERNATE_UNIQUE_KEY
[java] 12:23:31,508 DEBUG SchemaExport:149 - create table AuctionItem (
[java] id BIGINT not null,
[java] description VARCHAR(255),
[java] ends TIMESTAMP,
[java] condition INTEGER,
[java] seller BIGINT not null,
[java] successfulBid BIGINT,
[java] primary key (id)
[java] )
[java] 12:23:31,700 DEBUG SchemaExport:149 - create table AuctionUser (id BIGINT not null, userName VARCHAR(255) not null, "password" VARCHAR(255), email VARCHAR(255), firstName VARCHAR(255), "initial" CHAR(1), lastName VARCHAR(255), primary key (id))
[java] 12:23:31,798 DEBUG SchemaExport:149 - create table Bid (
[java] id BIGINT not null,
[java] isBuyNow CHAR(1) not null,
[java] amount FLOAT not null,
[java] datetime TIMESTAMP not null,
[java] bidder BIGINT not null,
[java] item BIGINT not null,
[java] primary key (id)
[java] )
[java] 12:23:31,914 DEBUG SchemaExport:149 - alter table AuctionItem add constraint FK522A9BD6C9FF4F7F foreign key (seller) references AuctionUser
[java] 12:23:32,060 DEBUG SchemaExport:149 - alter table AuctionItem add constraint FK522A9BD657291C03 foreign key (successfulBid) references Bid
[java] 12:23:32,118 DEBUG SchemaExport:149 - alter table Bid add constraint FK104DD317B13 foreign key (item) references AuctionItem
[java] 12:23:32,177 DEBUG SchemaExport:149 - alter table Bid add constraint FK104DDAD3189F4 foreign key (bidder) references AuctionUser
[java] 12:23:32,237 DEBUG SchemaExport:149 - create table hibernate_unique_key (
[java] next_hi INTEGER
[java] )
[java] 12:23:32,298 DEBUG SchemaExport:149 - insert into hibernate_unique_key values ( 0 )
[java] 12:23:32,390 INFO SchemaExport:160 - schema export complete
[java] 12:23:32,405 INFO DriverManagerConnectionProvider:137 - cleaning up connection pool: jdbc:qed:test
[java] Setting up some test data
[java] 12:23:33,171 DEBUG SQL:223 - insert into AuctionUser (userName, "password", email, firstName, "initial", lastName, id) values (?, ?, ?, ?, ?, ?, ?)
[java] Hibernate: insert into AuctionUser (userName, "password", email, firstName, "initial", lastName, id) values (?, ?, ?, ?, ?, ?, ?)
[java] 12:23:33,251 DEBUG SQL:223 - insert into AuctionItem (description, ends, condition, seller, successfulBid, id) values (?, ?, ?, ?, ?, ?)
[java] Hibernate: insert into AuctionItem (description, ends, condition, seller, successfulBid, id) values (?, ?, ?, ?, ?, ?)
[java] 12:23:33,279 DEBUG SQL:223 - insert into Bid (amount, datetime, bidder, item, isBuyNow, id) values (?, ?, ?, ?, 'N', ?)
[java] Hibernate: insert into Bid (amount, datetime, bidder, item, isBuyNow, id) values (?, ?, ?, ?, 'N', ?)
[java] 12:23:33,330 DEBUG SQL:223 - insert into AuctionItem (description, ends, condition, seller, successfulBid, id) values (?, ?, ?, ?, ?, ?)
[java] Hibernate: insert into AuctionItem (description, ends, condition, seller, successfulBid, id) values (?, ?, ?, ?, ?, ?)
[java] 12:23:33,446 DEBUG SQL:223 - insert into Bid (amount, datetime, bidder, item, isBuyNow, id) values (?, ?, ?, ?, 'N', ?)
[java] Hibernate: insert into Bid (amount, datetime, bidder, item, isBuyNow, id) values (?, ?, ?, ?, 'N', ?)
[java] 12:23:33,479 DEBUG SQL:223 - insert into Bid (amount, datetime, bidder, item, isBuyNow, id) values (?, ?, ?, ?, 'Y', ?)
[java] Hibernate: insert into Bid (amount, datetime, bidder, item, isBuyNow, id) values (?, ?, ?, ?, 'Y', ?)
[java] Viewing all auction item objects
[java] 12:23:33,882 DEBUG SQL:223 - select auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_, auctionite0_.ends as ends0_, auctionite0_.condition as condition0_, auctionite0_.seller as seller0_, auctionite0_.successfulBid as successf6_0_, bids1_.isBuyNow as isBuyNow1_, bids1_.amount as amount1_, bids1_.datetime as datetime1_, bids1_.bidder as bidder1_, bids1_.item as item1_, user2_.userName as userName2_, user2_."password" as y3_2_, user2_.email as email2_, user2_.firstName as firstName2_, user2_."initial" as y6_2_, user2_.lastName as lastName2_, bids1_.id as id__, bids1_.item as item__ from AuctionItem auctionite0_ left outer join Bid bids1_ on auctionite0_.id=bids1_.item left outer join AuctionUser user2_ on bids1_.bidder=user2_.id order by auctionite0_.ends desc
[java] Hibernate: select auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_, auctionite0_.ends as ends0_, auctionite0_.condition as condition0_, auctionite0_.seller as seller0_, auctionite0_.successfulBid as successf6_0_, bids1_.isBuyNow as isBuyNow1_, bids1_.amount as amount1_, bids1_.datetime as datetime1_, bids1_.bidder as bidder1_, bids1_.item as item1_, user2_.userName as userName2_, user2_."password" as y3_2_, user2_.email as email2_, user2_.firstName as firstName2_, user2_."initial" as y6_2_, user2_.lastName as lastName2_, bids1_.id as id__, bids1_.item as item__ from AuctionItem auctionite0_ left outer join Bid bids1_ on auctionite0_.id=bids1_.item left outer join AuctionUser user2_ on bids1_.bidder=user2_.id order by auctionite0_.ends desc
[java] 12:23:34,086 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: null
[java] 12:23:34,088 ERROR JDBCExceptionReporter:46 - Bad column name: AUCTIONITE0_.ENDS
[java] 12:23:34,091 WARN JDBCExceptionReporter:38 - SQL Error: 0, SQLState: null
[java] 2004-01-13 12:23:34.080: [main] bad col: AUCTIONITE0_.ENDS, c = ItemsCursor: {ID0_,ID1_,ID2_,DESCRIPT2_0_,ENDS0_,CONDITION0_,SELLER0_,SUCCESSF6_0_,ISBUYNOW1_,AMOUNT1_,DATETIME1_,BIDDER1_,ITEM1_,USERNAME2_,Y3_2_,EMAIL2_,FIRSTNAME2_,Y6_2_,LASTNAME2_,ID__,ITEM__} : (cursor = JoinInnerCursor: {SA.AUCTIONITE0_.ID,SA.AUCTIONITE0_.DESCRIPTION,SA.AUCTIONITE0_.ENDS,SA.AUCTIONITE0_.CONDITION,SA.AUCTIONITE0_.SELLER,SA.AUCTIONITE0_.SUCCESSFULBID,SA.BIDS1_.ID,SA.BIDS1_.ISBUYNOW,SA.BIDS1_.AMOUNT,SA.BIDS1_.DATETIME,SA.BIDS1_.BIDDER,SA.BIDS1_.ITEM,SA.USER2_.ID,SA.USER2_.USERNAME,SA.USER2_.PASSWORD,SA.USER2_.EMAIL,SA.USER2_.FIRSTNAME,SA.USER2_.INITIAL,SA.USER2_.LASTNAME})
[java] 12:23:34,094 ERROR JDBCExceptionReporter:46 - Bad column name: AUCTIONITE0_.ENDS
[java] 12:23:34,116 ERROR JDBCExceptionReporter:38 - Could not execute query
[java] java.sql.SQLException: Bad column name: AUCTIONITE0_.ENDS
[java] at com.quadcap.sql.OrderByCursor.<init>(OrderByCursor.java:96)
[java] Exception in thread "main" net.sf.hibernate.JDBCException: Could not execute query
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1478)
[java] at com.quadcap.sql.SelectStmt.execute(SelectStmt.java:81)
[java] at com.quadcap.sql.Session.doStatement(Session.java:264)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86)
[java] at com.quadcap.jdbc.PreparedStatement.execute(PreparedStatement.java:186)
[java] at com.quadcap.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:223)
[java] at org.hibernate.auction.Main.main(Main.java:366)
[java] Caused by: java.sql.SQLException: Bad column name: AUCTIONITE0_.ENDS
[java] at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
[java] at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
[java] at com.quadcap.sql.OrderByCursor.<init>(OrderByCursor.java:96)
[java] at com.quadcap.sql.SelectStmt.execute(SelectStmt.java:81)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
[java] at com.quadcap.sql.Session.doStatement(Session.java:264)
[java] at com.quadcap.jdbc.PreparedStatement.execute(PreparedStatement.java:186)
[java] at com.quadcap.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:223)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:940)
[java] at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
[java] at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:836)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
[java] at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:940)
[java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:836)
[java] at org.hibernate.auction.Main.main(Main.java:366)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
[java] ... 3 more

Try copying the SQL generated and execute it manually against the db - I suspect the ORDER BY part being the evildoer, try runing it without ORDER.

Hi Gloeglm,

I did as you suggested and ran the sql manually against
the QED database but without just the last part
starting with 'order by'

It worked.

Ran it again and added the 'order by' part and it failed
complaining about bad column id 'auctionite0_.ends'

So, order by in this sql case is *evil*

Is there something I can do in the QEDDialect.java file
that can work around this?

How should I proceed?

Well, this is actually a database limitation, which can not used aliased fields in the order by using the original column name. Try using the alias asigned in the query, eg ORDER BY ends0_. I don't think however you can easily change this using the dialect ...

Hi Gloeglm,

Yes, I confirmed that "order by ends0_" works okay.

This means just as you said -- a database limitation, which can not
use aliased fields in the 'order by' using the original column name.

I'll be pursuing a solution with QED to determine if and how
this database limitation can be removed.

Thank you for your help in clarifying what the problem is.

JFranks:

Did you ever work out the problem with QED, and finalize the QED Dialect class? If so, would you post the final version of the class (assuming it has changed from the example above)?


Thanks... Maury.

Hi Maury,

We're close to being provided with a beta release of QED.
The QED folks are working hard at resolveing open issues
with Hibernate.

As soon as the beta release is made available to me, I'll test
it. Looking forward to using QED with Hibernate.

Will make positive results available, otherwise continue working
with QED folks in order to help them with QED-Hibernate
operability.

regards,