Author |
Message
|
ank1117 |
Posted: Thu Jul 14, 2011 8:11 am Post subject: Need to compare CorelId in MQMD with corresponding BLOB |
|
|
Newbie
Joined: 13 Jul 2011 Posts: 4
|
Hi,
I have a database table in which 4 columns are there. One is CLOB which has the Message Data and One BLOB which has the MQMD CorrelIds stored.
Now i need to write an sql, which will retrieve the database rows by matching the CorrelId field from Input XML to corresponding CorrelId in BLOB format stored in database. The query looks like:
SELECT T.* FROM Database.Tablename AS T
WHERE T.CORRELID = '414d5120574d425f514d3120202020200b8d164e2000f0c2';
Please correct above querry, if its possible in such way to compare. In above example ''414d5120574d425f514d3120202020200b8d164e2000f0c2'" is the correlId from XML and T.CORRELID is BLOB column of CorrelIds. |
|
Back to top |
|
 |
Vitor |
Posted: Thu Jul 14, 2011 8:28 am Post subject: Re: Need to compare CorelId in MQMD with corresponding BLOB |
|
|
 Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
ank1117 wrote: |
Please correct above querry, if its possible in such way to compare. |
What happens when you try it?
ank1117 wrote: |
In above example ''414d5120574d425f514d3120202020200b8d164e2000f0c2'" is the correlId from XML and T.CORRELID is BLOB column of CorrelIds. |
No it isn't - it's a character string. That's not the same as a BLOB and won't match.
You need something like (and I say like):
Code: |
SELECT T.* FROM Database.Tablename AS T
WHERE T.CORRELID = InputRoot.MQMD.CorrelId; |
And as a side point SELECT * is never a good idea in code. Name the columns & save yourself some future problems. _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
 |
ank1117 |
Posted: Thu Jul 14, 2011 5:43 pm Post subject: |
|
|
Newbie
Joined: 13 Jul 2011 Posts: 4
|
But suppose, if i want to hard code for time being a particular CorrelId from XML to retrieve from database which is like 414d5120574d425f514d3120202020200b8d164e2000f0c2, can i do some cast to this bit stream to BLOB and use the below select query in WMB compute node?
If not Select *, how the query should be if i need all columns and number of columns are more? |
|
Back to top |
|
 |
fjb_saper |
Posted: Thu Jul 14, 2011 8:04 pm Post subject: |
|
|
 Grand High Poobah
Joined: 18 Nov 2003 Posts: 20756 Location: LI,NY
|
From a DBA perspective I would suggest you not use a CLOB column (difficult to index) but use something that can be the equivalent and indexed...  _________________ MQ & Broker admin |
|
Back to top |
|
 |
Vitor |
Posted: Fri Jul 15, 2011 3:57 am Post subject: |
|
|
 Grand High Poobah
Joined: 11 Nov 2005 Posts: 26093 Location: Texas, USA
|
ank1117 wrote: |
can i do some cast to this bit stream to BLOB and use the below select query in WMB compute node? |
Yes, or you could hard code a constant in the correct format (i.e. not as character).
But how much use is a Correl Id? It's a system generated value with no external reference except to a message id. What requirement has you retrieving the same one from a Compute node over and over?
ank1117 wrote: |
If not Select *, how the query should be if i need all columns and number of columns are more? |
If you don't name the columns in the query and the structure of the table is changed to increase/decrease the number of columns, that query will return more or less columns than your code expects. Unless you've got some very flexible code this will cause issues. It will cause very particular issues if your code expects a given colum to be the 3rd one returned and suddenly it's the 4th... _________________ Honesty is the best policy.
Insanity is the best defence. |
|
Back to top |
|
 |
|