Getting Data from a Remote Microsoft SQL DB in the Dialplan

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

MS changed their attitude

2 Likes

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

Show us your odbcinst.ini and your odbc.ini

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).

1 Like

Here you go

[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

[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 https://www.npmjs.com/package/sql-cli

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.

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

Probably remove the older one.

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.

[[email protected] 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:

[[email protected] 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:

[[email protected] 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. :frowning: ‘Function ODBC_REMOTEMSSQL’

 Executing [[email protected]:1] NoOp("Local/[email protected];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 [[email protected]:2] Set("Local/[email protected];1", "val=") in new stack
    -- Executing [[email protected]:3] NoOp("Local/[email protected];1", "Val is: ") in new stack

What am I missing?

Thanks much

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.

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.

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.