| Author |
Message
|
| bdaoust |
 Posted: Mon Aug 10, 2015 8:19 am Post subject: Best Approach To Insert Data To SQL DB - IIB9 |
 |
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
I have a requirement to data an XML file and store data from that XML if certain conditions are met. For instance, if the Elements Node has a Element description of 'SSD' then move the premium value over to a field called SSDPremium.
I was able to do this using a mapping node to DFDL definition and a temporary queue so I could see the results.
Now, I'm being asked to have this stored in a SQL database so the users can run a SSRS report.
I'm wondering what the best approach is to take here. I've used Database Node and ESQL back in WMB7, but didn't know if there is a better approach to consider. I read somewhere use .NET, but unfortunately our broker is on Unix.
I should also mention that I must first need to determine if the record in SQL exist because if not, then it's an insert, otherwise it's an update. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Aug 10, 2015 8:27 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| The mapping node will use JDBC to talk to databases. |
|
| Back to top |
|
|
| bdaoust |
| Posted: Mon Aug 10, 2015 9:12 am Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
Thanks mqjeff.
If there a way to connect using an existing ODBC connection? Our current use of database connections is using the Database node which we can supply the data source, which is a ODBC connection defined on the server.
I'm not certain that our Unix admins will be willing to install any additional drivers, such as the JDBC driver.
Thanks |
|
| Back to top |
|
|
| ganesh |
| Posted: Mon Aug 10, 2015 9:17 am Post subject: |
|
|
Master
Joined: 18 Jul 2010
Posts: 294
|
| bdaoust wrote: |
Thanks mqjeff.
If there a way to connect using an existing ODBC connection? Our current use of database connections is using the Database node which we can supply the data source, which is a ODBC connection defined on the server.
I'm not certain that our Unix admins will be willing to install any additional drivers, such as the JDBC driver.
Thanks |
Just create a data source and use, you do not need any additional drivers to connect to SQL DB.
Better approach for you to insert the data to db if it is not present already would be to create a stored procedure and let the stored procedure do all of that and just return a return code based on outcome. |
|
| Back to top |
|
|
| bdaoust |
| Posted: Mon Aug 10, 2015 9:44 am Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
When you say just create a data source and use, do you mean a Data Design Project?
Looks to me that a data source needs to be created on the server that hosts the broker, which I don't have access to.
According to the documentation, if I want to use a database in the mapping node, I have to have a data design project and cannot simply just indicate a data source like you can with a database node. |
|
| Back to top |
|
|
| ganesh |
| Posted: Mon Aug 10, 2015 10:07 am Post subject: |
|
|
Master
Joined: 18 Jul 2010
Posts: 294
|
| bdaoust wrote: |
Looks to me that a data source needs to be created on the server that hosts the broker, which I don't have access to.
. |
Ask your admins to create one for you. |
|
| Back to top |
|
|
| bdaoust |
| Posted: Mon Aug 10, 2015 11:36 am Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
Ganesh:
As I already said:
According to the documentation, if I want to use a database in the mapping node, I have to have a data design project and cannot simply just indicate a data source like you can with a database node. And it looks like a data design project requires the use of JDBC driver.
Can anyone confirm what I'm reading ?
Thanks |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 10, 2015 11:46 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bdaoust wrote: |
Ganesh:
As I already said:
According to the documentation, if I want to use a database in the mapping node, I have to have a data design project and cannot simply just indicate a data source like you can with a database node. And it looks like a data design project requires the use of JDBC driver.
Can anyone confirm what I'm reading ? |
If you're using a Mapping node, the node requires the layout of the database through a data design project in the same way it requires the layout of the message (through DFDL & XSD). It does not allow you to write anonymous ESQL the way the Database node does, because it's a graphical mapper and not a code container.
The Mapping node also used a JDBC connection instead of an ODBC connection. Any database that has ODBC drivers loaded probably has JDBC drivers loaded, and you don't need a "data source" because JDBC doesn't use data sources. You do need a JDBC connection but that's on the broker side not the database side.
You can of course use a Database node and ESQL like you did in v7. But
| bdaoust wrote: |
| didn't know if there is a better approach to consider |
That would be the Mapping node mentioned by my most worthy associate.
_________________
Honesty is the best policy.
Insanity is the best defence.
Last edited by Vitor on Mon Aug 10, 2015 11:47 am; edited 1 time in total |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Aug 10, 2015 11:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The mapping node requires a Data Design project *in the toolkit ONLY*. So that it can know about your database schemas so that you can build the map.
In runtime, you should get your admins to create a JDBC Configurable Service. |
|
| Back to top |
|
|
| bdaoust |
| Posted: Mon Aug 10, 2015 11:57 am Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
Thanks for the replies.
So if I get the data design working within my toolkit, there is a separate task the admins will need to do?
Usually they just deploy my BAR. If there will be additional tasks for them to do, I should make sure they can/will do that before I invest time into a data design on my end. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 10, 2015 12:16 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| bdaoust wrote: |
| So if I get the data design working within my toolkit, there is a separate task the admins will need to do? |
| mqjeff wrote: |
| In runtime, you should get your admins to create a JDBC Configurable Service |
| bdaoust wrote: |
| Usually they just deploy my BAR. If there will be additional tasks for them to do, I should make sure they can/will do that before I invest time into a data design on my end. |
You've got a fairly dull broker code base if the broker admins have never had to do anything except deploy the bar file. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bdaoust |
| Posted: Mon Aug 10, 2015 12:30 pm Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
Oh I don't disagree. Most of projects were built in WMB6 days and since then there hasn't been a lot of interest in doing tasks in message broker. I've been trying to do new things in broker to show them the possibilities, but often it's hard to get them to buy in.
It's unfortunate because I really enjoy working in the toolkit. I see a lot of potential. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Aug 11, 2015 4:16 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
May be you should provide them with the additional scripts to execute.
So on top of running the bar file, have them run a generic script.
Have that script call all the specific scripts you need for the deployment...
Makes it easier for them. They'd have to run 2 scripts only...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| bdaoust |
| Posted: Wed Aug 12, 2015 12:16 pm Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
fjb_saper:
Not sure what the other scripts would do that you are referring to.
Perhaps execute some mqsi commands?
One thing that also comes into play is that I'm running windows with only toolkit (no local broker instance) and broker runs in Unix on our servers. |
|
| Back to top |
|
|
| bdaoust |
| Posted: Wed Aug 12, 2015 12:21 pm Post subject: |
|
|
Centurion
Joined: 23 Sep 2010
Posts: 130
|
So ganesh made the suggestion of doing a stored procedure. I'm assuming the stored procedure would be on the SQL server side and broker just calls it passing the message as a parameter and waits for a return value.
the SP on SQL server would parse the XML message into the database.
Any pros/cons to that?
Again, looking for the best approach and if it means I go into unchartered terriority - that is fine. I'm not afraid.  |
|
| Back to top |
|
|
|
|
