ODBC write query with accent mark characters

Hi,

I’m facing a charset/language problem with my ODBC write queries. When I update a field with a special character it gets wrongly writen to database:

Lets set to blank the agent group field

asterisk -rx 'odbc write ODBC_CALL_LOG.UPDATE 1419669 agent_group,'
	UPDATE `call` SET agent_group = '' WHERE id = '1419669'
asterisk -rx 'odbc write ODBC_CALL_LOG.UPDATE 1419669 agent_group, exec'
	Affected 1 rows.  Query executed on handle 0 [asterisk]

Now read the empty value

asterisk -rx 'odbc read ODBC_SELECT agent_group,call,id,1419669'
	SELECT agent_group FROM `call` WHERE id='1419669'
asterisk -rx 'odbc read ODBC_SELECT agent_group,call,id,1419669 exec'
	agent_group           
	Returned 1 row.  Query executed on handle 0 [asterisk]

Lets now write a value with an acenturation mark “Dirección”

asterisk -rx 'odbc write ODBC_CALL_LOG.UPDATE 1419669 agent_group,dirección'
	UPDATE `call` SET agent_group = 'dirección' WHERE id = '1419669'
asterisk -rx 'odbc write ODBC_CALL_LOG.UPDATE 1419669 agent_group,dirección exec'
	Affected 1 rows.  Query executed on handle 0 [asterisk]

When I retrieve the value its wrong.

asterisk -rx 'odbc read ODBC_SELECT agent_group,call,id,1419669 exec'
	agent_group           dirección
	Returned 1 row.  Query executed on handle 0 [asterisk]

If I repeat the same actions with isql command line

echo 'UPDATE `call` SET agent_group = '\'''\'' WHERE id = 1419669' | isql asterisk-connector -c -d, | egrep -v ^"[\+\|]|^SQL"
echo 'SELECT agent_group FROM `call` WHERE id=1419669'  | isql asterisk-connector -c -d, | egrep -v ^"[\+\|]|^SQL"
agent_group
echo 'UPDATE `call` SET agent_group = '\'Dirección''\'' WHERE id = 1419669' | isql asterisk-connector -c -d, | egrep -v ^"[\+\|]|^SQL"
echo 'SELECT agent_group FROM `call` WHERE id=1419669'  | isql asterisk-connector -c -d, | egrep -v ^"[\+\|]|^SQL"
	agent_group
	Dirección

asterisk -rx 'odbc read ODBC_SELECT agent_group,call,id,1419669 exec'
	agent_group           Dirección
	Returned 1 row.  Query executed on handle 0 [asterisk]

I get the expected result.

I’m running

  • Asterisk 13.13.0
  • Ubuntu 14.04.5 LTS
  • unixODBC 2.3.4
  • mysql 5.5.57

My Configuration:

cat /etc/odbcinst.ini

[ODBC Drivers]
LibMyODBC5=installed
Oracle=installed

[LibMyODBC5]
Driver=/usr/local/lib/libmyodbc5w.so
Description = Unicode MySQL Connector/ODBC 5.3.8

[LibMyODBC5_ANSI]
Driver=/usr/local/lib/libmyodbc5a.so
Description = ANSI MySQL Connector/ODBC 5.3.8

cat /etc/odbc.ini

[ODBC Data Sources]
asterisk-mysql = Conector MySQL para Asterisk
Oracle = Conector para OracleCGES

[asterisk-connector]
Driver       = LibMyODBC5
Description  = conector para asterisk
SERVER       = accapre.backend.db.vip
PORT         = 3306
USER         = XXXXXX
Password     = XXXXXXXX
Database     = pbx
OPTION       = 3
SOCKET       =
Charset	     = UTF8
FileUsage    = 1
Pooling      = Yes
CPTimeout    = 120
readtimeout  = 2

cat res_odbc.conf |egrep -v '^;|^$' |head -n 20

[ENV]
[asterisk]
enabled => yes
dsn => asterisk-connector
pre-connect => yes
connect_timeout => 2
max_connections => 20

You should check the various MySQL charsets as well as the terminal charsets (character_set_connection, character_set_client, character_set_results). Even if everything is UTF-8 and you are using a Windows terminal, the client side maybe ANSI. To get a feeling what is happening you could start by playing with “set names utf8/latin1” and see whether it makes a difference.