History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: CASTOR-901
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Werner Guttmann
Reporter: Martin Fuchs
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
castor

Incorrect SQL bind syntax in SimpleQueryExecutor

Created: 15/Oct/04 01:08 PM   Updated: 19/Apr/05 10:52 AM
Component/s: JDO
Affects Version/s: 0.9.5.3
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. Text File castor-1764.patch (9 kb)

Environment:
Operating System: All
Platform: All

Bugzilla Id: 1764


 Description  « Hide
[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?



 All   Comments   Work Log   Change History      Sort Order: Ascending order - Click to sort in descending order
Martin Fuchs - 15/Oct/04 01:38 PM
Created an attachment (id=733)
proposed patch

Werner Guttmann - 16/Oct/04 09:35 AM
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.


Martin Fuchs - 16/Oct/04 10:32 AM
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.

Werner Guttmann - 17/Oct/04 12:13 AM
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 ?

Werner Guttmann - 18/Oct/04 12:59 PM
Patch committed as provided.

Martin Fuchs - 19/Oct/04 11:15 AM
Thanks - Closed.