| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Problem with PASSTRHU in order to do a SELECT in Oracle |
« View previous topic :: View next topic » |
| Author |
Message
|
| artykito |
 Posted: Thu Aug 26, 2010 11:56 am Post subject: Problem with PASSTRHU in order to do a SELECT in Oracle |
 |
|
Apprentice
Joined: 24 Jun 2010
Posts: 33
|
Hi guys,
I'm trying to do a SELECT in Oracle in order to get the a field value, that only can have 1 possible value. First at all, it force me to put a structure not a variable with only one vale, but okay...no problem.
I need to do a SELECT depending of 3 values (clause WHERE with 3 fields). I have done a test with only one field like this:
| Quote: |
| SET Environment.Variables.value[] = PASSTHRU('SELECT FIELDVALUE FROM ZTABLE_001 WHERE FIELDONE = ?', InputRoot.DataObject.ns38:SapZFiPagosCobrosWrapper.SapZFiPagosCobros.SapPioPagos.FIELDONE); |
And I get a return value, then I know the syntax and the access to DB is okay. The problem is when I try to do the clause depending of these 3 fields. This is the code I'm writing:
| Quote: |
| SET Environment.Variables.value[] = PASSTHRU('SELECT FIELDVALUE FROM ZTABLE_001 WHERE FIELDONE = ? AND FIELDTWO = ? AND FIELDTHREE = ?', InputRoot.DataObject.ns38:SapZFiPagosCobrosWrapper.SapZFiPagosCobros.SapPioPagos.FIELDONE, InputRoot.DataObject.ns38:SapZFiPagosCobrosWrapper.SapZFiPagosCobros.SapPioPagos.FIELDTWO, InputRoot.DataObject.ns38:SapZFiPagosCobrosWrapper.SapZFiPagosCobros.SapPioPagos.FIELDTHREE); |
Then I don't get nothing...but if I do the select with in the DB from the Toolkit, I get the value....so I think I'm failing in the syntax, but I couldn't find in the help anything for a WHERE clause with more than one field. Do you know how can I manage this?
Thank you in advance!! |
|
| Back to top |
|
 |
| Vitor |
| Posted: Thu Aug 26, 2010 12:00 pm Post subject: Re: Problem with PASSTRHU in order to do a SELECT in Oracle |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| artykito wrote: |
Do you know how can I manage this?
|
Have you considered using the VALUES clause of PASSTHRU? What might be happening is the only the 1st value is being used to replace the ? placeholder.
Also have you run a user trace? Not something I've done a lot of but if I remember correctly (which is optimistic today) it does at least show you the final substituted query it's passing through even if it doesn't (obviously) have much to say on what comes back & why.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Aug 26, 2010 12:36 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
This:-
| Code: |
InputRoot.DataObject.ns38:SapZFiPagosCobrosWrapper.SapZFiPagosCobros.SapPioPagos
|
Is ideal for a reference if you need to use more than one.
Anyway, as Vitor has suggested examining a user trace may well provide you with some enlightenment as to exactly what SQL Command is being issued.
I generally do something like this.
| Code: |
DECLARE InRef REFERENCE TO InputRoot.DataObject.ns38:SapZFiPagosCobrosWrapper.SapZFiPagosCobros.SapPioPagos;
DECLARE SQLCommand = 'SELECT FIELDVALUE FROM ZTABLE_001 WHERE FIELDONE = ? AND FIELDTWO = ? AND FIELDTHREE = ?';
|
Then the passthru can become a little more readable.
| Code: |
Set Environment.Date[] = Passtru(SQLCommand, InRef.FIELDONE, InRef.FIELDTWO, InRef.FIELDTHREE);
|
IF this fails then try it with two if the three params.
_________________
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 |
|
|
| Vitor |
| Posted: Thu Aug 26, 2010 12:39 pm Post subject: Re: Problem with PASSTRHU in order to do a SELECT in Oracle |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vitor wrote: |
| Also have you run a user trace? Not something I've done a lot of |
Just before someone comments on the above statement, I'd like to clarify that it's passthru I've not done a lot of. The user trace & I are close personal friends.
Especially today. Where code is being written with the same lack of clarity. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| artykito |
| Posted: Thu Aug 26, 2010 11:03 pm Post subject: |
|
|
Apprentice
Joined: 24 Jun 2010
Posts: 33
|
I could see the problem is when I try to get one of these field values. There is no problem if I try to get the others two, but I don't get anything with this other field...
And when I do a SQL Script, it returns me the value. Did you have this problem before??
For example, in my ZTABLE_001 I have one row like:
FIELDONE (1 CHAR nullable) = '1'
FIELDTWO (1 CHAR nullable) = 'A'
FIELDTHREE (10 CHAR nullable) = 'PROVNAC'
FIELDVALUE (10 CHAR nullable) = 'contact admin'
If I do the following query in SQL Script:
| Quote: |
| SELECT FIELDVALUE FROM ZTABLE_001 WHERE FIELDONE = '1' AND FIELDTWO = 'A ' AND FIELDTHREE = 'PROVNAC' |
I get the 'contact admin' in the FIELDVALUE, so it's okay. And if I only do the WHERE clause only with FIELDTHREE I also obtain the value. But when I try in my ESQL code the following sentence:
| Quote: |
| SET Environment.Variables.codInterfaz[] = PASSTHRU('SELECT FIELDVALUE FROM ZTABLE_001 WHERE FIELDTHREE = 'PROVNAC'); |
I can't get anything...but if I try with the FIELDONE and FIELDTWO in the WHERE clause, I obtain the value, so I know the problem is in why the PASSTHRU can't obtain the value for the FIELDTHREE only in the ESQL code, because with a SQL Script file the query works...any idea? |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
