| Author |
Message
|
| Diwakar |
 Posted: Mon Feb 14, 2011 8:17 pm Post subject: executing an INSERT via PASSTHRU into Oracle |
 |
|
Apprentice
Joined: 06 Apr 2005
Posts: 33
|
Hi people,
I have an SQL statement that works fine in SQL Developer.
It executes fine in MB v7.0.0.1 w/ MQ v7.0.1 when I supply hardcoded values via a PASSTHRU.
But I need to pass variables (from input xml) to the same insert statement. I am struggling to do that. I haven't been able to get it to work so far.
Below is the SQL statement. It first checks if a record exists in the database and inserts a record only if it doesn't already exist.
-----------------------------------------------------------------------------
Insert into IFD Select SEQ_IFD_KY.nextval,
?,
NULL, ?, ?,
?,?
FROM dual
where NOT exists ( SELECT 1 from IFD where IFD_fileName = ?)
--------------------------------------------------------------------------------
The sql statement above works perfectly in SQL developer when I pass hardcoded values. How to pass variable values to it in WMB v7 is my question. I need to pass variable values from my input xml into all the fields represented by the character '?' in the above SQL.
Can someone please post a sample SQL insert statement (that has a nested SELECT) that works via a PASSTHRU or otherwise ?
_________________
Diwakar. |
|
| Back to top |
|
 |
| Diwakar |
| Posted: Mon Feb 14, 2011 8:26 pm Post subject: |
|
|
Apprentice
Joined: 06 Apr 2005
Posts: 33
|
Here is the SQL that works in WMB v7 via a PASSTHRU with hardcoded values in place of ? above:
-----------------------------------------------------------------------------
PASSTHRU('Insert into IFD Select SEQ_IFD_KY.nextval,
1,
NULL, ''PROCESSING'', ''file_name123'',
to_date(''05-FEB-11'', ''DD-MON-RR''),''I''
FROM dual
where NOT exists ( SELECT 1 from IFD where IFD_fileName = ''file_name123'')'
);
--------------------------------------------------------------------------------
Every single quote above is escaped with another single quote. So you see pairs of single quotes everywhere above. How should I pass variables or fields from input-xml to the above SQL in WMB, in place of hard-coded values ? Please suggest with the precise syntax.
Thanks.
_________________
Diwakar. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Feb 14, 2011 10:26 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have you searched this forum for hints on how to do this?
They are there in abundance.
_________________
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 |
|
|
| Diwakar |
| Posted: Mon Feb 14, 2011 10:33 pm Post subject: |
|
|
Apprentice
Joined: 06 Apr 2005
Posts: 33
|
Hi
I was able to get the syntax correct. I deployed the msgflow.
And when a sample xml was sent, I saw the following exception in broker, but NO clue what it means (as the SQL works fine in SQL developer):
he message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2011-02-15 01:26:37.375167 3872 DatabaseException BIP2322E: Database error: SQL State ''HY000''; Native Error Code '936'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression''.
The error has the following diagnostic information: SQL State ''HY000'' SQL Native Error Code '936' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
_________________
Diwakar. |
|
| Back to top |
|
|
| Diwakar |
| Posted: Tue Feb 15, 2011 8:08 am Post subject: |
|
|
Apprentice
Joined: 06 Apr 2005
Posts: 33
|
As far as I know my SQL statement above can be done in WMB-ESQL only via a PASSTHRU.
Because of its nested structure I can't use the SQL in an EVAL statement.
Am I correct ?
Thanks
_________________
Diwakar. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 15, 2011 8:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You can do nested selects in general in ESQL.
Whether you can do this specific one or not, I won't speculate.
The Oracle error you last posted says that you didn't put the right things into the right parameters or that you didn't make the parameterized version syntactically correct. At least, as far as I read it. |
|
| Back to top |
|
|
|
|
