You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Martin Serrano <ma...@attivio.com> on 2013/06/06 17:33:46 UTC
issue with using optimizer hints in jdbc
Hi,
I am using version 10.9.1.0 with the embedded driver, spring, and using the BasicDataSource. I am trying to issue this query:
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DELETE FROM metric_data md --derby-properties index=metric_data_ts
WHERE metric_instance_id IN (SELECT metric_instance.metric_instance_id FROM projects, metrics, metric_instance WHERE projects.project_id = metrics.project_id AND metrics.metric_id = metric_instance.metric_id AND projects.online = true) AND md.timestamp <= 1369884755355]; nested exception is java.sql.SQLSyntaxErrorException: Syntax error: DERBY-PROPERTIES.
Caused by: ERROR 42X01: Syntax error: DERBY-PROPERTIES.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.checkInternalFeature(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.propertyList(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.deleteBody(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.preparableDeleteStatement(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.preparableSQLDataStatement(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.StatementPart(Unknown Source)
at org.apache.derby.impl.sql.compile.SQLParser.Statement(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
The line of code where I issue it is like this:
jdbc.update("DELETE FROM metric_data md --derby-properties index=metric_data_ts \r WHERE metric_instance_id IN (SELECT metric_instance.metric_instance_id FROM projects, metrics, metric_instance WHERE projects.project_id = metrics.project_id AND metrics.metric_id = metric_instance.metric_id AND projects.online = true) AND md.timestamp <= 19292929292");
What do I need to do to use optimizer hints in this setup?
Thanks,
-Martin
Re: issue with using optimizer hints in jdbc
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Martin <ma...@gmail.com> writes:
> It looks like hints are not allowed for deletes. :(
That's correct. There is a request for enabling it in JIRA:
https://issues.apache.org/jira/browse/DERBY-4136
Please feel free to give the JIRA issue a vote to make it more visible.
Thanks,
--
Knut Anders
Re: issue with using optimizer hints in jdbc
Posted by Martin <ma...@gmail.com>.
It looks like hints are not allowed for deletes. :(
--
View this message in context: http://apache-database.10148.n7.nabble.com/issue-with-using-optimizer-hints-in-jdbc-tp130950p130955.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: issue with using optimizer hints in jdbc
Posted by mike matrigali <mi...@gmail.com>.
On 6/6/2013 8:33 AM, Martin Serrano wrote:
> Hi,
>
> I am using version 10.9.1.0 with the embedded driver, spring, and using
> the BasicDataSource. I am trying to issue this query:
>
> org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad
> SQL grammar [DELETE FROM metric_data md --derby-properties
> index=metric_data_ts
>
> WHERE metric_instance_id IN (SELECT metric_instance.metric_instance_id
> FROM projects, metrics, metric_instance WHERE projects.project_id =
> metrics.project_id AND metrics.metric_id = metric_instance.metric_id AND
> projects.online = true) AND md.timestamp <= 1369884755355]; nested
> exception is java.sql.SQLSyntaxErrorException: Syntax error:
> DERBY-PROPERTIES.
>
> Caused by: ERROR 42X01: Syntax error: DERBY-PROPERTIES.
>
> at
> org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.checkInternalFeature(Unknown
> Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.propertyList(Unknown Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.deleteBody(Unknown Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.preparableDeleteStatement(Unknown
> Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.preparableSQLDataStatement(Unknown
> Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.StatementPart(Unknown Source)
>
> at
> org.apache.derby.impl.sql.compile.SQLParser.Statement(Unknown Source)
>
> at
> org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
>
> at
> org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
>
> at
> org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
>
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
>
> The line of code where I issue it is like this:
>
> jdbc.update("DELETE FROM metric_data md --derby-properties
> index=metric_data_ts \r WHERE metric_instance_id IN (SELECT
> metric_instance.metric_instance_id FROM projects, metrics,
> metric_instance WHERE projects.project_id = metrics.project_id AND
> metrics.metric_id = metric_instance.metric_id AND projects.online =
> true) AND md.timestamp <= 19292929292");
From tests that derby executes here is one example for a delete. All
our tests seem to use \n rather \r, so maybe try that:
lang/TriggerTest.java: s.executeUpdate("CREATE TRIGGER
APP.MYTRIG AFTER DELETE ON APP.TAB REFERENCING OLD_TABLE AS OLDROWS FOR
EACH STATEMENT INSERT INTO APP.LOG(i,name,deltime) SELECT OLDROWS.I,
NAMES.NAME, CURRENT_TIMESTAMP FROM --DERBY-PROPERTIES joinOrder=FIXED\n
NAMES, OLDROWS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP\n WHERE
(OLDROWS.i = NAMES.ID) AND (1 = 1)");
>
> What do I need to do to use optimizer hints in this setup?
>
> Thanks,
>
> -Martin
>