ODBC write query with accent mark characters

Asterisk Asterisk Support
1

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
1 Like
2

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.

3

Believe me or not, but solution was changing ODBC driver from UTF8 TO ANSI,

thanks mammuthus @ https://dev-qa.com/517516/how-to-force-asterisk-to-write-data-to-db-in-utf-8-encoding for the tip

1 Like
4

I have spent a few hours investigating this. Asterisk is buggy in that regard. If you set ‘Charset=utf8mb4’ in /etc/odbc.ini, select commands in asterisk as well as isql select, update and insert will work correctly. If You do not set charset in odbc.ini, insert and update will work correctly in asterisk.
If you want to have select, update and insert working correctly, you need to set ‘Charset=utf8mb4’ in odbc.ini and you need to workaround double encoded utf-8 characters in your update and insert statements this way:
COALESCE(CONVERT(CAST(CONVERT(‘value’ USING latin1) AS BINARY) USING utf8mb4)),‘value’)
I am not sure if all utf-8 characters are handled correctly but it seems asterisk needs a fix here…