Bugzilla Version 2.16.7
Bugzilla Bug 1764
  Incorrect SQL bind syntax in SimpleQueryExecutor
     Query page      Enter new bug
Bug#: 1764   Platform:   Reporter: martin-fuchs at gmx dot net (Martin Fuchs)
Product:   OS:   Add CC:
Component:   Version:   CC:
Remove selected CCs
Status: CLOSED   Priority:  
Resolution: FIXED   Severity:  
Assigned To: werner dot guttmann at gmx dot net (Werner Guttmann)   Target Milestone:  
URL:
Summary:

Attachment Type Modified Status Actions
proposed patch patch 2004-10-15 13:38 none Edit
Create a New Attachment (proposed patch, testcase, etc.) View All

Bug 1764 depends on: Show dependency tree
Show dependency graph
Bug 1764 blocks:
Votes: 0    Show votes for this bug    Vote for this bug

Additional Comments:


Leave as CLOSED FIXED
Reopen bug

View Bug Activity   |   Format For Printing

Description: Opened: 2004-10-15 13:08

[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.
Actions: New | Query | bug # | Reports   New Account | Log In