| Author |
Message
|
| gilc |
 Posted: Thu Mar 17, 2011 1:39 am Post subject: Calling dynamic SP |
 |
|
Apprentice
Joined: 17 Mar 2011
Posts: 32
|
Hello,
In the current project that I'm working on there's a need to call many stored procedures from an Oracle DB.
Because there are many SP to call with different types and numbers of paramers we build a dynamic code in java using JavaComputeNode that handles the connection and calling of the SP.
We used getJDBCType4Connection to establish connections with various JDBCProviders.
Unfortunately, there are some SP that must commit in their code. BUT as I know, when using getJDBCType4Connection you can't commit nor rollback inside SP.
The only 2 solutions I thought about were:
1. Getting a connection using DriverManager (and close it in the end) but then I'm taking away the responsibilty of managing connections from the broker.
2. Using ComputeNode and ESQL to call SP but then I can't change dynamiclly the name of the SP and its parametes.
So finally after a long introduction, I'm asking you guys -
Do you know of a better way to execute what I need? Do you have any insights about the things I said?
Thank you for the help,
Gil |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Thu Mar 17, 2011 5:39 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| gilc |
| Posted: Thu Mar 17, 2011 6:16 am Post subject: |
|
|
Apprentice
Joined: 17 Mar 2011
Posts: 32
|
Thank you for the reply.
I've been working with broker a few years now and I know its capabilities and its features.
I know that it's not a great idea to use a two phase commit, but our customer needs these commits because we are integrating from BizTalk and they don't want to change all their code in the DB that have been working fine for years.
Any more ideas or insights? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 17, 2011 6:24 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
There is a reason they are migrating from BizTalk. That reason is: WMB works better than BizTalk.
Reusing BizTalk stored procedures is a bad idea. Don't handicap WMB performance because of BizTalk's shortcomings.
If BizTalk were such a good product, they would still be using BizTalk, but they are not. Use your powers of persuasion to help them make an informed decision.
You can reuse much of the Stored Proc code, but to promote WMB's best use of the logic, you may need to restructure it. This is an opportunity to excel, embrace it.
Convince them they need to be more flexible in their thinking.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Mar 17, 2011 6:25 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
well....there's always ESQL evaluate. Slow but effective.
I only mention it in the hope that somebody will suggest something more elegant. |
|
| Back to top |
|
|
| john01 |
| Posted: Thu Mar 17, 2011 6:53 am Post subject: |
|
|
Novice
Joined: 16 Jun 2010
Posts: 21
Location: PT
|
Take a look about:
- call java methods through esql.
- eval
- passthrough
Be careful about performance! |
|
| Back to top |
|
|
| gilc |
| Posted: Thu Mar 17, 2011 7:15 am Post subject: |
|
|
Apprentice
Joined: 17 Mar 2011
Posts: 32
|
Thanks again for all the replies.
EVAL solves only half of the problem because it the declaration of the function\procedure will be static - meaning that i'll still have to declare a different procedure for each SP.
It only helps calling an already declared procedures dynamically.
Passthru isn't very good for SP because it lacks many options such as using out parameters.
Calling JAVA method through esql is the same for me as calling an EXTERNAL procedure with LANGUAGE DATABASE in esql.
And so I think I won't have any other choice than going with this solution..... |
|
| Back to top |
|
|
|
|
