java - 在 Mule CE 3.5 (Anypoint) 中执行 MSSQL 存储过程

Question

当我在 Mule CE 3.5(anypoint IDE)中执行 MSSQL 存储过程时收到错误消息。

******************************************************************************** 
Message : Query type must me '[STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException). Message payload is of type: String 
Code : MULE_ERROR--2 
-------------------------------------------------------------------------------- 
Exception stack is: 
1. Query type must me '[STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException) 
org.mule.module.db.internal.processor.AbstractDbMessageProcessor:164 (null) 
2. Query type must me '[STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.IllegalArgumentException). Message payload is of type: String (org.mule.api.MessagingException) 
org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor:32 (http://www.mulesoft.org/docs/site/cur...) 

这是我的代码。

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:spring="http://www.springframework.org/schema/beans" version="CE-3.5.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd">

    <db:generic-config name="db2_Database_Configuration" url="jdbc:sqlserver://localhost:1700;databaseName=db2;user=user;password=password;" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
    <flow name="DB_MSSQLFlow2" doc:name="DB_MSSQLFlow2">
        <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" path="DB_MSSQL/sp" doc:name="HTTP"/>
        <db:stored-procedure config-ref="db2_Database_Configuration" doc:name="Database">
            <db:parameterized-query><![CDATA[EXEC GetAllJobs;]]></db:parameterized-query>
        </db:stored-procedure>
    </flow>
</mule>

但是，存储过程“GetAllJobs”可以在 SQL Server Management Studio 上执行，没有问题。

请帮忙...存储过程有什么问题，而普通的 SQL select 可以很好地使用参数？

谢谢

卡林

Answer 1

试试这个 <db:parameterized-query>{ call GetAllJobs }</db:parameterized-query>在你的流程中..所以你的流程将如下:-

    <db:generic-config name="db2_Database_Configuration" url="jdbc:sqlserver://localhost:1700;databaseName=db2;user=user;password=password;" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" doc:name="Generic Database Configuration"/>
   <flow name="DB_MSSQLFlow2" doc:name="DB_MSSQLFlow2">
    <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8081" path="DB_MSSQL/sp" doc:name="HTTP"/>
     <db:stored-procedure config-ref="db2_Database_Configuration" doc:name="Database">
       <db:parameterized-query>{ call GetAllJobs }</db:parameterized-query>
     </db:stored-procedure>
  </flow>