| Author |
Message
|
| Raj2000 |
 Posted: Fri Sep 11, 2009 10:39 am Post subject: Calling stored procedure |
 |
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
My issue is i need to call a timestamp value which is calculated in other compute node
How can we call a function or procedure which is created in a seperate compute node ?
Sorry I am a newbie!!!
Actually i even tried to pass the timestamp value thru environment variable but when i copy the value to other normal variable it shows as
v(CHARACTER)= TIMESTAMP 'yyyyMMdd-HHmmss';
and i get error as invalid timestamp.
ANy suggestions?? |
|
| Back to top |
|
 |
| anuragdas |
| Posted: Sun Sep 13, 2009 1:43 am Post subject: Re: Calling stored procedure |
|
|
Newbie
Joined: 22 Jul 2009
Posts: 9
|
| Raj2000 wrote: |
My issue is i need to call a timestamp value which is calculated in other compute node
How can we call a function or procedure which is created in a seperate compute node ?
Sorry I am a newbie!!!
Actually i even tried to pass the timestamp value thru environment variable but when i copy the value to other normal variable it shows as
v(CHARACTER)= TIMESTAMP 'yyyyMMdd-HHmmss';
and i get error as invalid timestamp.
ANy suggestions?? |
Hi Raj,
Could you be a little more descriptive what you want to acheive? |
|
| Back to top |
|
|
| Vitor |
| Posted: Sun Sep 13, 2009 9:36 am Post subject: Re: Calling stored procedure |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Raj2000 wrote: |
| How can we call a function or procedure which is created in a seperate compute node ? |
You can't. WMB has no concept of global functions.
| Raj2000 wrote: |
when i copy the value to other normal variable it shows as
v(CHARACTER)= TIMESTAMP 'yyyyMMdd-HHmmss';
and i get error as invalid timestamp.
ANy suggestions?? |
Just the usual - take a user trace, see what's actually going on then fix it.
Just taking a wild guess I'd say you're not storing the environment variable where you think you are & it's not being parsed correctly.
Of course, your code doesnt make much sense either from the one line you've posted......
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Sep 13, 2009 10:43 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
to follow on from the suggestions by Vitor.
1) Put a trace node between the two compute nodes and output ${Environment} to user trace.
2) Make sure the properties on the Compute node allow the passing of the Environment folder.
_________________
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 |
|
|
| mqjeff |
| Posted: Sun Sep 13, 2009 3:21 pm Post subject: Re: Calling stored procedure |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| Raj2000 wrote: |
| How can we call a function or procedure which is created in a seperate compute node ? |
You can't. WMB has no concept of global functions. |
While that may be strictly accurate, for some meaning of "global function", it's entirely untrue in general.
You can not call any procedure that is declared in MODULE scope from outside of the module that contains it.
But you can easily call an ESQL function or procedure that is defined at Schema Scope from ANY other ESQL function or procedure, presuming you have either specified the full name including schema name or used a PATH statement.
And this is regardless of what ESQL *file* the function or procedure is declared in, or called from. They can be the same, they can be different. They can be in entirely different Message Flow Projects, provided that Project References are configured properly. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Sun Sep 13, 2009 5:40 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| Quote: |
| Could you be a little more descriptive what you want to acheive? |
i want to use a time stamp value in compute node 1 which is manipulated at say compute node 2 .So i was trying to call the procedure which manipulates the timestamp in compute node 2.
| Quote: |
| I'd say you're not storing the environment variable where you think you are & it's not being parsed correctly. |
yes you were correct actually when i copied the timestamp in compute node 2 to environment variable the format was CHARACTER format so that is the reason it looks like the one i mentioned and iu was tring to parse it directly but this time i explicitly casted to timestamp format back again and this time it worked!!!
Now @Jeff:
| Quote: |
| you can easily call an ESQL function or procedure that is defined at Schema Scope from ANY other ESQL function or procedure, presuming you have either specified the full name including schema name or used a PATH statement. |
can you give me an example or any reference where i can find it easy for me to understand becoz what i think is may be i need to declare the created procedure with some special extension(like as we type cast the variables as external or shared) so that it can be called from other functions.
lastly i could solve the above issue by using the environmet variables  but i want to use the calling and called function strategy. So any suggestions  ??? |
|
| Back to top |
|
|
| Luke |
| Posted: Sun Sep 13, 2009 11:59 pm Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
| Raj2000 wrote: |
can you give me an example or any reference where i can find it easy for me to understand becoz what i think is may be i need to declare the created procedure with some special extension(like as we type cast the variables as external or shared) so that it can be called from other functions.
|
There's nothing special you need to declare, as mqjeff said - make sure your function/proc is defined at schema scope, not Module scope. If it's in another project, make sure you have your project reference set. If it's in another schema, use PATH statement.
A typical approach would be to use one or several projects to hold common/generic subflows and ESQL functions/procs. Other projects would then have a reference to the common project and can use the required function or procedure.
I think what actually happens is that the function/procedure code gets pulled in to the flow when the BAR file is built. So what Vitor indicated is true in a sense, as you won't actually invoke a function that is running somewhere else - the flow will have its own copy.
Hope that helps ... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Sep 14, 2009 2:56 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Luke wrote: |
| I think what actually happens is that the function/procedure code gets pulled in to the flow when the BAR file is built. |
I believe it's the entire Schema, at least whatever parts of the schema are available (i.e. on project references or in esql files in the project).
Perhaps mgk will confirm or deny this, it's hard to say. A search on his old posts may provide a previous comment. |
|
| Back to top |
|
|
| Luke |
| Posted: Mon Sep 14, 2009 4:38 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
| mqjeff wrote: |
I believe it's the entire Schema, at least whatever parts of the schema are available (i.e. on project references or in esql files in the project).
Perhaps mgk will confirm or deny this, it's hard to say. A search on his old posts may provide a previous comment. |
If anyone else is interested in this, mgk commented on it here:
http://www.mqseries.net/phpBB2/viewtopic.php?t=47263&highlight=function+schema
See the part on "optimisation".
Thanks |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Mon Sep 14, 2009 5:11 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
Thank you Luke for the intime response i will try whta you have suggested and lets hope that it succeds for me!!  |
|
| Back to top |
|
|
|
|
