MQSeries.net :: View topic

jayZ · Posted: Wed Jan 16, 2013 6:30 am Post subject:

I am attempting to call a submap and then assign the result of that submap to an email attachment and then to a file output. However, every time I try to actually use or manipulate the result of the map, I get an error. Here is my latest attempt (email commented out to help debug):

[code] DECLARE xmlData ROW;
DECLARE encoding INT InputRoot.Properties.Encoding;
DECLARE ccsid INT 1208;
DECLARE test CHAR;
DECLARE btest BLOB;
DECLARE outData ROW;
DECLARE recordGroupType CHAR;
DECLARE cnt INT 0;
DECLARE fileName CHAR 'dlreligibility';
DECLARE fileExt CHAR '.xls';
DECLARE fileDate CHAR CAST(CURRENT_TIMESTAMP AS CHAR FORMAT 'YYYYMMDD');
DECLARE env REFERENCE TO Environment;
DECLARE xmlStrData CHAR;

SET env.DataResults = PASSTHRU(REPLACE(SQLQUERYDATA, TRANDSCSEARCHTEXT, env.tranDscID));

DECLARE data REFERENCE TO InputRoot.XMLNSC.ns:ExecuteTransmission.ns:transmission.ns:Request.ns:TransmissionRecords.ns:TransmissionRecordGroup[1];

WHILE LASTMOVE(data) DO

SET recordGroupType = data.(XMLNSC.Attribute)Type;
CREATE LASTCHILD OF outData DOMAIN('XMLNSC');
CREATE LASTCHILD OF outData.XMLNSC NAMESPACE ns1 NAME 'Workbook';
DECLARE refOutData REFERENCE TO outData.XMLNSC.ns1:Workbook;

IF (recordGroupType = TYADLRDATA) THEN
SET cnt = cnt + 1;
SET xmlStrData = data.ns:Data.ns:string;
CREATE LASTCHILD OF xmlData DOMAIN ('XMLNSC') PARSE(CAST(xmlStrData AS BLOB encoding ccsid), encoding, ccsid, 'BA752KG002001', 'VFPData');
DECLARE refInData REFERENCE TO xmlData.XMLNSC.VFPData;
CALL MM_TRANS_CUSTOMER_OUTBOUND_TOYOTA_DLRE(refInData, refOutData, InputLocalEnvironment);

/*
--Email the File
SET OutputRoot.Properties = InputProperties;
SET OutputLocalEnvironment = InputLocalEnvironment;
SET OutputLocalEnvironment.Destination.RouterList.DestinationData[1].labelName = EMAIL;
SET OutputRoot.EmailOutputHeader.To = TRIM(THE(SELECT ITEM(D.DESTINATION_VALUE) FROM env.DataResults[] as D WHERE UPPER(D.DATA_TYPE_NAME) = UPPER(EMAILTO)));
SET OutputRoot.EmailOutputHeader.From = EMAILFROM;
SET OutputRoot.EmailOutputHeader.Subject = EMAILSUBJ;
SET OutputLocalEnvironment.Destination.Email.Attachment.ContentType = 'application/octet-stream';
SET OutputLocalEnvironment.Destination.Email.Attachment.Content = BASE64ENCODE(outData);
SET OutputLocalEnvironment.Destination.Email.Attachment.ContentName = fileName || fileExt;
SET OutputLocalEnvironment.Destination.Email.Attachment.ContentEncoding = 'Base64';
SET OutputRoot.BLOB.BLOB = CAST('' AS BLOB CCSID ccsid);
PROPAGATE;
*/
--FTP the File
SET OutputRoot.Properties = InputProperties;
SET OutputLocalEnvironment = InputLocalEnvironment;
SET OutputLocalEnvironment.WrittenDestination.File.Name = fileName || fileDate || fileExt;
SET OutputLocalEnvironment.Destination.RouterList.DestinationData[1].labelName = FTP;
CREATE LASTCHILD OF OutputRoot DOMAIN('XMLNSC');
SET OutputRoot.XMLNSC.(XMLNSC.XmlDeclaration)*.(XMLNSC.Attribute)Version = '1.0';
SET OutputRoot.XMLNSC.(XMLNSC.XmlDeclaration)*.(XMLNSC.Attribute)"mso-application progid" = 'Excel.Sheet';
CREATE LASTCHILD OF OutputRoot.XMLNSC NAMESPACE ns1 NAME 'Workbook';
SET OutputRoot.XMLNSC.ns1:Workbook = outData.XMLNSC.ns1:Workbook;
PROPAGATE;[/code]

I should also note that my message sets define VFPData and Workbook as the source and target, respectively.

So far, my errors have focused on how I declare the row and OutputRoot.XMLNSC elements. I have tried:

1) Creating OutputRoot.XMLNSC/OutputRoot.XMLNSC.ns1:Workbook and then passing that reference into the map. This solution wouldn't help me with the email even if it did work (which it didn't).
2)Cast result of map as BLOB and assign it to OutRoot.BLOB.BLOB. CAST fails - unexpected foldertype
3)Cast result ASBITSTREAM and assign to OutRoot.BLOB.BLOB - Same error as #2
4) For email, BASE64ENCODING the result of the map
5) Sending the exact result of the map, unmanipulated (Resulting attachment shows no data)
6)More derivations/combinations of the above

It should be noted that I am creating an Excel Spreadsheet with this data. So, the formatting is important. I'd appreciate any insight.

Thanks