?
Solved

Java and DB - Query returns # instead of ñ (char encoding?)

Posted on 2013-01-10
13
Medium Priority
?
1,473 Views
Last Modified: 2013-01-14
Good day experts

I have a java program running on Linux (Java version 1.4.2) that connects to a DB2 database (Mainframe) that tries to get one value as follows:

...
public String getQuery(){
    ResultSet resultSet = null;
    Connection conn = null;
    PreparedStatement pst = null;
    String output1 = "";
   try
   {
       Class.forName("com.ibm.db2.jcc.DB2Driver");
       conn = DriverManager.getConnection
       ("jdbc:db2://db2url:db2port/dbssid:" + "user=prod;" + "password=xxx;" + "LogInTimeout=1;");

        String query= "SELECT BLNVTXT FROM BDP.BLV01 WHERE BLS=? AND BLN=?";
        pst = conn.prepareStatement(query);
        pst.setString(1,123);
        pst.setString(2,123);
        resultSet = pst.executeQuery();

        while (resultSet.next()) {
           output1 = resultSet.getString(1);
        }
    resultSet.close();
    conn.close();
   }
   catch (SQLException sqle)
       {
           System.out.println(sqle.getMessage());
           sqle.printStackTrace(System.out);
       } finally{
         return output1;
       }
   }

...
public static void main(String [ ] args)
{
     QueryProd prodA = new QueryProd();
     outputA = prodA.getQuery();
     System.out.println(outputA);
}

Open in new window


I can successfully connect to the db2 and get the result. The problem is that if the result has a "Ñ" character, I receive it as "#". I already checked the value from the database and it has a "Ñ". Where I think the problem is (please correct me if I'm wrong), is the character encoding, Mainframe uses EBCDIC encoding and I guess that when calling the getString() method it's trying to interpret it differently (?)  

Is there any way to solve this? I can't seem to get it :S!!

Thanks in advance
0
Comment
Question by:Arrismog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 38766559
Check the driver documentation as regards character encoding. It's pretty likely you should be able to pass the required encoding in the connection string.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 38766695
i don't know if this is a route you want to take but you can use a conversion method when accessing db2 and have the conversion happen there, for example

SELECT unicode_str(BLNVTXT ) FROM BDP.BLV01 WHERE BLS=? AND BLN=?
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 38767603
JDBC type 4 connectivity, which you appear to be using, requires Unicode support on the z/OS side, as described in http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0024156.html

Please check that the Unicode support is enabled.
0
Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

 

Author Comment

by:Arrismog
ID: 38767995
@CEHJ and @mustaccio: Unicode support is enabled, just confirmed this by the dba.

@momi_sabag : good suggestion, didn't know it existed, but still shows # instead of Ñ. Even tried adding the UTF8 and UTF16 as a second parameter as described in the db2 syntax doc, but still the same.

Any other ideas? :S!!
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 38768160
What DB2 client version? Are you using the IBM JVM to run your application?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38768184
@CEHJ and @mustaccio: Unicode support is enabled, just confirmed this by the dba.
I didn't mention anything about that. Did you check the driver connection string as i mentioned?
0
 

Author Comment

by:Arrismog
ID: 38768206
@mustaccio: No DB2 client involved to connect to DB2 Database, I'm using the jdbc drivers for db2 for zOS (JDBC type 4 connectivity). Yes, I'm using the IBM JVM (s390x) to run the apps, under Linux (also s390x architecture).

The App is running under Websphere Application Server 6.0 (which uses the same IBM JVM), but right now, for testing purposes, I'm running it directly from the linux terminal by compiling and running that specific program manually (using the same IBM JVM from Websphere).
0
 

Author Comment

by:Arrismog
ID: 38768217
@CEHJ : Sorry, didn't catch the comment before :( . As far as I've read the docs, I couldn't find if it's possible to pass char encoding during the connection string.
0
 
LVL 8

Accepted Solution

by:
mustaccio earned 2000 total points
ID: 38768550
>  No DB2 client involved to connect to DB2 Database, I'm using the jdbc drivers

The driver IS the client. Run "java com.ibm.db2.jcc.DB2Jcc -version -configuration" to determine its version.

I wonder how do you view the string. May be it's the terminal that does not support UTF-8. Try to print the string in Java as hex -- see if the character code is what you think it is.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38768691
I wonder how do you view the string. May be it's the terminal that does not support UTF-8.
Good point
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38769999
System.out.println(System.getProperty("file.encoding"));

Open in new window

Might be useful if you could post the output of the above too
0
 
LVL 27

Expert Comment

by:tliotta
ID: 38774442
UTF-8 "support" on the server is only part of it. The data must also actually be stored appropriately, i.e., the correct bit patterns with appropriate encoding. Support can exist while not being used. What is the full definition of the column on the server? If the column definition defaults, the higher level definitions will also be needed.

Tom
0
 

Author Closing Comment

by:Arrismog
ID: 38775340
You were right, turned on hex display for that specific character, and it was a X'7B'. Checked the ascii table and, actually, EVEN if my 3270 emulator displayed a Ñ, it was a "#"! I believe that my TN3270 terminal adjusts the encoding depending of what is displayed...

Asked the dba to create a column with one register, this time with the Ñ-with-correct hex code and it displayed correctly with the db2 client, so I'm sending my feedback to the devs to use the correct charset.

Thanks a lot mustaccio for the idea!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Suggested Courses
Course of the Month8 days, 12 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question