| Author |
Message
|
| mattynorm |
 Posted: Thu Mar 18, 2004 3:18 am Post subject: use of variables in calling stored procedures |
 |
|
Acolyte
Joined: 06 Jun 2003
Posts: 52
|
I have a node that is accessing a stored procedure with the following defintion:
CREATE PROCEDURE wGFLookup (
IN AlternativePolID CHARACTER,
IN GFPolicyNumber CHARACTER,
OUT ODSPolicyNumber CHARACTER,
OUT ReturnCode integer
) EXTERNAL NAME "D23GEB31.GREENFLAG_LOOKUP" ;
The problem that I have is the the D23GEB31 is region specific, and when promoting code I have to manually change this reference. Is there any way I can copy this value into a variable so that I can set the variable depending on region, and then reference it in the definition. I've treid creating a variable and copying D23GEB31.GREENFLAG_LOOKUP to it, but it fails saying it can't find the proc.
Any ideas?? |
|
| Back to top |
|
 |
| mgk |
| Posted: Thu Mar 18, 2004 3:54 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Which DB (and DB version) and which version and CSD of the broker are you using?
Cheers,
_________________
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 |
|
|
| mattynorm |
| Posted: Fri Mar 19, 2004 1:46 am Post subject: |
|
|
Acolyte
Joined: 06 Jun 2003
Posts: 52
|
I'm using DB2 7.2 (fix pack  , and WMQI version 2.1 (fix pack 4, although it appears my own PC is on fix pack 5, could this cause a problem?)
My only solution at the moment is to get the Environment Variables, and using the DB2 Instance name construct an IF statement with the whole create procedure code in it, and a different hardcoded external name, but I was looking for something a bit neater if possible |
|
| Back to top |
|
|
| akmar2k |
| Posted: Fri Nov 17, 2006 8:16 am Post subject: |
|
|
Novice
Joined: 01 Jun 2006
Posts: 22
|
Has any one found a resolution for this issue? I am also in a similar situation and I am currently hardcoding the values.
Any suggestions, anyone? |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Fri Nov 17, 2006 8:47 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi akmar2k,
Not sure if it works, but maybe you could try this
| Code: |
| CALL yourProcess(p1, p2) IN Database.{schema}; |
The schema can be a variable that is promoted to flow level or fetched from Db.
It seems the schema specified this way, would take precedence over what is specified in the EXTERNAL NAME...
Regards. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Nov 17, 2006 9:01 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You can only reference schemas dynamically using {} in WMB V6.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| akmar2k |
| Posted: Fri Nov 17, 2006 2:18 pm Post subject: |
|
|
Novice
Joined: 01 Jun 2006
Posts: 22
|
Thanks for the responses.
But problem that I face is that I am calling an Oracle procedure from the compute node and the code snippet for that procedure is:
CREATE PROCEDURE ProcedureName(
IN in1 DECIMAL,
IN in2 CHARACTER,
OUT out1 CHARACTER,
OUT out2 CHARACTER
) EXTERNAL NAME "SchemaName.PackageName.StoredProcName";
In this particular case, is there any way of replacing the schema name with the value that I specify in the UDP at flow level?
Are there any additional documentations on this that I can refer to? The Message broker help in IBM sites were not very clear on this.
Thanks!! |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Nov 17, 2006 4:12 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If you aren't using v6, I can guarantee that it's not possible.
If you are using v6, all I can say is that it might be possible.
But you probably need to open a PMR to find out.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Fri Nov 17, 2006 4:16 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi akmar2k,
| akmar2k wrote: |
| In this particular case, is there any way of replacing the schema name with the value that I specify in the UDP at flow level? |
Yes, specify a UDP, pick its value in the Compute node and use it as specified above...i'm only doubtful about the schema name getting replaced...ofcourse the UDP bit is simple...
Please experiment with it and let us know too.
Regards. |
|
| Back to top |
|
|
| akmar2k |
| Posted: Sat Nov 18, 2006 8:25 am Post subject: |
|
|
Novice
Joined: 01 Jun 2006
Posts: 22
|
Thanks jefflowrey and elvis_gn!
I am using MB 6.0. I will try this out and let you guys know about the results.
Regards! |
|
| Back to top |
|
|
| akmar2k |
| Posted: Wed Nov 22, 2006 7:18 am Post subject: Resolved |
|
|
Novice
Joined: 01 Jun 2006
Posts: 22
|
Hi Folks!
This issue got resolved.
1) First, I defined a UDP, say DBSchemaName.
2) Second, While defining the Procedure, I specify the schema name as %:
CREATE PROCEDURE ProcedureName(
IN IN_1 CHARACTER,
OUT OUT_1 CHARACTER,
OUT OUT_2 CHARACTER
) EXTERNAL NAME "%.PKGNAME.SPNAME";
3) Third, while calling the procedure, I use
CALL ProcedureName
(IN_1,OUT_1,OUT_2)EXTERNAL SCHEMA DBSchemaName;
This resolves the issue. I could completely remove all my hardcoding from my ESQL 
I just need to update the DB Name and Schema name in the bar file for deployment!
Thanks for all the help! |
|
| Back to top |
|
|
|
|
