E9.2 Converting Attachments and Placing Them Back

[BookClub]

Hello! I recently converted all of my attachments from RTF to HTML. I did this by pulling the GDTXFT field from F00165 using SQL and changing it from an image into readable RTF text like so:
cast(cast(F00165.GDTXFT as varbinary(max)) as nvarchar(max))
I then changed the RTF as needed to make it HTML. Now I am trying to pass this new code back into the table but my attempts have resulted in random characters. Has anyone had any experience of passing records like these back into image data types in JD Edwards? I've tried manually converting the text back into an image as well as just passing in the HTML text. The results are always an image data type but its always Chinese characters. Any help is much appreciated!

 

[Larry_Jones]

Always include your environment in your signature so we know the context of your question. I can infer from your SQL you're on SQL Server - but which version?

Attached file has the SQL to create a SQL Server Stored Procedure which accepts as input the MO Key values and a nvarchar(max ) string.
Some changes may be necessary to specify you database name, etc.

Pass those values and it will update the original MO Text item using the key fields you provide.

 

[BookClub]

Always include your environment in your signature so we know the context of your question. I can infer from your SQL you're on SQL Server - but which version?

Attached file has the SQL to create a SQL Server Stored Procedure which accepts as input the MO Key values and a nvarchar(max ) string.
Some changes may be necessary to specify you database name, etc.

Pass those values and it will update the original MO Text item using the key fields you provide.

Thanks for this reply! I still seem to be having the same problem.
I'm updating the GDTXFT field of F00165 based on the key values fine but I'm still getting Chinese characters.
Here is an example of what I have and what is happening:
SET @textbinary = cast('<pre> Sample text <br> </pre>' as varbinary(max))
UPDATE F00165 set GDTXFT [email protected] WHERE GDOBNM = 'ABGT' and gdtxky = '100003' and GDMOSEQN = '1'
When I pull that record in JDE I see this:

Any idea what could be going on here? Thanks again!

 

[Larry_Jones]

Seems you left out a few things - such as

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(F00165.GDTXFT) FROM f00165
WHERE gdobnm = @moObjectName and GDTXKY = @moTextKey and [email protected]
UPDATETEXT F00165.GDTXFT @ptrval 0 Null @textbinary;

That SQL is written that way for a reason.

This will work better for you if you just follow the example and use the Stored Procedure

 

[rival]

Always include your environment in your signature so we know the context of your question. I can infer from your SQL you're on SQL Server - but which version?

Attached file has the SQL to create a SQL Server Stored Procedure which accepts as input the MO Key values and a nvarchar(max ) string.
Some changes may be necessary to specify you database name, etc.

Pass those values and it will update the original MO Text item using the key fields you provide.

Hi larry, awesome that you share this with us. I will try it with our sql specialist. Unfortunately I am not very familiar with it. How do I execute the procedure and how do I need the text in to convert the existing RTF to HTML.

Second question, does this script handle the special characters? The JDE conversion batch or application cannot handle the special characters.

 

[Larry_Jones]

Assuming you create the Stored Procedure above, using it in your SQL is like this

EXECUTE JDE_MO_TEXT_UPDATE 'GT4101', '108670', 1, N'THIS BOM INCLUDES A FILTER, 106748, THAT IS OBSOLETE.<br>THE OIL REGULATOR THAT THIS FILTER WAS ASSEMBLED INTO<br>NOW COMES WITH THIS FILTER AS PART OF THE REGULATOR<br>ASSEMBLY, 139616. 3-18-15 BSC<br>';

You can replace the literal text with your nvarchar(max) variable that contains your converted text. The other Arguments identify what your updating

• Object Name - 'GT4101'
• MO Key - '108670' - the specific key values(s) that identify the owner. In this case the Short Item Number.
• Seq No - 1 - If there are multiple Media Objects associated to a specific owner then the sequence number makes them unique.

No this does not do any manipulation of the MO Text for you. The assumptions here is that:
1. You already converted the binary blob values into a NVARCHAR(max) variable (which if still RTF format would contain all the embedded RTF commands also).
2. You performed whatever conversion was needed to go to plain text or html text or hybrid of the two.
3. You use the JDE_MO_TEXT_UPDATE SP to replace the original text with your converted text.

 

[rival]

thx Larry, i will give it a try.

 

[BookClub]

Thanks for the correction Larry. This stored procedure is working brilliantly. Cheers.

 

[Alex_Pastuhov]

Convert RTF to HTML the Right Way:


Our new tool - RTF2HTML

 

[Larry_Jones]

Convert RTF to HTML the Right Way:

View attachment 19239
Our new tool - RTF2HTML

Very Nice! Wish that was around 3-4 years ago!