question Nifi - ExectueSQL - how to force a parameter to be a string in Support Questions

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

cdeloeul — Sat, 17 Aug 2019 22:30:50 GMT

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 ?

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

cdeloeul — Thu, 10 Jan 2019 22:19:53 GMT

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.

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

mburgess — Thu, 10 Jan 2019 22:22:32 GMT

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.

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

cdeloeul — Thu, 10 Jan 2019 23:12:02 GMT

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 !