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
