MuleSoft 4.4调用PostgreSQL存储过程配置错误修正求助

问题场景 #

配置MuleSoft调用PostgreSQL存储过程时，收到如下错误：

Message               : ERROR: sp_employee_generate(character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character varying) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15

原配置代码：

<db:stored-procedure doc:name="Stored procedure" doc:id="ace0cc98-d170-4d0c-b7b4-905162b03797" config-ref="Database_Config1">
    <db:sql ><![CDATA[{call sp_employee_generate(:pn_name,:pn_age,:pn_add1,:pn_add2,:pn_add3,:pn_city,:pn_state,:pn_pin,:pn_Mobile_no,:pn_Email_id,:pn_Employee_no)} ]]></db:sql>
    <db:input-parameters ><![CDATA[#[{
    "pn_name":vars.requestParam.p_name,
"pn_age":vars.requestParam.p_age,
"pn_add1":vars.requestParam.p_add1,
"pn_add2":vars.requestParam.p_add2,
"pn_add3":vars.requestParam.p_add3,
"pn_city":vars.requestParam.p_city,
"pn_state":vars.requestParam.p_state,
"pn_pin":vars.requestParam.p_pin,
"pn_Mobile_no":vars.requestParam.p_Mobile_no,
"pn_Email_id":vars.requestParam.p_Email_id}]]]></db:input-parameters>
    <db:output-parameters >
        <db:output-parameter key="pn_Employee_no" type="VARCHAR" />
    </db:output-parameters>
</db:stored-procedure>

修正方案 #

PostgreSQL对存储过程（PROCEDURE）要求使用原生CALL语法，不能用JDBC标准的{call ...}包裹格式。修改<db:sql>标签内的SQL语句即可解决：

修正后的配置：

<db:stored-procedure doc:name="Stored procedure" doc:id="ace0cc98-d170-4d0c-b7b4-905162b03797" config-ref="Database_Config1">
    <db:sql ><![CDATA[CALL sp_employee_generate(:pn_name,:pn_age,:pn_add1,:pn_add2,:pn_add3,:pn_city,:pn_state,:pn_pin,:pn_Mobile_no,:pn_Email_id,:pn_Employee_no)]]></db:sql>
    <db:input-parameters ><![CDATA[#[{
    "pn_name":vars.requestParam.p_name,
"pn_age":vars.requestParam.p_age,
"pn_add1":vars.requestParam.p_add1,
"pn_add2":vars.requestParam.p_add2,
"pn_add3":vars.requestParam.p_add3,
"pn_city":vars.requestParam.p_city,
"pn_state":vars.requestParam.p_state,
"pn_pin":vars.requestParam.p_pin,
"pn_Mobile_no":vars.requestParam.p_Mobile_no,
"pn_Email_id":vars.requestParam.p_Email_id}]]]></db:input-parameters>
    <db:output-parameters >
        <db:output-parameter key="pn_Employee_no" type="VARCHAR" />
    </db:output-parameters>
</db:stored-procedure>

关键修改点 #

• 移除SQL语句外层的{}包裹，直接使用CALL sp_employee_generate(...)
• 保持输入参数和输出参数的映射不变，PostgreSQL存储过程的OUT参数仍需在调用语句中作为占位符声明

内容的提问来源于stack exchange，提问作者ananda