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 » Need help on the use of PASSTHRU in WMB 6.1.0.4 and Oracle11

Post new topic  Reply to topic
 Need help on the use of PASSTHRU in WMB 6.1.0.4 and Oracle11 « View previous topic :: View next topic » 
Author Message
sankritya
PostPosted: Thu Feb 16, 2012 6:35 am    Post subject: Need help on the use of PASSTHRU in WMB 6.1.0.4 and Oracle11 Reply with quote

Centurion

Joined: 14 Feb 2008
Posts: 100

Need help on the use of PASSTHRU in WMB 6.1.0.4 and Oracle11g

I have used the following statement in my program
Quote:

PASSTHRU('UPDATE MYDB.LOG.SESSION_DB SET QUERY_IN_PROGRESS = ?, LAST_USED_TIME = ?, WMB_UNIQUE_ID = ? WHERE QUERY_IN_PROGRESS=? AND ID = ? AND ROWNUM <?', 'TRUE',CURRENT_TIMESTAMP,WMB_ID,'FALSE',APPLICATIONID,2);

Whenever I am running the flow it is returning the following fault message.

Quote:
<faultstring>DatabaseException: Child SQL exception, HY000, 971, [IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-00971: missing SET keyword</faultstring>


I tried searching for the correct query in the forum but could not find any for UPDATE. Have I missed something in the query?
Back to top
View user's profile Send private message
smdavies99
PostPosted: Thu Feb 16, 2012 7:06 am    Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

Does it work if you cast it all (the query and data) into a single char and then supply that 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
View user's profile Send private message
vijsam
PostPosted: Thu Feb 16, 2012 7:09 am    Post subject: Reply with quote

Apprentice

Joined: 01 Jun 2011
Posts: 46

hi,
use AS in your sql query like 'UPDATE MYDB.LOG.SESSION_DB AS T SET QUERY_IN_PROGRESS '

go through the link

http://www.mqseries.net/phpBB2/viewtopic.php?p=169860&sid=d2124a0bcf77c0531d09ebb965b66a74
Back to top
View user's profile Send private message
sankritya
PostPosted: Thu Feb 16, 2012 7:20 am    Post subject: Reply with quote

Centurion

Joined: 14 Feb 2008
Posts: 100

@vijsam : I have tried AS also but the result is same.

@smdavies99 : How can I can cast query and data as character if I am required to take the input from flow like CURRENT TIMESTAMP and Applicaion ID?
Back to top
View user's profile Send private message
vijsam
PostPosted: Thu Feb 16, 2012 7:26 am    Post subject: Reply with quote

Apprentice

Joined: 01 Jun 2011
Posts: 46

declare x character ;
SET x =
'UPDATE Database.tablename AS A
SET a= ?
,b = ?
,c= ?
,d= ?
WHERE A.fieldname = '';

passthru(x);
Back to top
View user's profile Send private message
sankritya
PostPosted: Thu Feb 16, 2012 7:46 am    Post subject: Reply with quote

Centurion

Joined: 14 Feb 2008
Posts: 100

@vijsam : Thanks for the help .. but still the result is same.
Back to top
View user's profile Send private message
smdavies99
PostPosted: Thu Feb 16, 2012 8:01 am    Post subject: Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

Something like

Code:

declare mychar char;
set mychar='insert into mytable values(' || cast(myint as char ||');';

pasthru(mychar);


when you get a complete string and it does not work then cut/paste it into SQLDeveloper and see how that gets on.
_________________
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
View user's profile Send private message
sankritya
PostPosted: Thu Feb 16, 2012 8:19 am    Post subject: Reply with quote

Centurion

Joined: 14 Feb 2008
Posts: 100

I have tried this
DECLARE SQLStatement character ;

SET SQLStatement = 'UPDATE MYDB.LOG.SESSION_DB AS A SET QUERY_IN_PROGRESS = ?, LAST_USED_TIME = ?, WMB_UNIQUE_ID = ? WHERE A.QUERY_IN_PROGRESS=? AND A.ID = ? AND A.ROWNUM <?';


PASSTHRU(SQLStatement,'TRUE',CURRENT_TIMESTAMP,WMB_ID,'FALSE',APPLICATIONID,2);


But it is still returning the same error.
Back to top
View user's profile Send private message
mqsiuser
PostPosted: Thu Feb 16, 2012 8:23 am    Post subject: Reply with quote

Yatiri

Joined: 15 Apr 2008
Posts: 637
Location: Germany

Carefully look at smdavies99's suggestion(s) !

Pass over a single string, without ? (questionsmarks) and use CAST AS CHAR on anything that is not a char (e.g. INT)

e.g. PASSTHRU('UPDATE MYDB.LOG.SESSION_DB AS A SET QUERY_IN_PROGRESS = 12, LAST_USED_TIME = 17:45:000, WMB_UNIQUE_ID = 28 WHERE A.QUERY_IN_PROGRESS= 2 AND A.ID = 1 AND A.ROWNUM < 5' );

Do not use the parameters-option of PASSTHRU.

Just work with a single String (CHAR) and test this string (the SQL-Oracle-statement) with SQLDeveloper (which is free to download and use) or TOAD or whatever. If you got it working in such a tool copy and paste it into your ESQL-Code and put in variables with CAST AS CHAR.
_________________
Just use REFERENCEs
Back to top
View user's profile Send private message
sankritya
PostPosted: Thu Feb 16, 2012 9:28 am    Post subject: Reply with quote

Centurion

Joined: 14 Feb 2008
Posts: 100

It is working in SQL Developer when I remove the Database and Schema name without using CAST as CHARACTER for Integer.
Back to top
View user's profile Send private message
mqsiuser
PostPosted: Thu Feb 16, 2012 9:37 am    Post subject: Reply with quote

Yatiri

Joined: 15 Apr 2008
Posts: 637
Location: Germany

sankritya wrote:
It is working in SQL Developer when I remove the Database and Schema name without using CAST as CHARACTER for Integer.


Great, solved! In ESQL you better use Cast + Formatpattern (if required) though.
_________________
Just use REFERENCEs
Back to top
View user's profile Send private message
adubya
PostPosted: Thu Feb 16, 2012 10:52 am    Post subject: Reply with quote

Partisan

Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK

sankritya wrote:
It is working in SQL Developer when I remove the Database and Schema name without using CAST as CHARACTER for Integer.


Try ditching the database name prefix and just using the schema + table name.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Need help on the use of PASSTHRU in WMB 6.1.0.4 and Oracle11
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.