| Author |
Message
|
| Bingo |
 Posted: Fri Apr 29, 2005 8:23 am Post subject: Insert BLOB data in DB2 |
 |
|
Novice
Joined: 31 Mar 2005
Posts: 22
|
Hi,
I am trying to Insert BLOB data into a DB2 table having a field of BLOB type.
Flow is
MQI --> Compute --> MQO.
Can anybody help me with some sample code that actually does it.
Regds |
|
| Back to top |
|
 |
| JT |
| Posted: Fri Apr 29, 2005 8:44 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
|
| Back to top |
|
|
| Bingo |
| Posted: Fri Apr 29, 2005 8:22 pm Post subject: Hi |
|
|
Novice
Joined: 31 Mar 2005
Posts: 22
|
Hi JT,
Thanks for the suggestion. I looked through the post , but i guess it is not solving my problem. Please help me solve the problem:
Problem Description: ( look at the code below)
===============================
CREATE COMPUTE MODULE CLOBTEST_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot = InputRoot;
DECLARE B BLOB;
DECLARE C CHAR;
SET B = CAST(ASBITSTREAM(OutputRoot) AS BLOB);
SET C = ''; -- Just for checking what value is geting set in B
PASSTHRU('INSERT INTO database.db2admin.TEST(TESTBLOB) VALUES( ? )',B);
-- INSERT INTO Database.db2admin.TEST(TESTBLOB) VALUES (B) ;
RETURN TRUE;
END;
The code runs perfectly fine till before the PASSTHRU statement. However it throws out an error when it tries to parse that statement. Am i doing anything wrong.
TESTBLOB is a blob data field in that table TEST in DB2 and i am using WBIMB V 5. Please advise what am i doing wrong.
However, if i am using only the INSERT statement (not with PASSTHRU) it is inserting a blank row in the database.
Please Help
Regds |
|
| Back to top |
|
|
| alexey |
| Posted: Sat Apr 30, 2005 12:33 am Post subject: |
|
|
Acolyte
Joined: 18 Dec 2003
Posts: 62
Location: Israel
|
Hi,
| Quote: |
PASSTHRU('INSERT INTO database.db2admin.TEST(TESTBLOB) VALUES( ? )',B); |
You shouldn't use PASSTHRU with database in it. Remove the "database.". |
|
| Back to top |
|
|
| Bingo |
| Posted: Sat Apr 30, 2005 5:35 am Post subject: hI |
|
|
Novice
Joined: 31 Mar 2005
Posts: 22
|
Hi Alexy,
You were correct. I removed the "database" word from the statement and the line got parsed. However, it is still inserting a blank row in the database. Any thoughts on that ?
Regds |
|
| Back to top |
|
|
| Bingo |
| Posted: Sat Apr 30, 2005 6:55 am Post subject: |
|
|
Novice
Joined: 31 Mar 2005
Posts: 22
|
Hi Guys,
Think the problem is solved. Thanx for all your help and suggestions.
Regds |
|
| Back to top |
|
|
| Bingo |
| Posted: Thu Jul 07, 2005 10:46 pm Post subject: Reverse Engineering |
|
|
Novice
Joined: 31 Mar 2005
Posts: 22
|
Hello Guys,
I need to retirve this message from the database as well as it was inserted in XML format. How do i do that ?
Regds |
|
| Back to top |
|
|
| recallsunny |
| Posted: Fri Jul 08, 2005 7:25 am Post subject: |
|
|
Disciple
Joined: 15 Jun 2005
Posts: 163
Location: Massachusetts
|
| Code: |
| CREATE FIRSTCHILD of OutputRoot DOMAIN 'XML' PARSE(TESTBLOB) ; |
Where TESTBLOB would the bitstream value you have inserted into the DB. Hope this helps...  |
|
| Back to top |
|
|
| CHF |
| Posted: Fri Jul 08, 2005 11:52 am Post subject: Re: Reverse Engineering |
|
|
Master
Joined: 16 Dec 2003
Posts: 297
|
| Bingo wrote: |
Hello Guys,
I need to retirve this message from the database as well as it was inserted in XML format. How do i do that ?
Regds |
Try this
SET myLogDta = THE(SELECT ITEM T1.LOG_DATA FROM
Database.ACTIVITY_LOG AS T1 WHERE ...........);
CREATE FIRSTCHILD OF Environment.Variables.Temp Domain('XML') PARSE(myLogDta, 0, 37, '', '', 'XML');
and you'll see your XML.
Hope it works for you.
_________________
CHF  |
|
| Back to top |
|
|
| CHF |
| Posted: Fri Jul 08, 2005 11:59 am Post subject: |
|
|
Master
Joined: 16 Dec 2003
Posts: 297
|
| recallsunny wrote: |
| Code: |
| CREATE FIRSTCHILD of OutputRoot DOMAIN 'XML' PARSE(TESTBLOB) ; |
Where TESTBLOB would the bitstream value you have inserted into the DB. Hope this helps... |
recallsunny,
I tried assigning it to outputroot but somehow I was not successful... so I had to assign it to environment variable.
May be I didn something wrong.
_________________
CHF |
|
| Back to top |
|
|
| mqmaniac |
| Posted: Tue Jun 27, 2006 5:43 pm Post subject: |
|
|
Master
Joined: 27 Dec 2005
Posts: 201
|
Hello Everyone...
I am having the same problem..
I am using the following code to PARSE
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
DECLARE myLogDta BLOB;
CREATE FIELD OutputRoot.XML.A;
SET myLogDta = InputRoot.XML.Envelope;
CREATE FIRSTCHILD OF Environment.Variables.Temp Domain('XML') PARSE(myLogDta, 0, 37, '', '', 'XML');
SET OutputRoot.XML.A = Environment.Variables.Temp.XML;
Now The Input I have given is:
<Envelope>4C939687C4A3816E4C4F6060D1D740D4969987819540C38881A2854060606E4CC595A5859396978540A7949395A27E7F88A3A3977A6161A28388859481A24BA79493A29681974B96998761A2968197618595A58593969785617F40404040A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F40404040A7949395A27AA7A2897E7F88A3A3977A6161A6A6A64BA6F34B96998761F2F0F0F161E7D4D3E28388859481608995A2A3819583857F40404040A7A2897AA28388859481D3968381A38996957E7F88A3A3977A6161A28388859481A24BA79493A29681974B96998761A2968197618595A5859396978561404040404040404040404040404040404040404040404040D7A3A8D781A885E49784D998E2D6C1D74BA7A2847F6E054CC88581848599616E054CC29684A86E40404040404040404C8386A27AC88499D6998987D998D99494D4A287C799976E2540404C8386A27A888499E5859940A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F6EF14BF1F24C618386A27A888499E585996E2540404C8386A27A888499D998C69994C381A285C7999740A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F6E25404040404C8386A27A888499C7A3A6A8C799976E254040404040404C8386A27A888499D49586D699876EC6C14C618386A27A888499D49586D699876E254040404040404C8386A27A888499C7A3A6A8C1979793D5946EC5F1D7C1E8C5C54C618386A27A888499C7A3A6A8C1979793D5946E254040404040404C8386A27A888499C7A3A6A8C1979793E585996EF14BF04C618386A27A888499C7A3A6A8C1979793E585996E25404040404C618386A27A888499C7A3A6A8C799976E2540404C618386A27A888499D998C69994C381A285C799976E2540404C8386A27A888499D998C69994C995A2A3C7999740A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F6E25404040404C8386A27A888499E394A2A394976EF2F0F0F660F0F660F1F9E3F0F67AF3F17AF2F74BF7F6F960F0F57AF0F04C618386A27A888499E394A2A394976E25404040404C8386A27A888499D99793A8E396D8A485C9846ED8D34BD9C5C3C5C9E5C54BC6D9D6D44BC3C8C1E2C54C618386A27A888499D99793A8E396D8A485C9846E25404040404C8386A27A888499D99793A8E396D8A485D48799D5946EC6C1F1D74C618386A27A888499D99793A8E396D8A485D48799D5946E25404040404C8386A27A888499C39395A3E3A8976EC94C618386A27A888499C39395A3E3A8976E25404040404C8386A27A888499D4A287C984C799976E254040404040404C8386A27A888499E4E4C9846EF4F4F9F6F882F08660F0F0F3F0F2608284F2F860F0F0F0F0F0F0F260F2F381F4F6F685F64C618386A27A888499E4E4C9846E25404040404C618386A27A888499D4A287C984C799976E2540404C618386A27A888499D998C69994C995A2A3C799976E2540404C8386A27A888499D998E396E28599A5C7999740A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F6E25404040404C8386A27A888499E28599A5D799A584C2A4A2C9846EC3C8C64C618386A27A888499E28599A5D799A584C2A4A2C9846E25404040404C8386A27A888499E28599A5D799A584C2A4A2E495A3C9846EE2E5C3C9D5C74C618386A27A888499E28599A5D799A584C2A4A2E495A3C9846E25404040404C8386A27A888499E28599A5D5946ED7C1E8C5C54C618386A27A888499E28599A5D5946E25404040404C8386A27A888499E28599A5C183A38996956ED7A3A8D781A885E49784D9984C618386A27A888499E28599A5C183A38996956E25404040404C8386A27A888499E28599A5E585996EF14BF04C618386A27A888499E28599A5E585996E2540404C618386A27A888499D998E396E28599A5C799976E2540404C8386A27A888499D6998987C69994E68896C7999740A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F6E25404040404C8386A27A888499C5A7A3C995A3D69987C995846EC5E7E34C618386A27A888499C5A7A3C995A3D69987C995846E25404040404C8386A27A888499C2A4A2E495A3C9846EC6C1E3C1E74C618386A27A888499C2A4A2E495A3C9846E25404040404C8386A27A888499C78596D987956EE3E74C618386A27A888499C78596D987956E25404040404C8386A27A888499E4A299C799976E254040404040404C8386A27A888499D7A3A8E2A8A2D39687C9846EE4F4F0D4E7C74C618386A27A888499D7A3A8E2A8A2D39687C9846E25404040404C618386A27A888499E4A299C799976E2540404C618386A27A888499D6998987C69994E68896C799976E254C618386A27AC88499D6998987D998D99494D4A287C799976E4C8386A27AD7A3A8D781A885E49784D9986E2540404C8386A27A8193A3E49784C7999740A7949395A27A8386A27E7F88A3A3977A6161A28388859481A24B838881A2854B839694618386A2617F6E25404040404C8386A27A9781A885E49784C799976E254040404040404C8386A27A8183A3C3A3936EE44C618386A27A8183A3C3A3936E254040404040404C8386A27A9285A8C799976E2540404040404040404C8386A27A9781A885E58595C9846EF1F8F0F0F2F0F0F2F54C618386A27A9781A885E58595C9846E2540404040404040404C8386A27AA2A8A2C984C799976E25404040404040404040404C8386A27A81979793E2A8A2D5946ED9C54C618386A27A81979793E2A8A2D5946E25404040404040404040404C8386A27A81979793E2A8A2C9846EF1F8F0F2F44C618386A27A81979793E2A8A2C9846E2540404040404040404C618386A27AA2A8A2C984C799976E2540404040404040404C8386A27AA2A8A2C984C799976E25404040404040404040404C8386A27A81979793E2A8A2D5946EC8D74C618386A27A81979793E2A8A2D5946E25404040404040404040404C8386A27A81979793E2A8A2C9846ED4C5C3F0F8F94C618386A27A81979793E2A8A2C9846E2540404040404040404C618386A27AA2A8A2C984C799976E254040404040404C618386A27A9285A8C799976E254040404040404C8386A27A9781A885D7A894A3E3859994A2C799976E2540404040404040404C8386A27A97A88293C4A3C799976E25404040404040404040404C8386A27A979593A3C4A36E6060F0F760F0F74C618386A27A979593A3C4A36E2540404040404040404C618386A27A97A88293C4A3C799976E2540404040404040404C8386A27A8489A283C799976E25404040404040404040404C8386A27A8489A283C4A36E6060F0F760F0F64C618386A27A8489A283C4A36E2540404040404040404C618386A27A8489A283C799976E254040404040404C618386A27A9781A885D7A894A3E3859994A2C799976E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885D7A894A3E3859994A2C79997616E254040404040404C8386A27A9781A885C99584C799976E2540404040404040404C8386A27A9781A885D4859496C799976E25404040404040404040404C8386A27A9781A885C68585C194A36EF0F04BF0F04C618386A27A9781A885C68585C194A36E2540404040404040404C618386A27A9781A885D4859496C799976E254040404040404C618386A27A9781A885C99584C799976E254040404040404C8386A27A81979793C4A381C5838896C799976E2540404040404040404C8386A27A85838896D998C995846EE84C618386A27A85838896D998C995846E2540404040404040404C8386A27AA58595C48586C5838896C799976E25404040404040404040404C8397997A8183A3899695C396848540A7949395A27A8397997E7F88A3A3977A6161A794934B8681998589A24B83969461E28388859481618397997F6EC34C618397997A8183A3899695C39684856E25404040404040404040404C8397997A998583969984E3A8978540A7949395A27A8397997E7F88A3A3977A6161A794934B8681998589A24B83969461E28388859481618397997F6EC1D74C618397997A998583969984E3A897856E25404040404040404040404C8397997A83A4A2A3C98440A7949395A27A8397997E7F88A3A3977A6161A794934B8681998589A24B83969461E28388859481618397997F6EF0F0F8F8F8F8F04C618397997A83A4A2A3C9846E25404040404040404040404C8397997AA4A28599C98440A7949395A27A8397997E7F88A3A3977A6161A794934B8681998589A24B83969461E28388859481618397997F6EE4F4F0D4E7C74C618397997AA4A28599C9846E25404040404040404040404C8397997AA3998195A28183A3899695C98440A7949395A27A8397997E7F88A3A3977A6161A794934B8681998589A24B83969461E28388859481618397997F6EF4F0F1F6F54C618397997AA3998195A28183A3899695C9846E2540404040404040404C618386A27AA58595C48586C5838896C799976E254040404040404C618386A27A81979793C4A381C5838896C799976E25404040404C618386A27A9781A885E49784C799976E2540404C618386A27A8193A3E49784C799976E254C618386A27AD7A3A8D781A885E49784D9986E054C61C29684A86E4C61C595A585939697856E4C61939687C4A3816E</Envelope>
The Flow is throwing Parsing Exceptions saying
( WBRKBK.default ) (.BLOB_TO_XML_BLOB.TO.XML.main, 25.1) Error detected whilst executing the SQL statement 'SET OutputRoot.XML.A = Environment.Variables.Temp.XML;'.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
Pls Help..I a digging into the whole site to get help.. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jun 27, 2006 6:39 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Here is your error:
SET myLogDta = InputRoot.XML.Envelope;
myLogDta is not a BLOB but the tree content as tree.
What you need is (from memory)
| Code: |
Declare soap Namespace = 'http.....' -- set soap namespace
myLogDta = ASBITSTREAM(typeformater(Root/Folder)
InputBody.soap:Envelope
options myoptions
CCSID myccsid
Encoding MQENC_NATIVE
) |
This at least will give you a BLOB. Remember to parse it with the same options.
Enjoy
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqmaniac |
| Posted: Tue Jun 27, 2006 6:48 pm Post subject: |
|
|
Master
Joined: 27 Dec 2005
Posts: 201
|
Hi fjb_saper,
I have declared...
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
DECLARE myLogDta BLOB;
CREATE FIELD OutputRoot.XML.A;
SET myLogDta = InputRoot.XML.Envelope;
I am using 2.1 and is the reason I refer "InputRoot.XML.Envelope"..
BUT its not a SOAP message..Its just an XML Tag..
I tried with "InputRoot.XML.Temp" too..
Did not work |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jun 27, 2006 6:56 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Same reason. Even though the field is declared as BLOB the content is not.
You take the content from the tree!
In order to get a blob you MUST use the ASBITSTREAM function.
Read up on it in the ptf/CSD documentation if you are running on 2.1
The only way I see your code working is if you have the field Envelope declared as a CDATA field containing hex value type stuff. (XML representation of a blob) but somehow I don't think this is the case...
If you did not see my code in the previous post... scroll to the right
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
