Cidlookup and odbc support

I recently updated my third party module dynroute for odbc support. The code I modified was originally copied from cidlookup. I noticed that cidlookup does not have odbc support. It would not take much for me to back port the modifications to cidlookup to give it odbc support.

I opened a feature request to track the need. I would like to use only odbc and eliminate reliance on MYSQL dial plan application which is no longer the asterisk recommended way of interrogating a mysql database.

Can I proceed with this patch or someone else already planning to work on adding odbc support to cidlookup.

Any feedback would be appreciated.

opened a feature request to track the need. I would like to use only odbc and You stated:- . . .

I opened a feature request to track the need. I would like to use only odbc and eliminate reliance on MYSQL dial plan application which is no longer the asterisk recommended way of interrogating a mysql database.

This makes no sense, Asterisk used to use BerkleyDb and now uses sqlite3, FreePBX uses mysql as a database engine, but that is nothing to do with Asterisk. FreePBX uses mysql to write to the old fashioned /etc/asterisk files, the buck stops there. If you don’t want to rely on mysql then you will have to find another front end for Asterisk than FreePBX.

You misunderstood me. I don’t want to remove reliance on mysql database, which is used for freepbx configurations and where I store additional tables containing information about callerid lookups and dynamic routing.

You say that asterisk does not use mysql. What you probably are missing is that asterisk can connect to a mysql database using the MYSQL dial plan application. Modules like cidlookup and also my own module dynroute use that dialplan application. However MYSQL dial plan application is now not the recommended way to interogate a mysql database from asterisk. That is why I am suggesting to add ODBC support which is the recommended way for asterisk to connect to a mysql database.

If compiled into asterisk there is both:-

core show functions like ODBC

ODBC ODBC(property[,argument]) Controls ODBC transaction properties.
ODBC_ANTIGF ODBC_ANTIGF() Check if a specified callerid is contained in the ex-gf database
ODBC_FETCH ODBC_FETCH(result-id) Fetch a row from a multirow query.
ODBC_PRESENCE ODBC_PRESENCE([…[,]]) Runs the referenced query with the specified arguments
ODBC_SQL ODBC_SQL([…[,]]) Runs the referenced query with the specified arguments

and

core show application mysql

-= Info about application ‘MYSQL’ =-

[Synopsis]
Do several mySQLy things

[Description]
MYSQL(): Do several mySQLy things
Syntax:
MYSQL(Set timeout )
Set the connection timeout, in seconds.
MYSQL(Connect connid dhhost dbuser dbpass dbname [dbcharset])
Connects to a database. Arguments contain standard MySQL parameters
passed to function mysql_real_connect. Optional parameter dbcharset
defaults to ‘latin1’. Connection identifer returned in ${connid}
MYSQL(Query resultid ${connid} query-string)
Executes standard MySQL query contained in query-string using established
connection identified by ${connid}. Result of query is stored in ${resultid}.
MYSQL(Nextresult resultid ${connid}
If last query returned more than one result set, it stores the next
result set in ${resultid}. It’s useful with stored procedures
MYSQL(Fetch fetchid ${resultid} var1 var2 … varN)
Fetches a single row from a result set contained in ${result_identifier}.
Assigns returned fields to ${var1} … ${varn}. ${fetchid} is set TRUE
if additional rows exist in result set.
MYSQL(Clear ${resultid})
Frees memory and datastructures associated with result set.
MYSQL(Disconnect ${connid})
Disconnects from named connection to MySQL.
On exit, always returns 0. Sets MYSQL_STATUS to 0 on success and -1 on error.

Be aware of the difference between an application and a function.

The options are endless, but there is to my knowledge no “recommended” way of using these, it is up to you.

I can’t find an authoritative reference, but I have read on several occasions that starting in asterisk 1.8.? that MySQL is deprecated in favor of odbc.

If you install asterisk from source, in order to enable MYSQL dial plan application via make menuselect, you see that app_mysql is listed in the deprecated section.

If you look under dial plan functions in menuselect, you see that funct_odbc is part of core.


                                             Asterisk Module and Build Option Selection
                                         **************************************************

                                                         Press 'h' for help.

                                                            --- extended ---
                                                        XXX chan_mobile
                                                        [ ] chan_ooh323
                                                        [*] format_mp3
                                                        [ ] res_config_mysql
                                                            --- deprecated ---
                                                        [*] app_mysql
                                                        [ ] app_saycountpl
                                                        [*] cdr_mysql

Deprecated does not mean it is not working, It is up to those that write that code to rewrite it. Please feel free to do so.

The point is that MYSQL dial plan application, while it may be working is no longer the recommended way to access a mysql database from the dial plan. The MYSQL dial plan application appears also to have some limitations.

I’m not sure who on the development team maintains cidlookup, but I would like to get feedback whether such a patch for odbc would be welcome or is not already in the works.