JDBC Transliteration Error

[HawkFan]

I am currently running CFMX 6.1 (latest updater has been installed) on Win2K,
IIS 5, and Oracle 8.1.7 database. When attempting to retrieve international
characters that have been stored as UTF8 such as the Euro symbol, etc. I am
getting the following error:
[Macromedia][Oracle JDBC Driver]Transliteration failed, reason: invalid UTF8
data

I have been assured by our DBA that the database is using UTF8 charset. He
indicates that it is a problem with the driver. I am using the DataDirect JDBC
driver that ships with CFMX Enterprise. Actually, I moved back to the previous
version of their driver, (1.3 I think) in order to resolve another database
retrieval issue with CLOB's. I was told at the end of 2003 that a new version
of the driver was coming, but have heard nothing further.

I have also tested this with Oracle's thin JDBC driver and receive the
following error:
Fail to convert between UTF8 and UCS2: failUTF8Conv

Any idea of what could be causing this problem?

[hokugawa]

Are you using convert function in your sql? Could you show me the your sql?
If so, just remove it and try it again.

HTH
--
Hiroshi

[HawkFan]

I am not using the Oracle convert function. I am just using a simple select on the column from the database.

[HawkFan]

I have moved to the latest Data Direct JDBC driver. The most current version
is 3.3. Still having the same problem. Can anyone shed some light on this?
My query is very simple and is as follows:

<cfquery name="MyQuery" datasource="#variables.dsn#">
select currency_symbol
from x
where currency_code = 'EUR'
</cfquery>

[csthsl]

Did you ever get an answer to this problem?

[webfor...@macromedia.com]

We're getting the same exact error

-----------------------------------
Error Executing Database Query.

[Macromedia][Oracle JDBC Driver]Transliteration failed, reason: invalid UTF8
data

The error occurred in ...\Webapps\globalization\wddx1.cfm: line 4

2 :
3 : <!--- cfquery --->
4 : <cfquery name="get" datasource="...">
5 : select name, sample_date, sample_number
6 : from character_sample_test

-----------------------------------

We're on Oracle 9i, MX 6.1, anyone have any ideas? Do we need to insert our
characters in a particular fashion into the DB? I'm currently in the middle of
stepping through individual chars to recognize the troublemakers....

Thanks,
rich

[webfor...@macromedia.com]

OK, just got some data I think might help some folks....

We'd been experiencing the same problem as described above, but what I didn't
realize was that when the data was entered into our tables via a CF query, i.e.
via the JDBC driver, we could retrieve the data without error. The error only
occurred when we used a third party tool to enter the data into the tables
initially (we used PL/SQL Developer by AllRoundAutomations, which we love,
BTW), then attempted to select records with those suspected characters. At that
point we got the error ".... Transliteration failed, reason: invalid UTF8
data...."

So if it's of any value a potential solution, allbeit not a pretty one, might
be down the path of a data reload/scrub/etc around the JDBC driver....

We're pursuing this with AllRoundAutomations, so stay tuned for updates...

Hope this helps-

Rich

[Andrew (Webworld)]

I just encountered this same exact error running CFMX 6.1 against an Oracle 8i
database. I was testing out my database using Unicode characters from another
language. What was weird was that Oracle accepts the data but it shows up in
Oracle as upside-down question marks. And when I tried to retrieve it I got the
Transliteration failed error.

I was terminal serviced into the machine and was pasting my unicode content
from my own machine through the terminal service window into the machine I was
terminal serviced into. When I tried directly on that machine, still through
terminal services, but this time copying AND pasting from a browser on that box
into my CF app, I was able to store the unicode and retrieve it without
throwing any errors. Interestingly enough, the data still shows up in my Oracle
DBA Studio as upside down question marks. I figure that client just doesn't
display unicode very well. The important part is that my data is going in and
getting out properly to my CF app. So there is something to Rich's comment
about third party software. By pasting through a terminal service window I was
somehow distorting my unicode.

Also remember to check the box that says "String Format" under the advanced
settings when setting up your datasource in the CF Administrator as well as to
use the <cfqueryparam> tag in your queries, particularly when inserting and
updating.

Hope this helps somehow...

[ekozlova]

After a few days of reasearch, and thanks to the forums on Macromedia site, I
have found a working solution to this problem.
It is caused by invalid character(s), or characters that CF MX does not read
correctly. They may or may not be foreign chars. In my case, for example, it
was a Mac. char -- '?'

To solve the problem, determine which column(s) may contain problematic
characters and add this function to your select statement:

<cfquery name="test" datasourse="#dsn#">
Select convert(TABLE1.COL1, 'US7ASCII', 'WE8ISO8859P1') AS COL1_ALIAS, col2,
col3...
from table1, table2, table3
where ...
</cfquery>

Hope this helps!
Ekozlova