| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Calling External Stored Procedure |
« View previous topic :: View next topic » |
| Author |
Message
|
| Yanghui |
 Posted: Wed Sep 10, 2003 2:19 am Post subject: Calling External Stored Procedure |
 |
|
Disciple
Joined: 08 May 2002
Posts: 151
Location: Dublin, Ireland
|
Hi, there,
I am experiencing a problem of calling External Stored Procedure right now. The same msgflow works fine on my NT broker. It only happens on my Solaris broker. Does anybody come cross this problem or has any idea about what's goin on behind scene? I couldn't find information about this error in manual. It's highly appreciated if you share your thoughts here. Looking forward to your response.
ESQL:
SET p_iban = ...
SET p_valid = 'N';
CALL valid_iban(p_iban, p_valid);
CREATE PROCEDURE valid_iban(IN p_iban CHAR, INOUT p_valid CHAR)
EXTERNAL NAME "mr$utilities.p$valid_iban";
Error Msg:
<RecoverableException>
<File>/build/S210_P/src/DataFlowEngine/ImbComputeNode.cpp</File>
<Line>453</Line>
<Function>ImbComputeNode::evaluate</Function>
<Type>ComIbmComputeNode</Type>
<Name>ce5ee5d2-f600-0000-0080-868d5246bb02.cd22e2d2-f600-0000-0080-868d5246bb02.667f3964-f500-0000-0080-868d5246bb02</Name>
<Label>MR2_MF.MsgBuildMainFlow1.Build MT1031.Mapping to NN</Label>
<Text>Caught exception and rethrowing</Text>
<Catalog>WMQIv210</Catalog>
<Severity>3</Severity>
<Number>2230</Number>
<RecoverableException>
<File>/build/S210_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp</File>
<Line>479</Line>
<Function>SqlRoutine::resolveExternalParameters</Function>
<Type>ComIbmComputeNode</Type>
<Name>ce5ee5d2-f600-0000-0080-868d5246bb02.cd22e2d2-f600-0000-0080-868d5246bb02.667f3964-f500-0000-0080-868d5246bb02</Name>
<Label>MR2_MF.MsgBuildMainFlow1.Build MT1031.Mapping to NN</Label>
<Text>The procedure is unknown to the database and no definition could be found.</Text>
<Catalog>WMQIv210</Catalog>
<Severity>3</Severity>
<Number>2920</Number>
<Insert>
<Type>5</Type>
<Text>mr$utilities.p$valid_iban</Text>
</Insert>
</RecoverableException>
</RecoverableException> |
|
| Back to top |
|
 |
| Craig B |
| Posted: Wed Sep 10, 2003 4:18 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
What database are you connecting to and what CSD level is yor WMQI V2.1 running at? The error you are getting is indicating that it could not find the stored procedure name with that parameter configuration in your database. Some things to check are :
1) Is p$valid_iban in a schema called mr$utilities on your Solaris machine?
2) Is p$valid defined with parameters that match what you have defined on CREATE procedure. Ie (IN p_iban CHAR, INOUT p_valid CHAR)
3) Are you using Oracle? If so, does your stored procedure belong to a package where you have to use mr$utilities.packageName.p$valid_iban
4) On the EXTERNAL parameter you have specified EXTERNAL NAME "mr$utilities.p$valid_iban"; Shouldn't single quotes be used for this CHARACTER parameter. Double quotes are used to shield tokens from the Syntax Checker.
Hope these help. If they dont could you provide further information.
_________________
Regards
Craig |
|
| Back to top |
|
|
| Yanghui |
| Posted: Wed Sep 10, 2003 5:38 am Post subject: |
|
|
Disciple
Joined: 08 May 2002
Posts: 151
Location: Dublin, Ireland
|
Hi, Craig,
Many thanks again.
I just got the problem solved. The comfusing part was that it has been working fine on a broker on NT. So, I didn't suspect ESQL syntext at all.
The ExternalRoutineName on NT is:
"mr$utilities.p$valid_iban"
in which
mr$utilities is the package name and
p$valid_iban is the procedure name.
The ExternalRoutineName on Solaris needs to be:
"mr_plsql_owner2.mr$utilities.P$valid_iban"
in which
mr_plsql_owner2 is the schema name.
Frankly, I don't understand why there is such difference. I also don't like the fact that I have to specify schema name because it can be different in different enviornments for Development, System test, UAT or Production, which means I have to go to modify ESQL after msgflow is promoted into a new environment.
Is there any work around this issue?
BTW, I am using Oracle and WMQI2.1+CSD2 on NT and WMQI2.1+CSD4 on Solaris. I know CSD difference can cause the problem but I can't believe the higher CSD version is more strict.
Looking forward to your response. Thanks again.
Regards
-Yanghui |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
