Jean, what you are experiencing is the expected behavior.
In Derby, everything after the comment delimiter which is -- is considered a
comment and not part of the sql. Optimizer override is just a special
comment with it's own syntax rule. And the syntax rule for optimizer
overrides expects propertyname=value.
When a user specifies --derby-properties index=IDX1 order by num, Derby
determines that is an optimizer override. index=IDX1 follows the syntax rule
of optimizer override but the string "order by num" and hence the user will
see a syntax error.
A Jira issue for java example in the docs would be great. If you have any
other feedback on the optimizer override doc page, please post that too.
Thanks,
Mamta
On 3/9/07, Jean T. Anderson <[EMAIL PROTECTED]> wrote:
I got a question today on how to do optimizer overrides in a java app
and thought I would post my answer here to verify what I said was
correct and see if anyone has better ideas on how to do it. The docs [1]
have good examples that can be run from ij, but it wasn't clear to the
user how to execute them in a java app. The main sticking poing was the
optimizer override must be in a comment at the end of the line.
I modified the SimpleApp.java that comes with the Derby distribution to
add two indexes:
s.execute("create index IDX1 on derbyDB(num)");
s.execute("create index IDX2 on derbyDB(addr)");
I showed two queries that work (notice how there must be a carriage
return after the comment in the second query):
ResultSet rs = s.executeQuery(
"SELECT num, addr FROM derbyDB --derby-properties index=IDX1");
ResultSet rs = s.executeQuery(
"SELECT num, addr FROM derbyDB --derby-properties index=IDX1 \r order
by num");
The "\r" worked, but is there a better way to do this?
I also showed two queries that get syntax errors.
This query gets an error because there isn't a carriage return after the
--derby-properties clause and before the "order by" clause:
ResultSet rs = s.executeQuery(
"SELECT num, addr FROM derbyDB --derby-properties index=IDX1 order
by num");
This query gets a syntax error because the --derby-properties clause
must come after the table name:
ResultSet rs = s.executeQuery(
"SELECT num, addr FROM derbyDB order by num --derby-properties
index=IDX1");
Any suggestions/corrections? I'll open a Jira issue with a java example
to add to the existing docs.
thanks,
-jean
[1] http://db.apache.org/derby/docs/dev/tuning/ctunoptimzoverride.html
