Author |
Message
|
sankritya |
Posted: Thu Feb 16, 2012 6:35 am Post subject: Need help on the use of PASSTHRU in WMB 6.1.0.4 and Oracle11 |
|
|
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 |
|
 |
smdavies99 |
Posted: Thu Feb 16, 2012 7:06 am Post subject: |
|
|
 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 |
|
 |
vijsam |
Posted: Thu Feb 16, 2012 7:09 am Post subject: |
|
|
Apprentice
Joined: 01 Jun 2011 Posts: 46
|
|
Back to top |
|
 |
sankritya |
Posted: Thu Feb 16, 2012 7:20 am Post subject: |
|
|
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 |
|
 |
vijsam |
Posted: Thu Feb 16, 2012 7:26 am Post subject: |
|
|
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 |
|
 |
sankritya |
Posted: Thu Feb 16, 2012 7:46 am Post subject: |
|
|
Centurion
Joined: 14 Feb 2008 Posts: 100
|
@vijsam : Thanks for the help .. but still the result is same.  |
|
Back to top |
|
 |
smdavies99 |
Posted: Thu Feb 16, 2012 8:01 am Post subject: |
|
|
 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 |
|
 |
sankritya |
Posted: Thu Feb 16, 2012 8:19 am Post subject: |
|
|
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 |
|
 |
mqsiuser |
Posted: Thu Feb 16, 2012 8:23 am Post subject: |
|
|
 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 |
|
 |
sankritya |
Posted: Thu Feb 16, 2012 9:28 am Post subject: |
|
|
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 |
|
 |
mqsiuser |
Posted: Thu Feb 16, 2012 9:37 am Post subject: |
|
|
 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 |
|
 |
adubya |
Posted: Thu Feb 16, 2012 10:52 am Post subject: |
|
|
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 |
|
 |
|