Laszlo and Oracle

[intellagent]

Hi,
I use OpenLaszlo3.0 and Oracle 9.2 (UTF-8)
When Laszlo inserts a row into a table, the charactercode of the varchar2 field is not the same as I suppose to be according to the unicode code-set. The Laszlo application displays the characters properly.
If I insert the same string via SQL-plus, the encoding matches the unicode code-set, but the application doesn't display the special characters.
Do You have any idea?

Thanks
Janos

[hqm]

How are you retrieving the data? Using a servlet in Java? As XML directly from Oracle somehow?

You need to tell more about your set up, there are several places or steps that web servers can use the wrong encoding on character data.

[intellagent]

I use Eclipse 3.0.2 and IDE4Laszlo 2.0 on a Windows XP.

The editor preference is UTF-8.
The first line he lzx source is <?xml version="1.0" encoding="utf-8"?>

The Oracle 9.2 database takes place on a LINUX server.
I send and retrieve the data by jsp, which uses the Oracle's XML SQL Utility (XSU).

In the Oracle database the NLS_CHARACTERSET=EE8ISO8859P2 (for the varchar2 data) and NLS_NCHAR_CHARACTERSET=AL16UTF16 (for the nvarchar2 data.) The Laszlo application writes into the nvarchar fields. I tried the application with setting the NLS_NCHAR_CHARACTERSET to UTF8, but the result was the same, except, the code differed.
The Laszlo application itself could display the special Hungarian characters correctly, but the codes I am not able to translate to the other characterset. I use for the translation the Oracle's TRANSLATE USING sql-command.
If I write into the nvarchar2 field the same Hungarian string via sqlplus, the codes are the defined unicodes and the TRANSLATE works perfectly.

(I need both characterset in one database, because we have an old application wich doesn't like UTF-8 and the Laszlo application will handle new data for the old application from the Internet.)

Thanks

[intellagent]

...I forgot to tell You, we Use Tomcat 5.5...

[intellagent]

... one more thing...
I changed the http request-type from GET to POST and now the character encoding in the Oracle database is correct, but the Laszlo application can not display the special Hungarian characters correctly in the browser.

[hqm]

This article might be some help

http://www.openlaszlo.org/wiki/JSPUTF8

[intellagent]

I would like to tell You, that everything is fine now.

Using the XSU utility Oracle gives back the row in XML. In the calling jsp there is necessary to write the line <%@ page language="java" contentType="text/html;charset=UTF-8"%>

To write UTF-8 codes into Oracle we use requesttype POST!


Janos

[hqm]

Could you post a complete example of calling the Oracle utility from a jsp page, and I will add it to my article on Unicode handling.

thank you

[intellagent]

Here is the two jsp. Because Oracle XSU deals with elements, in Laszlo we convert every tablerow-elements into nodes with attributes and vica-versa.

This is the ToOracle jsp:

<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ page language="java" import="java.util.*"%>
<%@ page language="java" import="java.lang.*"%>
<%@ page language="java" import="java.sql.*"%>
<%@ page language="java" import="oracle.xml.sql.dml.OracleXMLSave"%>
<%
// Create connection
Connection connection = null;
String xmlString = "";
try {
String action = request.getParameter("action");
int actionInt = 0;
if (action.equals("update"))
{
actionInt = 1;
}
if (action.equals("delete"))
{
actionInt = 2;
}
String tableName = request.getParameter("tableName");
String tableKeys = request.getParameter("tableKeys");
String curXML = request.getParameter("curXML");
String oraHostName = request.getParameter("oraHostName");
String dbName = request.getParameter("dbName");
String usrName = request.getParameter("usrName");
String usrPwd = request.getParameter("usrPwd");
if (oraHostName == null || oraHostName == "") {
oraHostName = "Hostname";
}
if (dbName == null || dbName == ""){
dbName = "DataBaseName";
}
if (usrName == null || usrName == ""){
usrName = "UserName";
}
if (usrPwd == null || usrPwd == ""){
usrPwd = "Password";
}

Class.forName("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@" + oraHostName + ":1521:"+ dbName;
connection = DriverManager.getConnection(conStr, usrName, usrPwd);
if (curXML != null && curXML != "")
{
// curXML = "<?xml version='1.0' encoding='utf-8'?>"+ "\n" + curXML;
System.out.println("curXML: " + curXML);

OracleXMLSave sav = new OracleXMLSave(connection, tableName);
// load columns of compound key into array keyColumns
// from string tableKeys with format: key1 key2 key3 ... keyN
// ...start
String[] keyColumns;
int rows=0;
int pStart = 0;
int pEnd = 0;
boolean ok = true;
while( (tableKeys.length()> 0) && ok)
{
pEnd = tableKeys.indexOf(" ", pStart);
if (pEnd < 0)
{
ok = false;
}
pStart = pEnd + 1;
rows++;
}
keyColumns = new String[rows];
ok = true;
rows = 0;
pStart = 0;
while( (tableKeys.length()> 0) && ok)
{
pEnd = tableKeys.indexOf(" ", pStart);
if (pEnd < 0)
{
ok = false;
pEnd = tableKeys.length();
}
String sor = tableKeys.substring(pStart, pEnd);
pStart = pEnd + 1;
keyColumns[rows] = sor;
rows++;
}
// ...end

switch (actionInt)
{
case 0:
sav.insertXML(curXML);
break;
case 1:
sav.setKeyColumnList(keyColumns);
sav.updateXML(curXML);
break;
case 2:
sav.setKeyColumnList(keyColumns);
sav.deleteXML(curXML);
break;
default:
}
xmlString = "<ROWSET><ROW>OK</ROW></ROWSET>";
}
}
catch (SQLException se) {
se.printStackTrace();
xmlString = "<ROWSET><ROW><![CDATA[" + se.getMessage()+ "]]></ROW></ROWSET>";
}
catch (Exception e) {
e.printStackTrace();
xmlString = "<ROWSET><ROW><![CDATA[" + e.getMessage()+ "]]></ROW></ROWSET>";
} finally {
try {
connection.close();
} catch (SQLException e) {
}
}
%>
<%=xmlString%>


This is the FromOracle jsp

ns <%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ page language="java" import="java.util.*"%>
<%@ page language="java" import="java.io.*" %>
<%@ page language="java" import="java.lang.*"%>
<%@ page language="java" import="java.sql.*" %>
<%@ page language="java" import="oracle.xml.sql.query.OracleXMLQuery" %>
<%
// Create connection
Connection connection = null;
String xmlString = "";
try {
request.setCharacterEncoding("UTF-8");
String querySrc = request.getParameter("sqlQuerySrc"); // get an sql statement used as a parameter to OracleXMLQuery
String oraHostName = request.getParameter("oraHostName");
String tableName = request.getParameter("tableName");
String dbName = request.getParameter("dbName");
String usrName = request.getParameter("usrName");
String usrPwd = request.getParameter("usrPwd");
if (oraHostName == null || oraHostName == "") {
oraHostName = "HostName";
}
if (dbName == null || dbName == ""){
dbName = "DatabaseName";
}
if (usrName == null || usrName == ""){
usrName = "UserName";
}
if (usrPwd == null || usrPwd == ""){
usrPwd = "Password";
}
Class.forName("oracle.jdbc.driver.OracleDriver"); // get Connection to the Oracle database
String conStr = "jdbc:oracle:thin:@" + oraHostName + ":1521:"+ dbName;
connection = DriverManager.getConnection(conStr, usrName, usrPwd);
OracleXMLQuery qry = new OracleXMLQuery(connection, querySrc);
qry.setEncoding("UTF-8");
qry.setRowIdAttrName("");
xmlString = qry.getXMLString();
int xml_sor_vege = xmlString.indexOf("?>");
if (xml_sor_vege != -1)
xmlString = xmlString.substring(xml_sor_vege+2, xmlString.length());
}
catch (Exception e) {
xmlString = "ERROR: " + e.getMessage();
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
}
}
%>
<%=xmlString%>

[hqm]

Thank you, I added a pointer to your example to
the resources article at

http://www.openlaszlo.org/wiki/JSPUTF8