E9.2 Exporting media object blobs with orchestrator

[Chad Thomas]

Hi. I am working to create an orchestration that can be used to extract records from F00165, including the blob data in GDTXFT. We have an external CPQ tool that we have integrated with Salesforce and perform a nightly export of configuration master data from JDE and pass to the CPQ tool. We are currently using MagicXPI for this nightly task, but are in the process of replacing MagicXPI with Workato and relying heavily on orchestrations for Workato to interact with JDE. We are running 9.2 on an AS400.

I'm using a database connector and Groovy script to execute an SQL statement to SELECT records from F00165. Below is the SQL statement.

SELECT
GDOBNM,
GDTXKY,
GDMOSEQN,
GDGTMOTYPE,
GDLNGP,
GDUSER,
GDUPMJ,
GDTDAY,
GDGTITNM,
GDQUNAM,
GDGTFILENM,
GDGTFUTS1,
GDGTFUTS2,
GDGTFUTS3,
GDGTFUTS4,
GDGTFUTM1,
GDGTFUTM2,
GDTXFT
FROM ${sDta}.F00165
JOIN ${sDta}.F554101 ON LEFT(GDTXKY,(LOCATE('|',GDTXKY)-1)) = IMITM
WHERE
(
GDOBNM='GT3291'
OR
GDOBNM = 'GT3292'
)
AND
LOCATE('|',GDTXKY) > 0


When I run the orchestration the output for GDTXFT does not contain the actual data. Note, I'm looking to return the raw binary data, not the converted readable text. Anyone know how to accomplish this, or if it's possible?

 

[DaveWagoner]

Use a Data Request component instead of the connector & groovy. Return the BLOB field that way, then you can parse the blob if it's text using a groovy script

 

[lfurino]

If you want to get the raw data you can use a REST connector to call one of the standard REST APIs. Here is the documentation on how to download a file and there is an example that shows the raw data output.

REST API for JD Edwards EnterpriseOne AIS Server

REST API for JD Edwards EnterpriseOne AIS Server

docs.oracle.com

Keep in mind, that you will not need to create a new connection, because connectors can call all of the APIs from the local AIS server.

 

[Luyendijkleo]

Use a Data Request component instead of the connector & groovy. Return the BLOB field that way, then you can parse the blob if it's text using a groovy script

Dave, when extracting BLOB data from F95631 (where BIP pdf's are store as compressed binary) it seems that the data is returned in Base64 format, However when I convert this back to PDF it fails. Any idea?

 

[DaveWagoner]

Ooh. With true binary files stored in BLOB, all bets are off if you're trying to do more than just download or upload existing ones. Most of my experience/posts on here regarding MO's involve simple text attachments.

To do exactly what you need you're probably talking about installing a vendor's java library to handle your conversions. Quite a rabbit hole

 

[Luyendijkleo]

Ooh. With true binary files stored in BLOB, all bets are off if you're trying to do more than just download or upload existing ones. Most of my experience/posts on here regarding MO's involve simple text attachments.

To do exactly what you need you're probably talking about installing a vendor's java library to handle your conversions. Quite a rabbit hole

It's not a problem to convert with groovy, but the issue is that when extracting the data from the BLOB field via a Data Request in Orchestrator it appears to automatically convert to Base64 - it is easy to convert Base64 back to PDF with groovy/java but in my case it won't open in pdf after conversion. Will have to research further.

e.g. sample of BLOB field as received by Orchestrator:
eJyMugN0Zs22NdxxR52kY9u2bdsdGx3btm3btm27Y9vml35P9z33/t+54/ufjJGnaq2queauqrWfWbU3oYyAECUtFQM0BCMODY6lrik0BAcHNAS1grOVAQ61irSuqYGe3Wdd3l7X7h+T6E8dI4NPg5CJuZ2BDQ61kLmOnYGAgZ6l/m+rhIGFkZ0xDgMrHc1nTdlE/7NCR8/4WRYxMDEytsOhpfnt4DOxs5UxsOG3/GllaWFgYYfD8mnktzS3tJG30tH7jCJg4GCiZyAnzAcNwcUFDWFrZ2Og8xMawintTAP8h8JiO6hArmgKSmreT82VxP38hbaC+kqT2IXEaJ3qCtncgaLppAEByVLnpBLaeRp0n9aveMC8tCQD7siwjj944BD04WSAFWAFlAzQmy7Y6f16xtodz8fSk9m7njw+Mrn7bSc5D9g33jI/VHVQIP7zx1okcyTtBbFVI0FcgwUCQj4LsMB

 

[DaveWagoner]

Ahh! I thought you were trying to modify & regenerate PDFs which led to my custom lib comment..

My "use data request" works for text attachments but I think I found the same as you that for non-text BLOB attachments it doesn't quite work right. Perhaps try using Larry's suggestion of the Media Obj API via REST Connector in this case?