Nifi - ExectueSQL - how to force a parameter to be a string

[#1]

Hi,

I've created a parameterized SQL query in the ExecuteSQL processor, this processor is predecessed by an UpdateAttribute with those parameters:

here is the part of the where clause that uses this parameter :

AND (( ?sql.args.4.value IS NULL AND mm.docno IS NULL ) OR ( mm.docno = ?sql.args.4.value))

The issue is that I keep having this error message :

"java.sql.SQLDataException: Value of the sql.args.4.type attribute is 'CHAR', which is not a valid JDBC numeral type"

Any clues ?

[#2]

based on : Processor's code, line 714+

Am I'm right to suppose that the way this function works, will prevent any non numeric parameter to work ?

This seems to be an error in the logic itself.

[#3 · Accepted solution]

The sql.args.N.type parameters are actually numeric codes indicating the type, not the string name of the type. So instead of setting sql.args.4.type to "CHAR", set it to "1" instead. The mapping of numbers to types is given here.

[#4]

Thanks Matt for pointing this out.

Seems I had read the documentation too quickly :

From executeSQL usage :

sql.args.N.type

Incoming FlowFiles are expected to be parametrized SQL statements. The type of each Parameter is specified as an integer that represents the JDBC Type of the parameter.

sorry !