|
|
Incorrect SQL bind syntax in SimpleQueryExecutor |
Query page
Enter new bug
[error report of Sean Liang]
When I execute a query with the following OQL statement:
select count(t.id) from model.Test t where (status = $1) and (showDate <=
$2)
An exception occurs:
org.exolab.castor.jdo.QueryException: java.sql.SQLException: Syntax error
or access violation message from server: "You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near '2 ))' at line 1"
I didn't change anything of my code or database, so I guess it is not my
problem.
The final sql is something like this:
SELECT COUNT(test.id) FROM test WHERE (( test.status = ?1 ) and (
test.showDate <= ?2 ))
[comments]
I could reproduce this problem. It is related to the usage of COUNT() or
similar functions. It's been caused by the recent changes to implement
LIMIT/OFFSET.
I will create and attach a patch to solve this issue.
Sean - could you please apply this patch to your source tree and test, if it
solves your problems?
------- Additional Comment
#1 From
Martin Fuchs
2004-10-15 13:38 -------
Created an attachment (id=733)
proposed patch
------- Additional Comment
#2 From
Werner Guttmann
2004-10-16 09:35 -------
Martin, I just tested this against the test suite for bug 1676, where I am
writing a little performance test suite to compare execution times for
lazy-loading vs. not lazy-loaded.
But committing your patch, can you please elaborate as to why this was necessary.
------- Additional Comment
#3 From
Martin Fuchs
2004-10-16 10:32 -------
Well, I think you remember we changed QueryExpression and the derived database
specific classes, so that getStatement() now returns SQL bind expressions of the
form "?1", "?2", ... instead of only "?". This forces any function, which uses
the generated SQL statement strings with such bind variables to use
SqlBindParser in order to transform them into the JDBC compatible form "?".
Otherwise the JDBC driver will report an error because of the non-standard syntax.
We (well it was me ;-) previously did this only for the most common case, the
function SQLEngine.execute(). Sean used a "select count(...)" OQL statement,
which executes a JDBC statement directly in SimpleQueryExecutor, not using
SQLEngine.execute(). This patch adds the missing SqlBindParser postprocessing to
SimpleQueryExecutor.
I hope, there is no other code location needing such an addition. I already
searched for any other, but could not find any. Most uses of getStatement() just
don't need it because there are no bind variables to process.
------- Additional Comment
#4 From
Werner Guttmann
2004-10-17 00:13 -------
Thanks, Martin. Bruce, can you please explain the raison d'etre for the
SimpleQueryExecutor ? What does it try to solve that could not be done through
SQLEngine ?
------- Additional Comment
#5 From
Werner Guttmann
2004-10-18 12:59 -------
Patch committed as provided.
------- Additional Comment
#6 From
Martin Fuchs
2004-10-19 11:15 -------
Thanks - Closed.
Query page
Enter new bug
This is Bugzilla: the Mozilla bug system. For more
information about what Bugzilla is and what it can do, see
bugzilla.org.
|
|