Hi,
I'm developing a servlet application that show a table result, but that table is in a AS/400 server an the data there is in EBCDIC format.
I have review some previous messages about how translate from EBCDIC to ASCII. But I couldn't find the solution to my problems.
Could you give me some idea how I could get the solutions.
Thanks a lot
Pedro
It is maybe my not knowing AS400, but I do not completely get how the data are stored there: in a (plain) file, in an index-sequential one, in a hierarchical or a relational data base management system etc.
I want to hint however that there is a standard Java input-stream encoding for EBCDIC.
Message was edited by:
BIJ001
I don't have knowing about AS400, the AS400 administrator told me that the data in the tables are in EBCDIC format.
I'm looking for how I could convert the EBCDIC to ASCII format. Currently, they are using an AS400 command to do that and they receive an alpha numeric string.
But my application is in a Linux server and this is connected to AS400 database using JDBC.
I've read so far, the JDBC convert the EBCDIC to Unicode format, no?
Then, Will be the solution convert from Unicode to ASCII?
Please, I'd like to find more information and code about that.
Thanks for all...
Unfortunately, the administrator confirm that the CCSID of the table is 65535.
What can I do?
in this case, what format does the JDBC resultset.getString("KYDESCR")
is returning me?
Could I take that one and convert in ASCII format?
As you can see, I have more question...
Thanks for your help.
There are several variations of EBCDIC. Bascially, the way it works is by getting the bytes directly from the database then converting the bytes using the right encoding. The following scenario is the only time in which using new String() is actually necessary.
Example:
String kydescr = new String( resultset.getBytes("KYDESCR"), "Cp1047");
The second parameter is the characeter set encoding.
If the above doesn't work for you, go look at the Javadoc for the JDK that you are using to see what other encoding might work ie: Cp500 etc,
HTH,
Eugenio
Hi,
The administrator confirm that the CCSID of the field is 65535 and the field structure is like this:
NAMETYPE-FIELDBUFFER-POSITIONLENGTH
KEYFLDCHAR19
KYTRNLATECHAR109
...
EYPREM1PAKED1042
KEYORDPAY1CHAR1061
KEYFTERM1PACKED1072
...
UCSHVAL1PACKED1843
UCSHNBINS1ZONED1871
UCSHPHAMT1PACKED1883
As you can see, the field is a string which every data has a buffer position , a length and data type (CHAR, PACKED and ZONED). I know the field length and the position of every data.
When I did...
String kydescr = new String( resultset.getBytes("KYDESCR"), "Cp1047");
I got a garbage string
What would you recommend to do? I need to get the correct information inside the field.
Thanks for your help.
Packed data fields? Then you have a problem. Those are BCD (binary-coded decimal) fields which store two decimal digits in a byte. You do not want to do an EBCDIC-to-Unicode conversion on those fields, that will turn them into complete garbage. You want to transfer them as is and extract the data with no conversion. Remember that the last half-byte indicates the sign, I think it's F for plus and D for minus but your system could be different.
Are you also getting garbage for the char and zoned fields? You should be able to read them using the code Eugenio provided. If you can't then try some other encoding. Cp-037 has worked for me but that's US EBCDIC and your system may be using a different encoding. (That's what the CCSID is supposed to tell you.) Also note that the zoned fields have a sign at the end just like packed fields, only it takes a whole byte instead of a half byte.
This would be so much easier if your administrators had your AS/400 set up properly and had a proper CCSID on that file. You would just use plain old JDBC and everything would work normally.
Hi
I couldn't covert the EBCDIC in ASCII yet.
I'm using the following driver to access to AS400 database
Class.forName("com.ibm.as400.access.AS400JDBCDriver");
and when I get the field data with
String kydescr = new String( resultset.getBytes("MISCREC"), "Cp037");
System.out.println(kydescr);
the results is:
☺☼☻═☼0 ☼ ☺☼☻? ☼ ☺☼
☼0 ☼ ☺☼☻═☼0 ☼ ☺☼☻? ☼ ☺☼
☼0 ☼ ☺☼☻═☼0 ☼ ☺☼☻ε? ☼ ☺☼
☼0 ☼ ☺☼☻═☼0 ☼ ☺☼☻ε? ☼ ☺☼
☼0 ☼ ☺☼♥? ☼ ☺☼♥⌂→0 ☼ ☺☼i? ☼ ☺☼♥? ☼ ☺☼♥⌂→0 ☼ ☺☼i? ☼ ☺☼♥? ☼
☺☼♥? ☼ ☺☼i? ☼ ☺☼♥? ☼ ☺☼♥? ☼ ☺☼i? ☼ ☼ ☼0 ☼ ☻☼?├|0 ☼ ☼ ☼
0 ☼ ☼ ☼0 ☼ ☻☼?├|0 ☼ ☼ ☼0 ☼
the first 9 character are a char type and it should be 'WEBS7' using EBCDIC it would be 'E6 C5 C2 E2 F7', no?
Actually, I don't know what format is returning by my program.
I haven't found a lot of sample about who covert between EBCDIC and ASCII.
Would be the task like:
to read the EBCDIC field,
to convert it in ASCII,
to substring in the corresponding field according to its length,
and translate the packed and zoned field.
I appreciate your help.
Thanks a lot.
Hi Araujo Guntin,
Have you tried the following (found on the IBM website at http://www-03.ibm.com/servers/eserver/iseries/toolbox/faqjdbc.html)
you can set the "translate binary" connection property to "true", which instructs the JDBC driver to translate all fields, including those tagged with CCSID 65535. The easiest way to do this is to add
";translate binary=true"
to the end of the URL used when connecting to the database.
my code is:
public void getQuery(String magabbr, String keyfld)
{
String sqlQuery = new String();
try
{
con = (AS400JDBCConnection) Connector.getSystemConnection();
sqlQuery = "SELECT KEYFLD, KYMAILDATE, KYDESCR, MISCREC FROM MAGLIB"+magabbr+"2.KEYPF WHERE KEYFLD = '"+keyfld+"' ";
System.out.println(sqlQuery);
statement = con.createStatement();
resultset = statement.executeQuery(sqlQuery);
String todo = new String();
ResultSetMetaData rsmd = resultset.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
while(resultset.next())
{
byte[] lola = resultset.getBytes("MISCREC");
for (int k = 0; k < lola.length; k++) {
System.out.println("[" + k + "] = " + "0x" + byteToHex(lola[k]));
}
}//end: While
} catch(Exception ex)
{
System.out.println(ex.getMessage());
}
}
static public String byteToHex(byte b) {
// Returns hex String representation of byte b
char hexDigit[] = {
'0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', 'a', 'b', 'c', 'd', 'e', 'f'
};
char[] array = { hexDigit[(b >> 4) & 0x0f], hexDigit[b & 0x0f] };
return new String(array);
}
I'm not using translate binary=true' and the bytes are:
[0] = 0x01
[1] = 0x0f
[2] = 0x02
[3] = 0x75
[4] = 0x0f
[5] = 0xf0
[6] = 0x00
[7] = 0x00
[8] = 0x0f
[9] = 0x40
[10] = 0x01
[11] = 0x0f
[12] = 0x02
[13] = 0x38
[14] = 0x5f
[15] = 0xf0
[16] = 0x00
[17] = 0x00
[18] = 0x0f
[19] = 0x40
[20] = 0x01
[21] = 0x0f
[22] = 0x05
[23] = 0x25
[24] = 0x0f
[25] = 0xf0
[26] = 0x00
[27] = 0x00
[28] = 0x0f
[29] = 0x40
[30] = 0x01
[31] = 0x0f
[32] = 0x02
[33] = 0x75
[34] = 0x0f
[35] = 0xf0
[36] = 0x00
[37] = 0x00
[38] = 0x0f
[39] = 0x40
[40] = 0x01
[41] = 0x0f
[42] = 0x02
[43] = 0x38
[44] = 0x5f
[45] = 0xf0
[46] = 0x00
[47] = 0x00
[48] = 0x0f
[49] = 0x40
[50] = 0x01
[51] = 0x0f
[52] = 0x05
[53] = 0x25
[54] = 0x0f
[55] = 0xf0
[56] = 0x00
[57] = 0x00
[58] = 0x0f
[59] = 0x40
[60] = 0x01
[61] = 0x0f
[62] = 0x02
[63] = 0x75
[64] = 0x0f
[65] = 0xf0
[66] = 0x00
[67] = 0x00
[68] = 0x0f
[69] = 0x40
[70] = 0x01
[71] = 0x0f
[72] = 0x02
[73] = 0x56
[74] = 0x5f
[75] = 0xf0
[76] = 0x00
[77] = 0x00
[78] = 0x0f
[79] = 0x40
[80] = 0x01
[81] = 0x0f
[82] = 0x05
[83] = 0x25
[84] = 0x0f
[85] = 0xf0
[86] = 0x00
[87] = 0x00
[88] = 0x0f
[89] = 0x40
[90] = 0x01
[91] = 0x0f
[92] = 0x02
[93] = 0x75
[94] = 0x0f
[95] = 0xf0
[96] = 0x00
[97] = 0x00
[98] = 0x0f
[99] = 0x40
[100] = 0x01
[101] = 0x0f
[102] = 0x02
[103] = 0x56
[104] = 0x5f
[105] = 0xf0
[106] = 0x00
[107] = 0x00
[108] = 0x0f
[109] = 0x40
[110] = 0x01
[111] = 0x0f
[112] = 0x05
[113] = 0x25
[114] = 0x0f
[115] = 0xf0
[116] = 0x00
[117] = 0x00
[118] = 0x0f
[119] = 0x40
[120] = 0x01
[121] = 0x0f
[122] = 0x03
[123] = 0x39
[124] = 0x9f
[125] = 0xf0
[126] = 0x00
[127] = 0x00
[128] = 0x0f
[129] = 0x40
[130] = 0x01
[131] = 0x0f
[132] = 0x03
[133] = 0x07
[134] = 0x3f
[135] = 0xf0
[136] = 0x00
[137] = 0x00
[138] = 0x0f
[139] = 0x40
[140] = 0x01
[141] = 0x0f
[142] = 0x05
[143] = 0x89
[144] = 0x9f
[145] = 0xf0
[146] = 0x00
[147] = 0x00
[148] = 0x0f
[149] = 0x40
[150] = 0x01
[151] = 0x0f
[152] = 0x03
[153] = 0x39
[154] = 0x9f
[155] = 0xf0
[156] = 0x00
[157] = 0x00
[158] = 0x0f
[159] = 0x40
[160] = 0x01
[161] = 0x0f
[162] = 0x03
[163] = 0x07
[164] = 0x3f
[165] = 0xf0
[166] = 0x00
[167] = 0x00
[168] = 0x0f
[169] = 0x40
[170] = 0x01
[171] = 0x0f
[172] = 0x05
[173] = 0x89
[174] = 0x9f
[175] = 0xf0
[176] = 0x00
[177] = 0x00
[178] = 0x0f
[179] = 0x40
[180] = 0x01
[181] = 0x0f
[182] = 0x03
[183] = 0x39
[184] = 0x9f
[185] = 0xf0
[186] = 0x00
[187] = 0x00
[188] = 0x0f
[189] = 0x40
[190] = 0x01
[191] = 0x0f
[192] = 0x03
[193] = 0x30
[194] = 0x5f
[195] = 0xf0
[196] = 0x00
[197] = 0x00
[198] = 0x0f
[199] = 0x40
[200] = 0x01
[201] = 0x0f
[202] = 0x05
[203] = 0x89
[204] = 0x9f
[205] = 0xf0
[206] = 0x00
[207] = 0x00
[208] = 0x0f
[209] = 0x40
[210] = 0x01
[211] = 0x0f
[212] = 0x03
[213] = 0x39
[214] = 0x9f
[215] = 0xf0
[216] = 0x00
[217] = 0x00
[218] = 0x0f
[219] = 0x40
[220] = 0x01
[221] = 0x0f
[222] = 0x03
[223] = 0x30
[224] = 0x5f
[225] = 0xf0
[226] = 0x00
[227] = 0x00
[228] = 0x0f
[229] = 0x40
[230] = 0x01
[231] = 0x0f
[232] = 0x05
[233] = 0x89
[234] = 0x9f
[235] = 0xf0
[236] = 0x00
[237] = 0x00
[238] = 0x0f
[239] = 0x40
[240] = 0x00
[241] = 0x0f
[242] = 0x00
[243] = 0x00
[244] = 0x0f
[245] = 0xf0
[246] = 0x00
[247] = 0x00
[248] = 0x0f
[249] = 0x40
[250] = 0x02
[251] = 0x0f
[252] = 0x04
[253] = 0x66
[254] = 0x4f
[255] = 0xf0
[256] = 0x00
[257] = 0x00
[258] = 0x0f
[259] = 0x40
[260] = 0x00
[261] = 0x0f
[262] = 0x00
[263] = 0x00
[264] = 0x0f
[265] = 0xf0
[266] = 0x00
[267] = 0x00
[268] = 0x0f
[269] = 0x40
[270] = 0x00
[271] = 0x0f
[272] = 0x00
[273] = 0x00
[274] = 0x0f
[275] = 0xf0
[276] = 0x00
[277] = 0x00
[278] = 0x0f
[279] = 0x40
[280] = 0x02
[281] = 0x0f
[282] = 0x04
[283] = 0x66
[284] = 0x4f
[285] = 0xf0
[286] = 0x00
[287] = 0x00
[288] = 0x0f
[289] = 0x40
[290] = 0x00
[291] = 0x0f
[292] = 0x00
[293] = 0x00
[294] = 0x0f
[295] = 0xf0
[296] = 0x00
[297] = 0x00
[298] = 0x0f
[299] = 0x40
Funny, the data doesn't seem to be in any code page. Are you able to select the data using the db2 client? Also does it work from your desktop windows pc or is it a linux only problem?
Also, have you tried to post this to the ibm toolkit website? You might get a quick response from the ibm team itself.
Other things to consider is getting the latest jt400.jar.