| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Insert into database |
« View previous topic :: View next topic » |
| Author |
Message
|
| rkford11 |
 Posted: Thu Oct 28, 2004 11:09 am Post subject: Insert into database |
 |
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
I have created an xml message from the database using
SET OutputRoot.XML.MESSAGE.A[] = (SELECT T.* FROM Database.FIRST.TABLE1 as T);
which brings all my columns in the database to the message.
I have same columns in another database, now how do i insert this message into second database.
Thanks |
|
| Back to top |
|
 |
| kirani |
| Posted: Thu Oct 28, 2004 11:30 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Loop thru the XML tree and then use INSERT statement to insert this data into second table. For example,
| Code: |
DECLARE I INT 1;
DECLARE TOTROWs INT;
SET TOTROWS = CARDINALITY(InputRoot.XML.MESSAGE.A[]);
WHILE ( I<= TOTROWS) DO
INSERT INTO Database.Table1(col1, col2, col3) VALUES (InputRoot.XML.MESSAGE.A[I].Col1, InputRoot.XML.MESSAGE.A[I].Col2, InputRoot.XML.MESSAGE.A[I].Col3)
SET I = I + 1;
END WHILE;
|
For faster performance you can modify this code to use REFERENCES to loop the tree.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| rkford11 |
| Posted: Thu Oct 28, 2004 12:49 pm Post subject: |
|
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
| kirani wrote: |
Loop thru the XML tree and then use INSERT statement to insert this data into second table. For example,
| Code: |
DECLARE I INT 1;
DECLARE TOTROWs INT;
SET TOTROWS = CARDINALITY(InputRoot.XML.MESSAGE.A[]);
WHILE ( I<= TOTROWS) DO
INSERT INTO Database.Table1(col1, col2, col3) VALUES (InputRoot.XML.MESSAGE.A[I].Col1, InputRoot.XML.MESSAGE.A[I].Col2, InputRoot.XML.MESSAGE.A[I].Col3)
SET I = I + 1;
END WHILE;
|
For faster performance you can modify this code to use REFERENCES to loop the tree. |
I have two different databases but not tables in a database. so using two compute nodes one followed by another,
in the first compute node i am retreiving the record from database_1 using SET command as described above and in the
second compute node i am trying to insert the retrived data. do i have to do the same coding.
is there a way to access two databases using compute node only once. how can i use database or datainsert node in this situatuion.
Thanks |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Oct 28, 2004 1:27 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
You cannot connect to two ODBC connections from single node, so you need to split your processing into two nodes. In first node you will retrieve the data from first DB and store into Environment/Output/LocalEnvironment tree. In the second node you can retrieve the values from the Tree and insert them into second table.
Instead of Compute node you can also use DB node to do this.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| rkford11 |
| Posted: Thu Oct 28, 2004 5:45 pm Post subject: |
|
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
| kirani wrote: |
You cannot connect to two ODBC connections from single node, so you need to split your processing into two nodes. In first node you will retrieve the data from first DB and store into Environment/Output/LocalEnvironment tree. In the second node you can retrieve the values from the Tree and insert them into second table.
Instead of Compute node you can also use DB node to do this. |
Thanks Kirani, your feed back really helped me a lot! |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
