MQSeries.net :: View topic

ARAMARK · Posted: Fri Sep 07, 2007 9:47 am Post subject:

Hi,
I have developed a message flow which will connect to a database and retrieve data from a table and put it as xml message in the output queue.I have configured the DSN through ODBC and had also given the DataSourceName in Compute Node Properties.But,I am not able to retrieve data from the table.My ESQL code is as below:

CREATE COMPUTE MODULE msg_flowSep05_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
CALL CopyEntireMessage();
RETURN TRUE;
END;

CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER;
DECLARE J INTEGER;
SET I = 1;
SET J = CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
DELETE FIELD OutputRoot.*[<];
SET OutputRoot.XML.Test.Result[] =
(SELECT T.Column1, T.Column2 FROM Database.DataSourceName.USERTABLE AS T);

END;
END MODULE;

But,I am getting the exception as Unable to resolve identifiers as T.Column1 and unable to resolve identifiers as Database.DataSourceName.USERTABLE AS T etc etc.
When I am replacing the database name as SAMPLE .i.e.(SELECT T.Column1, T.Column2 FROM SAMPLE.Test.USERTABLE AS T), where SAMPLE is the database alias name and Test is the DSN,then also it is throwing error
Please advice if anything is wrong in the above code or have I missed something to configure WBIMB in order to connect to remote database.