Skip to main content
cdeloeul
cdeloeul
Silver ⭐️⭐️
January 10, 2019
Solved

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

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 ?

    mburgess
    mburgessAnswer
    Gold ⭐️⭐️⭐️
    January 10, 2019

    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 in thread

    3 replies

    cdeloeul
    cdeloeulAuthor
    Silver ⭐️⭐️
    January 10, 2019

    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.

    Translate
    mburgess
    Gold ⭐️⭐️⭐️
    mburgessAnswer
    Gold ⭐️⭐️⭐️
    January 10, 2019

    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.

    Translate
      cdeloeul
      cdeloeulAuthor
      Silver ⭐️⭐️
      January 10, 2019

      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 !

      Translate
      Terms & ConditionsAccessibility statement