Agreed. Error logging an query logging are rather important when trying to figure out why problems are occuring. Right now its only useful if we submit it to your engineers or something obvious is happening.

Query logging right now only logs successful queries. It would be nice to have an option for invalid SQL as well.

This is also true of the JDBC driver, we really need better error messages for bad queries as well as the 6000/7000 series errors.

Thank you for investigating. I think it is easier than it is made out to be. Dialects will really help us.

I have to second this (+3), without the Dialects we're running into problems. I'm implementing a JBoss solution (Hibernate) and we need a Dialect (Which I can't imagine would be a massive job--literally we're talking about extending a class).

My code is littered with things that look like ->
<code>
Query q = em.createNativeQuery("SELECT Max(ID) FROM $Table");
Integer max = (Integer)q.getSingleResult();
....
</code>
to get the Entities up and rolling. JPA is still usable, but I'm having looking at doing anything quickly its a pain.

This is particularly complicated in multiple queries within a single transaction. Basically I have to do a lot of native SQL to make this work. It's frustrating, and honestly, with the plethera of other Dialects out there porting to MySQL/SQL Server isn't an issue. This system has not been moved specificially because we need Advantage compatibility, but I can say I would not use advantage for any Enterprise system until this is resolved.

Hibernate is the big one, but really all we need is a JPA package _OR_ a hibernate Dialect. I don't think people are using a Toplink/EclipseLink implementation at this point very often with Advantage. But support for Eclipselink 2.0 would be an absolute plus. This, again, is simply a dialect.

--------
Addendum:
If the JDBC Drive would report the text of the bad query (via an option even) that would save a lot of headaches. Either that or enable the logging of bad queries.
<code>
Caused by: com.extendedsystems.jdbc.advantage.ADSException: [Extended Systems][Advantage JDBC]State = 42000; NativeError = 2115; [iAnywhere Solutions][Advantage SQL Engine]Expected lexical element not found: identifier or expression -- Location of error in the SQL statement is: 55 There was a problem parsing the WHERE clause in your SELECT statement.
</code>
Isn't useful when you can't see inside the ORM easily to see what the query being generated is.

Summary -> Make the JDBC have a verbose options (massive help and this wouldn't be too hard I think--I'm seeing the parser output)
Add a Hibernate Dialect and an Eclipselink Dialect.

http://wiki.eclipse.org/EclipseLink
http://ajava.org/online/hibernate3api/org/hibernate/dialect/package-summary.html

Thanks,
-Daniel