Convert a COMP-3 (signed) field to ASCII

I'm not familiar with the Cache language/db but am thoroughly familiar with the class of storage formats loosely labeled COMP-3 (I call this a class of formats because there is no actual standard definition of COMP-3 other than "the most efficient way to store/process" integers for the COBOL compiler which usually creates/uses the data.  COMP-3 data storage format originated with IBM back in the 1960's and their then new 360 series of mainframe computers.

Generally speaking, COMP-3 = "packed decimal".  Integer values are usually stored as 2-digits per byte with the rightmost half of the last byte containing a bit-pattern representing a "sign".  Interchanging packed decimal data among different programs and/or different platforms can present some interesting problems... this is usually a result of "big endian" architecure vs. "little endian" where the byte-orders of the storage formats are opposite each other.

I noticed that Cache provides 2 versions of the "unpack" function... one for each architecture.  Your problem might be a simple as using the alternate version for the file in which the current code doesn't work... but, after looking at the attached file sample, I doubt it.

The 3 fields in the 3 records don't contain legitimate values to represent a packed decimal value... unless these fields are in some encoding other than ASCII.  If the file was originally produced on an EBCDIC platform then "translated" via ftp or other mechanisms then that might explain the problem.  Binary data (and packed decimal is a binary-type) has no "encoding" and should look the same on any platform.  Translating an entire file would also translate the packed decimal fields and corrupt them.  It is usually possible to "reverse" translate just these fields to recover their original bit-patterns but that is a fairly significant (but simple in the right programming language) coding effort.

I'd recommend examining the datafile with a "hex" editor/viewer to confirm which byte-order these files are using. You should especially compare the files that work with those that don't.  You may be able to determine the cause of the problem and, if it is a "procedural" issue between you and your client, get the procedure changed.

Regards,
Lynn

Accept Multiple Solutions Accept as Solution

   

 ID:21434625Author:SRG041808Date:04.24.2008 at 03:04PM ESTYour Comment  


Is this what you were looking for?Yes No
SRG041808:
I am looking into your suggestion that the data does not contain legitimate values.  I do Not believe that it is EBCDIC - as the remainder of the fields (non comp-3 fields) are in ASCII (I can read them - as opposed to EBCDIC which looks like just a bunch of random characters when "viewed".))

In the meantime, what algorithm would you use to convert a legitimate COMP-3 value to Ascii (assuming the sign in the right most digit)?Accept and Award Points Accept as Solution

   

 ID:21434803Author:JesterTooDate:04.24.2008 at 03:26PM ESTExpert Comment  


Rank: Guru
Is this what you were looking for?Yes No
JesterToo:
I will need to mock up a function in either pseudo-code or a real programming language... which languages would work for you?  I have existing code written in Clipper (an xBase language) somewhere that I could provide... will have to dig it out of my archives tonight/tomorrow, though.

Is there any possibility this file was originally created as EBCDIC then translated into ASCII?  It certainly shows some evidence of that.  If that is the case, then simply reverse translating the packed decimal fields should work (I've had to do that with client files in the past).  I'll examine that possibility more thoroughly tonight after I leave the office.

LynnAccept Multiple Solutions Accept as Solution

   

 ID:21434938Author:SRG041808Date:04.24.2008 at 03:42PM ESTYour Comment  


Is this what you were looking for?Yes No
SRG041808:
pseudo-code would probably be best -- The only language I've ever coded in was Mumps/Cache... and I've been doing a little bit of VB.  

It's possible that the file was created in EBCDIC (but I don't know).  I have sent an email to the client explaining my dilema (along with some of your comments) - so if this is the case I'm sure they'll let me know.

My exposure has really only ever been with ASCII files.  I have one client who sends me EBCDIC data with COMP-3 data which I convert every week.  (The code I had above is used to convert the COMP-3 values and seems to work without issue). I've read so much in the last couple of days about how COMP-3s may be different depending on the system they came from -- that I didn't want to go straight back to the client telling them their data is wrong.  (I didn't even do that now -- just let them know that I was having trouble with it.)  Hopefully they'll reply soon on how the data looks to them and I'll have better info to supply to you.

I also sent them the attachment you looked at to show them what I'm working with.  The original source was encrypted on their end with GNU-PG and then unencrypted on our end.  They have used this method with their other vendors ( without issue) -- but I asked them to validate the data to confirm that some corruption did not occur either during FTP transmission or Encryption/Unencryption.Accept and Award Points Accept as Solution

   

 ID:21435040Author:JesterTooDate:04.24.2008 at 03:54PM ESTExpert Comment  


Rank: Guru
Is this what you were looking for?Yes No
JesterToo:
Sounds good.  Do you happen to know what the correct ASCII values for these 3 fields in the 3 records should be?  That would probably aid in figuring out how to solve the problem.Accept Multiple Solutions Accept as Solution

   

 ID:21439719Author:JesterTooDate:04.25.2008 at 09:10AM ESTExpert Comment  


Rank: Guru
Is this what you were looking for?Yes No
JesterToo:
If you can confirm the accuracy of the following data I can provide you an algorithm that
will reproduce it.  Note:  the chart at the bottom of this message aligns better if you view
or print it using a non-proportional font.

The columns labeled HEX contain the hexadecimal representation of the bytes in your sample
file.  The UNPACKED columns were derived by first applying an EBCDIC-to-ASCII translation
to the original bytes then unpacking them normally.

If this translation prooves to be accurate, then that means the original EBCDIC data was
translated (whole record) to ASCII (probably during the FTP operation).  Since the packed
fields should be the same in either encoding, but the FTP translation doesn't know that, it
translates EVERY BYTE in the datastream thus "mangling" the packed fields.

There is no way to avoid some kind of translation on your end of the process;  Either the entire
file is ftp's with ASCII translation and you have to reverse that translation on the packed
fields before unpacking them, or the file is ftp's without translation and the packed fields
need no translation but all the others do.

There are only 2 ways to avoid this dilemma, as far as I know, and both require the client
to change the file they are sending you:

  1.  Send the numeric data in "unpacked" format (also known as DISPLAY DECIMAL).
  2.  Pre-convert the packed decimal fields to values that the ftp translation results in
      the correct values, or pre-translate the non-packed fields to ASCII and ftp the file
      without ASCII translation so that nothing gets altered.

  Of the two choices, the first is far more likely to be the easiest for the client to
  implement... if he's willing to make any changes at all.



--INVNO--  --------INVDT---------    ----------INVAMT-----------    ----------AMTDUE-----------
           HEX          UNPACKED     HEX              UNPACKED      HEX              UNPACKED
---------  ----------------------    ---------------------------    ---------------------------
250501995  00 12 C3 C6   0012808+    00 00 01 13 C6   000001138+    00 00 01 13 C6   000001138+
810764434  00 BA 2D 40   0070607+    00 00 04 04 8C   000037372+    00 00 04 04 8C   000037372+
820954119  00 90 90 C6   0030308+    00 00 03 BA 2A   000003705+    00 00 03 BA 2A   000003705+


I can give you the source code to a complete program that can be used to translate just the
EBCDIC packed fields back to ASCII packed fields which you could then process thru your normal
routine if you're interested.  My "language of choice" for this is a dialect of xBase known as
xHarbour (a free, open-source language).  Alternatives are VBScript, PHP, VB6, VB.Net, C#, or
maybe even Java but any of these would require some extra time for me to re-code my existing
xHarbour logic.
Accept Multiple Solutions Accept as Solution

   

 ID:21440824Author:SRG041808Date:04.25.2008 at 11:00AM ESTYour Comment  


Is this what you were looking for?Yes No
SRG041808:
The values you came up with are correct. (Thank you!! Its unlikely I'll be able to get the client to modify their output)
I will have to incorporate something into my existing code to convert the EBCDIC packed fields to ASCII packed fields. It may be that I'll be able to read your xHarbour code. Can you send me it and I'll see if I can read it and re-code it use Cache? If not, pseudo-code or VBScript would be best.Accept and Award Points Accept as Solution

   

 ID:21442004Author:SRG041808Date:04.25.2008 at 01:18PM ESTYour Comment  


Is this what you were looking for?Yes No
SRG041808:
I checked with the client (IT instead of OPs) and the amount of InvNo 810764434 is supposed to be $40.42.  Can you check this for me and let me know why you came up with 373.72.  All of the other amounts are correct.Accept and Award Points Accept as Solution

   

 ID:21442617Author:JesterTooDate:04.25.2008 at 02:36PM ESTExpert Comment  


Rank: Guru
Is this what you were looking for?Yes No
JesterToo:
The bytes in the file you posted are (HEX):  00 00 04 04 8C

The "00" values are the same in EBCDIC and ASCII.

The "04" byte translates from ASCII into the EBCDIC byte "37"

The "8C" byte translates from ASCII into the EBCDIC byte "2C"

Putting the 5 translated EBCDIC values together yields 000037372C which is 373.72+ (assuming 2 decimal places).

The above translations use the EBCDIC encodings as originally defined by IBM for the 370/370/30XX/390 families of mainframes.  Long ago I saw some examples of EBCDIC files produced on AS/400 machines that appeared to use a different translation for a few of the characters.

Is there any chance the data value changed on the client's side after you received the file or that this isn't the same record they checked at their end?  Also, some machine implementations of EBCDIC use different encodings... do you know what platform (computer brand/model and OS) produced this file?

Here is one web resource that you could check (there are several others but most of them document the two encodings in a manner that makes it difficult to decipher).  It bears out the encoding I used in my translate table.  

   http://docs.hp.com/en/32212-90008/apcs01.html
Accept Multiple Solutions Accept as Solution

   

 ID:21447067 Author:SRG041808Date:04.26.2008 at 06:03PM ESTYour Comment  


Is this what you were looking for?Yes No
SRG041808:
Got it.  I'm checking with the client to find out their system.  I made need your help in figuring out the correct table.  I don't want to Accept the solution just yet -- because I may still need your assistance on this -- but you have helped me greatly and I'm convinced that it's got to be a minor difference in the tables.  I'll post again when the client get's back to me on their platform.