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-state=
ments 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 conversi=
on, 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. (d=
ue to the fact that I need to store special characters from 10-12 differe=
nt languages)

So, I understand it "needs" conversion but do not quite know how to overc=
ome this. (I could bypass this using a stored procedure returning a resul=
tset, 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:Leif@TechnologyCatalyst.com]
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 t=
he
translation without formal conversion where the compiled version in the v=
iew
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:Kris.Bekkers@atosorigin.com]=20
Sent: Tuesday, March 21, 2006 6:05 AM
To: win32@lists.mysql.com
Subject: Strange _UTF Problem

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

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

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

CREATE VIEW vAdmin_SelectColumns
AS=20
select=20
c.Table_Catalog TableCatalog,=20
c.table_schema TableSchema,=20
c.TABLE_NAME TableName,=20
c.COLUMN_NAME ColumnName,=20
c.Column_Default ColumnDefault,=20
c.IS_NULLABLE IsNullable,=20
c.DATA_TYPE DataType,=20
c.CHARACTER_MAXIMUM_LENGTH MaxLength,=20
c.numeric_precision NumPrecision,
CASE WHEN (data_type =3D 'set')=20
then=20
-- Set value
trim(Trailing ')' from
SUBSTRING(Column_Type, 5))
else=20
CASE WHEN (data_type =3D 'enum')
then=20
trim(Trailing ')' from
SUBSTRING(Column_Type, 6))=20
else=20
''=20
end
end SetOrEnum
from=20
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')') els=
e
(case when (`c`.`DATA_TYPE` =3D _utf8'enum') then rtrim(substr(`c`.' at l=
ine
1"=20

annyone?

Thx
************************************************************ *************=
***
Disclaimer:=20
This electronic transmission and any files attached to it are strictly=20
confidential and intended solely for the addressee. If you are not=20
the intended addressee, you must not disclose, copy or take any
action in reliance of this transmission. If you have received this=20
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=20
resulting from any virus transmitted.=20
Thank You.
************************************************************ *************=
***

--=20
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=3Dleif@technologycatalyst .com

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

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:Kris.Bekkers@atosorigin.com]
Sent: Thursday, March 23, 2006 12:14 PM
To: Leif@TechnologyCatalyst.com; win32@lists.mysql.com
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:Leif@TechnologyCatalyst.com]
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:Kris.Bekkers@atosorigin.com]
Sent: Tuesday, March 21, 2006 6:05 AM
To: win32@lists.mysql.com
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/win32?unsub=leif@technologycatalyst.c om


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org