RE: Strange _UTF Problem - views in Mysql

UTF and single char formats are so different that I would suggest it is
unreasonable to think they can be treated the same. Also compilation is very
different than a run time warning.

Sounds like you have the issues in hand and need to convert them all to the
same character representation. Make all tables UTF8. Single and multi char
can then be supported.

-----Original Message-----
From: Bekkers, Kris [mailto:[email protected]] 
Sent: Thursday, March 23, 2006 12:14 PM
To: [email protected]; [email protected]
Subject: RE: Strange _UTF Problem - views in Mysql

Thanx Leif,

I already figured that out.
But what I do not understand:
when issuing the command it issues a warning. When creating the
sql-statements into a View the view "automaticly" adds conversion options
and that results into an error.

I wonder if there is a way to overcome this problem of automatic conversion,
or a simple guide on how to avoid such problems.

When I exame the information_schema the information_schema has the UTF8. THe
other databases (the one where the view is created in) has latin1. (due to
the fact that I need to store special characters from 10-12 different
languages)

So, I understand it "needs" conversion but do not quite know how to overcome
this. (I could bypass this using a stored procedure returning a resultset,
that would simply issue a warning that there has been a conversion, but that
is a "bypass" of the original problem"

Thanx

Kris


-----Original Message-----
From: Leif Johnston [mailto:[email protected]]
Sent: dinsdag 21 maart 2006 14:12
To: Bekkers, Kris
Subject: RE: Strange _UTF Problem


I don't have the complete answer, but I suggest the answer lay in the
dynamic conversion of the string value to UTF8. The query likely forces the
translation without formal conversion where the compiled version in the view
does not allow that kind of ad hoc conversion.

Not sure that helps, but it would be my expectation. 'enum' <> utf8('enum')

Leif

-----Original Message-----
From: Bekkers, Kris [mailto:[email protected]] 
Sent: Tuesday, March 21, 2006 6:05 AM
To: [email protected]
Subject: Strange _UTF Problem

Anyone saw this error? (never mind the usefullness of the query, simply
playing arround)

This query works fine:
select 
        c.Table_Catalog TableCatalog, 
        c.table_schema TableSchema, 
        c.TABLE_NAME TableName, 
        c.COLUMN_NAME ColumnName, 
        c.Column_Default ColumnDefault, 
        c.IS_NULLABLE IsNullable, 
        c.DATA_TYPE DataType, 
        c.CHARACTER_MAXIMUM_LENGTH MaxLength, 
        c.numeric_precision NumPrecision,
        CASE WHEN (data_type = 'set') 
			then 
				-- Set value
				trim(Trailing ')' from
SUBSTRING(Column_Type, 5))
			else 
				CASE WHEN (data_type = 'enum')
					then 
						trim(Trailing ')' from
SUBSTRING(Column_Type, 6)) 
					else 
						'' 
					end
			end SetOrEnum
    from 
        information_schema.columns c

However when creating a view based on the query it fails:

CREATE VIEW vAdmin_SelectColumns
AS 
select 
        c.Table_Catalog TableCatalog, 
        c.table_schema TableSchema, 
        c.TABLE_NAME TableName, 
        c.COLUMN_NAME ColumnName, 
        c.Column_Default ColumnDefault, 
        c.IS_NULLABLE IsNullable, 
        c.DATA_TYPE DataType, 
        c.CHARACTER_MAXIMUM_LENGTH MaxLength, 
        c.numeric_precision NumPrecision,
        CASE WHEN (data_type = 'set') 
			then 
				-- Set value
				trim(Trailing ')' from
SUBSTRING(Column_Type, 5))
			else 
				CASE WHEN (data_type = 'enum')
					then 
						trim(Trailing ')' from
SUBSTRING(Column_Type, 6)) 
					else 
						'' 
					end
			end SetOrEnum
    from 
        information_schema.columns c

Then selecting the records from the view :
select * from vAdmin_SelectColumns gives me the error:

[Error] Script lines: 1-2 --------------------------
 You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '_utf8')') else
(case when (`c`.`DATA_TYPE` = _utf8'enum') then rtrim(substr(`c`.' at line
1" 

annyone?

Thx
****************************************************************************
Disclaimer: 
This electronic transmission and any files attached to it are strictly 
confidential and intended solely for the addressee. If you are not 
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this 
transmission in error, please notify the sender by return and delete
the transmission.  Although the sender endeavors to maintain a
computer virus free network, the sender does not warrant that this
transmission is virus-free and will not be liable for any damages 
resulting from any virus transmitted. 
Thank You.
****************************************************************************

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/[email protected]


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]