MySql / Oracle special characters
538614Jan 4 2008 — edited Jun 6 2008Searched all around, my apologies if this is a redundant thread.
I have an Oracle (10.2.0.3) database set up to connect to MySql (4.1) database using HSODBC. Using the 3.51 MySql driver. Has a couple bugs. Distributed transactions, erroneous record counts, etc -- but I know from reading other threads that the fixes are coming. There is one problem I can't seem to get around...
In MySql, a certain field is defined as CHAR(40). I would think any content short of 40 characters is padded with spaces. But when I pull pipeline characters concatenated on both sides of the values (MySql query browser), there are no extra spaces. It's behaving more like a VARCHAR on the MySql end:
[select concat( '|', field, '|') from table]
returns '|value|'
NOT '|value |'
Now, when I pull this data through to Oracle, there are special characters padding the content to the length of the column. When I pull the ASCII code value, they're 0 NUL (null) characters. Every CHAR field in every table that doesn't fill the column is coming across the database link padded with these NUL characters, which look like little hollow squares (in SqlDeveloper and JDeveloper). Since they're not spaces, I can't TRIM them out. I can remove them with TRANSLATE, but I would have to identify every CHAR field in every table and manually code a TRANSLATE into views against the DB_LINK, and then always pull from the views. There must be an easier way to get around this.
Has anybody had a similar experience? Is this the version of the MySql driver (3.51) that I'm using? I see they have a version 5 out now. Is this addressed by a 10.2.0.4 patchset? Is that available yet? (Redhat) Is this fixed with 11g?
Oracle - AL32UTF8, UTF8
MySql - uses both UTF8 and Latin1, but the default on the source table is Latin1 / Latin_swedish_ci