{"id":1585,"date":"2025-01-09T06:00:15","date_gmt":"2025-01-09T06:00:15","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1585"},"modified":"2025-01-09T23:57:17","modified_gmt":"2025-01-09T23:57:17","slug":"postgres-ebcdic-decoding-through-a-javascript-function","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1585","title":{"rendered":"Postgres: EBCDIC decoding through a JavaScript Function"},"content":{"rendered":"\n

EBCDIC? Didn’t that die out with punch cards and the Dinosaurs?<\/h2>\n\n\n\n

EBCDIC<\/a> (Extended Binary Coded Decimal Interchange Code) is an eight-bit character encoding that was created by IBM in the ’60s. <\/p>\n\n\n\n

While the rest of the world went on with ASCII and UTF-8; we still find fields in our DB2 database encoded in EBCDIC 037 just to make our lives miserable.<\/p>\n\n\n\n

Qlik Replicate when replicating from these fields on its default settings; brings it across as a normal “string” and becomes quite unusable when loaded into a destination system. <\/p>\n\n\n\n

Decoding EBCDIC in Postgres<\/h2>\n\n\n\n

To have the flexibility to decode particular fields in EBCDIC; we need to bring the fields across as BYTES instead of that QR suggests. This can be done in the Table Settings for the table in question:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

On the destination Postgres database; load the table into a bytea field.<\/p>\n\n\n\n

Now with a udf function in Postgres; we can decode the EBCDIC bytes fields into something readable:<\/p>\n\n\n

\nCREATE OR REPLACE FUNCTION public.fn_convert_bytes2_037(\n    in_bytes bytea)\n    RETURNS character varying\n    LANGUAGE 'plv8'\n    COST 100\n    VOLATILE PARALLEL UNSAFE\nAS $BODY$\n    const hex_037 = new Map([\n        ["40", " ",],\n        ["41", " ",],\n        ["42", "\u00e2",],\n        ["43", "\u00e4",],\n        ["44", "\u00e0",],\n        ["45", "\u00e1",],\n        ["46", "\u00e3",],\n        ["47", "\u00e5",],\n        ["48", "\u00e7",],\n        ["49", "\u00f1",],\n        ["4a", "\u00a2",],\n        ["4b", ".",],\n        ["4c", "<",],\n        ["4d", "(",],\n        ["4e", "+",],\n        ["4f", "|",],\n        ["50", "&",],\n        ["51", "\u00e9",],\n        ["52", "\u00ea",],\n        ["53", "\u00eb",],\n        ["54", "\u00e8",],\n        ["55", "\u00ed",],\n        ["56", "\u00ee",],\n        ["57", "\u00ef",],\n        ["58", "\u00ec",],\n        ["59", "\u00df",],\n        ["5a", "!",],\n        ["5b", "$",],\n        ["5c", "*",],\n        ["5d", ")",],\n        ["5e", ";",],\n        ["5f", "\u00ac",],\n        ["60", "-",],\n        ["61", "\/",],\n        ["62", "\u00c2",],\n        ["63", "\u00c4",],\n        ["64", "\u00c0",],\n        ["65", "\u00c1",],\n        ["66", "\u00c3",],\n        ["67", "\u00c5",],\n        ["68", "\u00c7",],\n        ["69", "\u00d1",],\n        ["6a", "\u00a6",],\n        ["6b", ",",],\n        ["6c", "%",],\n        ["6d", "_",],\n        ["6e", ">",],\n        ["6f", "?",],\n        ["70", "\u00f8",],\n        ["71", "\u00c9",],\n        ["72", "\u00ca",],\n        ["73", "\u00cb",],\n        ["74", "\u00c8",],\n        ["75", "\u00cd",],\n        ["76", "\u00ce",],\n        ["77", "\u00cf",],\n        ["78", "\u00cc",],\n        ["79", "`",],\n        ["7a", ":",],\n        ["7b", "#",],\n        ["7c", "@",],\n        ["7d", "'",],\n        ["7e", "=",],\n        ["7f", ","],\n        ["80", "\u00d8",],\n        ["81", "a",],\n        ["82", "b",],\n        ["83", "c",],\n        ["84", "d",],\n        ["85", "e",],\n        ["86", "f",],\n        ["87", "g",],\n        ["88", "h",],\n        ["89", "i",],\n        ["8a", "\u00ab",],\n        ["8b", "\u00bb",],\n        ["8c", "\u00f0",],\n        ["8d", "\u00fd",],\n        ["8e", "\u00fe",],\n        ["8f", "\u00b1",],\n        ["90", "\u00b0",],\n        ["91", "j",],\n        ["92", "k",],\n        ["93", "l",],\n        ["94", "m",],\n        ["95", "n",],\n        ["96", "o",],\n        ["97", "p",],\n        ["98", "q",],\n        ["99", "r",],\n        ["9a", "\u00aa",],\n        ["9b", "\u00ba",],\n        ["9c", "\u00e6",],\n        ["9d", "\u00b8",],\n        ["9e", "\u00c6",],\n        ["9f", "\u00a4",],\n        ["a0", "\u00b5",],\n        ["a1", "~",],\n        ["a2", "s",],\n        ["a3", "t",],\n        ["a4", "u",],\n        ["a5", "v",],\n        ["a6", "w",],\n        ["a7", "x",],\n        ["a8", "y",],\n        ["a9", "z",],\n        ["aa", "\u00a1",],\n        ["ab", "\u00bf",],\n        ["ac", "\u00d0",],\n        ["ad", "\u00dd",],\n        ["ae", "\u00de",],\n        ["af", "\u00ae",],\n        ["b0", "^",],\n        ["b1", "\u00a3",],\n        ["b2", "\u00a5",],\n        ["b3", "\u00b7",],\n        ["b4", "\u00a9",],\n        ["b5", "\u00a7",],\n        ["b6", "\u00b6",],\n        ["b7", "\u00bc",],\n        ["b8", "\u00bd",],\n        ["b9", "\u00be",],\n        ["ba", "[",],\n        ["bb", "]",],\n        ["bc", "\u00af",],\n        ["bd", "\u00a8",],\n        ["be", "\u00b4",],\n        ["bf", "\u00d7",],\n        ["c0", "{",],\n        ["c1", "A",],\n        ["c2", "B",],\n        ["c3", "C",],\n        ["c4", "D",],\n        ["c5", "E",],\n        ["c6", "F",],\n        ["c7", "G",],\n        ["c8", "H",],\n        ["c9", "I",],\n        ["ca", "\u00ad",],\n        ["cb", "\u00f4",],\n        ["cc", "\u00f6",],\n        ["cd", "\u00f2",],\n        ["ce", "\u00f3",],\n        ["cf", "\u00f5",],\n        ["d0", "}",],\n        ["d1", "J",],\n        ["d2", "K",],\n        ["d3", "L",],\n        ["d4", "M",],\n        ["d5", "N",],\n        ["d6", "O",],\n        ["d7", "P",],\n        ["d8", "Q",],\n        ["d9", "R",],\n        ["da", "\u00b9",],\n        ["db", "\u00fb",],\n        ["dc", "\u00fc",],\n        ["dd", "\u00f9",],\n        ["de", "\u00fa",],\n        ["df", "\u00ff",],\n        ["e0", "\\\\",],\n        ["e1", "\u00f7",],\n        ["e2", "S",],\n        ["e3", "T",],\n        ["e4", "U",],\n        ["e5", "V",],\n        ["e6", "W",],\n        ["e7", "X",],\n        ["e8", "Y",],\n        ["e9", "Z",],\n        ["ea", "\u00b2",],\n        ["eb", "\u00d4",],\n        ["ec", "\u00d6",],\n        ["ed", "\u00d2",],\n        ["ee", "\u00d3",],\n        ["ef", "\u00d5",],\n        ["f0", "0",],\n        ["f1", "1",],\n        ["f2", "2",],\n        ["f3", "3",],\n        ["f4", "4",],\n        ["f5", "5",],\n        ["f6", "6",],\n        ["f7", "7",],\n        ["f8", "8",],\n        ["f9", "9",],\n        ["fa", "\u00b3",],\n        ["fb", "\u00db",],\n        ["fc", "\u00dc",],\n        ["fd", "\u00d9",],\n        ["fe", "\u00da"]\n    ]);\n \n    let in_varchar = "";\n    let build_string = "";\n     \n    for (var loop_bytes = 0; loop_bytes < in_bytes.length; loop_bytes++)\n    {\n        \/* Converts a byte character to a hex representation*\/\n        let focus_char = ('0' + (in_bytes[loop_bytes] & 0xFF).toString(16)).slice(-2); \n        let return_value = hex_037.get(focus_char.toLowerCase());\n \n        \/* If no mapping found - replace the character with a space *\/\n        if(return_value === undefined)\n        {\n            return_value = " ";\n        }\n \n        build_string = build_string.concat(return_value)\n    }\n \n    return build_string\n$BODY$;\n<\/pre><\/div>\n\n\n
The function can now be used in SQL:<\/p>\n\n\n
\nSELECT public.fn_convert_bytes2_037(my_EBCDIC_byte_column)\nFROM public.foo;\n<\/pre><\/div>\n\n\n
Reference<\/h2>\n\n\n\n
JavaScript bytes to HEX string function: Code Shock – How to Convert Between Hexadecimal Strings and Byte Arrays in JavaScript<\/a><\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
EBCDIC? Didn’t that die out with punch cards and the Dinosaurs? EBCDIC (Extended Binary Coded Decimal Interchange Code) is an eight-bit character encoding that was created...<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[64,16],"tags":[79,78,75,56,36],"class_list":["post-1585","post","type-post","status-publish","format-standard","hentry","category-postgres","category-qlik-replicate","tag-79","tag-ebcdic","tag-javascript","tag-postgres","tag-qlikreplicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1585","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1585"}],"version-history":[{"count":2,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1585\/revisions"}],"predecessor-version":[{"id":1590,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1585\/revisions\/1590"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}