UTF characters Java JDBC

[Barney]

Hi,

I have a Java app that connects to an Oracle DB and I'm having
problems when saving data that include some characters that are only
available in UTF-8.

The characters in question : € ™ Œ œ ƒ

I tried to add the following argument to the JVM but it did not
worked : -Dclient.encoding.override=UTF-8

Question #1 : Is there something I can do on the Java app side ?

Question #2 : Is there something I can do on the Oracle DB side ?

Below the result of the query SELECT * FROM NLS_DATABASE_PARAMETERS on
the database, along with the Java code that interacts whit it.

Thank you very much.

-- ------------------------------
-- DB

PARAMETER
VALUE
------------------------------
----------------------------------------
NLS_LANGUAGE
FRENCH
NLS_TERRITORY
CANADA
NLS_CURRENCY
$
NLS_ISO_CURRENCY
CANADA
NLS_NUMERIC_CHARACTERS .
NLS_CHARACTERSET
WE8ISO8859P1
NLS_CALENDAR
GREGORIAN
NLS_DATE_FORMAT YYYY/MM/
DD
NLS_DATE_LANGUAGE
FRENCH
NLS_SORT
FRENCH
NLS_TIME_FORMAT
HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR-MM-DD
HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF
TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF
TZR
NLS_DUAL_CURRENCY
$
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
NLS_NCHAR_CONV_EXCP
FALSE
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_RDBMS_VERSION
9.2.0.3.0

// ------------------------------
// Java code

package charset;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class TestClass {

private String url;

public TestClass() {
// localhost
url = "jdbc:oracle:thin:@localhost:1521:XE";
}

public int executeUpdate() throws Exception {

Class.forName ("oracle.jdbc.driver.OracleDriver").newInstance
();

Properties props = new Properties();
props.put("user", "username");
props.put("password", "password");

Connection connJHA = DriverManager.getConnection(url, props);
Statement stmt = connJHA.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

String descr = "€ ™ Œ œ ƒ";
String strUpdate = "UPDATE PARAM SET DESCR='" + descr + "'
WHERE CODE='CODE_1'";
return stmt.executeUpdate(strUpdate);
}

public void executeSelect() throws Exception {

Class.forName ("oracle.jdbc.driver.OracleDriver").newInstance
();

Properties props = new Properties();
props.put("user", "username");
props.put("password", "password");

Connection connJHA = DriverManager.getConnection(url, props);
Statement stmt = connJHA.createStatement
(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
String strQuery = "SELECT DESCR FROM PARAM WHERE
CODE='CODE_1'";
ResultSet rs = stmt.executeQuery(strQuery);
rs.next();
String descr = rs.getString(1);
System.out.println("descr = " + descr);
}

public static void main(String[] args) {
TestClass test = new TestClass();
try {
if (test.executeUpdate() != 0) {
test.executeSelect();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

[Laurenz Albe]

Barney wrote:
> I have a Java app that connects to an Oracle DB and I'm having
> problems when saving data that include some characters that are only
> available in UTF-8.
>

> The characters in question : ? T O o f

>
> I tried to add the following argument to the JVM but it did not
> worked : -Dclient.encoding.override=UTF-8
>
> Question #1 : Is there something I can do on the Java app side ?
>
> Question #2 : Is there something I can do on the Oracle DB side ?
>
> Below the result of the query SELECT * FROM NLS_DATABASE_PARAMETERS on
> the database, along with the Java code that interacts whit it.

[...]

> NLS_CHARACTERSET WE8ISO8859P1
> NLS_NCHAR_CHARACTERSET AL16UTF16

The database character set is Latin-1, so you won't be able to
store the characters in a CHAR, VARCHAR2 or CLOB column.
No matter what you try.

So the answer the Question #1 is "no".

On the database side you have two options:
1) Migrate the database to the AL32UTF8 character set.
This is the better, but more difficult option.
If you can start from scratch, I would definitely recommend that.
Make sure that you define text fields with [VAR]CHAR2(n CHAR) and
not [VAR]CHAR2(n).
2) Use NCHAR, NVARCHAR2 or NCLOB for text fields in which you
want to store the non-ASCII characters.

Yours,
Laurenz Albe