Re: CLOB Queries return rubbish data

[PaulH]

define "rubbish".

are you using the JDBC driver (marked as oracle in cfadmin)? what encoding was used in the database? how was the arabic data entered into the databse?

[Amer.Sayyed]

I am quering a table which has 2 CLOB attributes as well as other attributes,
the Dbase is an oracle 10g 10.1.0.3.0 and i am using CFMX 6.1, at first the
query hanged and did not retrieve any data, i upgraded the coldfusion drivers
and was able to perform the query just fine, however, the 2 CLOB attributes
contain text one in english the other is arabic, the english text is being
desiplayed just fine whereas the arabic text is being displayed as rubbish data
no matter what type of encoding i use , i have tried everything from utf-8 to
windows encoding, the data from other attributes is being displayed just fine
even if it was in arabic fonts, meaning that this problem occurs only with CLOB
attributes && arabic fonts, i thought the Jrun might solve the problem but i
am not using the J2ee server configuration im using the self contained CFMX
server.
i am pretty much out of tricks.
your help is most appreciated .
Amer Sayyed

[Amer.Sayyed]

first of all, thanx for your reply...

i am using the oracle jdbc driver defined in the oracle administrator console,
all connections to this database is successfull and no problems has ever arised
except when i tried to retrieve a CLOB attribute.
the encoding used for the database and the server on which it resides is UTF-8.
the arabic data has been imported from another database which has the exact
same schema with the same attributes,
except the oracle driver was oracle 10g 10.1.0.2.0 on the old server,- which
worked fine by the way even with the clob data- and the new server is
10.1.0.3.0.
what i mean by rubbish is fonts of characters that is neither arabic nor
english and is sure as hell unreadable.

again the problem is like this :

an oracle 10g databse has a table containing CLOB attributes as well as other
attributes including varchar2.
when i query this table, arabic fonts from the CLOB attributes appear as
rubbish where it works fine for varchar2 attributes.
this problem happens when using the database server on a unix machine which
has a default utf-8 encoding and runs an oracle 10.1.0.3.0 database driver
which i have been instructed to use.
i was using an oracle 10.1.0.2.0 driver based database which worked fine for
all attributes CLOB and others, note that the old server configuration is
exactly the same configuration on the new server.

i'd appreciate the response as i am running out of time and ideas.

Warmest Regards

[PaulH]

does that DSN have an option for unicode? if so, did you turn it on?

are the garbaged text question marks "???" or something else?

[hokugawa]

It sounds like Oracle JDBC driver's problem. Can you verify that the same problem will occur with pure Java program?

-- Hiroshi

[Amer.Sayyed]

the data returned in english fonts are fine, where as the data returned from
arabic fonts are like this :

?( ? )= ?? ( ?? ? ), ?( ? ) = ?, ?( ? ) = ? ^? ??? ? ??? ????, ?( ? ) = ?? ?
?( ? ) ( ?? ? ), ????? ???????? ( ?? ???? ) ?????? ????????, ????? ??? ????????
( ?? ???? ) ?????? ????????

i configured the datasource to enable Enable retrieval of (CLOB) And (BLOB)
values, and i even increased the BLOB buffer size,
what is realy confusing is that the old datasource which was using the
10.1.0.2.0 database driver wasnt configured for retrieval of CLOB and BLOB
values, and still it worked fine and retrieved the values without any problems
in speed or text fonts , and that database server "the old one" has the exact
same configuration as the current server im using now.

[Amer.Sayyed]

the DSN does not have a UTF encoding option in the coldfusion administrator
console, but i am positive that the server and the database server uses a
utf-encoding.
with regard to the oracle jdbc driver, i wrote a simple program to retrieve
all values from the damned table using java, the output is complete with no
problems at all, meaning that accessing the database using java and jdbc works
perfectly.

please note that i am now using ColdFusion MX 7 in an effort to solve this
problem and i haven?t been successful so far.

Your help is MOST appreciated.

[sdupre]

So you've confirmed you're using a UTF8 NLS_Charcterset (select * from
sys.props$)? Are you using Standard or Enterprise? Must be enterprise since
it sounds like you're using the built-in JDBC drivers that come with ColdFusion
6.1 Did you install the 6.1 updater ? Where did you obtain the 'newer
drivers' and what version are they? If you open up the
'macromedia_drivers.jar' and look at base.properties, do you see 3.3 build 29?
There were some issues with the older drivers but we need to know which drivers
you're using exactly. That still isn't clear to me whether you're using Oracle
thin (ojdbc14.jar) or the build-in datadirect drivers available with
Enterprise. Stephen Dupre Macromedia QA

[sdupre]

There is a difference between the two databases. The old one (that works) has
NLS_CHARACTERSET= ALT16UTF8 The new one has AR8ISO8859P6 and causes the
corruption. I see the problem using NLS_characterset AR8ISO8859P6 with an
Oracle 9.2 DB instance I created. This is bug 59863. Both the latest 3.3
drivers (build 74) and 3.4 (build 34) have this problem. As mentioned, it
reproduced on Oracle 9 ( as suspected, it wasn't a version issue but a charset
issue with the DataDirect driver ). In the meantime, we'll log a bug with
DataDirect and get this moving. You're probably looking at 4 weeks or more
turnaround. Your best bet is switching to the ALT16UTF8 NLS_CHARACTERSET -
the one that worked - and what was used on the OLD database. Stephen Dupre
Macromedia QA

[Amer.Sayyed]

SOLUTION:

i modified the database character set using "Alter Database Command", shutdown
the database server and restart it in a restricted session first then alter the
character set, the data that was already in the database was now corrupted, i
imported a backup version of all schemas to a new user that i created under the
new character set "AL32UTF8", the data now displayed correctly in an SQL editor
instead of being gibberish, i performed a test using CFMX application server
and the query performed quite nicely, everything is working well now with no
problems at all, if anybody requires the procedure of altering the database
characterset please EMAIL me.

Many thanx to Paul Hastings & Stephen Dupre for their relentless efforts to
help me solve this problem.