Return-Path: X-Original-To: apmail-camel-issues-archive@minotaur.apache.org Delivered-To: apmail-camel-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 595D6100DC for ; Fri, 21 Jun 2013 07:01:35 +0000 (UTC) Received: (qmail 54680 invoked by uid 500); 21 Jun 2013 07:01:34 -0000 Delivered-To: apmail-camel-issues-archive@camel.apache.org Received: (qmail 54591 invoked by uid 500); 21 Jun 2013 07:01:33 -0000 Mailing-List: contact issues-help@camel.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@camel.apache.org Delivered-To: mailing list issues@camel.apache.org Received: (qmail 53160 invoked by uid 99); 21 Jun 2013 07:01:25 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 21 Jun 2013 07:01:25 +0000 Date: Fri, 21 Jun 2013 07:01:25 +0000 (UTC) From: "Sergey Galkin (JIRA)" To: issues@camel.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (CAMEL-6473) NULL values are not supported in named parameters MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/CAMEL-6473?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Sergey Galkin updated CAMEL-6473: --------------------------------- Description: Due to the bug in the DefaultSqlPrepareStatementStrategy there no ability to use NULL values. Following query will be failed, if we try to use NULL as a parameter value: bq. select a, b from foo where (:#param1 IS NOT NULL AND a > 12 ) OR (:#param2 IS NOT NULL AND b > 12) We'll get an error: {quote} Caused by: java.sql.SQLException: Number of parameters mismatch. Expected: 2, was:1 at org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy.populateStatement(DefaultSqlPrepareStatementStrategy.java:132) ~[camel-sql-2.11.0.jar:2.11.0] at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:74) ~[camel-sql-2.11.0.jar:2.11.0] at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:57) ~[camel-sql-2.11.0.jar:2.11.0] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ~[spring-jdbc-3.1.4.RELEASE.jar:3.1.4.RELEASE] {quote} Fix is quite simple: iterator implementation (returned by DefaultSqlPrepareStatementStrategy.createPopulateIterator()) should be changed as follows: {code:java} return new Iterator() { private NamedQueryParser parser = new NamedQueryParser(query); private String nextParam; private boolean done; @Override public boolean hasNext() { if (done) { return false; } if (nextParam == null) { nextParam = parser.next(); if (nextParam == null) { done = true; } } return nextParam != null; } @Override public Object next() { if(!hasNext()){ throw new NoSuchElementException(); } boolean contains = bodyMap != null ? bodyMap.containsKey(nextParam) : false; contains |= headerMap != null ? headerMap.containsKey(nextParam) : false; if (!contains) { throw new RuntimeExchangeException("Cannot find key [" + nextParam + "] in message body or headers to use when setting named parameter in query [" + query + "]", exchange); } // get from body before header Object next = bodyMap != null ? bodyMap.get(nextParam) : null; if (next == null) { next = headerMap != null ? headerMap.get(nextParam) : null; } nextParam = null; return next; } @Override public void remove() { // noop } }; {code} was: Due to the bug in the DefaultSqlPrepareStatementStrategy there no ability to use NULL values. Following query will be failed, if we try to use NULL as a parameter value: bq. select a, b from foo where (:#param1 IS NOT NULL AND a > 12 ) OR (:#param2 IS NOT NULL AND b > 12) We'll get an error: {quote} Caused by: java.sql.SQLException: Number of parameters mismatch. Expected: 4, was:2 at org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy.populateStatement(DefaultSqlPrepareStatementStrategy.java:132) ~[camel-sql-2.11.0.jar:2.11.0] at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:74) ~[camel-sql-2.11.0.jar:2.11.0] at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:57) ~[camel-sql-2.11.0.jar:2.11.0] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ~[spring-jdbc-3.1.4.RELEASE.jar:3.1.4.RELEASE] {quote} Fix is quite simple: iterator implementation (returned by DefaultSqlPrepareStatementStrategy.createPopulateIterator()) should be changed as follows: {code:java} return new Iterator() { private NamedQueryParser parser = new NamedQueryParser(query); private String nextParam; private boolean done; @Override public boolean hasNext() { if (done) { return false; } if (nextParam == null) { nextParam = parser.next(); if (nextParam == null) { done = true; } } return nextParam != null; } @Override public Object next() { if(!hasNext()){ throw new NoSuchElementException(); } boolean contains = bodyMap != null ? bodyMap.containsKey(nextParam) : false; contains |= headerMap != null ? headerMap.containsKey(nextParam) : false; if (!contains) { throw new RuntimeExchangeException("Cannot find key [" + nextParam + "] in message body or headers to use when setting named parameter in query [" + query + "]", exchange); } // get from body before header Object next = bodyMap != null ? bodyMap.get(nextParam) : null; if (next == null) { next = headerMap != null ? headerMap.get(nextParam) : null; } nextParam = null; return next; } @Override public void remove() { // noop } }; {code} > NULL values are not supported in named parameters > ------------------------------------------------- > > Key: CAMEL-6473 > URL: https://issues.apache.org/jira/browse/CAMEL-6473 > Project: Camel > Issue Type: Bug > Components: camel-sql > Affects Versions: 2.11.0 > Reporter: Sergey Galkin > > Due to the bug in the DefaultSqlPrepareStatementStrategy there no ability to use NULL values. > Following query will be failed, if we try to use NULL as a parameter value: > bq. select a, b from foo where (:#param1 IS NOT NULL AND a > 12 ) OR (:#param2 IS NOT NULL AND b > 12) > We'll get an error: > {quote} > Caused by: java.sql.SQLException: Number of parameters mismatch. Expected: 2, was:1 > at org.apache.camel.component.sql.DefaultSqlPrepareStatementStrategy.populateStatement(DefaultSqlPrepareStatementStrategy.java:132) ~[camel-sql-2.11.0.jar:2.11.0] > at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:74) ~[camel-sql-2.11.0.jar:2.11.0] > at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:57) ~[camel-sql-2.11.0.jar:2.11.0] > at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ~[spring-jdbc-3.1.4.RELEASE.jar:3.1.4.RELEASE] > {quote} > Fix is quite simple: iterator implementation (returned by DefaultSqlPrepareStatementStrategy.createPopulateIterator()) should be changed as follows: > {code:java} > return new Iterator() { > private NamedQueryParser parser = new NamedQueryParser(query); > private String nextParam; > private boolean done; > @Override > public boolean hasNext() { > if (done) { > return false; > } > > if (nextParam == null) { > nextParam = parser.next(); > if (nextParam == null) { > done = true; > } > } > return nextParam != null; > } > @Override > public Object next() { > if(!hasNext()){ > throw new NoSuchElementException(); > } > > boolean contains = bodyMap != null ? bodyMap.containsKey(nextParam) : false; > contains |= headerMap != null ? headerMap.containsKey(nextParam) : false; > if (!contains) { > throw new RuntimeExchangeException("Cannot find key [" + nextParam + "] in message body or headers to use when setting named parameter in query [" + query + "]", exchange); > } > > // get from body before header > Object next = bodyMap != null ? bodyMap.get(nextParam) : null; > if (next == null) { > next = headerMap != null ? headerMap.get(nextParam) : null; > } > nextParam = null; > return next; > } > @Override > public void remove() { > // noop > } > }; > {code} -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira