Binary fields - IBM AS400

This is a discussion on Binary fields - IBM AS400 ; I have an Access project which includes reading a spool entry which has been copied using COPYPRT so has Page#, Line# and Record# as binary fields at the front of each record. Because it is not a native file, I ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: Binary fields

  1. Binary fields

    I have an Access project which includes reading a spool entry which has been
    copied using COPYPRT so has Page#, Line# and Record# as binary fields at the
    front of each record. Because it is not a native file, I cannot import it
    directly into Access, but I could read it using an ADODB connection. The
    CCSID of the file is 65535, so I expected a straight transfer, but that the
    binary fields do not always give a sensible value.
    The AS/400 values for the first five records are:
    1 10 1
    1 12 2
    1 13 3
    1 14 4
    1 15 5

    Using Asc(Mid(String, Byte#, 1)), Access sees then as
    1 142 1
    1 12 2
    1 13 3
    1 14 156
    1 15 9

    Has anyone with a long memory ever needed to do anything similar please?

    Many thanks

    Peter Kinsman



  2. Re: Binary fields

    Peter Kinsman wrote:
    > I have an Access project which includes reading a spool entry
    > which has been copied using COPYPRT so has Page#, Line# and Record#
    > as binary fields at the front of each record. Because it is not a
    > native file, I cannot import it directly into Access, but I could
    > read it using an ADODB connection. The CCSID of the file is 65535,
    > so I expected a straight transfer, but that the binary fields do
    > not always give a sensible value.
    >
    > The AS/400 values for the first five records are:
    > 1 10 1
    > 1 12 2
    > 1 13 3
    > 1 14 4
    > 1 15 5
    >
    > Using Asc(Mid(String, Byte#, 1)), Access sees then as
    > 1 142 1
    > 1 12 2
    > 1 13 3
    > 1 14 156
    > 1 15 9
    >
    > Has anyone with a long memory ever needed to do anything similar please?


    Given the file identifies its data as CCSID(*HEX) [aka CCSID(65535)],
    that means the data would transport as binary versus alphanumeric data
    that would otherwise automatically translate into ASCII. As such, to
    maintain the binary values, just drop the ASC() of the binary data; so
    it remains binary. Note: the length=1 used for MID() implies an
    assumption that the binary encoded values will never exceed 255... and
    of course the ASCII version will have three one-byte binary values
    versus three two0-byte binaries; just in case that was an accident.

    Refer to the table "EBCDIC to ISO-8" which describes the "Control
    Character Mapping - SBCS EBCDIC to ISO-8", from which it should be
    obvious that the expression being used, the ASCII function against the
    one byte string from MID() function, is incapable of converting binary
    encoded values into ASCII, with the intent of making it [remain] as the
    same binary encoded value; i.e. it will convert to ASCII as requested.
    http://www-03.ibm.com/systems/i/soft...trolcodes.html

    The data in the COPYPRT binary stream, in two-byte increments:
    0x0001 0x000A 0x0001
    0x0001 0x000C 0x0002
    0x0001 0x000D 0x0003
    0x0001 0x000E 0x0004
    0x0001 0x000F 0x0005

    The values that are not /sensible/, because they are changed, are:
    10, 4, and 5. The one byte strings being converted using ASC() become
    the noted hex and decimal values; the ASCII character described in words:
    ASC(0x0A) => 0x8E => 0d142 => Single Shift Two
    ASC(0x04) => 0x9C => 0d156 => String Terminator
    ASC(0x05) => 0x09 => 0d009 => Character Tabulation

    Regards, Chuck

  3. Re: Binary fields

    Chuck

    Thank you very much for your interest.
    In the earlier posting, I had used the Mid function to extract each byte
    from the AS/400 string and the Asc function to convert it to a numeric
    value.
    To check what was happening, I wrote the strings as received to a PC file
    and the result agrees with your explanation.
    00000000 00 01 00 8E 00 00 00 01
    00000090 00 01 00 0C 00 00 00 02
    00000130 00 01 00 0D 00 00 00 03
    000001C0 00 01 00 0E 00 00 00 9C
    00000260 00 01 00 0F 00 00 00 09
    In my original function, when dealing with packed fields, I had to use a
    look-up table to get the original value. As I understand it, I now need to
    use the ISO-8 to EBCDIC table, to convert for example 8E back to 0A.

    Again many thanks

    Peter



    "CRPence" wrote in message
    news:lKLXj.74$ph.69@newsfe02.lga...
    > Peter Kinsman wrote:
    >> I have an Access project which includes reading a spool entry
    >> which has been copied using COPYPRT so has Page#, Line# and Record#
    >> as binary fields at the front of each record. Because it is not a
    >> native file, I cannot import it directly into Access, but I could
    >> read it using an ADODB connection. The CCSID of the file is 65535,
    >> so I expected a straight transfer, but that the binary fields do
    >> not always give a sensible value.
    > >
    >> The AS/400 values for the first five records are:
    >> 1 10 1
    >> 1 12 2
    >> 1 13 3
    >> 1 14 4
    >> 1 15 5
    >>
    >> Using Asc(Mid(String, Byte#, 1)), Access sees then as
    >> 1 142 1
    >> 1 12 2
    >> 1 13 3
    >> 1 14 156
    >> 1 15 9
    >>
    >> Has anyone with a long memory ever needed to do anything similar please?

    >
    > Given the file identifies its data as CCSID(*HEX) [aka CCSID(65535)],
    > that means the data would transport as binary versus alphanumeric data
    > that would otherwise automatically translate into ASCII. As such, to
    > maintain the binary values, just drop the ASC() of the binary data; so it
    > remains binary. Note: the length=1 used for MID() implies an assumption
    > that the binary encoded values will never exceed 255... and of course the
    > ASCII version will have three one-byte binary values versus three
    > two0-byte binaries; just in case that was an accident.
    >
    > Refer to the table "EBCDIC to ISO-8" which describes the "Control
    > Character Mapping - SBCS EBCDIC to ISO-8", from which it should be obvious
    > that the expression being used, the ASCII function against the one byte
    > string from MID() function, is incapable of converting binary encoded
    > values into ASCII, with the intent of making it [remain] as the same
    > binary encoded value; i.e. it will convert to ASCII as requested.
    > http://www-03.ibm.com/systems/i/soft...trolcodes.html
    >
    > The data in the COPYPRT binary stream, in two-byte increments:
    > 0x0001 0x000A 0x0001
    > 0x0001 0x000C 0x0002
    > 0x0001 0x000D 0x0003
    > 0x0001 0x000E 0x0004
    > 0x0001 0x000F 0x0005
    >
    > The values that are not /sensible/, because they are changed, are: 10,
    > 4, and 5. The one byte strings being converted using ASC() become the
    > noted hex and decimal values; the ASCII character described in words:
    > ASC(0x0A) => 0x8E => 0d142 => Single Shift Two
    > ASC(0x04) => 0x9C => 0d156 => String Terminator
    > ASC(0x05) => 0x09 => 0d009 => Character Tabulation
    >
    > Regards, Chuck




  4. Re: Binary fields

    Great!

    I copied the table into an Access table via Excel - had to add the Analysis
    Tools to use HEX2DEC.
    Added a lookup to the VBA, using the file value if no match and the result
    is:

    1 10 1
    1 12 2
    1 13 3
    1 14 4
    1 15 5
    1 16 6
    1 18 7
    1 20 8
    1 21 9
    1 22 10
    1 24 11
    1 55 12
    1 57 13
    2 10 14
    2 12 15
    2 13 16

    The sudden jump to line 55 is to print totals, after which we go to the next
    page.

    Thanks again

    Peter
    "Peter Kinsman" wrote in message
    news:jzSXj.20272$U61.7913@newsfe12.ams2...
    > Chuck
    >
    > Thank you very much for your interest.
    > In the earlier posting, I had used the Mid function to extract each byte
    > from the AS/400 string and the Asc function to convert it to a numeric
    > value.
    > To check what was happening, I wrote the strings as received to a PC file
    > and the result agrees with your explanation.
    > 00000000 00 01 00 8E 00 00 00 01
    > 00000090 00 01 00 0C 00 00 00 02
    > 00000130 00 01 00 0D 00 00 00 03
    > 000001C0 00 01 00 0E 00 00 00 9C
    > 00000260 00 01 00 0F 00 00 00 09
    > In my original function, when dealing with packed fields, I had to use a
    > look-up table to get the original value. As I understand it, I now need
    > to use the ISO-8 to EBCDIC table, to convert for example 8E back to 0A.
    >
    > Again many thanks
    >
    > Peter
    >
    >
    >
    > "CRPence" wrote in message
    > news:lKLXj.74$ph.69@newsfe02.lga...
    >> Peter Kinsman wrote:
    >>> I have an Access project which includes reading a spool entry
    >>> which has been copied using COPYPRT so has Page#, Line# and Record#
    >>> as binary fields at the front of each record. Because it is not a
    >>> native file, I cannot import it directly into Access, but I could
    >>> read it using an ADODB connection. The CCSID of the file is 65535,
    >>> so I expected a straight transfer, but that the binary fields do
    >>> not always give a sensible value.
    >> >
    >>> The AS/400 values for the first five records are:
    >>> 1 10 1
    >>> 1 12 2
    >>> 1 13 3
    >>> 1 14 4
    >>> 1 15 5
    >>>
    >>> Using Asc(Mid(String, Byte#, 1)), Access sees then as
    >>> 1 142 1
    >>> 1 12 2
    >>> 1 13 3
    >>> 1 14 156
    >>> 1 15 9
    >>>
    >>> Has anyone with a long memory ever needed to do anything similar please?

    >>
    >> Given the file identifies its data as CCSID(*HEX) [aka CCSID(65535)],
    >> that means the data would transport as binary versus alphanumeric data
    >> that would otherwise automatically translate into ASCII. As such, to
    >> maintain the binary values, just drop the ASC() of the binary data; so it
    >> remains binary. Note: the length=1 used for MID() implies an assumption
    >> that the binary encoded values will never exceed 255... and of course the
    >> ASCII version will have three one-byte binary values versus three
    >> two0-byte binaries; just in case that was an accident.
    >>
    >> Refer to the table "EBCDIC to ISO-8" which describes the "Control
    >> Character Mapping - SBCS EBCDIC to ISO-8", from which it should be
    >> obvious that the expression being used, the ASCII function against the
    >> one byte string from MID() function, is incapable of converting binary
    >> encoded values into ASCII, with the intent of making it [remain] as the
    >> same binary encoded value; i.e. it will convert to ASCII as requested.
    >> http://www-03.ibm.com/systems/i/soft...trolcodes.html
    >>
    >> The data in the COPYPRT binary stream, in two-byte increments:
    >> 0x0001 0x000A 0x0001
    >> 0x0001 0x000C 0x0002
    >> 0x0001 0x000D 0x0003
    >> 0x0001 0x000E 0x0004
    >> 0x0001 0x000F 0x0005
    >>
    >> The values that are not /sensible/, because they are changed, are: 10,
    >> 4, and 5. The one byte strings being converted using ASC() become the
    >> noted hex and decimal values; the ASCII character described in words:
    >> ASC(0x0A) => 0x8E => 0d142 => Single Shift Two
    >> ASC(0x04) => 0x9C => 0d156 => String Terminator
    >> ASC(0x05) => 0x09 => 0d009 => Character Tabulation
    >>
    >> Regards, Chuck

    >
    >




+ Reply to Thread