List:MySQL on Win32« Previous MessageNext Message »
From:Kris Bekkers Date:March 21 2006 11:05am
Subject:Strange _UTF Problem
View as plain text  
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.
****************************************************************************
Thread
Strange _UTF ProblemKris Bekkers21 Mar