Support Questions
Find answers, ask questions, and share your expertise

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

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

Super 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

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.

Super 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.

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 !

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.