| Author |
Message
|
| neWMBGuy |
 Posted: Wed Aug 01, 2012 10:40 am Post subject: Generate XML Schema (Message Model) from an Oracle Table |
 |
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
Hey WMB Experts,
I'm very new to WMB and learning my ways in it.
i have a need to read several XML and CSV files and map them in to a single universal Schema (Transformation). That schema is basically the structue of an Oracle table.
now I've done this similar thing in other ESB products but not in WMB. in past i would generate the XML schema from oracle table and then Map the incoming file to this Schema and then it would Insert the records in to oracle table.
how do i GENERATE XML SCHEMA from an Oracle Table in WMB? i am able to make the ODBC connection to Oracel database and i also made a Database Definition file dbname.dbm and its visible in Data Source Explorer.
My Idea is to get the Incoming files MAP them to Oracle table Schema and then DBInsert the records.
is that OK approach?    |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Aug 01, 2012 10:45 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You don't say what version of Broker.
In general, you don't need to create an XML schema for the table.
You create the db project in Toolkit, and make sure your message flow references it, and then you tell the Mapping node to use the database table as the source or destination of your mapping. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Wed Aug 01, 2012 10:51 am Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
| mqjeff wrote: |
You don't say what version of Broker.
In general, you don't need to create an XML schema for the table.
You create the db project in Toolkit, and make sure your message flow references it, and then you tell the Mapping node to use the database table as the source or destination of your mapping. |
My Bad its MB 8.0 running with MQ on Red hat linux connecting to Oracle R12.
O really is it taht simple? choosing the table on Mapping Node? i will try that.
Also is there any Developr network for WMB's test cases/sample walkthru's like MSDN or something? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Aug 01, 2012 10:57 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| neWMBGuy wrote: |
| mqjeff wrote: |
You don't say what version of Broker.
In general, you don't need to create an XML schema for the table.
You create the db project in Toolkit, and make sure your message flow references it, and then you tell the Mapping node to use the database table as the source or destination of your mapping. |
My Bad its MB 8.0 running with MQ on Red hat linux connecting to Oracle R12.
O really is it taht simple? choosing the table on Mapping Node? i will try that. |
Pretty much that simple.
| neWMBGuy wrote: |
| Also is there any Developr network for WMB's test cases/sample walkthru's like MSDN or something? |
There's a lot of information available on IBM's website, under DeveloperWorks, including a forum that is staffed by people who officially work for IBM, rather than random strangers who may or may not work for IBM.
I.e. they're more likely to be 'helpful' with simple stuff.
There are a LOT of samples that come with the Product that demonstrate a LOT of things. And are easy to install and run. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Tue Sep 04, 2012 8:52 am Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
I have not been able to make it work thru Mapping node... Mapping node doesnt give me tables from the database.
trying different approaches. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Sep 04, 2012 8:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| neWMBGuy wrote: |
I have not been able to make it work thru Mapping node... Mapping node doesnt give me tables from the database.
trying different approaches. |
You need to be at FixPack 1 of Broker v8 for the Mapping node to be able to talk to Databases. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Sep 05, 2012 12:43 am Post subject: Re: Generate XML Schema (Message Model) from an Oracle Table |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| neWMBGuy wrote: |
| I'm very new to WMB and learning my ways in it. |
That is hard... you will try out the choices available and make good and bad experiences... finally you will reduce the set of things you use to what works (for you).
| neWMBGuy wrote: |
| i have a need to read several XML and CSV files |
Parse CSV with MRM (with msg sets) (or DFDL), XML can be parsed with XMLNSC (and without a msgset).
| neWMBGuy wrote: |
| and map them in to a single universal Schema (Transformation). |
I (and I think a log of others) will just use ESQL (for Transformation) and no (XSD) schema in runtime. Use xsd-schema during design time (during development with your tooling).
| neWMBGuy wrote: |
| That schema is basically the structue of an Oracle table. |
I would just use PASSTHRU('INSERT ..., (?)) (no dbname.dbm... I think thats just a toolkit-artefact).
Use e.g. SQL-Developer to check if your SQL-Statements work.
| neWMBGuy wrote: |
| in past i would generate the XML schema from oracle table and then Map the incoming file to this Schema and then it would Insert the records in to oracle table. |
You can use a xsd-schema but you can also not use xml-schema (in the runtime)... I tend to spare it if I can.
To use an xsd schema during development is a (very) good practice.
| neWMBGuy wrote: |
| how do i GENERATE XML SCHEMA from an Oracle Table in WMB?. |
Spare it. Or think about creating it manually (an advantage of that is that you will understand what you are doing and do not have to understand what and how a tool is doing/working). I am only aware of the WBI-Adapter framework that has support for generating xsds (from tables).
| neWMBGuy wrote: |
| i am able to make the ODBC connection to Oracel database |
Remember that you can configure the ODBC-connection for the toolkit and for the runtime. I usually spare the first and do the second.
| neWMBGuy wrote: |
| and i also made a Database Definition file dbname.dbm and its visible in Data Source Explorer.. |
Think about whether you need the database defintion files and what the advantage is. Then tell me.
| neWMBGuy wrote: |
| My Idea is to get the Incoming files MAP them to Oracle table Schema and then DBInsert the records. |
I would map with ESQL and insert with ESQL (PASSTHRU). Untyped (no xsd, no db-def-files).
To transform a message and insert into a DB (probably in 2 flows) is relativly easy. You can also make it complicated. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Wed Sep 05, 2012 4:52 am Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
mqsiuser,
im going to try the DB node to INSERT using ODBC connection to call INSERT package on Oracle.
giving up on mapping or direct SQL to INSERT for now. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Wed Sep 05, 2012 8:04 pm Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
going Insane here....
ok here's what i've done so far
1. Created a ODBC DSN connection to the remote Oracle database. Which TESTs perfectly
2. Created Package and SP on the Oracle DB to INSERT records. TESTED them on Oracle works Perfectly.
3. Used Database NODE in the flow to INSERT. in ESQL i parsed the values of incoming message and they all shows up on the TRACE Log.
| Code: |
SET Environment.Variables.sentCode = Root.XMLNSC.ns:GeneralLedger01.Message.sending_System_Code;
SET Environment.Variables.groupIdOf = Root.XMLNSC.ns:GeneralLedger01.Message.Message_ID;
SET Environment.Variables.fileCount = CAST(Root.XMLNSC.ns:GeneralLedger01.Message.file_count AS INTEGER);
SET Environment.Variables.sessionID = Root.XMLNSC.ns:GeneralLedger01.Message.SessionID;
TRACE LOG
=========
( ['MQROOT' : 0x23e944e0]
(0x01000000:Name):Variables = (
(0x03000000:NameValue):fileName = 'xElectricalIn.XML' (CHARACTER)
(0x03000000:NameValue):lobName = 'ELECTRICAL' (CHARACTER)
(0x03000000:NameValue):messageID = '0000662287_20120226_elecprd_GL_3995' (CHARACTER)
(0x03000000:NameValue):transactionCount = '7' (CHARACTER)
(0x03000000:NameValue):ClientStartTime = TIMESTAMP '2012-09-05 23:36:18.006910' (TIMESTAMP)
(0x03000000:NameValue):sentCode = 'elecprd' (CHARACTER)
(0x03000000:NameValue):groupIdOf = '0000662287_20120226_elecprd_GL_3995' (CHARACTER)
(0x03000000:NameValue):fileCount = 54 (INTEGER)
(0x03000000:NameValue):sessionID = '0000662287' (CHARACTER)
(0x03000000:NameValue):transCount = '7' (CHARACTER)
(0x03000000:NameValue):totalDollarValue = 33452.39 (DECIMAL)
)
CALL INSERT_HEADER_RECORD(Value1, value2 etc);
at the end of the DB Compute code i have
CREATE PROCEDURE INSERT_HEADER_RECORD(
IN value1 Char,
IN value2 Char,
IN value3 TIMESTAMP,
.
..
.
)
LANGUAGE DATABASE
EXTERNAL NAME "Schema.PackageName.SPName";
|
it FAILS with this error
( MB8BROKER.default ) Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
What the heck is going on ..... |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Sep 05, 2012 8:26 pm Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
1. Stored Procedures move program/business logic into the DB, which is bad for several reasons. What you gain though is speed.
2. Use ESQL and not the DB-insert-node. This highly increases testability with your DB-Dev-Tool (e.g. Toad or Oracle-SQL-Developer).
3. Likely you have 2 tasks: 1. Mapping from an Input-Msg-Format to Output-DBMsg-Format and 2. Inserting that into the DB
Give up now or give up later  |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Sep 05, 2012 9:57 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| mqsiuser wrote: |
| 1. Stored Procedures move program/business logic into the DB, which is bad for several reasons. |
This is not always true.
- Standardising DB access via SP's can make the overall business run better/more reliably.
- You can put auditing inside the SP thus negating the need to each app to do it possibly in a slightly different way.
- You can put parameter checking an validation into the SP, etc etc etc.
You don't always have to move business logic into the SP.
also, you get more than just speed. You get repeatability and re-use. If there is a bug in the SP (heaven forbid  ) you can fix it without having to retest all the flows/apps that use it.
_________________
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 |
|
|
| mqsiuser |
| Posted: Wed Sep 05, 2012 11:46 pm Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| smdavies99 wrote: |
| This is not always true. |
Thanks... I thought mqjeff would reply and adjust that a bit... (but he is not awake yet).
Look OP: If I am wrong (on major points), someone will (likely) correct me ... well... likely!
| neWMBGuy wrote: |
| to call INSERT package on Oracle. |
@OP: I am not sure "INSERT package": Are you using a stored proc or are you accessing the tables directly ? |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Sep 06, 2012 12:38 am Post subject: Re: Generate XML Schema (Message Model) from an Oracle Table |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| neWMBGuy wrote: |
| ... See the following messages for information obtained from the database concerning this error ... |
Were there any following messages, and if so, what did they say? If not, perhaps a usertrace would help. |
|
| Back to top |
|
|
| neWMBGuy |
| Posted: Thu Sep 06, 2012 5:30 am Post subject: |
|
|
Novice
Joined: 01 Aug 2012
Posts: 19
|
MQSIUSER,
I'm new to MB but not new to EAI & SOA principles.
i always advocate having a loose-couple integration with other dependencies; SP call is preferred to avoid any DB Definition changes. You just have to pass params and you are done with it.
I agree im going thru the learning curve on this and its not fair for me to compare other Middleware Biztalk & Oracle SOA Suite with MB in which this was very easy for me.
and im sure its easy in MB as well but i just need to find right resources on it....
Anyhow enough pep talk.... :-|
back at the topic i believe ODBC setup on my box is the problem..
1. ( MB8BROKER.default ) Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
2. ( MB8BROKER.default ) Database error: SQL State ''23000''; Native Error Code '1400'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 ORA-06512: at line 1''.
The error has the following diagnostic information: SQL State ''23000'' SQL Native Error Code '1400' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("SYSADM"."PS_ZY_OF_HDR_TBL"."SYSTEM_CODE") ORA-06512: at "SYSADM.HDS_ETGEL_MBINSERT_PKG", line 42 ORA-06512: at line 1''
SYSTEM_CODE value shows up in Trace i generated so i know its not blank.
Not sure how to troubleshoot that in respect to MB. Googled/Searched.
| mqsiuser wrote: |
1. Stored Procedures move program/business logic into the DB, which is bad for several reasons. What you gain though is speed.
2. Use ESQL and not the DB-insert-node. This highly increases testability with your DB-Dev-Tool (e.g. Toad or Oracle-SQL-Developer).
3. Likely you have 2 tasks: 1. Mapping from an Input-Msg-Format to Output-DBMsg-Format and 2. Inserting that into the DB
Give up now or give up later |
yes #3 is the scope of my little task here. |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Sep 06, 2012 5:35 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| Not sure how to troubleshoot that in respect to MB |
Take a user trace. You will need to execute three command-line tools in the following order:
1. mqsichangetrace to switch on user trace
...now invoke your message flow
2. mqsireadlog to read the xml-formatted trace log
3. mqsiformatlog to convert it to human-readable text
You may find it useful to use the -r parameter when you switch on user trace, to avoid getting trace from previous flow invocations. |
|
| Back to top |
|
|
|
|
