| Author |
Message
|
| Fayaz |
 Posted: Thu Jul 21, 2011 8:29 am Post subject: SQL Stored Procedures in DBInput or DataInsert nodes |
 |
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
| Is there anyway we can use SQL stored procedures within the DBInput or the DataInsert nodes? I have triggers created to initiate the flow via the DBInput node but all our business logic needs to come via SQL server stored procedures (due to logic combining various tables) and written to a XML message file...is this possible? |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Thu Jul 21, 2011 10:05 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi Fayaz
DBInput node / DBInsert Node, you can just specify the DSN, and through ESQL/Java you can call the Stored procedure.
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| Fayaz |
| Posted: Thu Jul 21, 2011 10:23 am Post subject: |
|
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
| Thanks Gayathri...is there any documentation available for this? For the DB input node how do I call a stored procedure which directly writes to an existing xml structure? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Jul 21, 2011 11:51 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Fayaz wrote: |
| Thanks Gayathri...is there any documentation available for this? For the DB input node how do I call a stored procedure which directly writes to an existing xml structure? |
Oh Dear. There is no such thing as an XML structure inside broker.
May I humbly suggest that you take some training.
_________________
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 |
|
|
| Fayaz |
| Posted: Thu Jul 21, 2011 11:58 am Post subject: |
|
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
| Sorry a mistake what I mean that it writes to a message file defined under message set....irrespective how do I define a DB input node so that it's queries a sql stored proc instead of a source table? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Jul 21, 2011 12:20 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
A good point would be to look for say...
Language Database
In the infocentre.
_________________
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 |
|
|
| lancelotlinc |
| Posted: Thu Jul 21, 2011 12:27 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| Fayaz |
| Posted: Thu Jul 21, 2011 12:32 pm Post subject: |
|
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
| it says it only supports procedures from DB2 & Oracle...so I guess nothing for SQL procedures as yet? anyplace else this can be looked into? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Jul 21, 2011 12:34 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| Fayaz wrote: |
| it says it only supports procedures from DB2 & Oracle...so I guess nothing for SQL procedures as yet? anyplace else this can be looked into? |
You mean to say that the world's best messaging and transformation product does not support SQL? 
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Fayaz |
| Posted: Thu Jul 21, 2011 12:38 pm Post subject: |
|
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
That is what the documentation says:
Relational database stored procedures
•
Map RDB stored procedure (written in any of the supported languages on the database)
•
Provide input for IN parameter of stored procedure
•
Map stored procedure output to target message
–
OUT parameters
–
INOUT parameters
–
Return value
–
Results sets
•
Supported databases: DB2® and Oracle |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Jul 21, 2011 12:45 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
If you would have attended the WM663 training, you would have learned that WMB supports any ODBC Type 2 or ODBC Type 4 driver. So if your supported database is able to respond to a Type 2 or Type 4 ODBC command, then WMB can access its stored procedure.
Therefore, SQLServer stored procedures are able to be called by WMB.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Fayaz |
| Posted: Thu Jul 21, 2011 12:50 pm Post subject: |
|
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
| In ESQL yes they are and I have built applications which query it but in mapping nodes sql server stored proc does not seem to be supported (tables are)......let me know if otherwise without going to training just for this aspect... |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Jul 21, 2011 12:52 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Thinnk hard for a moment. If you can access stored procedure in ESQL Compute nodes, and you can call ESQL procedures from mapping nodes, do you think that you could call a stored procedure from a mapping node? What happens when you try it?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Fayaz |
| Posted: Thu Jul 21, 2011 12:56 pm Post subject: |
|
|
Novice
Joined: 21 Jul 2011
Posts: 18
|
| I agree and that is surprising but it's not letting me select that checkbox option for stored procedures... |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Jul 21, 2011 12:59 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
|
|
