| 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 |
|
|
|
|
