Simon Johnston - [13/Sep/04 06:23 PM ] This is the main dialect file, it uses the CaseFragment class and also over rides the generation of native IDs.

Simon Johnston - [13/Sep/04 06:23 PM ] Subordinate to the primary dialect class.

Joel Rosi-Schwartz - [15/Sep/04 01:16 PM ] This is much better then the DB2Dialect, but it still seems to have some unresolved issues. I have used the DerbyDialect to run the Junit tests against CaveatEmptor 0.95 and there are several failures. I will attach the out put from those tests to this issue. My setup was exactly the same as the one Simon is using. I used the nserverdemo that comes packaged with Cloudscape as the test database.

Joel Rosi-Schwartz - [15/Sep/04 01:19 PM ] The ouput from CaveatEmptor Junit tests using the new Driver.

Joel Rosi-Schwartz - [15/Sep/04 02:19 PM ] I have looked into the errors from the junit tests and it seems as if Cloudscase does not care for unique keys where one of the columns is nullable; in this case [b]unique (NAME, PARENT_CATEGORY_ID)[/b] caused the hicup. I got all of the tests to run successfully by removing [b]PARENT_CATEGORY_ID[/b] from the unique key. I am not sure if this is a problem with Cloudscape or CaveatEmptor. If it is Cloudscape, which is what I suspect, what can be done about it? Should this be escalated to the Derby team with a test case? There are also still reports of SQL errrors in the junit output, but these do not trigger test failures, so I assume that the testcases are swallowing them. They do, however, look worrysome to me. Advice would be appreciated. 19:27:33,875 ERROR SchemaExport: Unsuccessful: alter table BANK_ACCOUNT add constraint FK1_BANK_ACCOUNT_ID foreign key (BANK_ACCOUNT_ID) references BILLING_DETAILS 19:27:33,875 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_BANK_ACCOUNT_ID^T18^T42622 19:27:34,062 ERROR SchemaExport: Unsuccessful: alter table CATEGORY add constraint FK1_PARENT_CATEGORY_ID foreign key (PARENT_CATEGORY_ID) references CATEGORY 19:27:34,062 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_PARENT_CATEGORY_ID^T18^T42622 19:27:34,062 ERROR SchemaExport: Unsuccessful: alter table ITEM add constraint FK1_APPROVED_BY_USER_ID foreign key (APPROVED_BY_USER_ID) references USERS 19:27:34,062 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_APPROVED_BY_USER_ID^T18^T42622 19:27:34,171 ERROR SchemaExport: Unsuccessful: alter table ITEM add constraint FK3_SUCCESSFUL_BID_ID foreign key (SUCCESSFUL_BID_ID) references BID 19:27:34,171 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK3_SUCCESSFUL_BID_ID^T18^T42622 19:27:34,171 ERROR SchemaExport: Unsuccessful: alter table USERS add constraint FK1_DEFAULT_BILLING_DETAILS_ID foreign key (DEFAULT_BILLING_DETAILS_ID) references BILLING_DETAILS 19:27:34,171 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_DEFAULT_BILLING_DETAILS_ID^T18^T42622

Joel Rosi-Schwartz - [17/Sep/04 04:25 AM ] The dialect also needs to override dropConstraints to return true. The attached file makes this change.

Joel Rosi-Schwartz - [17/Sep/04 04:27 AM ] I forgot to mention that by overiding dropConstraints() all of the cascade problems that I reported previously are now cleared up.

Eugene Bekker - [20/Sep/04 04:13 PM ] The JAR file appears to be forbidden, I get a 403 error. But the description page about the Dialect seems to be ok at: http://www.johnstonshome.org/~simon/projects/derby/

Gavin King - [26/Sep/04 11:03 AM ] I have added this Dialect, with small modifications to Hibernate3 CVS. I'm getting fairly good results from the test suite. Most failures seem to be due to limitations/bugs in derby, not problems with the dialect. Of special concern: * at this time, it appears that special characters in table names do not work * I was unable to get identity columns to work

CVS Notification Service - [26/Sep/04 11:09 AM ] CVS COMMIT LOG: SUBJECT: [Hibernate-commits] Hibernate3/src/org/hibernate/dialect DerbyDialect.java,NONE,1.1 added Derby dialect by Simon Johnston HB-1224

CVS Notification Service - [26/Sep/04 11:09 AM ] CVS COMMIT LOG: SUBJECT: [Hibernate-commits] Hibernate3/src/org/hibernate/sql DerbyCaseFragment.java,NONE,1.1 added Derby dialect by Simon Johnston HB-1224

Daniel Debrunner - [11/Oct/04 01:30 PM ] * at this time, it appears that special characters in table names do not work * I was unable to get identity columns to work What's the best way to get specifics on these issues so they can be addressed in Derby?

dju dju - [24/Mar/05 09:02 AM ] I've tried using this DerbyDialect with derby 10.0.2.1 on the Hibernate distribution basic auction example (eg). Whatever the Derby mode choosen (embedded or network) I get the following errors: ================================================================      [java] Viewing all auction item objects      [java] Hibernate: select * from ( select rownumber() over(order by auctionite0_.ends desc) as rownumber_, auctioni te0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_, auctionite0_.ends as en ds0_, 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_, bi ds1_.item as item1_, user2_.userName as userName2_, user2_."password" as y3_2_, user2_.email as email2_, user2_.firstNam e as firstName2_, user2_."initial" as y6_2_, user2_.lastName as lastName2_, bids1_.item as item__, bids1_.id as id__ fro m 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 ) as temp_ where rownumber_ <= ?      [java] 15:07:30,354 WARN JDBCExceptionReporter:57 - SQL Error: 20000, SQLState: 42X01      [java] 15:07:30,354 ERROR JDBCExceptionReporter:58 - Syntax error: Encountered "(" at line 1, column 40.      [java] 15:07:30,432 WARN JDBCExceptionReporter:57 - SQL Error: 20000, SQLState: 42X01      [java] 15:07:30,432 ERROR JDBCExceptionReporter:58 - Syntax error: Encountered "(" at line 1, column 40.      [java] net.sf.hibernate.exception.SQLGrammarException: Could not execute query      [java] at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:58)      [java] at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)      [java] at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)      [java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1557)      [java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)      [java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86)      [java] at org.hibernate.auction.Main.main(Main.java:366)      [java] Caused by: SQL Exception: Syntax error: Encountered "(" at line 1, column 40.      [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java)      [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java)      [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java)      [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java)      [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java)      [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(EmbedPreparedStatement.java)      [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(EmbedPreparedStatement20.java)      [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(EmbedPreparedStatement30.java)      [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Driver30.java)      [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java)      [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(EmbedConnection.java)      [java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:263)      [java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:236)      [java] at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:67)      [java] at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:784)      [java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)      [java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)      [java] at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)      [java] at net.sf.hibernate.loader.Loader.list(Loader.java:1054)      [java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)      [java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)      [java] ... 3 more      [java] Exception in thread "main" BUILD FAILED D:\Gly-dev\HIBERN~1.8\build.xml:509: Java returned: 1 ====================================================================== Can you help with this?

Knut Forkalsrud - [02/May/05 01:33 AM ] The corresponding Derby bug http://issues.apache.org/jira/browse/DERBY-127 says the culprit is named columns in the ORDER BY clause. They counter that expressions in the ORDER BY clause is an optional feature in the SQL standard. This feature is exposed via the JDBC metadata call java.sql.DatabaseMetadata.supportsExpressionsInOrderBy(), and the Derby driver answers truthfully: false. I know nothing about Hibernate internals, but the workaround ("order by 1" instead of "order by col1") would be possible.

Knut Forkalsrud - [02/May/05 02:14 AM ] Actually, Hibernate doesn't even do that. It orders by a column that does not exist in the result set. In the "eg" example in the Hibernate 3.0.2 distribution the first failing query is: select auctionite0_.id as col_0_0_,        auctionite0_.description as col_1_0_,        auctionite0_.ends as col_2_0_,        max(bids1_.amount) as col_3_0_   from AuctionItem auctionite0_ left outer join Bid bids1_     on auctionite0_.id=bids1_.item  group by auctionite0_.id , auctionite0_.description , auctionite0_.ends  order by auctionite0_.ends desc; The result set columns are "col_0_0_" ... "col_3_0_", still the database is asked to order by "auctionite0_.ends". I'm not sure if that works in other databases, but it should not be expected to work, and I would call it a bug.