Hi All,

We have users entering text information into an attachment field of a sales order. The data is then stored in a BLOB field with RTF tags. I want to get this text and use it in a report.

What I've done (and I'm not saying this is good) is to read the BLOB field into a A4096V field. It appears that the output field contains the data as a continuous character string where each sequential 2 characters represent a hexadecimal value.

I want to convert the string to legible text and locate some marker text somewhere within the string, extract a substring of the information that the user entered until I hit some ending marker text.

So the first part of this string looks like this:
7B005C0072007400660031005C006100

And after loading into a hex-editor it looks like this:
{ \ r t f 1 \ a

Eventually you get to the user-entered text of "no charge sales error".

I've searched but found nothing about converting a hexadecimal value to an ascii character. And reading/parsing such a large string without arrays? I'm stumped.

Anyone have any ideas on how to do this beyond writing a Business Function within J.D. Edwards?

Thanks,

Norb

Is your app server on a mainframe or a ascii system? Reason I ask is we had real issues with hex conversion to ascii code in that instance due to WebFOCUS converting the data first coming down in a character string that really had packed decimal data and we never could get it to process correctly.

I wonder if CTRAN would help you. I think that's the function name? I'm home and nowhere near manuals at the moment.

Hi Leah,

I found some posts from Mickey Grackin that discussed TX types, AxxV types, loops etc. so I did some power futzing. Lo and behold, I got everything to work for me. The only stumper is that I cannot find the position of a substring longer than 1 character. When I changed the data type from A4096V to TX250 the data was coming in translated from hex to ascii text but in chunks. Then I read the data in, looped, extracted substrings and got the text I wanted. Because this data is really RTF text I need to test more with different fonts, colors etc. but I'm pretty excited. Woo Hoo!

Here's the details for anyone that's interested:

In the MFD the field was defined as FIELDNAME=GDTXFT, ALIAS=GDTXFT, USAGE=BLOB, ACTUAL=BLOB,
MISSING=ON, $

I changed it to FIELDNAME=GDTXFT, ALIAS=GDTXFT, USAGE=TX250, ACTUAL=TX,
MISSING=ON, $

Here is the code:

DEFINE FILE F00165
NJETXT/A1000=GDTXFT;
END
TABLE FILE F00165
PRINT NJETXT
WHERE GDOBNM EQ 'GT4211A';
WHERE GDTXKY CONTAINS '43130';
WHERE GDGTMOTYPE EQ 0;
ON TABLE HOLD AS TESTBLOB FORMAT ALPHA
END
-RUN

-BEGLOOP
-READ TESTBLOB &VBLOB.A1000.
-IF (&IORETURN NE 0) GOTO ENDLOOP;

-* NOTE: For some reason I cannot locate a substring longer than 1 char. So I can't zero directly into where the text is located. This doesn't work: -SET &STIDX = POSIT(&VBLOB, VBLOB.LENGTH, 'fs24', 4, 'I2'); It only returns zero. "Why Santy Claus? Why?"

-* find the position of a '4' in the string.
-SET &STIDX = POSIT(&VBLOB, &VBLOB.LENGTH, '4', 1, 'I2');
-* didn't find one? go get another record
-IF &STIDX EQ 0 THEN GOTO BEGLOOP;
-* get rid of any trailing spaces
-SET &TEMP = TRUNCATE(&VBLOB);
-* get the remaining text from where we found the '4'
-SET &SUBTEXT = SUBSTR(&TEMP.LENGTH, &TEMP, &STIDX, &TEMP.LENGTH, (&TEMP.LENGTH - &STIDX + 1), 'A500');

-* find the position of ' ' in the substring.
-SET &STIDX = POSIT(&SUBTEXT, 500, ' ', 1, 'I2');
-* get the remaining text from where we found the ' ' after we truncate it
-SET &TEMP = TRUNCATE(&SUBTEXT);
-SET &SUBTEXT = SUBSTR(&TEMP.LENGTH, &TEMP, &STIDX, &TEMP.LENGTH, (&TEMP.LENGTH - &STIDX + 1), 'A500');

-* find the position of '\' in the substring.
-SET &STIDX = POSIT(&SUBTEXT, 500, '\|', 1, 'I2');
-* new get the text from the start to where we found the '\' after we truncate the string
-SET &TEMP = TRUNCATE(&SUBTEXT);
-SET &SUBTEXT = SUBSTR(&TEMP.LENGTH, &TEMP, 1, &TEMP.LENGTH, &STIDX - 1, 'A500');
-TYPE &SUBTEXT

-GOTO BEGLOOP
-ENDLOOP

Here is the source data:

{ \ r t f 1 \ a n s i \ a n s i c p g 1 2 5 2 \ d e f f 0 \ d e f l a n g 1 0 3 3 { \ f o n t t b l { \ f 0 \ f s w i s s \ f p r q 2 \ f c h a r s e t 0 S y s t e m ; } } { \ c o l o r t b l ; \ r e d 0 \ g r e e n 0 \ b l u e 0 ; } \ v i e w k i n d 4 \ u c 1 \ p a r d \ c f 1 \ f 0 \ f s 2 4 n o c h a r g e s a l e s e r r o r . j . t 4 / 1 2 \ p a r } %$%

I extracted the phrase "no charge sales error. j.t 4/12"

Cool huh?

Thanks,

Norb

So cool Norb, that my keyboard froze this morning and logged me out of the "point"!

Just goes to show that if you apply your mind you can achieve what you require.

T