Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor

Hi,

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

97576-capture-param-sql.png

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 ?

1 ACCEPTED SOLUTION

avatar
Master Guru

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.

View solution in original post

3 REPLIES 3

avatar
New Contributor

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.

avatar
Master Guru

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.

avatar
New Contributor

Thanks Matt for pointing this out.

Seems I had read the documentation too quickly :

From executeSQL usage :

sql.args.N.typeIncoming 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 !