Monitoring Service plugin and Oracle LONG type

Openfire Openfire Support
1

Hello all,

We have installed Monitoring Service plugin and Oracle 9i where we have WE8ISO8859P1 default character set and UTF8 NCHAR character set. The convesations though OpenFire can be not only in WE8ISO8859P1 thus. As a result the current configuration all characters out WE8ISO8859P1 setare corrupted (??? chars). Of course the chars must be saved in UTF8 set. But as far as OFMESSAGEARCHIVE.BODY field uses LONG type (which is deprecated by Oracle!) I cannot change the column to NCLOB type for example.Is it possible to change BODY filed type to CLOB type?

Another possibility to save texts in properly is to use Oracle connection property defaultNChar=“true”. Can I specify this parameter somewhere to pass it to Oracle connection?

Best regards

Sergey

2

I fixed this problem. The problem was not only in Monitoring plugin: my roster also lost all non Latin-1 chars after rebooting of OpenFire instance.

So if you have Oracle with not UTF-8 as default character set but you have it as a secondary character set for NCHAR types you can apply my solution:

  1. You have to instruct Oracle JDBC driver to use NChar conversation for all character columns by setting “defaultNChar” property to true. You can do it by editing (create if not exists) bin/openfire.vmoptions file. Add the following line in this file:

-Doracle.jdbc.defaultNChar=true

To get more details about this instruction read here pls: http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/nchar/re adme.html

  1. You have to modify the type the character columns which can contain national characters to NVARCHAR2 type, for example to have the correct Nick you have to modify ofRoster table, column nick:

alter table ofRoster modify nick nvarchar2( 255 );

  1. You have to modify the type LONG to NCLOB in the tables where national content can be saved, e.g. ofMessageArchive table, body column. If a table doesnt’t have any data you can drop old column and create new one instead:

alter table ofMessageArchive drop column body;
alter table ofMessageArchive add body nclob;

But if table contains data which you want to save you need more complicated way: you should create new table with NCLOB column, fill-in this table with contain from original table using PL/SQL function to_lob, drop original table and rename new table:

CREATE TABLE ofMessageArchiveClob (
conversationID INTEGER NOT NULL,
fromJID VARCHAR2(1024) NOT NULL,
toJID VARCHAR2(1024) NOT NULL,
sentDate INTEGER NOT NULL,
body NCLOB
);

INSERT INTO ofMessageArchiveClob
(SELECT conversationID, fromJID, toJID, sentDate, to_lob(body) FROM ofMessageArchive);

DROP TABLE ofMessageArchive;

RENAME ofMessageArchiveClob TO ofMessageArchive;

CREATE INDEX ofMessageArchive_con_idx ON ofMessageArchive (conversationID);

Best regards

Sergey