Utf8 errors when using mysql odbc 5.1 connector

Utf8 errors when using mysql odbc 5.1 connector

am 01.02.2008 12:32:45 von Kjartan Andreassen

Hi,

I have the following table filled with utf8 data:

CREATE TABLE `accounts2` (
`account_id` int(10) unsigned NOT NULL auto_increment,
`account_name` varchar(250) NOT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D5 DEFAULT CHARSET=3Dutf8

When using the odbc 5.1 (or 3.51) connector I'm not getting utf8 data
correctly back to my client.=20
It looks like im getting utf-8 data as ISO, eg. øæå, instead=
of
æø=E5. I've tried different table creations with collations etc, but wi=
th
no luck.

Any help would be appreciated.

See below for my settings.

Best regards

Kjartan Andreassen

+---------------------------------+------------------------- ------------+
| Variable_name | Value |
+---------------------------------+------------------------- ------------+
| auto_increment_increment | 1 |=20
| auto_increment_offset | 1 |=20
| automatic_sp_privileges | ON |=20
| back_log | 50 |=20
| basedir | / |=20
| binlog_cache_size | 32768 |=20
| bulk_insert_buffer_size | 8388608 |=20
| character_set_client | latin1 |=20
| character_set_connection | latin1 |=20
| character_set_database | latin1 |=20
| character_set_filesystem | binary |=20
| character_set_results | latin1 |=20
| character_set_server | utf8 |=20
| character_set_system | utf8 |=20
| character_sets_dir | /usr/share/mysql/charsets/ |=20
| collation_connection | latin1_swedish_ci |=20
| collation_database | latin1_swedish_ci |=20
| collation_server | utf8_general_ci |=20
| completion_type | 0 |=20
| concurrent_insert | 1 |=20
| connect_timeout | 5 |=20
| datadir | /var/lib/mysql/ |=20
| date_format | %Y-%m-%d |=20
| datetime_format | %Y-%m-%d %H:%i:%s |=20
| default_week_format | 0 |=20
| delay_key_write | ON |=20
| delayed_insert_limit | 100 |=20
| delayed_insert_timeout | 300 |=20
| delayed_queue_size | 1000 |=20
| div_precision_increment | 4 |=20
| engine_condition_pushdown | OFF |=20
| expire_logs_days | 0 |=20
| flush | OFF |=20
| flush_time | 0 |=20
| ft_boolean_syntax | + -><()~*:""&| |=20
| ft_max_word_len | 84 |=20
| ft_min_word_len | 2 |=20
| ft_query_expansion_limit | 20 |=20
| ft_stopword_file | |=20
| group_concat_max_len | 1024 |=20
| have_archive | YES |=20
| have_bdb | NO |=20
| have_blackhole_engine | YES |=20
| have_compress | YES |=20
| have_crypt | YES |=20
| have_csv | YES |=20
| have_dynamic_loading | NO |=20
| have_example_engine | YES |=20
| have_federated_engine | YES |=20
| have_geometry | YES |=20
| have_innodb | YES |=20
| have_isam | NO |=20
| have_merge_engine | YES |=20
| have_ndbcluster | DISABLED |=20
| have_openssl | NO |=20
| have_ssl | NO |=20
| have_query_cache | YES |=20
| have_raid | NO |=20
| have_rtree_keys | YES |=20
| have_symlink | YES |=20
| init_connect | |=20
| init_file | |=20
| init_slave | |=20
| innodb_additional_mem_pool_size | 1048576 |=20
| innodb_autoextend_increment | 8 |=20
| innodb_buffer_pool_awe_mem_mb | 0 |=20
| innodb_buffer_pool_size | 8388608 |=20
| innodb_checksums | ON |=20
| innodb_commit_concurrency | 0 |=20
| innodb_concurrency_tickets | 500 |=20
| innodb_data_file_path | ibdata1:10M:autoextend |=20
| innodb_data_home_dir | |=20
| innodb_doublewrite | ON |=20
| innodb_fast_shutdown | 1 |=20
| innodb_file_io_threads | 4 |=20
| innodb_file_per_table | OFF |=20
| innodb_flush_log_at_trx_commit | 1 |=20
| innodb_flush_method | |=20
| innodb_force_recovery | 0 |=20
| innodb_lock_wait_timeout | 50 |=20
| innodb_locks_unsafe_for_binlog | OFF |=20
| innodb_log_arch_dir | |=20
| innodb_log_archive | OFF |=20
| innodb_log_buffer_size | 1048576 |=20
| innodb_log_file_size | 5242880 |=20
| innodb_log_files_in_group | 2 |=20
| innodb_log_group_home_dir | ./ |=20
| innodb_max_dirty_pages_pct | 90 |=20
| innodb_max_purge_lag | 0 |=20
| innodb_mirrored_log_groups | 1 |=20
| innodb_open_files | 300 |=20
| innodb_rollback_on_timeout | OFF |=20
| innodb_support_xa | ON |=20
| innodb_sync_spin_loops | 20 |=20
| innodb_table_locks | ON |=20
| innodb_thread_concurrency | 8 |=20
| innodb_thread_sleep_delay | 10000 |=20
| interactive_timeout | 28800 |=20
| join_buffer_size | 131072 |=20
| key_buffer_size | 8388600 |=20
| key_cache_age_threshold | 300 |=20
| key_cache_block_size | 1024 |=20
| key_cache_division_limit | 100 |=20
| language | /usr/share/mysql/english/ |=20
| large_files_support | ON |=20
| large_page_size | 0 |=20
| large_pages | OFF |=20
| lc_time_names | en_US |=20
| license | GPL |=20
| local_infile | ON |=20
| locked_in_memory | OFF |=20
| log | ON |=20
| log_bin | OFF |=20
| log_bin_trust_function_creators | OFF |=20
| log_error | |=20
| log_queries_not_using_indexes | OFF |=20
| log_slave_updates | OFF |=20
| log_slow_queries | OFF |=20
| log_warnings | 1 |=20
| long_query_time | 10 |=20
| low_priority_updates | OFF |=20
| lower_case_file_system | OFF |=20
| lower_case_table_names | 0 |=20
| max_allowed_packet | 20970496 |=20
| max_binlog_cache_size | 4294967295 |=20
| max_binlog_size | 1073741824 |=20
| max_connect_errors | 10 |=20
| max_connections | 1000 |=20
| max_delayed_threads | 20 |=20
| max_error_count | 64 |=20
| max_heap_table_size | 16777216 |=20
| max_insert_delayed_threads | 20 |=20
| max_join_size | 18446744073709551615 |=20
| max_length_for_sort_data | 1024 |=20
| max_prepared_stmt_count | 16382 |=20
| max_relay_log_size | 0 |=20
| max_seeks_for_key | 4294967295 |=20
| max_sort_length | 1024 |=20
| max_sp_recursion_depth | 0 |=20
| max_tmp_tables | 32 |=20
| max_user_connections | 0 |=20
| max_write_lock_count | 4294967295 |=20
| multi_range_count | 256 |=20
| myisam_data_pointer_size | 6 |=20
| myisam_max_sort_file_size | 2147483647 |=20
| myisam_recover_options | OFF |=20
| myisam_repair_threads | 1 |=20
| myisam_sort_buffer_size | 8388608 |=20
| myisam_stats_method | nulls_unequal |=20
| ndb_autoincrement_prefetch_sz | 32 |=20
| ndb_force_send | ON |=20
| ndb_use_exact_count | ON |=20
| ndb_use_transactions | ON |=20
| ndb_cache_check_time | 0 |=20
| ndb_connectstring | |=20
| net_buffer_length | 16384 |=20
| net_read_timeout | 30 |=20
| net_retry_count | 10 |=20
| net_write_timeout | 60 |=20
| new | OFF |=20
| old_passwords | OFF |=20
| open_files_limit | 5000 |=20
| optimizer_prune_level | 1 |=20
| optimizer_search_depth | 62 |=20
| port | 3306 |=20
| preload_buffer_size | 32768 |=20
| profiling | OFF |=20
| profiling_history_size | 15 |=20
| protocol_version | 10 |=20
| query_alloc_block_size | 8192 |=20
| query_cache_limit | 1048576 |=20
| query_cache_min_res_unit | 4096 |=20
| query_cache_size | 0 |=20
| query_cache_type | ON |=20
| query_cache_wlock_invalidate | OFF |=20
| query_prealloc_size | 8192 |=20
| range_alloc_block_size | 2048 |=20
| read_buffer_size | 131072 |=20
| read_only | OFF |=20
| read_rnd_buffer_size | 262144 |=20
| relay_log_purge | ON |=20
| relay_log_space_limit | 0 |=20
| rpl_recovery_rank | 0 |=20
| secure_auth | OFF |=20
| secure_file_priv | |=20
| server_id | 0 |=20
| skip_external_locking | ON |=20
| skip_networking | OFF |=20
| skip_show_database | OFF |=20
| slave_compressed_protocol | OFF |=20
| slave_load_tmpdir | /tmp/ |=20
| slave_net_timeout | 3600 |=20
| slave_skip_errors | OFF |=20
| slave_transaction_retries | 10 |=20
| slow_launch_time | 2 |=20
| socket | /var/lib/mysql/mysql.sock |=20
| sort_buffer_size | 2097144 |=20
| sql_big_selects | ON |=20
| sql_mode | |=20
| sql_notes | ON |=20
| sql_warnings | OFF |=20
| ssl_ca | |=20
| ssl_capath | |=20
| ssl_cert | |=20
| ssl_cipher | |=20
| ssl_key | |=20
| storage_engine | MyISAM |=20
| sync_binlog | 0 |=20
| sync_frm | ON |=20
| system_time_zone | CET |=20
| table_cache | 64 |=20
| table_lock_wait_timeout | 50 |=20
| table_type | MyISAM |=20
| thread_cache_size | 0 |=20
| thread_stack | 126976 |=20
| time_format | %H:%i:%s |=20
| time_zone | SYSTEM |=20
| timed_mutexes | OFF |=20
| tmp_table_size | 33554432 |=20
| tmpdir | /tmp/ |=20
| transaction_alloc_block_size | 8192 |=20
| transaction_prealloc_size | 4096 |=20
| tx_isolation | REPEATABLE-READ |=20
| updatable_views_with_limit | YES |=20
| version | 5.0.45-community-log |=20
| version_comment | MySQL Community Edition (GPL) |=20
| version_compile_machine | i686 |=20
| version_compile_os | pc-linux-gnu |=20
| wait_timeout | 28800 |=20
+---------------------------------+------------------------- ------------+=

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

RE: Utf8 errors when using mysql odbc 5.1 connector

am 01.02.2008 15:39:17 von Osvaldo Sommer

ODBC do not use unicode. For that you need to use connector 5 .Net

Osvaldo Sommer

-----Mensaje original-----
De: Kjartan Andreassen [mailto:kjartan@webhuset.no]=20
Enviado el: viernes, 01 de febrero de 2008 05:33 a.m.
Para: myodbc@lists.mysql.com
Asunto: Utf8 errors when using mysql odbc 5.1 connector

Hi,

I have the following table filled with utf8 data:

CREATE TABLE `accounts2` (
`account_id` int(10) unsigned NOT NULL auto_increment,
`account_name` varchar(250) NOT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D5 DEFAULT CHARSET=3Dutf8

When using the odbc 5.1 (or 3.51) connector I'm not getting utf8 data
correctly back to my client.=20
It looks like im getting utf-8 data as ISO, eg. øæå, =
instead of
æø=E5. I've tried different table creations with collations etc, but =
with
no luck.

Any help would be appreciated.

See below for my settings.

Best regards

Kjartan Andreassen

+---------------------------------+------------------------- ------------+=

| Variable_name | Value |
+---------------------------------+------------------------- ------------+=

| auto_increment_increment | 1 |=20
| auto_increment_offset | 1 |=20
| automatic_sp_privileges | ON |=20
| back_log | 50 |=20
| basedir | / |=20
| binlog_cache_size | 32768 |=20
| bulk_insert_buffer_size | 8388608 |=20
| character_set_client | latin1 |=20
| character_set_connection | latin1 |=20
| character_set_database | latin1 |=20
| character_set_filesystem | binary |=20
| character_set_results | latin1 |=20
| character_set_server | utf8 |=20
| character_set_system | utf8 |=20
| character_sets_dir | /usr/share/mysql/charsets/ |=20
| collation_connection | latin1_swedish_ci |=20
| collation_database | latin1_swedish_ci |=20
| collation_server | utf8_general_ci |=20
| completion_type | 0 |=20
| concurrent_insert | 1 |=20
| connect_timeout | 5 |=20
| datadir | /var/lib/mysql/ |=20
| date_format | %Y-%m-%d |=20
| datetime_format | %Y-%m-%d %H:%i:%s |=20
| default_week_format | 0 |=20
| delay_key_write | ON |=20
| delayed_insert_limit | 100 |=20
| delayed_insert_timeout | 300 |=20
| delayed_queue_size | 1000 |=20
| div_precision_increment | 4 |=20
| engine_condition_pushdown | OFF |=20
| expire_logs_days | 0 |=20
| flush | OFF |=20
| flush_time | 0 |=20
| ft_boolean_syntax | + -><()~*:""&| |=20
| ft_max_word_len | 84 |=20
| ft_min_word_len | 2 |=20
| ft_query_expansion_limit | 20 |=20
| ft_stopword_file | |=20
| group_concat_max_len | 1024 |=20
| have_archive | YES |=20
| have_bdb | NO |=20
| have_blackhole_engine | YES |=20
| have_compress | YES |=20
| have_crypt | YES |=20
| have_csv | YES |=20
| have_dynamic_loading | NO |=20
| have_example_engine | YES |=20
| have_federated_engine | YES |=20
| have_geometry | YES |=20
| have_innodb | YES |=20
| have_isam | NO |=20
| have_merge_engine | YES |=20
| have_ndbcluster | DISABLED |=20
| have_openssl | NO |=20
| have_ssl | NO |=20
| have_query_cache | YES |=20
| have_raid | NO |=20
| have_rtree_keys | YES |=20
| have_symlink | YES |=20
| init_connect | |=20
| init_file | |=20
| init_slave | |=20
| innodb_additional_mem_pool_size | 1048576 |=20
| innodb_autoextend_increment | 8 |=20
| innodb_buffer_pool_awe_mem_mb | 0 |=20
| innodb_buffer_pool_size | 8388608 |=20
| innodb_checksums | ON |=20
| innodb_commit_concurrency | 0 |=20
| innodb_concurrency_tickets | 500 |=20
| innodb_data_file_path | ibdata1:10M:autoextend |=20
| innodb_data_home_dir | |=20
| innodb_doublewrite | ON |=20
| innodb_fast_shutdown | 1 |=20
| innodb_file_io_threads | 4 |=20
| innodb_file_per_table | OFF |=20
| innodb_flush_log_at_trx_commit | 1 |=20
| innodb_flush_method | |=20
| innodb_force_recovery | 0 |=20
| innodb_lock_wait_timeout | 50 |=20
| innodb_locks_unsafe_for_binlog | OFF |=20
| innodb_log_arch_dir | |=20
| innodb_log_archive | OFF |=20
| innodb_log_buffer_size | 1048576 |=20
| innodb_log_file_size | 5242880 |=20
| innodb_log_files_in_group | 2 |=20
| innodb_log_group_home_dir | ./ |=20
| innodb_max_dirty_pages_pct | 90 |=20
| innodb_max_purge_lag | 0 |=20
| innodb_mirrored_log_groups | 1 |=20
| innodb_open_files | 300 |=20
| innodb_rollback_on_timeout | OFF |=20
| innodb_support_xa | ON |=20
| innodb_sync_spin_loops | 20 |=20
| innodb_table_locks | ON |=20
| innodb_thread_concurrency | 8 |=20
| innodb_thread_sleep_delay | 10000 |=20
| interactive_timeout | 28800 |=20
| join_buffer_size | 131072 |=20
| key_buffer_size | 8388600 |=20
| key_cache_age_threshold | 300 |=20
| key_cache_block_size | 1024 |=20
| key_cache_division_limit | 100 |=20
| language | /usr/share/mysql/english/ |=20
| large_files_support | ON |=20
| large_page_size | 0 |=20
| large_pages | OFF |=20
| lc_time_names | en_US |=20
| license | GPL |=20
| local_infile | ON |=20
| locked_in_memory | OFF |=20
| log | ON |=20
| log_bin | OFF |=20
| log_bin_trust_function_creators | OFF |=20
| log_error | |=20
| log_queries_not_using_indexes | OFF |=20
| log_slave_updates | OFF |=20
| log_slow_queries | OFF |=20
| log_warnings | 1 |=20
| long_query_time | 10 |=20
| low_priority_updates | OFF |=20
| lower_case_file_system | OFF |=20
| lower_case_table_names | 0 |=20
| max_allowed_packet | 20970496 |=20
| max_binlog_cache_size | 4294967295 |=20
| max_binlog_size | 1073741824 |=20
| max_connect_errors | 10 |=20
| max_connections | 1000 |=20
| max_delayed_threads | 20 |=20
| max_error_count | 64 |=20
| max_heap_table_size | 16777216 |=20
| max_insert_delayed_threads | 20 |=20
| max_join_size | 18446744073709551615 |=20
| max_length_for_sort_data | 1024 |=20
| max_prepared_stmt_count | 16382 |=20
| max_relay_log_size | 0 |=20
| max_seeks_for_key | 4294967295 |=20
| max_sort_length | 1024 |=20
| max_sp_recursion_depth | 0 |=20
| max_tmp_tables | 32 |=20
| max_user_connections | 0 |=20
| max_write_lock_count | 4294967295 |=20
| multi_range_count | 256 |=20
| myisam_data_pointer_size | 6 |=20
| myisam_max_sort_file_size | 2147483647 |=20
| myisam_recover_options | OFF |=20
| myisam_repair_threads | 1 |=20
| myisam_sort_buffer_size | 8388608 |=20
| myisam_stats_method | nulls_unequal |=20
| ndb_autoincrement_prefetch_sz | 32 |=20
| ndb_force_send | ON |=20
| ndb_use_exact_count | ON |=20
| ndb_use_transactions | ON |=20
| ndb_cache_check_time | 0 |=20
| ndb_connectstring | |=20
| net_buffer_length | 16384 |=20
| net_read_timeout | 30 |=20
| net_retry_count | 10 |=20
| net_write_timeout | 60 |=20
| new | OFF |=20
| old_passwords | OFF |=20
| open_files_limit | 5000 |=20
| optimizer_prune_level | 1 |=20
| optimizer_search_depth | 62 |=20
| port | 3306 |=20
| preload_buffer_size | 32768 |=20
| profiling | OFF |=20
| profiling_history_size | 15 |=20
| protocol_version | 10 |=20
| query_alloc_block_size | 8192 |=20
| query_cache_limit | 1048576 |=20
| query_cache_min_res_unit | 4096 |=20
| query_cache_size | 0 |=20
| query_cache_type | ON |=20
| query_cache_wlock_invalidate | OFF |=20
| query_prealloc_size | 8192 |=20
| range_alloc_block_size | 2048 |=20
| read_buffer_size | 131072 |=20
| read_only | OFF |=20
| read_rnd_buffer_size | 262144 |=20
| relay_log_purge | ON |=20
| relay_log_space_limit | 0 |=20
| rpl_recovery_rank | 0 |=20
| secure_auth | OFF |=20
| secure_file_priv | |=20
| server_id | 0 |=20
| skip_external_locking | ON |=20
| skip_networking | OFF |=20
| skip_show_database | OFF |=20
| slave_compressed_protocol | OFF |=20
| slave_load_tmpdir | /tmp/ |=20
| slave_net_timeout | 3600 |=20
| slave_skip_errors | OFF |=20
| slave_transaction_retries | 10 |=20
| slow_launch_time | 2 |=20
| socket | /var/lib/mysql/mysql.sock |=20
| sort_buffer_size | 2097144 |=20
| sql_big_selects | ON |=20
| sql_mode | |=20
| sql_notes | ON |=20
| sql_warnings | OFF |=20
| ssl_ca | |=20
| ssl_capath | |=20
| ssl_cert | |=20
| ssl_cipher | |=20
| ssl_key | |=20
| storage_engine | MyISAM |=20
| sync_binlog | 0 |=20
| sync_frm | ON |=20
| system_time_zone | CET |=20
| table_cache | 64 |=20
| table_lock_wait_timeout | 50 |=20
| table_type | MyISAM |=20
| thread_cache_size | 0 |=20
| thread_stack | 126976 |=20
| time_format | %H:%i:%s |=20
| time_zone | SYSTEM |=20
| timed_mutexes | OFF |=20
| tmp_table_size | 33554432 |=20
| tmpdir | /tmp/ |=20
| transaction_alloc_block_size | 8192 |=20
| transaction_prealloc_size | 4096 |=20
| tx_isolation | REPEATABLE-READ |=20
| updatable_views_with_limit | YES |=20
| version | 5.0.45-community-log |=20
| version_comment | MySQL Community Edition (GPL) |=20
| version_compile_machine | i686 |=20
| version_compile_os | pc-linux-gnu |=20
| wait_timeout | 28800 |=20
+---------------------------------+------------------------- ------------+=

--=20
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:
http://lists.mysql.com/myodbc?unsub=3Dosommerh@intelnett.com

No virus found in this incoming message.
Checked by AVG Free Edition.=20
Version: 7.5.516 / Virus Database: 269.19.18/1255 - Release Date: =
01/02/2008
09:59 a.m.
=20

No virus found in this outgoing message.
Checked by AVG Free Edition.=20
Version: 7.5.516 / Virus Database: 269.19.18/1255 - Release Date: =
01/02/2008
09:59 a.m.
=20


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Re: Utf8 errors when using mysql odbc 5.1 connector

am 01.02.2008 17:33:10 von Jim Winstead

On Fri, Feb 01, 2008 at 08:39:17AM -0600, Osvaldo Sommer wrote:
> ODBC do not use unicode. For that you need to use connector 5 .Net

That is not correct. Connector/ODBC 5.1 handles Unicode data correctly.

Kjartan Andreassen wrote:
> When using the odbc 5.1 (or 3.51) connector I'm not getting utf8 data
> correctly back to my client.
> It looks like im getting utf-8 data as ISO, eg. ??????, instead of
> ???. I've tried different table creations with collations etc, but with
> no luck.

How are you retrieving the data? If you are using SQL_C_CHAR, you will
get data in the latin1 character set. For Unicode data, you need to use
SQL_C_WCHAR. There is no portable way to get Unicode data as UTF-8 data
directly using ODBC.

Jim Winstead
MySQL Inc.

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org