SQL context.expand自定义属性错误ORA-01756

Question

尝试在groovy中运行sql语句并将结果存储到def变量中，但遇到以下错误消息：

java.sql.SQLException: ORA-01756:引号未正确终止的字符串。

这是我的sql语句：

def Indicator = sql.firstRow("SELECT COUNT(*) FROM REQUEST WHERE REQD_IND IS NOT NULL AND CUST_FORENAMES = '" + context.expand('${#Project#Forename}')+ "'AND CUST_SURNAME = '" + context.expand('${#Project#Surname}'));

当我运行不带sql.firstRow函数的语句时，它将返回日志中的整个select语句，但能够很好地访问自定义属性，但这是我所追求的结果，即true/false

Answer 1

ORA-01756:引号未正确终止的字符串是因为SELECT中的最后一个'丢失了。

为了避免错误并保持SELECT清晰，我建议使用firstRow(String sql,List params)方法：

def indicator = sql.firstRow(
    '''SELECT COUNT(*) 
    FROM REQUEST 
    WHERE REQD_IND IS NOT NULL 
    AND CUST_FORENAMES = ?
    AND CUST_SURNAME = ?''',
    [context.expand('${#Project#Forename}'),context.expand('${#Project#Surname}')])

此外，如果只想获得行数，请为COUNT(*)使用别名，并从firstRow方法返回的结果中获取它：

def indicator = sql.firstRow(
    '''SELECT COUNT(*) as count
    FROM REQUEST 
    WHERE REQD_IND IS NOT NULL 
    AND CUST_FORENAMES = ?
    AND CUST_SURNAME = ?''',
    [context.expand('${#Project#Forename}'),context.expand('${#Project#Surname}')])

// use the alias to get simply the number
def count = indicator.count

Answer 2

你能试试吗

def forename = context.expand('${#Project#Forename}')
def surname = context.expand('${#Project#Surname}')
def indicator = sql.firstRow("SELECT COUNT(*) FROM REQUEST WHERE REQD_IND IS NOT NULL AND CUST_FORENAMES = $forename AND CUST_SURNAME = $surname");