Here is my situation...
Data has been extracted from an Oracle database in UTF-8 format.
Data was loaded into a Teradata database into fields defined as LATIN (not defined as Unicode).
For sake of simplicity I will use first_name as an example field.
This column in the Teradata database is defined as follows:
first_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
The first_name data is loaded into Teradata using a load utility called Multiload without a session character set specified. When Multiload is run it has an option to specify the character set for the Multiload "load session". If one is not specified Multiload defaults to ASCII. (Note: for any Teradata gurus: DBC.Hosts does not have a character set specification set for Multiload to pick up).
So in a situation in which the name "Günther" is loaded.
The following is the hex code of the data after loading:
The correct hex representation for this string is the following which is in Oracle:
So when I select the row of data containing this name from Teradata I get back the following
Note: To do this select I used a basic query tool with a UTF8 ODBC connection to Teradata. I can connect via an ASCII ODBC connection and I get the same output (no surprise).
Now here is the thing. Our users of data in this Teradata database use Business Objects (BO) to get at the data. BO connects to Teradata via a UTF8 ODBC connection. But BO also uses a special font called "BO Japan" to display the data. This font seems to be doing something to display in this example first_name correctly.
Now here is the problem...
I have to use the first_name data in a ColdFusion email application. My email application will pull data directly from views on the Teradata database and I can set the connection type to whatever I want (have total control of that). I will be displaying first_name in an HTML table in the email that is sent out (note I can set the font to whatever I want but of course not everyone has a BO Japan font making that a non-optimal solution and I'm not sure that it would work when not on BO servers).
So does anyone know if it's possible to get at this data from my email application and get it in the correct format so I can display it propertly in an email?
1) I will have to deal with Japanese data also.
2) I know that having the Teradata database load the data properly is the ultimate solution - just want to know if there are others.