Solved

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

Posted on 2013-01-10
13
1,348 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
  • 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now