Home » SQL & PL/SQL » SQL & PL/SQL » Root cause for the white space while fetching the value from ORACLE
Root cause for the white space while fetching the value from ORACLE [message #380157] Fri, 09 January 2009 05:54 Go to next message
rags_oracle
Messages: 6
Registered: January 2009
Junior Member
Hi all,

I have developed a sample java application which tries to fetch the value of a default indicator value(Y or N)from the database. I have created a table test and inserted the value for default_ind with datatype char(1). When the java program is connected to ORACLE database, along with the indicator value, I am getting some 3 trailing white spaces like 'N '. So the length of that ind is 4. The same program I connected to MYSQL database and tried to get the indicator value. At that time no white spaces was appended.
Anyway I have removed the spaces by trimming it in java program. But I need to get the root cause for this
Could some one please help me out in finding the root cause for this. Also in the database side there was a migration done in the character set. The character set has been migrated to UTF8 now. Is it something to do with this?
This is the java program created for ORACLE database

package com.test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.DriverManager;

public class TestDB {
	public Connection getConnection()throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		return DriverManager.getConnection("jdbc:oracle:thin:@machinename:portnum:db name","userid","password");
	}
	public static void main(String[] args) {
		TestDB t=new TestDB();
		try{
			java.sql.Connection conn=t.getConnection();
			java.sql.Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("select * from test");
			while(rs.next())
			{
				System.out.println(rs.getString("default_ind"));
				String s=rs.getString("default_ind");
				System.out.println("<" +s +">");
				System.out.println(s.length());
				
			}
		}
		catch (Exception e)
		{
			e.getStackTrace();
		}
		
	}
}


This is the java program connected to MYSQL database.

package com.test;

import java.sql.Connection;
import java.sql.ResultSet;
import org.gjt.mm.mysql.Driver;
import java.sql.DriverManager;

public class TestDB {

	public Connection getConnection()throws Exception {
		
		Class.forName("org.gjt.mm.mysql.Driver");
		return DriverManager.getConnection("jdbc:mysql://localhost:3306/DB","userid","password");
	}
	public static void main(String[] args) {
		TestDB t=new TestDB();
		try{
			java.sql.Connection conn=t.getConnection();
			//t.getConnection();
			java.sql.Statement stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("select * from test");
			while(rs.next())
			{
				System.out.println(rs.getString("default_ind"));
				String s=rs.getString("default_ind");
				System.out.println("<" +s +">");
				System.out.println(s.length());
				
			}
		}
		catch (Exception e)
		{
			e.getStackTrace();
		}
		
	}
}


Thanks
Re: Root cause for the white space while fetching the value from ORACLE [message #380159 is a reply to message #380157] Fri, 09 January 2009 06:03 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Can you please post a describe of test? I suspect that "default_ind" is defined as CHAR(4).
Re: Root cause for the white space while fetching the value from ORACLE [message #380162 is a reply to message #380159] Fri, 09 January 2009 06:24 Go to previous messageGo to next message
rags_oracle
Messages: 6
Registered: January 2009
Junior Member
Hi,

Description of test table

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 DEFAULT_IND                                        CHAR(1 CHAR)
 START_DT                                           DATE


It is defined as char(1)

Thanks
Re: Root cause for the white space while fetching the value from ORACLE [message #380171 is a reply to message #380162] Fri, 09 January 2009 06:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It seems it is defined as a Char with a lenght of 1 Char.

With UTF-16 it seems that one Char is 4 bytes, and there is a mixup in the driver somewhere regarding the length.

Quote from here :

Quote:

Character Set

If the database character set is US7ASCII or WE8ISO8859P1, then the data is transferred to the client without any conversion. The driver then converts the character set to UTF-16 in Java.

If the database character set is something other than US7ASCII or WE8ISO8859P1, then the server first translates the data to UTF-8 before transferring it to the client. On the client, the JDBC Thin driver converts the data to UTF-16 in Java.



Have a look at the Character/Byte semantics regarding table/variable definitions, where it is said :

Quote:

Be aware that converting a schema to use a multibyte character set may involve more work than simply rebuilding the schema and recompiling any PL/SQL code to use character semantics. For example, you'll need to look at any external programs, such as those written in C++ or Java, because they may need to be adjusted to handle the multibyte data now coming from the database. Due diligence is still required.

Re: Root cause for the white space while fetching the value from ORACLE [message #380174 is a reply to message #380162] Fri, 09 January 2009 07:02 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Very strange, I'm unable to simulate the problem. What JDBC version are you using?

import java.sql.*;
import oracle.jdbc.*;

public class TestDB {
        public Connection getConnection()throws Exception {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:o102","scott","tiger");
        }

        public static void main(String[] args) {
                TestDB t=new TestDB();
                try{
                        java.sql.Connection conn=t.getConnection();

                        DatabaseMetaData meta = conn.getMetaData();
                        System.out.println("JDBC driver version: " + meta.getDriverVersion());

                        java.sql.Statement stmt=conn.createStatement();
                        ResultSet rs=stmt.executeQuery("select * from test");
                        while(rs.next()) {
                                System.out.println(rs.getString("default_ind"));
                                String s=rs.getString("default_ind");
                                System.out.println("<" +s +">");
                                System.out.println(s.length());
                        }
                }
                catch (Exception e) {
                        e.printStackTrace();
                }
        }
}


And my output:
JDBC driver version: 10.2.0.2.0
1
<1>
1
Re: Root cause for the white space while fetching the value from ORACLE [message #380176 is a reply to message #380171] Fri, 09 January 2009 07:09 Go to previous messageGo to next message
rags_oracle
Messages: 6
Registered: January 2009
Junior Member
Thanks for the reply.
So it you mean to say that the value from database UTF8 when converted to java becomes UTF16 and hence 4 bytes

So what could be done for resolving this? Is there anything that needs to be changed in the jdbc driver or database side?

Thanks
Re: Root cause for the white space while fetching the value from ORACLE [message #380187 is a reply to message #380176] Fri, 09 January 2009 07:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Could you also do a select on that column with SQL*Plus?

To rule out that something funny has happened to that column during the conversion itself.

Re: Root cause for the white space while fetching the value from ORACLE [message #380193 is a reply to message #380187] Fri, 09 January 2009 08:16 Go to previous messageGo to next message
rags_oracle
Messages: 6
Registered: January 2009
Junior Member
SQL> select default_ind, length(default_ind) from test;

D LENGTH(DEFAULT_IND)
- -----------------
N 1
Re: Root cause for the white space while fetching the value from ORACLE [message #380195 is a reply to message #380193] Fri, 09 January 2009 08:27 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Please post a dump of the column's data:

SELECT default_ind, DUMP(default_ind) FROM test;
Re: Root cause for the white space while fetching the value from ORACLE [message #380213 is a reply to message #380195] Fri, 09 January 2009 09:04 Go to previous messageGo to next message
rags_oracle
Messages: 6
Registered: January 2009
Junior Member
SQL> select default_ind, dump(default_ind) from test;

D DUMP(DEFAULT_IND)
---------------------------------------------------
N Typ=96 Len=1: 78

What does dump(default_ind) mean?


Thanks
Re: Root cause for the white space while fetching the value from ORACLE [message #380223 is a reply to message #380213] Fri, 09 January 2009 10:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
dump in the documentation.


So what is your JDBC driver version? And the Java version? And while we are at it, what is the client OS and the exact( 5digit) Oracle version?

The one thing I can imagine at the moment is that it could be that one of those parts can't handle Unicode data correctly.

Re: Root cause for the white space while fetching the value from ORACLE [message #380461 is a reply to message #380157] Mon, 12 January 2009 00:13 Go to previous message
rags_oracle
Messages: 6
Registered: January 2009
Junior Member
I am using jdbc version 4 and the jdk version is 1.4. Database is
Oracle9i Enterprise Edition Release 9.2.0.7.0

Thanks
Ragendu
Previous Topic: Help in Pivot Query (merged)
Next Topic: SQL - Date group by
Goto Forum:
  


Current Time: Sun Dec 11 06:35:38 CST 2016

Total time taken to generate the page: 0.10190 seconds