Variable Collation doesn't match Extractvalue Variable
Posted by: Jonny Test
Date: December 14, 2009 10:00PM

Hello everyone.

I come with what I hope is an easy issue to resolve.

I have the following lines of code:

SET v_DetailPath = CONCAT('/Recipe/RecipeDetails[',v_DetailLoop,']/');

SELECT v_RecipeID
, ExtractValue (v_XML, CONCAT(v_DetailPath,'@StepTypeID')) AS StepType
, ExtractValue (v_XML, CONCAT(v_DetailPath,'@SortOrder')) AS SortOrder
, ExtractValue (v_XML, CONCAT(v_DetailPath,'Header/text()')) AS Header
, ExtractValue (v_XML, CONCAT(v_DetailPath,'StepDetails/text()')) AS StepDetails;

When trying to run these two statements I get the following error:

Error 12/14/2009 10:54:40 PM 0:00:00.053 MySQL Database Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'extractvalue' 15 0

Now, I went and figured out that the v_DetailPath is the one using utf8_general_ci, and the code pulling from the xml is the utf8_unicode_ci.

I have all my tables and database settings set to utf8_unicode_ci.

What I am trying to figure out is two this.

1) how can I convert v_DetailPath to utf8_unicode_ci? I have tried the convert function, but I apparently am not using it correctly.
2) why if my setting in database are what they say below, why is my variable getting set to general and not unicode?

Variable_name Value
collation_connection utf8_unicode_ci
collation_database utf8_unicode_ci
collation_server utf8_unicode_ci


Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets

Note that this will eventially get dropped into a table and as such, I have verified that the table also has the same unicode collation.

Any help will be greatly appreciated.

Sincerely,

Johny Test

Options: ReplyQuote


Subject
Views
Written By
Posted
Variable Collation doesn't match Extractvalue Variable
4943
December 14, 2009 10:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.