| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Having problems with dynamic DSN in Stored Proc CALL... |
« View previous topic :: View next topic » |
| Author |
Message
|
| alienartefact |
 Posted: Fri Nov 02, 2012 4:17 am Post subject: Having problems with dynamic DSN in Stored Proc CALL... |
 |
|
Novice
Joined: 23 May 2012
Posts: 13
Location: East Midlands, UK
|
Hello all,
I am having problems figuring out how to use a dynamic dsn with a stored proc call. I have searched the forum (and nearly every page of the latest version of the Internet!  ) but have found similar questions without answers or solutions that deal with dynamic DSNs but not in the context of stored procedures.
For example http://www.mqseries.net/phpBB/viewtopic.php?p=230831&sid=9c080fb77a2ddcfa4043f0362dd95f06 seemed like it was going to get close to answering my question but the thread was abandoned before the issue was resolved.
My broker flow needs to reference 3 different DSNs (myDSN1, myDSN2, myDSN3).
The details are as follows...
| Code: |
DECLARE ileRef REFERENCE TO InputLocalEnvironment.Variables;
SET ileRef.mydsn2 = 'myDSN2';
SET ileRef.mydsn3 = 'myDSN3'; |
I have configured the 'Data source' property on the Compute node to reference myDSN1.
myDSN1 is used to access a stored proc...
| Code: |
| CALL PROCA(parm1, parm2); |
| Code: |
CREATE PROCEDURE PROCA(IN parm1 CHAR, IN parm2 CHAR)
LANGUAGE DATABASE EXTERNAL NAME "PROCA"; |
...this works fine.
myDSN2 is set in the ESQL and used for some PASSTHRU statements like this...
| Code: |
SET TABLEARESULTS.ROW[] = PASSTHRU
('SELECT VALUETEXT, VALUEDESC
FROM TABLEA
WHERE my_ID = ? AND my_TYPE = ?'
TO Database.{ileRef.mydsn2}
VALUES ('a', 'b'));
|
...this also works fine.
myDSN3 is also set in the ESQL and needs to be used to access another stored proc...
| Code: |
CALL PROCB(parm1, parm2)
IN Database.{ileRef.mydsn3}; |
| Code: |
CREATE PROCEDURE PROCB(IN parm1 CHAR, IN parm2 CHAR)
LANGUAGE DATABASE EXTERNAL NAME "PROCB"; |
...this doesn't work. Broker issues the following exception...
| Code: |
| BIP2920E: When attempting to obtain a procedure definition, the database reported that the procedure 'myDSN1.myDSN3.PROCB' does not exist, or cannot be accessed. |
...as you can see from the exception message, it appears I am making an error that is causing the myDSN1 to be prefixed to myDSN3.
This is running on Broker 7.0.0.2.
Can anyone provide me with some guidance as to where I am going wrong please?
Thanks,
Rob |
|
| Back to top |
|
 |
| mgk |
| Posted: Fri Nov 02, 2012 5:24 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Great post Rob, lots of info that helps me see your problem:
The syntax for the DatabaseSchemaReference for the CALL statement is:
| Code: |
|--Database--+-------------------------+--.--SchemaClause-------|
'-.--DatabaseSourceClause-'
|
This shows that if you only provide one clause then it is taken to be the schema. So to override the DSN you need to provide the schema as well.
Many thanks,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| alienartefact |
| Posted: Fri Nov 02, 2012 6:16 am Post subject: |
|
|
Novice
Joined: 23 May 2012
Posts: 13
Location: East Midlands, UK
|
Thanks very much for your quick response MGK.
Now you've highlighted my error it seems embarassingly obvious  ! I tried making the call with the correct syntax as you detailed in your post and tadaaaa - it worked!
Thanks again for setting me straight.
Have a good weekend.
Rob |
|
| Back to top |
|
|
| SVWMB |
| Posted: Mon May 11, 2015 1:05 am Post subject: |
|
|
Newbie
Joined: 10 May 2015
Posts: 1
|
| alienartefact wrote: |
Thanks very much for your quick response MGK.
Now you've highlighted my error it seems embarassingly obvious ! I tried making the call with the correct syntax as you detailed in your post and tadaaaa - it worked!
Thanks again for setting me straight.
Have a good weekend.
Rob |
Hello,
Can you please tell me how you resolved the issue.
Regards,
SV |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon May 11, 2015 3:11 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The poster does not seem to be aregular visitor here so you might be waiting a long time.
Why not start a new thread, reference this one and show us your code? (and the errors)
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| 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
|
|
|
|
