在Groovy中使用命名参数调用SQL存储过程 [英] Call SQL stored procedure with named parameter in Groovy

问题描述

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

我可以使用上面的代码成功调用存储过程.

I am able to call the stored procedure successful with the above code.

但是，当我使用命名参数而不是?"时占位符.我得到了:

But, when I am using named parameters instead of '?' placeholder. I am getting:

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

我更改的内容替换为?"用:OUTP1"，"OUTP2"和:INP1".并在call语句中，相应地使用命名参数.更改后的代码:

What I changed is replaced the '?' with ":OUTP1", "OUTP2" and ":INP1". And in the call statement, using the named parameters accordingly. The 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}")
}

推荐答案

您正在做的是将 map 传递给 call()，我认为我们没有一个api.此外，SP的占位符必须为?.

What you are doing is passing a map to call() which I do not think we have an api for. Moreover, the placeholders for the SP has to be ?.

您可以坚持以前的方法，也可以尝试如下使用GString:

Either you can stick to your former approach or try using GString as below:

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

我宁愿使用前一种方法.:-)

I would prefer the former approach instead. :-)

这篇关于在Groovy中使用命名参数调用SQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！