Getting Data from a Remote Microsoft SQL DB in the Dialplan

[PitzKey (Itzik)]

Hello everyone,

Before I break stuff, I’m wondering if anyone has ever done it and can provide some tips.

My goal is to connect from the dialplan to a remote MS SQL DB and route based on the data from the DB. Similar to:

exten => _.,n,MYSQL(Connect connid localhost ${AMPDBUSER} ${AMPDBPASS} ${AMPDBNAME})
exten => _.,n,MYSQL(Query resultid ${connid} SELECT item from table where field like '${CALLERID(num)}')
exten => _.,n,MYSQL(Fetch fetchid ${resultid} MyVar)
exten => _.,n,MYSQL(Disconnect ${connid})
exten => _.,n,GotoIf($["foo" = "${MyVar}"]?continue:check)

The MS Server is on the same local network.

Any pointers appreciated.

Thanks

[dicko]

MS changed their attitude

Install the Microsoft ODBC driver for SQL Server (Linux) - SQL Server

[PitzKey (Itzik)]

Hey Dicko,

Honestly, it’s my first time doing this and have trouble figuring this out. Would you be able to help me out here?

I believe I installed the driver and added the server to the ini file. Not sure where to take it from here.

Thanks

[dicko]

Show us your odbcinst.ini and your odbc.ini

[billsimon]

Once you have the ODBC connection arranged, I suggest using func_odbc instead of putting the SQL statements directly into your dialplan. The best documentation for it is in the sample config (https://github.com/asterisk/asterisk/blob/master/configs/samples/func_odbc.conf.sample).

[PitzKey (Itzik)]

Here you go

@dicko

odbcinst.ini

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1

And the other file

@dicko

odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to 'asteriskcdrdb' database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

[Remote-MSSQL]
Description=Remote MySQL Server
driver=ODBC Driver 13 for SQL Server
server=ip.of.the.server,49497
trace=no

There is no issue connecting to the MSSQL Server, I verified by installing sql-cli - npm

And then connected to the server by running

mssql -s ip.of.the.server -o 49497 -u USERNAME -p PASSWORD

[billsimon]

@PitzKey

And then connected to the server by running

mssql -s ip.of.the.server -o 49497 -u USERNAME -p PASSWORD

A more accurate test would be via the odbc driver using the isql command, e.g.

isql Remote-MSSQL username password

type “help” to give a listing of tables.

You can check against a known working ODBC connection by connecting to your MySQL-asteriskcdrdb DSN using the credentials found in /etc/freepbx.conf.

[dicko]

the link i posted suggests only one version of the driver be installed.

Probably remove the older one.

[PitzKey (Itzik)]

Getting somewhere…

First of all, thanks so much to both of you.

So, I can successfully connect now (with both drivers still installed) I rebooted in the meantime, I guess that did something.

[root@freepbx asterisk]# isql Remote-MSSQL username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT location FROM table WHERE unitName='1022'
+---------------------+
| table        |
+---------------------+
| 1234          |
+---------------------+
SQLRowCount returns 0
1 rows fetched

in the ODBC custom file I have:

[root@freepbx asterisk]# cat res_odbc_custom.conf
[REMOTEMSSQL]
enabled=>yes
dsn=>Remote-MSSQL
pre-connect=>yes
max_connections=>5
username=>username
password=>password
readsql=>SELECT location FROM table WHERE unitName='${SQL_ESC(${ARG1})}'

Asterisk sees it:

[root@freepbx asterisk]# asterisk -x"odbc show all"

ODBC DSN Settings
-----------------

  Name:   REMOTEMSSQL
  DSN:    Remote-MSSQL
    Number of active connections: 1 (out of 5)
    Logging: Disabled

  Name:   asteriskcdrdb
  DSN:    MySQL-asteriskcdrdb
    Number of active connections: 2 (out of 5)
    Logging: Disabled

…So I wrote a dialplan:

[test-odbc]
exten => s,1,Noop(Getting there)
exten => s,n,Set(val=${ODBC_REMOTEMSSQL(1022)})
exten => s,n,Noop(Val is: ${val})
exten => s,n,Playback(beep)
exten => s,n,Hangup()

But it fails. ‘Function ODBC_REMOTEMSSQL’

 Executing [s@test-odbc:1] NoOp("Local/6406@from-internal-00000002;1", "Getting here") in new stack
[2020-12-30 16:58:52] ERROR[6686][C-00000006]: pbx_functions.c:608 ast_func_read: Function ODBC_REMOTEMSSQL not registered
    -- Executing [s@test-odbc:2] Set("Local/6406@from-internal-00000002;1", "val=") in new stack
    -- Executing [s@test-odbc:3] NoOp("Local/6406@from-internal-00000002;1", "Val is: ") in new stack

What am I missing?

Thanks much

[billsimon]

You’ve mixed the res_odbc config and the func_odbc config.

func_odbc.conf is not managed by FreePBX. You can just edit the file directly and then do a reload to incorporate it.

You need to define your custom dsn in res_odbc_custom.conf (as you have done) and define the SQL function in func_odbc.conf.

Specifically, remove the readsql line you added to res_odbc_custom.conf, and instead, in func_odbc.conf:

[REMOTEMSSQL]
dsn=Remote-MSSQL
readsql=SELECT location FROM table WHERE unitName='${SQL_ESC(${ARG1})}'

then fwconsole reload to incorporate the changes.

[PitzKey (Itzik)]

You da man!

I have it working now…

It’s interesting that FreePBX does not have this func_odbc.conf by default. I added that file anf it is working right now.

This image here helped me understand the logic:

Source

Thank you Bill and Dicko

Hopefully, I will have some time soon and write this down. Perhaps a Wiki article? Not sure if I have access to publish wiki articles.