Func_odbc returning 0 columns from MSSQL query

[viiiwonder (Levi Shores)]

Having configured odbcinst.ini thusly:

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

And odbc.ini:

[CW-ReportingODBC]
Description=Reporting DB
Driver=ODBC Driver 17 for SQL Server
Server=host,port

and res_odbc_custom.conf:

[CW-ReportingA]
enabled=>yes
dsn=>CW-ReportingODBC
pre-connect=>yes
max_connections=>5
username=>un
password=>pwd
database=>db

and func_odbc.conf:

[CW-Reporting]
dsn=CW-ReportingA
readsql=${ARG1}

And verifying that I can connect with isql, and get a result (here, I look up Project 225 from our CRM, and return the Manager’s extension):

SQL> use db select PhoneNbr_Ext from v_rpt_member mem inner join v_rpt_projectheader proj on proj.Project_Manager = mem.Member_ID where proj.PM_Project_RecID = 225
[01000][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to ‘db’.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
±------------+
| PhoneNbr_Ext|
±------------+
| 114 |
±------------+
SQLRowCount returns 0
1 rows fetched

When I use the function in dynamic routes, the function fails to return results (from the log):

func_odbc.c: Returned 0 columns [use db select PhoneNbr_Ext from v_rpt_member mem inner join v_rpt_projectheader proj on proj.Project_Manager = mem.Member_ID where proj.PM_Project_RecID = 225]

I’ve tested this against another MSSQL DB, with similar results, and I’ve tested against a MySQL DB with some test data, and it works fine with MySQL. I have also tested with freetds odbc driver - no difference.

Does anyone have any experience with func_odbc and MSSQL, or suggestions as to where to go from here?

[viiiwonder (Levi Shores)]

For anyone finding this post in similar frustration, here was our further testing and resolution.

First off, we could see the query hitting the server.

Our query was including the ‘use database’, followed by the select… I think that this was seen as two different queries, and we were getting 0 columns back (because use doesn’t return anything, but it is ‘successful’).

We ended up actually getting into the asterisk console and using odbc read function parameter

From this, we could ascertain that if we issued the use database, followed by the select in two different instances of the function, it would work. So… let’s take out the use database statement, and make the ODBC configuration do what it’s supposed to with the default database you say? Well, that had no impact … we set the default in the ODBC.ini, in res_odbc_custom.conf … no difference, it wasn’t using the default database as configured in the files.

Then we found the documentation of func_odbc that’s included in the sample on github.

In the sample of func_odbc (here: https://github.com/asterisk/asterisk/blob/master/configs/samples/func_odbc.conf.sample) they reference the actual configuration options available to the file. Most of the documentation floating around the internet about func_odbc just references ‘dsn=>’ … well, turns out that dsn is an alias to ‘writehandle’.

After updating the parameter dsn to readhandle, for whatever reason the query was now defaulting to the database as specified in res_odbc_custom.conf, and we were able to drop the use database portion of the query, and dive straight into our select, which then brought back results…

Now to go figure out what problems to solve with database access in routing!