| Author |
Message
|
| grobledo |
 Posted: Mon Apr 15, 2013 3:43 pm Post subject: Cast BLOB to XMLNSC |
 |
|
Newbie
Joined: 15 Apr 2013
Posts: 3
|
Hi, I need some one to help me please.
This is my case. I have a esql, this file execute a PASSTHRU function. This sentence executed correctly:
This is the result: Query
1:BLOB:[B@43ba43ba
but, I would like to change the result set BLOB to XMLNSC format. Exist some thing to do this. I need to generate a message set? |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Apr 15, 2013 5:20 pm Post subject: Re: Cast BLOB to XMLNSC |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| grobledo wrote: |
| I would like to change the result set BLOB to XMLNSC format. |
If you mean this result is in some way well formed XML (and no I couldn't be bothered to check as you couldn't be bothered to make it clear) then you can have broker parse it into the XMLNSC domain. If you want the results to be assigned as values in a document to be produced from the XMLNSC domain you can assign it.
| grobledo wrote: |
| I need to generate a message set? |
You never need a message set with XMLNSC unless you want to validate an XML document, and questionably then.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Apr 16, 2013 12:40 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
You cannot 'cast' a BLOB to XML. The CAST function is for type conversion. 'XML' is not an ESQL type.
You need to *parse* your BLOB using a CREATE...PARSE statement. There are lots of examples in this forum if you use the search facility. |
|
| Back to top |
|
|
| grobledo |
| Posted: Tue Apr 16, 2013 1:56 pm Post subject: |
|
|
Newbie
Joined: 15 Apr 2013
Posts: 3
|
This is part of my code:
| Code: |
CREATE LASTCHILD OF OutputRoot.XMLNSC DOMAIN ('XMLNSC') PARSE(PASSTHRU(SQLQuery));
|
Where SQLQuery is the sentence which i want to executed. but when i executed. this is the error:
| Code: |
Severity:INTEGER:3
Number:INTEGER:2556
Text:CHARACTER:Trying to assign or use a list as a scalar
|
Why is the reason of this error and how to resolve this problem. Can you show me an example please? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Apr 16, 2013 7:28 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you checked the return type of your passthru query? Could it possibly be of type row? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Apr 17, 2013 1:00 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| The PARSE clause takes a BLOB as its input. Is that what your ESQL is providing? |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Apr 17, 2013 1:47 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
Save your result from DB PASSTHRU statement in some BLOB variable (probably use array in Environment or use THE SELECT if its only one value that you expect from DB) and then pass the BLOB value to the CREATE..PARSE statemnet.
You are trying to acheive everything in a single line of code. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Apr 17, 2013 5:07 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
first of all, why are you using passthru to do your sql query ?, use the select statement and that will return a tree which you will be able to manipulate. If the esql query uses syntax that is particular to a specific DB, then you can code a stored procedure that returns a result set and invoke the stored procedure from your esql program.
from the error message , it looks like what you need is something like
SET OutputRoot.XMLNSC.Test.Values[] = PASSTHRU(SQLQuery);
BUT I insist, use SELECT or stored procedure |
|
| Back to top |
|
|
| kash3338 |
| Posted: Thu Apr 18, 2013 12:09 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| dogorsy wrote: |
first of all, why are you using passthru to do your sql query
...
BUT I insist, use SELECT or stored procedure |
Can you explain why PASSTHRU should not be used and why do you insist on using Stored Procedure for a SELECT query?
Can you explain the advantage of using a normal SELECT than a PASSTHRU? |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Apr 18, 2013 12:39 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| wmb guarantees that SELECT is NOT database dependant, ie. it will execute in any of the supported DBs. PASSTHRU will depend on what the query is, as the sql syntax may be database dependant. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Apr 18, 2013 1:08 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| dogorsy wrote: |
| wmb guarantees that SELECT is NOT database dependant, ie. it will execute in any of the supported DBs. PASSTHRU will depend on what the query is, as the sql syntax may be database dependant. |
Selects are all well and good and they have their place.
However, I've just had to do a major edit on some flows and change the SELECT ESQL to PASSTHRU simply because we were getting SQLServer Table deadlocks. This is a not so nice quirk of that DB so you have to do this
| Code: |
.... FROM MYTABLE where....
|
to
| Code: |
.... FROM MYTABLE with(nolock) where....
|
In an ideal world yes we would only ever need ESQL Select but when
- The SQL gets complicated (joins etc)
- The underlying DB does funny things (locks row/pages on READS)
Then you have to revert to PASSTHRU.
In my experience a good percentage of DB 'selects' and are really READONLY. If the ESQL Select could support that then there would be far less need to turn to PASSTHRU.
_________________
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 |
|
|
| dogorsy |
| Posted: Thu Apr 18, 2013 1:19 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
correct, you are using db specific syntax in your query. If you read my previous posts, I am asking why using passthru.
I am also saying that if you need to use db specific syntax, it is better to use stored procedures, that makes WMB independent of the database, if you want to move your database, say, from db2 to oracle, you do not need to change your message flow, just code an equivalent stored procedure in oracle. |
|
| Back to top |
|
|
| adubya |
| Posted: Thu Apr 18, 2013 1:53 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
Must admit, we use passthru in preference to native WMB SELECT when performing DB queries. Gives us more flexibility in the SQL we can use and we know exactly what SQL is being executed on the DB server.
The chances of us moving DB vendor are virtually zero and if we did so then the WMB port would be the least of our worries  |
|
| Back to top |
|
|
| grobledo |
| Posted: Thu Apr 18, 2013 9:02 am Post subject: |
|
|
Newbie
Joined: 15 Apr 2013
Posts: 3
|
Hi, I used PASSTHRU sentence because i need to execute a complex sentence, This use a XQUERY sentence to retrive part of XML document which is store into colum of DB. This is part of the sentence:
| Code: |
SELECT XMLQUERY('declare namespace x="http://gnp.com.mx/esb/general/asignacion/ejecucion/AsignarTransaccion.xsd"
$h/x:asignarTransaccionRequest/asignarTransaccionData'
PASSING ODS.XML_BITACORA_IN AS "h")
FROM ESEONEG.ODS_EVENTO ODS............
|
But this sentence retrive the value like a BLOB type but the question is How can trasform this value to XMLNSC?. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Apr 18, 2013 9:09 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| grobledo wrote: |
| But this sentence retrive the value like a BLOB type |
That's a bold statement. Nothing in this post shows anything to indicate that the query returns an ESQL BLOB type and broker does not want "like a BLOB type", it wants an ESQL BLOB type. Depending on your database software I'd not be surprised to find the result is a CBLOB or other type which (according to the error you posted) broker considers a CHARACTER.
| grobledo wrote: |
| How can trasform this value to XMLNSC?. |
In the way both @kimbert and I have explained. The better question is what is the datatype actually being returned to the ESQL and how can you make that valid for parsing?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
