Summary:ASTERISK-02115: [new app] [post-1.0] app_sql_mysql
Reporter:constfilin (constfilin)Labels:
Date Opened:2004-07-26 19:17:32Date Closed:2004-09-25 02:01:42
Versions:Frequency of
Environment:Attachments:( 0) app_sql_mysql.c
( 1) app_sql_mysql.c
( 2) app_sql_mysql.c
( 3) apps_Makefile
Description:I coded a new Asterisk application called MYSQL which
is similar to application PGSQL but works with MYSQL.
The syntax of the application is:

*CLI> show application Mysql

 -= Info about application 'MYSQL' =-

Do several mySQLy things

MYSQL():  Do several mySQLy things
 MYSQL(Connect connid dhhost dbuser dbpass dbname)
   Connects to a database.  Arguments contain standard MySQL parameters
   passed to function mysql_real_connect.  Connection identifer returned
   in ${var}
 MYSQL(Query resultid ${connid} query-string)
   Executes standard MySQL query contained in query-string using established
   connection identified by ${connection_identifier}. Result of query is
   is stored in ${var}.
 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}.  ${statusvar} is set TRUE
   if additional rows exist in reseult set.
 MYSQL(Clear ${resultid})
   Frees memory and datastructures associated with result set.
 MYSQL(Disconnect ${connid})
   Disconnects from named connection to MySQL.


I am attaching the source file and the new applicatins Makefile for the change. My disclaimer is on file with
Comments:By: Olle Johansson (oej) 2004-07-28 13:52:39

Thank you for contributing! Please e-mail the -dev list and see if we can get
other users to comment and test this addition. /Olle

By: Olle Johansson (oej) 2004-08-08 15:47:34

What's the situation with MySQL? Does this go into addons or cvs head?

By: twisted (twisted) 2004-08-12 19:00:40

Please respond, as no response has been recieved, nor comments received on this, We will be closing soon......

By: constfilin (constfilin) 2004-08-12 19:05:46

I posted to -dev mailing list and the comment I've got is that we need
to come up with the generic solution for accessing databases instead of
writing a separate app for each DB. I suggested to use ODBC but was told
that ODBC is slow.

Meanwhile I have been using MYSQL all along in my dialplans. In fact the original MYSQL source was of good quality and I didn't have to make any additional patches.

Also, I got a message from another person saying that MYSQL works for him to.

I think many people will benefit if this gets to CVS.

By: twisted (twisted) 2004-08-12 19:28:12

I have to agree with the benefits here, but I have a couple of questions:

1) since we're linking to MySQL, would this belong in the -addons or could it be dropped into head?  I'm not sure of the MySQL licensing methods.

2) There is some truth to the statement that we need a unified method of db access, rather than creating new apps for each one; how difficult would it be to create a framework for db access that we could just 'slap in' access modules?

By: constfilin (constfilin) 2004-08-13 12:41:34

1. If I were to choose, I'd put it in the head. Rationale: there is mysql sip
  friends implementations that is already in the head (chan_sip.c). So adding
  mysql into the head will not create any new licensing problems.

  But I'd be happy to see it in add-ons as well.

2. I'd estimate it to be 2-4 days including the initial modules for PostgreSQL
  and MYSQL. However, I am unlikely to find this time unless this is necessary
  for the business my company is in.

By: twisted (twisted) 2004-08-13 14:24:54

I would suggest we make a crack at some sort of external data storage/retrieval framework, that we could just plug in modules for , and based on the calls in the dialplan logic, we determine what type of db we're connecting to, assuming we have that module installed..

something like... res_database, and called from the cli/extensions.conf similar to the method you are using above, except we could use options to specify the db type -

Database([action][|query string[|dbtype]])

could prove useful, and being 'pluggable' theoretically our access methods are unlimited...

By: constfilin (constfilin) 2004-08-13 16:23:15

1. just when I said that I didn't have to need any patches to original source,
  I ran into a bug :(. The problem was in scenario when SQL query does not
  return any rows (e.g. INSERT or UPDATE).

  Now MYSQL() now handles these  scenarios correctly. Moreover, for
  INSERTs, UPDATEs and DELETEs there is no need to call MYSQL(Clear ...)
  after MYSQL(Query).

  I am attaching new app_sql_mysql.c for completeness. If anyone will
  need MYSQL then (s)he can pick it up here.

2. Yes, something like "Database([action][|query string[|dbtype]])" would be
  good. Another improvement must be done for caching SQL connection
  handles. Right now a new SQL connection opens and closes on each channel.
  this is ineffective.

3. Until we are done with "Database", we can put MYSQL into -addons. But
  - eventually - it is your call.



By: constfilin (constfilin) 2004-08-16 14:35:17

Yet another improvement to MYSQL application. Now MYSQL uses mutexes to avoid 2 threads changing its static variables at the same time.

By: Brian West (bkw918) 2004-08-22 22:57:56

this will have to go in addons I suspect.

By: Mark Spencer (markster) 2004-09-17 09:24:44

Added to asterisk-addons, thanks!