[CAMEL-6473] NULL values are not supported in named parameters

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.11.0
• Fix Version/s: 2.11.1, 2.12.0
• Component/s: camel-sql
• Estimated Complexity: Unknown
• Assignee: Unassigned
• Reporter: Sergey Galkin
• Created: 21/Jun/13 07:00
• Updated: 10/Nov/14 21:26
• Resolved: 23/Jun/13 07:39

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:

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:

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]

Fix is quite simple: iterator implementation (returned by DefaultSqlPrepareStatementStrategy.createPopulateIterator()) should be changed as follows:

                return new Iterator<Object>() {
                    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
                    }
                };