CODEWITHSUNDEEP
javajdbcibm-midrangejt400
Hetti
Hetti

Reputation: 55

AS400 SQL Script on a parameter file returns

I'm integrating an application to the AS400 using Java/JT400 driver. I'm having an issue when I extract data from a parameter file - the data retrieved seems to be encoded.

SELECT SUBSTR(F00001,1,20) FROM QS36F."FX.PARA" WHERE K00001 LIKE '16FFC%%%%%' FETCH FIRST 5 ROWS ONLY

Output

00001: C6C9D9C540C3D6D4D4C5D9C3C9C1D34040404040,  - 1
00001: C6C9D9C5406040C3D6D4D4C5D9C3C9C1D3406040,  - 2

How can I convert this to a readable format? Is there a function which I can use to decode this?

On the terminal connection to the AS400 the information is displayed correctly through the same SQL query.

I have no experience working with AS400 before this and could really use some help. This issue is only with the parameter files. The database tables work fine.

Upvotes: 0

Views: 509

Answers (4)

Hetti
Hetti

Reputation: 55

A Big thank you for all the answers provided, they are all correct.

It is a flat parameter file in the AS400 and I have no control over changing anything in the system. So it has to be at runtime of the SQL query or once received.

I had absolutely no clue about what the code page was as I have no prior experience with AS400 and files in it. Hence all your answers have helped resolve and enlighten me on this. :)

So, the best answer is the last one. I have changed the SQL as follows and I get the desired result. 

SELECT CAST(F00001 AS CHAR(20) CCSID 37) FROM QS36F."FX.PARA" WHERE K00001 LIKE '16FFC%%%%%' FETCH FIRST 5 ROWS ONLY

00001: FIRE COMMERCIAL , - 1 00001: FIRE - COMMERCIAL - , - 2

Thanks once again.

Dilanke

Upvotes: 0

Player1st
Player1st

Reputation: 1605

What you are seeing is EBCDIC output instead of ASCII. This is due to the CCSID not being specified in the database as mentioned in other answers. The ideal solution is to assign the CCSID to your field in the database. If you don't have the ability to do so and can't convince those responsible to do so, then the following solution should also work:

SELECT CAST(SUBSTR(F00001,1,20) AS CHAR(20) CCSID(37))
FROM QS36F."FX.PARA"
WHERE K00001 LIKE '16FFC%%%%%'
FETCH FIRST 5 ROWS ONLY

Replace the CCSID with whichever one you need. The CCSID definitions can be found here: https://www-01.ibm.com/software/globalization/ccsid/ccsid_registered.html

Upvotes: 2

Joop Eggen
Joop Eggen

Reputation: 109547

It is hex of bytes of a text in EBCDIC, the AS/400 charset.

static String fromEbcdic(String hex) {
    int m = hex.length();
    if (m % 2 != 0) {
        throw new IllegalArgumentException("Must be even length");
    }
    int n = m/2;
    byte[] bytes = new byte[n];
    for (int i = 0; i < n; ++i) {
        int b = Integer.parseInt(hex.substring(i*2, i*2 + 2), 16);
        bytes[i] = (byte) b;
    }
    return new String(bytes, Charset.forName("Cp500"));
}

passing "C6C9D9C540C3D6D4D4C5D9C3C9C1D34040404040".

Convert the file with Cp500 as charset:

Path path = Paths.get("...");
List<String> lines = Files.readAllLines(path, Charset.forName("Cp500"));

For line endings, which are on AS/400 the NEL char, U+0085, one can use regex:

content = content.replaceAll("\\R", "\r\n");

The regex \R will match exactly one line break, whether \r, \n, \r\n, \u0085.

Upvotes: 0

David G
David G

Reputation: 4014

Since the file is in QS36F, I would guess that the file is a flat file and not externally defined ... so the data in the file would have to be manually interpreted if being accessed via SQL.

You could try casting the field, after you substring it, into a character format.

(I don't have a S/36 file handy, so I really can't try it)

Upvotes: 1

Related Questions