B2B Integration

IBM Sterling B2B Integrator Version: 6.1.0.0

MSSQL Server 15, 2019

We're trying to display the actual EDI X12 payload to end users in a report. It looks like this is stored as an image data-type in the TRANS_DATA table. The column is DATA_OBJECT. Is there a way to convert this to a string or nvarchar datatype? It seems like it may be encrypted and compressed. I've tried decompress, but it still shows unreadable characters. Has anyone tried to do this? 

SELECT DATA_OBJECT
, DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX))) AS UNCOMPRESS_TEST
, convert(nvarchar(max), DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX)))) AS HUMAN_READABLE
from DOCUMENT
JOIN TRANS_DATA ON DATA_ID = BODY
WHERE DOC_ID = 'VS-GIS2k21:node1:18b59339e31:18303339';

You're pretty close !   Next step is to decode the base64.
This works for pulling data from the mailbox:

SELECT 
    CONVERT
    (   VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS mbx_message
FROM
    (
        SELECT decompress(cast(DT.DATA_OBJECT as varbinary (max))) as BASE64_COLUMN
        FROM MBX_MESSAGE MM (nolock), DOCUMENT D (nolock), TRANS_DATA DT  (nolock)
        WHERE (MM.DOC_ID = D.DOC_ID) AND (D.BODY = DT.DATA_ID) AND (MM.MESSAGE_ID = 812494   ) 
        AND DT.PAGE_INDEX = 0
    ) t

IBM Sterling B2B Integrator Version: 6.1.0.0

MSSQL Server 15, 2019

We're trying to display the actual EDI X12 payload to end users in a report. It looks like this is stored as an image data-type in the TRANS_DATA table. The column is DATA_OBJECT. Is there a way to convert this to a string or nvarchar datatype? It seems like it may be encrypted and compressed. I've tried decompress, but it still shows unreadable characters. Has anyone tried to do this? 

SELECT DATA_OBJECT
, DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX))) AS UNCOMPRESS_TEST
, convert(nvarchar(max), DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX)))) AS HUMAN_READABLE
from DOCUMENT
JOIN TRANS_DATA ON DATA_ID = BODY
WHERE DOC_ID = 'VS-GIS2k21:node1:18b59339e31:18303339';

Hi Dan,

Thanks for the response and sorry for the late reply. I was out of the office. 

I plugged in my query to yours and it returns the below result.

¼Ý sr java.util.HashMap┌┴├`Ð F  loadFactorI     thresholdxp?@     w      t nodenamet  VS-GIS2k21t filenamet SD:\EdiDocs\2023\December\0342\VS-GIS2k21_node1_18c31b86bdf_50981314_WF120000466.datt typesr java.lang.IntegerÔáñ¸üç8
I valuexr java.lang.Numberå¼ò öÓï  xp  
x

I am looking for the EDI body, which I've highlighted in the below screenshot. Is this possible?

You're pretty close !   Next step is to decode the base64.
This works for pulling data from the mailbox:

SELECT 
    CONVERT
    (   VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS mbx_message
FROM
    (
        SELECT decompress(cast(DT.DATA_OBJECT as varbinary (max))) as BASE64_COLUMN
        FROM MBX_MESSAGE MM (nolock), DOCUMENT D (nolock), TRANS_DATA DT  (nolock)
        WHERE (MM.DOC_ID = D.DOC_ID) AND (D.BODY = DT.DATA_ID) AND (MM.MESSAGE_ID = 812494   ) 
        AND DT.PAGE_INDEX = 0
    ) t

IBM Sterling B2B Integrator Version: 6.1.0.0

MSSQL Server 15, 2019

We're trying to display the actual EDI X12 payload to end users in a report. It looks like this is stored as an image data-type in the TRANS_DATA table. The column is DATA_OBJECT. Is there a way to convert this to a string or nvarchar datatype? It seems like it may be encrypted and compressed. I've tried decompress, but it still shows unreadable characters. Has anyone tried to do this? 

SELECT DATA_OBJECT
, DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX))) AS UNCOMPRESS_TEST
, convert(nvarchar(max), DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX)))) AS HUMAN_READABLE
from DOCUMENT
JOIN TRANS_DATA ON DATA_ID = BODY
WHERE DOC_ID = 'VS-GIS2k21:node1:18b59339e31:18303339';

I'm guessing it's a disconnect between using DB storage vs FS Storage, especially with the path details in your screenshot.
If the core contents of the file are on the file system, a DB query won't be able to pull the data.

We use DB storage and this returned fine:

I failed to explain why PAGE_INDEX was included.

Files are broken into chunks (I believe it's 2MB chunks, but can't remember this morning).  
If it is a small file, all the data will be in PAGE_INDEX zero.
Larger files would have to be appended together through each PAGE_INDEX row.

I'd suggest temporarily changing to DB Storage and see what happens.

Hi Dan,

Thanks for the response and sorry for the late reply. I was out of the office. 

I plugged in my query to yours and it returns the below result.

¼Ý sr java.util.HashMap┌┴├`Ð F  loadFactorI     thresholdxp?@     w      t nodenamet  VS-GIS2k21t filenamet SD:\EdiDocs\2023\December\0342\VS-GIS2k21_node1_18c31b86bdf_50981314_WF120000466.datt typesr java.lang.IntegerÔáñ¸üç8
I valuexr java.lang.Numberå¼ò öÓï  xp  
x

I am looking for the EDI body, which I've highlighted in the below screenshot. Is this possible?

You're pretty close !   Next step is to decode the base64.
This works for pulling data from the mailbox:

SELECT 
    CONVERT
    (   VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS mbx_message
FROM
    (
        SELECT decompress(cast(DT.DATA_OBJECT as varbinary (max))) as BASE64_COLUMN
        FROM MBX_MESSAGE MM (nolock), DOCUMENT D (nolock), TRANS_DATA DT  (nolock)
        WHERE (MM.DOC_ID = D.DOC_ID) AND (D.BODY = DT.DATA_ID) AND (MM.MESSAGE_ID = 812494   ) 
        AND DT.PAGE_INDEX = 0
    ) t

IBM Sterling B2B Integrator Version: 6.1.0.0

MSSQL Server 15, 2019

We're trying to display the actual EDI X12 payload to end users in a report. It looks like this is stored as an image data-type in the TRANS_DATA table. The column is DATA_OBJECT. Is there a way to convert this to a string or nvarchar datatype? It seems like it may be encrypted and compressed. I've tried decompress, but it still shows unreadable characters. Has anyone tried to do this? 

SELECT DATA_OBJECT
, DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX))) AS UNCOMPRESS_TEST
, convert(nvarchar(max), DECOMPRESS(CAST(DATA_OBJECT AS VARBINARY(MAX)))) AS HUMAN_READABLE
from DOCUMENT
JOIN TRANS_DATA ON DATA_ID = BODY
WHERE DOC_ID = 'VS-GIS2k21:node1:18b59339e31:18303339';