| Author |
Message
|
| br_other |
 Posted: Wed May 05, 2010 5:30 am Post subject: ESQL INSERT .. SELECT |
 |
|
Newbie
Joined: 16 Mar 2010
Posts: 7
|
Dear friends,
I have the following problem and I hope that some of you with more experience will help me.
I'm trying to insert data in a table with ESQL into oracle table as the data is taken from local variable list.
| Code: |
INSERT INTO Database.ORACLEDB.SCH.TEST_TABLE
(
MSG_ID, XML_TYPE, STRUCT_ID, FIELD_VALUE
)
VALUES
(
SELECT RR.MSG_ID, RR.XML_TYPE, RR.STRUCT_ID, RR.FIELD_VALUE FROM Environment.Variables.SELECT_RES[] AS RR
); |
The problem is coming when I try to execute the flow:
| Code: |
Inconsistent number of values in VALUES clause.
The number of values specified in the VALUES clause does not match the number of columns specified. |
Dump of the variables looks like:
| Code: |
LocalEnvironment :
Environment : ( ['MQROOT' : 0x107e37260]
(0x01000000):Variables = (
...
...
(0x01000000):SELECT_RES = (
(0x03000000):MSG_ID = '1223684' (CHARACTER)
(0x03000000):XML_TYPE = 'VAL1' (CHARACTER)
(0x03000000):STRUCT_ID = 22 (DECIMAL)
(0x03000000):FIELD_VALUE = 'VAL2' (CHARACTER)
)
...
...
|
I'll really appreciate if you can advice me how to solve that, or at least some workaround how to store all that data from the local variable into the table at once.
Thank you in advance! |
|
| Back to top |
|
 |
| matuwe |
| Posted: Wed May 05, 2010 7:27 am Post subject: |
|
|
Master
Joined: 05 Dec 2007
Posts: 296
|
| MMMmmm I am not sure if BROKER can do that. I usually put this complex sql statement into a PASSTHRU, works like a charm. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 05, 2010 8:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Your select statement doesn't return what you think it does.
You don't need the select statement anyway, at least from what you have posted. |
|
| Back to top |
|
|
| br_other |
| Posted: Thu May 06, 2010 6:00 am Post subject: |
|
|
Newbie
Joined: 16 Mar 2010
Posts: 7
|
Hi people,
I did workaround with:
| Code: |
FOR XMLItem AS Environment.Variables.SELECT_RES[] DO
..
END FOR; |
but it's a sloppy solution and it's OK if there are couple of items to insert.
But I'm looking for more general solution. Is there any possibility to unload the whole content from the local variables into a table?
@matuwe
I agree that for more complex SQLs PASSTHRU is the way, but in this case, can you please advice how to organize the dynamic data from the local variables into regular string which is required for PASSTHRU execution?
Thank you in advance all! |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu May 06, 2010 9:22 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Put your command into a string
with the missing variable replaced by a "?"
Then call passthru with the variable after the command string
_________________
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 |
|
|
| mqjeff |
| Posted: Thu May 06, 2010 10:46 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Passthru will not allow for insertion of multiple rows at the same time.
Passthru will not allow for insertion of multiple values in a single column.
The original ESQL INSERT statement can not be made to work by using PASSTHRU instead. |
|
| Back to top |
|
|
| br_other |
| Posted: Fri May 07, 2010 12:02 am Post subject: |
|
|
Newbie
Joined: 16 Mar 2010
Posts: 7
|
| Well, guys, I understand the limitations of the PASSTHRU, but this does not answer my original question: Is there any possibility to store multiple rows in the same time, instead calling INSERT for every row? |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Fri May 07, 2010 1:20 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
|
| Back to top |
|
|
|
|
