骡子根据条件执行查询

Question

我的查询在这里，我需要选择什么选项才能作为骡子应用程序运行。

 <http:inbound-endpoint exchange-pattern="request-response"  host="myhost" port="${port}" path="offcycle" doc:name="HTTP"/>
        <set-variable variableName="companies" value="${companies.torun}" doc:name="Variable"/>
        <db:insert config-ref="hrlites_Configuration" doc:name="Database" >
            <db:dynamic-query>
insert into PAYRNUM (PAYGROUP,T2_PAYROLL_NUM,SEPCHK,NAME....)
        select T2_SOURCE_COMPANY,T2_PAYGROUP_TYPE,...
        from   (
        SELECT T2_SOURCE_COMPANY,T2_PAYGROUP_TYPE,PAY_END_DT,...
        FROM   CHK_STG2
        WHERE  COMPANY = #[flowVars.companies]
          AND  T2_PAYGROUP_TYPE = 'ZZ'
          AND  PAY_END_DT =  '30/JUN/2013'
        ORDER BY PAY_END_DT,
              CASE WHEN T2_INVOICE_NBR LIKE 'IN%' then 1
                   WHEN T2_INVOICE_NBR LIKE 'CM%' then 2                   
                   ELSE 3
              END
        ) a,
        (
        select t2_invoice_nbr, OFF_CYCLE, CASE When b.OFF_CYCLE ='N' THEN 0
                    ELSE rownum
                    END as T2_PAYROLL_NUM
        from   (
                select distinct t2_invoice_nbr, OFF_CYCLE
                from   (
                        SELECT T2_INVOICE_NBR,...
                        FROM   PS_T2_INV_CHK_STG2
                        WHERE  COMPANY = #[flowVars.companies]
                          AND  T2_PAYGROUP_TYPE = 'ZZ'
                          AND  PAY_END_DT =  '30/JUN/2013'
                        ORDER BY PAY_END_DT,
                            CASE WHEN T2_INVOICE_NBR LIKE 'IN%' then 1
                                  WHEN T2_INVOICE_NBR LIKE 'CM%' then 2                                 
                                  ELSE 3
                            END
                      )
               ) b
        ) bb
    where a.t2_invoice_nbr = bb.t2_invoice_nbr      
    ORDER BY PAY_END_DT,
        CASE WHEN T2_INVOICE_NBR LIKE 'IN%' then 1            
             ELSE 5
        END;
        </db:dynamic-query>
        </db:insert>        
    </flow>

1）如果运行查询，它在数据库中执行正常。但是如果我在骡子流中运行，则会出现以下错误：

2015-01-28 17:18:48 ERROR DefaultMessagingExceptionStrategy:337 - 
********************************************************************************
Message               : ORA-00911: invalid character
 (java.sql.SQLSyntaxErrorException). Message payload is of type: String
Type                  : org.mule.api.MessagingException
Code                  : MULE_ERROR--2
Payload               : /offcycle
SQL Code              : 911
JavaDoc               : http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html
SQL State             : 42000
********************************************************************************
Exception stack is:
1. ORA-00911: invalid character
(SQL Code: 911, SQL State: + 42000) (java.sql.SQLSyntaxErrorException)
  oracle.jdbc.driver.SQLStateMapping:91 (null)
2. ORA-00911: invalid character
 (java.sql.SQLSyntaxErrorException). Message payload is of type: String (org.mule.api.MessagingException)
  org.mule.module.db.internal.processor.AbstractDbMessageProcessor:81
********************************************************************************
Root Exception stack trace:
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

我选择了类型作为参数而不是动态，但没有使用。

2）如何在控制台中显示已执行的查询，以便我了解传递的参数和最终查询。

Answer 1

我看到您在查询中使用了 flowvar。您是否在查询执行之前定义了公司的 flowvar？如果不是，您的查询会像

..其中公司 =和...

这将是无效的。

可以使用 Mule 调试器并在数据库消息处理器中添加断点，以查看属性和变量的值。

如果在执行查询后要记录查询结果，则可以尝试添加对象到字符串转换器，然后添加包含 #[payload] 表达式的记录器。