Mssql query arabic values received as (? ?) question marks

Hello Experts :slight_smile:
I am using the FreePBX 14 with asterisk 13 up-to-date
trying to query a msSQL database everything is OK except when I am query for an Arabic values it appears as question marks ??? ???
here is my configurations files for ODBC
/etc/odbcinst.ini
[FreeTDS]
Description = ODBC for Microsoft SQL
Driver = /lib64/libtdsodbc.so.0
UsageCount = 1
Threading = 2

/etc/odbc.ini
[MSSQL-connector]
Description = MS SQL connection to database
Driver = FreeTDS
Database = DB_name
Server = DB_hostname
UserName = username
Password = password
Trace = No
TDS_Version = 7.0
Port = 1433

/etc/asterisk/res_odbc.conf
[TA_SOPRA]
enabled => yes
dsn => MSSQL-connector
username => username
password => password
pre-connect => yes

/etc/asterisk/func_odbc.conf
[readorders]
dsn=TA_SOPRA
readsql=SELECT day, qty FROM orders WHERE accountID = ‘{SQL_ESC({ARG1})}’

I tried to use multiple Charset like ( UCS-2 ,utf-8 ,big5 ) in the odbc.ini configuration file with no success

the output form the cli is Ok when I run

echo “SELECT accountID, Day, trDate, qty FROM orders” | isql -v MSSQL-connector username password

Could any one help or advise ?

This sounds more like an MS SQLServer or ODBC driver issue, than an Asterisk one.

Big5 is a traditional Chinese character set, and unlikely to be useful for Arabic.

Where are the ? ?s appearing, as, for example, the default console character set may be different from the default SIP one.

I don’t think Asterisk does character set transcoding, and I seem to remember that the default coding in SIP is utf8.

I am trying to query form the dial plan as below
exten => s,n(trans),Set(ARRAY(value1,value2)={ODBC_readorders2({CALLERID(num)},${TR})})
the output:

Executing [s@macro-CustomerService-Transactions:4] Set(“SIP/4848-00000109”, “ARRAY(value1,value2)=???,60”) in new stack

BUT when i issued the command from the terminal directly I have no problems and Arabic appears normally without any issues

[root@freepbx ~]# echo “SELECT accountID, Day, trDate, qty FROM orders” | isql -v MSSQL-connector username password

In that case try running isql using the SHELL function.

Perfect! thanks a lot, I applied the below and it’s OK. Also I added some explanation if it can help someone else:

into the dialplan i used,
exten => 4567,n,Set(TR=“2”)
exten => 4567,n,Set(VAL={SHELL(echo "select day,"1" from orders where ID= {TR}" | isql MSSQL-connector username password -q -b -d,)})
exten => 4567,n,Set(cutVar=${CUT(VAL,1)})

by default it add a new line after the selected value so I added extra “1” in the selection and removed it by CUT() function.

I don’t know if something more easy of my solution?