| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Unconvertable character error from MySQL Select in WMB |
« View previous topic :: View next topic » |
| Author |
Message
|
| GeneRK4 |
 Posted: Mon Jun 23, 2014 9:58 pm Post subject: Unconvertable character error from MySQL Select in WMB |
 |
|
Master
Joined: 08 Jul 2013
Posts: 220
|
Hi,
I need help in solving this issue.
' Ã… ' is the special character that needs to be obtained from select
query in the WMB flow.
U+00C5Ã…c3 85LATIN CAPITAL LETTER A WITH RING ABOVE
We captured the error in trace,
See the following messages for
details of the error.
2014-06-19 17:34:32.750386 6 RecoverableException BIP2488E:
('.DB_Lookup.Main', '18.4') Error detected whilst executing the SQL
statement ''SET TXREF_CACHE.FR[I].RECORD[ ] = SPECIFICPASSTHRU('SELECT
FLOW_REFERENCE as TBL_FLOW_REFERENCE,
SOURCE_VALUE_C_1 as
TBL_SOURCE_VALUE_C_1,
SOURCE_VALUE_C_2 as
TBL_SOURCE_VALUE_C_2,
SOURCE_VALUE_C_3 as
TBL_SOURCE_VALUE_C_3,
SOURCE_VALUE_C_4 as
TBL_SOURCE_VALUE_C_4,
SOURCE_VALUE_C_5 as
TBL_SOURCE_VALUE_C_5,
XREF_TYPE_DESC as
TBL_XREF_TYPE_DESC,
TARGET_VALUE_C_1 as
TBL_TARGET_VALUE_C_1,
TARGET_VALUE_C_2 as
TBL_TARGET_VALUE_C_2,
TARGET_VALUE_C_3 as
TBL_TARGET_VALUE_C_3,
TARGET_VALUE_C_4 as
TBL_TARGET_VALUE_C_4,
TARGET_VALUE_C_5 as
TBL_TARGET_VALUE_C_5
FROM T_LOOKUP
WHERE
FLOW_REFERENCE=?', Database.APPINT, Environment.Variables.FlowSpecific.
RT.CACHE.FLOW_REFERENCE[I]);''.
The message broker detected an
error whilst executing the given statement. An exception has been
thrown to cut short the SQL program.
See the following messages for
details of the error.
2014-06-19 17:34:32.750392 6 RecoverableException BIP2135E:
Source character ''c5'' in field ''c54c414e44'' cannot be converted to
Unicode from codepage '1208'.
The source character is an
invalid code point within the given codepage.
Correct the application that
generated the message.
2014-06-19 17:34:32.750428 6 UserTrace BIP4080I: Message
propagated to try terminal from try-catch node ONBOARDING_SERVICE.
Try Catch'.
The try-catch node
'ONBOARDING_SERVICE.Try Catch' has received a message and is
propagating it to any nodes connected to its try terminal.
mysql> SHOW CREATE DATABASE APPINT;
+----------
+-------------------------------------------------------------------+
| Database | Create
Database |
+----------
+-------------------------------------------------------------------+
| APPINT | CREATE DATABASE `APPINT` /*!40100 DEFAULT CHARACTER SET
latin1 */ |
+----------
+-------------------------------------------------------------------+
Our WMB has 1208-UTF8 as CCSID
MySQL is LATIN-1
I tried this command in MySQL directly.Its not showing output
SELECT FLOW_REFERENCE as TBL_FLOW_REFERENCE, SOURCE_VALUE_C_1 as TBL_SOURCE_VALUE_C_1, SOURCE_VALUE_C_2 as TBL_SOURCE_VALUE_C_2, SOURCE_VALUE_C_3 as TBL_SOURCE_VALUE_C_3, SOURCE_VALUE_C_4 as TBL_SOURCE_VALUE_C_4, SOURCE_VALUE_C_5 as TBL_SOURCE_VALUE_C_5, XREF_TYPE_DESC as TBL_XREF_TYPE_DESC, CONVERT(TARGET_VALUE_C_1 USING utf8) as TBL_TARGET_VALUE_C_1 , TARGET_VALUE_C_2 as TBL_TARGET_VALUE_C_2, TARGET_VALUE_C_3 as TBL_TARGET_VALUE_C_3, TARGET_VALUE_C_4 as TBL_TARGET_VALUE_C_4, TARGET_VALUE_C_5 as TBL_TARGET_VALUE_C_5 FROM T_LOOKUP WHERE FLOW_REFERENCE='NEWHIRENATIONALITY' AND SOURCE_VALUE_C_1='Aland Islander' ;
But when I use the same command in WMB,during I am still getting Runtime error as Unconvertable character.
It would be really great help if someone could help me on this.
http://www-01.ibm.com/support/docview.wss?uid=swg21382185 -Same issue
fixed for ODBC connection in WMB7,8 for ORACLE.
But for MySQL,its not there.. |
|
| Back to top |
|
 |
| Tibor |
| Posted: Tue Jun 24, 2014 6:17 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
For MySQL / ODBC connection, it is recommended using same setting for client and server in $MYSQL_HOME/my.cnf, e.g. when codepage is Latin-2:
| Code: |
[mysqld]
port=3306
socket=/tmp/mysql.sock
default-storage-engine=InnoDB
character-set-server=latin2
lower_case_table_names=1
sql_mode=ANSI_QUOTES
log_warnings=2
skip-name-resolve
query-cache-size=16M
[client]
default-character-set=latin2
port=3306
socket=/tmp/mysql.sock
|
|
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Fri Jul 04, 2014 3:59 am Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
We found one more solution for this problem..
Steps followed as below,
1)We changed the CCSID of the table column for which UTF-3 conversion is required,
ALTER TABLE T_TABLE MODIFY TARGET_FIELD_1 char(100) CHARACTER SET utf8;
2)In WMB esql code,for Select query,the column value is picked up as Hexa value and then CAST to BLOB ,BLOB to CHAR done as below,
SELECT ....HEX(TARGET_FIELD_1) as TBL_TARGET_VALUE_C_1,
....
SET Environment.Variables.FlowSpecific.TARGET_VALUE_C_1=CAST(CAST(Environment.Variables.FlowSpecific.TARGET_VALUE_C_1 AS BLOB )AS CHAR CCSID 1208 );
This is working fine...
But even after changing the Table column to UTF-8 ,still in WMB we have to pick up as hex and then from hex to blob and blob to char.
Any idea why we have to do esql level changes also?Without making this change in esql,just with alter table column is not working... |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jul 04, 2014 4:08 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
That may well be because the internal representation is CCSID 1200 and not UTF-8. So in order to insert into the DB you first have to convert to UTF-8...
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Tibor |
| Posted: Fri Jul 04, 2014 6:15 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| GeneRK4 wrote: |
1)We changed the CCSID of the table column for which UTF-3 conversion is required
...
2)In WMB esql code,for Select query,the column value is picked up as Hexa value and then CAST to BLOB ,BLOB to CHAR done as below |
Sorry, but it seems terrible and not really sustainable for the future.
Please check the codepage settings at MySQL and MySQL/ODBC level first, as it was mentioned in my earlier comment. If the problem persist, you can check the ODBC connector if you switch its trace on.
And please give more information about versions of WMB, ODBC, etc. |
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Mon Jul 07, 2014 5:36 am Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
| fjb_saper wrote: |
| That may well be because the internal representation is CCSID 1200 and not UTF-8. |
WMB internal representation is 1208 which is actually UTF-8.Correct me if I am wrong in understanding your statement.
| fjb_saper wrote: |
| So in order to insert into the DB you first have to convert to UTF-8.... |
We are using Select query in WMB.. |
|
| Back to top |
|
|
| kimbert |
| Posted: Mon Jul 07, 2014 12:55 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| WMB internal representation is 1208 which is actually UTF-8. Correct me if I am wrong |
You should check your sources, because that is not correct. Maybe you are getting confused between Unicode and UTF-8 ( not the same thing).
In the message tree, all CHARACTER data is in CCSID 1200 ( UTF-16, platform-dependent byte order ). The parser converts from the input document CCSID to UTF-16 when reading. It converts from UTF-16 to the specified output CCSID ( OutputRoot.Properties.CodedCharSetId ) when writing.
_________________
Before you criticize someone, walk a mile in their shoes. That way you're a mile away, and you have their shoes too. |
|
| Back to top |
|
|
| GeneRK4 |
| Posted: Wed Jul 09, 2014 2:02 am Post subject: |
|
|
Master
Joined: 08 Jul 2013
Posts: 220
|
Thanks...
So ,if we use ALTER table for UTF16 as below,
ALTER TABLE T_TABLE MODIFY TARGET_VALUE_C_1 char(100) CHARACTER SET utf16;
We will be directly able to read the field values without any conversions from Hex->BLOB->CHAR..
Correct me if I am wrong.. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
