I am evaluating your Access v3.2 driver for a project that uses an Access 2003 format database (Vista, with Access 2007 installed) and Hibernate for the persistence layer.
It appears that the driver works fine for Hibernate's literal prepared queries:
SELECT a,b,c FROM myTable m1 LEFT OUTER JOIN myTable2 m2 ON m1.id = m2.id WHERE c='abc'
...but it does not seem to work when the query contains binding parameters:
SELECT a,b,c FROM myTable m1 LEFT OUTER JOIN myTable2 m2 ON m1.id = m2.id WHERE c=?;
--> bind param is 'abc'
In the latter case, the driver's result set contains no rows. I've traced the code thoroughly in my debugger (difficult, because you have obfuscated all the member variable names in your code) and it appears that the binding is being made correctly. The SQL statement works fine if I type it directly into an MS Access query and returns the proper # of rows.
Alternately, the issue may be the left outer join. I just noticed that all of my working queries use inner joins but my non-working ones use outer joins.
Any help would be appreciated. The driver seems excellent otherwise, but without a solution to this problem, I can't consider purchasing it.
|
Here is some additional information that will probably be of assistance to you.
A non-parameterized, non-join (single table) query works fine, returning the correct row from a table of over 60,000 records regardless of where in the table it is located.
The second (parameterized) query, however, does not work unless the rows I need are in the first 1,000 rows of my join result. When I stripped down one of the associative tables (which has about 300,000 records) so that it only contained the 200 or so rows that would match, the driver worked fine and returned the correct results. The documentation for the eval version says that a SELECT statement is limited to 1,000 rows returned. It appears, however, that when a join, an outer join, and/or a parameter is involved, then the driver only looks at the first 1000 rows of a table. How can I get by this limitation to complete my evaluation?
|
Could someone from HXTT please reply to my question quickly? My evaluation period expires in a few days, and I cannot commit to a purchase until I have a resolution to this issue. Thank you.
|
Supported. The latest package will be released in 3 hours.
|
Thank you - the new package fixed the bug. My only disappointment is that the query on the two very large tables is quite slow, even with every relevant column indexed. However, that may be due to Access/Jet being naturally slow.
Well done.
|
>only disappointment is that the query on the two very large tables is quite slow,
>even with every relevant column indexed.
LEFT/RIGHT/FULL JOIN is slower than inner join, which inner join will optimize c=? prior to m1.id = m2.id .
For LEFT JOIN, you need to use
SELECT a,b,c FROM myTable m1 LEFT OUTER JOIN myTable2 m2 ON m1.id = m2.id and c=?;
then it will be quicker.
|