|
|
|
Below code is a sample with PostgreSQL.
String query = "SELECT o FROM Person o ORDER BY personId LIMIT $1, $2"; This code generates SQL shown below: SELECT ... FROM "person" ORDER BY "person"."personid" OFFSET ? LIMIT ? FOR UPDATE Because I'm only using Castor with PostgreSQL, This bug prevents us (RollerWeblogger.org) from using LIMIT effectively (kills
HSQLDB dead) - which has significant performance implications. For the problem I'm seeing I think you are on the right track; a solution came Add JDBCSyntax " OFFSET " as you suggested. But also add it to the the if ( _limit != null ) { sql.append(JDBCSyntax.Limit).append(_limit); if (_offset != null) sql.append(JDBCSyntax.Offset).append(_offset); }Where the MySQL version would look like: if ( _limit != null ) { if (_offset != null) sql.append(_offset).append(","); sql.append(JDBCSyntax.Limit).append(_limit); }HSQLDB doesn't support a single-argument LIMIT... if ( _limit != null ) else { sql.append(" TOP ").append(_limit); }} All queries could then be written Ugh, I messed up the last two examples:
Where the MySQL version would look like: HSQLDB doesn't support a single-argument LIMIT... else { sql.append(" TOP ").append(_limit); }} Lance et alias, I'll work to get this patch integrated with the latest and
greatest code in CVS (where some things might have changed in the meantime, given that a year has passed since the bug had been raised). I'll make progress visible through this bug report. Thinking aloud, it would be really great if we could extend the offered I have managed to successfully apply above patch to the current codebase.
Testing (on mySQL) looks fine to me, being able to restrict the size of the result set according to the spec. I do have one problem, though: it looks like I can only use queries such as select n from test1221.Account n limit $1, $2 bug get an exception for a query select n from test1221.Account n limit 5, 5 Exception: Any idea why ? I had a look at ParseTreeWalker, and there's CASE statements for If you look at one of my comments you'll see I ended it with:
All queries could then be written So if you have replicated my solution (on the new codebase) your query should select n from test1221.Account n limit $1 offset $2 Does capitilization of the keywords matter? Lance, I agree with you in that I expected to be able to use the OFFSET literal
in my queries (as a result of the additions to Token.java et alias), but as I have mentioned above, the only way I could get it to work was with the following syntax: select n from test1221.Account n limit $1, $2 And no, capitalization didn't matter as I've tried this as well. I haven't looked a tthe code since I submitted my patch, and I doubt I'll have
time soon. The Roller project is no longer using Castor (for this and other reasons) so if you want to back out your changes it is fine by me. Which patch were you using, the original (inline) or mine at Tha latter, as attached by yourself on 05/04/2003. Don't worry, I'll find a
solution one way or the other .. ![]() he sees the TreeWalker as one of his personal babies. Created an attachment (id=619)
Modified patch I've just attached a (slightly ..
![]() clause working as discussed above. With this patch applied, one can now specify OQLQueries such as "SELECT o FROM Person o ORDER BY personId LIMIT $1 OFFSET $2" and "SELECT o FROM Person o ORDER BY personId LIMIT $1" I was working with this last week and I thought I left a comment here about it.
IIRC, that must have been when my browser crashed on me ;-/. Werner, this patch looks good. Just get rid of the System.out.println() calls. JUnit test case does exist, and I'll get rid of the System.out.println().
Though I'd love to know where they came from originally .. ![]() But I thought I had replaced them anyhow when starting to look at the patch.
Well, eagle-eyed Bruce always betas me here ... ![]() Created an attachment (id=620)
Proposed test case Here's the TestCase I have been using. I guess we should be adding this to the
test suite, correct ? Committed patch as above. I'd appreciate if somebody could briefly review the
added functionality abd close this bug. Created an attachment (id=622)
Patch for updating the documentation on OQL Above patch updates the documentation for OQL by adding a limit and an offset
clause to the grammar spec. At the sime time, I added a 'News' section at the top, and an 'Examples' section Created an attachment (id=646)
Proposed CTF test
The syntax currently used for HSQL queries with LIMIT/OFFSET isn't correct.
For queries with LIMIT without OFFSET should be for example: select top 5 * – 5=LIMIT The syntax for HSQL queries with LIMIT and OFFSET should be: select limit 0 3 * – 0=OFFSET 3=LIMIT The implementation will require reordering of bind parameters. It's even more Created an attachment (id=701)
fix for HSQL LIMIT implementation including TC62 for HSQL Martin, can you please have a close look at the patch provided by you. There's a
couple of problems with it: a) Some files appear to be patched twice ? Any idea what's wrong ? You are right. Somehow I put the HsqlQueryExpression changes twice in the
patch. However they seem to be identically. Please first commit the cleanup patch of entry 1753. I already tested it Will commit bug 1753 tonight, if that's alright for you. And a new patch would
really make sense ... as I really, really do not have the time to play around with hard-to-apply patches at the moment. Too many bugs to be worked on at the moment. Created an attachment (id=709)
activated TC62 for HSQL, reorderParamInfo() removed, some comments fixed Patch applied as provided. Please close this bug report once you've assessed its
validity. The commit is OK. However we see now the key generator exceptions also here,
when running the HSQL test case: D:\java\castor-1221>run-tests -verbose .hsql testsuite: castor Time: 3,453 OK (1 test) Couldn't it be this is because of the recent log4j configuration changes? |
users from Postgresql to mySQL to HSQLDB, and all have somewhat different LIMIT
syntax. My research of Castor's code doesn't reveal support for these different
syntaxes.
Thanks.