SQLOPS Module

sqlops module focuses to offer fast and easy access to SQL backends directly from the configuration file. Among its features:

• many DB connections - the module can connect to many databases on different servers using different DB driver modules (mysql, postgres, ...) at the same time.
• many DB results - the module can store many results of different SQL queries in separate structures at the same time. Thus is possible to work in parallel with several DB results.
• access via pseudo-variables - the content of SQL query result is accessible via pseudo-variables.
• array indexes - fast access to result values via array possition: [row,column].
• persistence in process space - a result can be used many times in same worker process. Query once, use many times.

Here is an example of fetching and printing the content of a table:

...

modparam("sqlops","sqlcon","ca=>mysql://openser:abc@localhost/openser")

...

sql_query("ca", "select * from domain", "ra");

xlog("rows: $dbr(ra=>rows) cols: $dbr(ra=>cols)\n");

if($dbr(ra=>rows)>0)

{

    $var(i) = 0;

    while($var(i)<$dbr(ra=>cols))

    {

        xlog("--- SCRIPT: column[$var(i)] = $dbr(ra=>colname[$var(i)])\n");

        $var(i) = $var(i) + 1;

    }

    $var(i) = 0;

    while($var(i)<$dbr(ra=>rows))

    {

        $var(j) = 0;

        while($var(j)<$dbr(ra=>cols))

        {

            xlog("[$var(i),$var(j)] = $dbr(ra=>[$var(i),$var(j)])\n");

            $var(j) = $var(j) + 1;

        }

        $var(i) = $var(i) + 1;

    }

}

sql_result_free("ra");

...

Other advantages than the ones listed above against old avp_db_query() function:

- does not mess up fields position if the value in db is null

- use private memory, no locking

- does not iterate through linked (avp) list to get each field, direct

reference to each data structure in result array, thus much faster.

More info at:

http://kamailio.org/docs/modules/devel/sqlops.html