Spanish characters and collation latin1_spanish_ci

[ricardfn]

Hello!

I'm following the online documentation and forum, I'm learning a lot but, my problem persists. I explain that:

** I can't load to the database typical spanish characters using this simple script:

drop database DBclpriv;
create database DBclpriv DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
connect DBclpriv;

-- Tabla de los clientes virtuales.
create table TCliente(
NOMBRE varchar(50) not null unique,
APELLIDOS varchar(70) CHARACTER SET latin1 COLLATE latin1_spanish_ci,
primary key(NOMBRE)
) CHARACTER SET latin1 COLLATE latin1_spanish_ci;
-- ---------------- --
-- BANCO DE PRUEBAS --
-- ---------------- --
insert into TCliente values('Ricard','Fernández Ñieto');


** The result I obtain is always:

mysql> SELECT * FROM TCliente;
+--------+-----------------+
| NOMBRE | APELLIDOS |
+--------+-----------------+
| Ricard | Fernßndez Ðieto |
+--------+-----------------+
1 row in set (0.00 sec)

** If I insert values into this table manually using MySQL client command line, typical spanish characters are loaded ok. (I don't understand why):

mysql> INSERT INTO TCliente VALUES('Carácteres','Españoles');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tcliente;
+------------+-----------------+
| NOMBRE | APELLIDOS |
+------------+-----------------+
| Carácteres | Españoles |
| Ricard | Fernßndez Ðieto |
+------------+-----------------+
2 rows in set (0.00 sec)

** Why inserting values using script don't appear spanish characters?
It is a problem to me because, using an Enterprise Java Bean with SQL operations I have the same problem. I think if I solve the load problem using script, using SQL operations into Bean will run ok too.

** I'm using Windows XP SP 2.
** I’m using MySQL Server 4.5 and MySQL Server 4.1.14, I obtain the same result with both versions.

** The configuration I have used in “my.ini” is:

[client]
port=3306
default-character-set=latin1

[mysqld]
init_connect='set collation_connection=latin1_spanish_ci'
default-character-set=latin1
default-collation=latin1_spanish_ci


** With this configuration and creating an user without all privileges I obtain this values for the variables:

mysql> SHOW VARIABLES LIKE 'C%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | C:\Archivos de programa\MySQL\MySQL Server 5.0\shar
e\charsets/ |
| collation_connection | latin1_spanish_ci |
| collation_database | latin1_spanish_ci |
| collation_server | latin1_spanish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
+--------------------------+----------------------------------------------------------------+
13 rows in set (0.00 sec)


** All seems good but, character_set_system value is utf8. Could be the reason spanish characters don’t load ok using script? How I can configure character_set_system?

** I have supervised info into “shared\charsets\latin1.xml” and collation latin1_spanish_ci is implemented as:

<collation name="latin1_spanish_ci">
<map>
00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
10 11 12 13 14 15 16 17 18 19 1A 1B 1C 1D 1E 1F
20 21 22 23 24 25 26 27 28 29 2A 2B 2C 2D 2E 2F
30 31 32 33 34 35 36 37 38 39 3A 3B 3C 3D 3E 3F
40 41 51 53 57 5B 65 67 69 6B 75 77 79 7B 7D 81
8F 91 93 95 98 9A A4 A6 A8 AA AF B3 B4 B5 B6 B7
B8 41 51 53 57 5B 65 67 69 6B 75 77 79 7B 7D 81
8F 91 93 95 98 9A A4 A6 A8 AA AF B9 BA BB BC BF
C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 CA CB CC CD CE CF
D0 D1 D2 D3 D4 D5 D6 D7 D8 D9 DA DB DC DD DE DF
E0 E1 E2 E3 E4 E5 E6 E7 E8 E9 EA EB EC ED EE EF
F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 FA FB FC FD FE FF
41 41 41 41 41 41 41 53 5B 5B 5B 5B 6B 6B 6B 6B
57 7F 81 81 81 81 81 BD 81 9A 9A 9A 9A AA B1 97
41 41 41 41 41 41 41 53 5B 5B 5B 5B 6B 6B 6B 6B
57 7F 81 81 81 81 81 BE 81 9A 9A 9A 9A AA B1 AA
</map>
</collation>

** I’m lost, I fell all given solutions but, I continue with the problem. Could anybody helps me please? Thank you a lot!


Bye!!