[Freeswitch-dev] mod_odbc_query

[Michael Collins]

Leon,

Two things: first, would you mind putting this info into the wiki? Just
create this page:
http://wiki.freeswitch.org/index.php?title=Mod_odbc&action=edit&redlink=1

Second, I was curious what kind of throughput you've seen with this? Just
curious how much you think it might scale. I'm sure someone will find a way
to mix this with SIPp in an effort to break it. :P

-MC

On Mon, Sep 14, 2009 at 7:23 PM, Leon de Rooij <leon at scarlet-internet.nl>wrote:

> Hi,
>
> I wrote a new module mod_odbc_query the last few days and I'm wondering
> what other people think of it. It works here, tested it and valgrind
> sees no leaks..
>
> You can check it out here:
>
> http://fisheye.freeswitch.org/browse/FreeSWITCH/contrib/ledr/c/mod_odbc_query
>
> It's pretty flexible and useful for me.
>
> regards,
>
> Leon
>
> ---
>
> mod_odbc_query readme
>
> This module can be used for doing odbc queries from the dialplan.
>
> Usage:
>
> <application name="odbc_query" data="SELECT some_column_name AS
>    target_channel_variable_name FROM some_table_name WHERE 1;"/>
>
> or:
>
> <application name="odbc_query" data="my-query"/>
>
> The module simply checks whether the data attr contains a space. If it
> does, then that field will be seen as an SQL query, otherwise it will be
> seen as a query 'name' which then has to be defined in the modules
> configuration in the <queries> section like this:
>
> <queries>
>  <query name="my-query" value="SELECT some_column_name AS
>     target_channel_variable_name FROM some_table_name WHERE 1;"/>
> </queries>
>
> The module will do the query and store each returned column name as
> channel variable name together with its corresponding value.
>
> Another feature is, that if only two columns are returned, which have
> the column names "name" and "value", then the channel variables will be
> set according to them. This way you can have the query return multiple
> rows with different channel variables. If the query returns something
> else than column-names "name" and "value" and it returns multiple rows,
> then the channel variables will be overwritten with each iteration of
> the rows - which is probably useless.
>
> The query may contain ${blah} variables that will be expanded from
> channel variables before the query is performed.
>
>
> For example:
>
> query: "SELECT foo, bar FROM huk WHERE tilde='kek';"
>
> returns:
>
> foo    bar
> ----------
> a      b
> c      d
>
> then the channel variables that will be set are:
>
> foo=c
> bar=d
>
> ---
>
>
> query: "SELECT foo AS name, bar AS value FROM huk WHERE tilde='kek';"
>
> returns:
>
> name   value
> ------------
> a      b
> c      d
>
> then the channel variables that will be set are:
>
> a=b
> c=d
>
> ---
>
>
> So, the first example should only be used when you know that only zero
> or one row will be returned, and second one if you know zero or more
> rows will be returned.
>
> If zero rows are returned (in either foo/bar or name/value case) then
> no channel variables will be set, overwritten or deleted.
>
> ---
>
> <configuration name="odbc_query.conf" description="ODBC Query Module">
>
>  <settings>
>    <param name="odbc-dsn" value="freeswitch:freeswitch:secret"/>
>  </settings>
>
>  <queries>
>    <!-- the ${foo} variable will be expanded from channel variables at
> query time -->
>    <query name="map-did-users" value="
>      SELECT
>        dir_users.username AS dest_user,
>        dir_domains.name AS dest_domain
>      FROM
>        map_did_users, dir_domains, dir_users
>      WHERE
>        map_did_users.did='${destination_number}' AND
>        dir_users.id = map_did_users.user_id AND
>        dir_domains.id = dir_users.dir_domain_id;"/>
>  </queries>
> </configuration>
>
>
> _______________________________________________
> FreeSWITCH-dev mailing list
> FreeSWITCH-dev at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-dev
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-dev
> http://www.freeswitch.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-dev/attachments/20090914/e640b618/attachment-0001.html