Description:
What I am trying to do: Link / Import a view in MS Access 2003 / 2007.
What happens: Every char / varchar field is considered binary by MS Access.
What is the result: The char / varchar fields are shown as in Korean.
LOGS: The SQL.LOG example (Will beattached as .zip to the bug):
db4 17f8-1058 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 10EB1A48
UWORD 2
SWORD -2 <SQL_C_BINARY>
PTR <unknown type>
SQLLEN 512
SQLLEN * 0x0013C668 (3)
db4 17f8-1058 ENTER SQLFetch
HSTMT 10EB1A48
db4 17f8-1058 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 10EB1A48
The view is:
mysql> show create view borrame;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| borrame | CREATE ALGORITHM=UNDEFINED DEFINER=`itx`@`%` SQL SECURITY DEFINER VIEW `borrame` AS select `animals`.`id` AS `id`,cast(`animals`.`name` as char(50) charset utf8) AS `Aname` from `animals` | utf8 | utf8_general_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> show full columns from borrame;
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | mediumint(9) | NULL | NO | | 0 | | select,insert,update,references | |
| Aname | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
How to repeat:
Create a dtabase (latin1/latin1_swedish_ci):
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)) DEFAULT CHARSET='latin1' COLLATE='latin1_bin';
INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
CREATE OR REPLACE VIEW borrame AS select id, name AS Aname from animals;
Open a msaccess database (2003/2007) and link the view borrame.
The Aname looks like Korean.