ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Passthru function in esql - CHAR column issue

Post new topic  Reply to topic
 Passthru function in esql - CHAR column issue « View previous topic :: View next topic » 
Author Message
learner.ibm
PostPosted: Mon Feb 10, 2020 2:19 am    Post subject: Passthru function in esql - CHAR column issue Reply with quote

Novice

Joined: 10 Nov 2016
Posts: 10

I have a table in the Oracle database with couple of columns datatype as CHAR.

Eg: PTR CHAR( NOT NULL,
ECP CHAR(5) NOT NULL

The table has some of the records already inserted and when I'm trying to retrieve the table information in the esql using Passthru the retrieved information matching the query is empty

PASSTHRU('SELECT T.* FROM XLTAB T WHERE T.PTR=? AND T.ECP=?, 'CA123', 'TX303') - Records retrieved is empty

Also tried as

PASSTHRU('SELECT T.* FROM XLTAB T WHERE T.PTR=? AND T.ECP=?, CAST(Root.XMLNSC.xml.PTR AS CHAR), CAST(Root.XMLNSC.xml.ECP AS CHAR)) - Records retrieved is empty

When I directly execute the query in DB, records are retrieved.

Please suggest if something is wrong in my query execution. I'm executing this in Database node.
Back to top
View user's profile Send private message
Vitor
PostPosted: Mon Feb 10, 2020 5:33 am    Post subject: Re: Passthru function in esql - CHAR column issue Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

learner.ibm wrote:
Please suggest if something is wrong in my query execution.


I'm unclear why you're executing a simple select using a PASSTHRU. You're going to get more efficiency (and more diagnostics) executing this directly from ESQL (remembering to add the AS keyword.

In your query, how do you know the retrieved record set is empty? Where are you storing the results of the PASSTHRU? How are you interogating it?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
learner.ibm
PostPosted: Tue Feb 11, 2020 1:52 am    Post subject: Reply with quote

Novice

Joined: 10 Nov 2016
Posts: 10

Thanks Vitor for your response.

Actually, I'm using Passthru because I have many parameters to pass it. I just gave a sample of the query having only 2 params with database type as CHAR since I'm facing the problem for this dataype columns declared as CHAR. The response is success if the values passed apart from this CHAR datatype column. To replicate the issue for this CHAR datatype columns, I tried to create a sample exercise by creating table having only 2 columns with CHAR datatype since I can't post my query (security reasons) in this post. I'm capturing the result something like this

OutputRoot.XMLSC.SapPostingData.SapSchema[] = PASSTRHU('select T.Sptn as SPTN ... many columns from table T where T.A=?,....., CAST(....as CHAR), many more params to be casted..... )

Checking the cardinality for the above if the record is empty or not.

Please suggest.
Back to top
View user's profile Send private message
learner.ibm
PostPosted: Tue Feb 11, 2020 3:18 am    Post subject: Reply with quote

Novice

Joined: 10 Nov 2016
Posts: 10

Just to add one more point to the above, I have used the query without PASSTHRU and this is working fine. Wanted to check why this issue for PASSTHRU with CHAR datatype in the database.
Back to top
View user's profile Send private message
timber
PostPosted: Tue Feb 11, 2020 4:13 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

This code:
Code:
PASSTHRU('SELECT T.* FROM XLTAB T WHERE T.PTR=? AND T.ECP=?, 'CA123', 'TX303')
looks wrong to me. You have not terminated the query string after the 2nd '?'.

Your other ESQL:
Code:
PASSTHRU('SELECT T.* FROM XLTAB T WHERE T.PTR=? AND T.ECP=?, CAST(Root.XMLNSC.xml.PTR AS CHAR), CAST(Root.XMLNSC.xml.ECP AS CHAR))
uses exactly the same query string (also not terminated properly) and looks up the parameter values in the message tree. Not sure why you even tried that - the previous ESQL is simpler because it hard-codes the parameter values. In every other way, the scenario is identical to the first ESQL.

Quote:
When I directly execute the query in DB, records are retrieved
IDouble-check that your ESQL PASSTHRU is identical to the SQL that you used.
Quote:
Checking the cardinality for the above if the record is empty or not
Using CARDINALITY in your ESQL is a 'bad smell' because it performs badly. There is always a more efficient option. In this case, EXISTS would be a better choice.
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Feb 11, 2020 8:23 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

learner.ibm wrote:
I'm using Passthru because I have many parameters to pass it.


I meant "simple" in the sense of no joins, sub-selects or other constructs. A select query with 200 tests in the where clause is still a simple query with a where clause.

learner.ibm wrote:

Checking the cardinality for the above if the record is empty or not.


I agree with my most worthy associate. If using CARDINALITY is the answer, you're probably asking the wrong question.

learner.ibm wrote:

I have used the query without PASSTHRU and this is working fine


If this is the case then you should raise a PMR; you've discovered a fairly major bug in the ODBC drivers (given that all PASSTHRU does is throw the SQL, unparsed, at the database)
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
learner.ibm
PostPosted: Tue Feb 11, 2020 11:27 pm    Post subject: Reply with quote

Novice

Joined: 10 Nov 2016
Posts: 10

Thanks a lot timber and Vitor.
I'll ensure not to use CARDINALITY check in esql.

Meanwhile, I'm going to create PMR as suggested by Vitor and shall keep updated the post.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Passthru function in esql - CHAR column issue
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.