Call SQL stored procedure with named parameter in Groovy

Question

private static String XXX = "{call SP_XXX(?,?,?)}"
sql.call (XXX, [Sql.NUMERIC, Sql.NUMERIC,'SOME STRING'){
    outPara1, outPara2 ->
    log.info("${outPara1}, ${outPara2}")
}

I can successfully execute the stored procedure with the above code.

But when do I use named parameters instead of '?' aggregate. I get:

WARNING: Failed to execute: {call SP_XXX(:OUTP1, :OUTP2, :INP1)} 
because: Invalid column type

What have I changed, replaced '?' with ": OUTP1", "OUTP2" and ": INP1". And in the call expression, using named parameters, respectively. Code after change:

private static String XXX = "{call SP_XXX(:OUTP1, :OUTP2, :INP1)}"
sql.call (XXX, [OUTP1: Sql.NUMERIC, OUTP2: Sql.NUMERIC, INP1: 'SOME STRING']){
    outPara1, outPara2 ->
    log.info("${outPara1}, ${outPara2}")
}

Answer 1

What you do is pass mapin call(), which I don’t think we have api. Moreover, placeholders for SP should be ?.

Either you can stick to your previous approach, or use GString, as shown below:

def inp1 = 'SOME STRING'
sql.call "{call SP_XXX(${Sql.NUMERIC}, ${Sql.NUMERIC}, $inp1)}", {
    outPara1, outPara2 ->
    log.info("${outPara1}, ${outPara2}")
}

.: -)