[SR-Users] using avpops for querying an external db on kamailio 3.0.2

[Nathan Angelacos]

Hi yaron,

On 06/30/10 20:44, yaron nahcum wrote:
> Hi Daniel,
> I looked at the module and it seems more suitable for my needs.
> I have two questions regarding this module:

Let's say your proprietary database has a table with "username" as one 
of the columns.  For example, a table "Users" with the following columns:

username | acct_balance | acct_active | secret_code


> 1. It says that it is possible to use psuedo variables in the query. How
> is it done? I would like to make a query of the from username.

You just include it in the SQL query of the sql_query function:

sql_query ("ca", "select username from from Users where username = '$fu' 
and acct_active = 1", "rs");


You'll need some knowledge of how the SQL language works.  But here's an 
example based on your example code:

loadmodule "sqlops.so"
         modparam ( "sqlops", "sqlcon", 
ca=>dbdriver://username:password@dbhost/dbname")
#  For example, to connect to a mysql database
# "ca=>mysql://yaron:Open_Sesame@192.168.6.1/mydatabase")

if (is_method("REGISTER"))
  {
   # Ask if $fu is in the database
   sql_query ("ca", "select username from from Users where username = 
'$fu' and acct_active = 1", "rs");
   # If we get a row back, we know we had a match
   if ($dbr(rs=>rows) == 1 ) {
	append_branch("sip:registrarIP");
	sl_send_reply("302","Test Redirect");
	}
	else
	sl_send_reply("404","Test Not found");
	}
   }

> 2. If I query the whole column how can I look for a specific value? The
> only way is to go over all the values with a loop?

You have to use the SQL language to make sure you specify a query that 
only gets the answer you want. Assuming the username is unique in the 
table, the query above will return 1 or 0 rows.   The key is knowing how 
to use SQL.   You should check the website for the SQL server you are 
using for more information on the SQL language.


Hope this helps.