Examples of using avp_db_query()


Table of Contents

1. OpenSer Documentation
2. Setting up the tables
3. Setting up openser.cfg
4. Resulting log messages

1. OpenSer Documentation

AVPops Module: http://www.openser.org/docs/modules/1.1.x/avpops.html

XLOG Module: http://www.openser.org/docs/modules/1.1.x/xlog.html

MySQL Module: http://www.openser.org/docs/modules/1.1.x/mysql.html

2. Setting up the tables

These examples will use two tables. The usr_preferences table is distributed with OpenSER. The user_table is locally defined.

Table 1. usr_preferences

usernameattributevalue
5551212Account_ID123456789
5551212Country_Code1
5551212NPA212
5551212CallerID_URIsip:5551212@test.com
5551212CallerID_NameJohn Doe
5551212Call_Forward_Blindsip:john.doe@test.com

Table 2. user_table

usernameAccount_IDCountry_CodeNPACallerID_URICallerID_NameCall_Forward_Blind
55512121234567891212sip:5551212@test.comJohn Doesip:john.doe@test.com

3. Setting up openser.cfg

...
mpath="/usr/local/lib/openser/modules"
loadmodule "mysql.so"
loadmodule "avpops.so"
loadmodule "xlog.so"
...

  # Example 1.
  #
  # Note: Prior to most example, an avp_delete() is issued to
  #       clean up any AVP's from previous examples.
  #
  # This example reads two columns from the usr_preferences table
  # into two dynamically created AVP lists.
  #
  # An element in an AVP list can be accessed using an index, for
  # example: avp(i:1[1]), avp(i:1[2]), ...
  #
  avp_delete("*");
  if (avp_db_query("SELECT attribute, value
                    FROM usr_preferences 
                    WHERE username='5551212' 
                    ORDER BY attribute")) {

    xlog("L_NOTICE", "avp(i:1[1])=$avp(i:1[1]) avp(i:2[1])=$avp(i:2[1])\n");
    xlog("L_NOTICE", "avp(i:1[2])=$avp(i:1[2]) avp(i:2[2])=$avp(i:2[2])\n");
    xlog("L_NOTICE", "avp(i:1[3])=$avp(i:1[3]) avp(i:2[3])=$avp(i:2[3])\n");

  } else {
    xlog("L_NOTICE", "avp_db_query() returned no records\n");
  };

  # Example 2.
  #
  # This example shows the use of is_avp_set() to determine if a dynamically
  # created AVP exists.  We are using the results from the previous example.
  #
  if (is_avp_set("$avp(i:1[1])")) xlog("L_NOTICE", "avp(i:1[1]) is set to: $avp(i:1[1])\n");
  if (is_avp_set("$avp(i:1[2])")) xlog("L_NOTICE", "avp(i:1[2]) is set to: $avp(i:1[2])\n");
  if (is_avp_set("$avp(i:1[3])")) xlog("L_NOTICE", "avp(i:1[3]) is set to: $avp(i:1[3])\n");
  if (is_avp_set("$avp(i:1[4])")) xlog("L_NOTICE", "avp(i:1[4]) is set to: $avp(i:1[4])\n");
  if (is_avp_set("$avp(i:1[5])")) xlog("L_NOTICE", "avp(i:1[5]) is set to: $avp(i:1[5])\n");
  if (is_avp_set("$avp(i:1[6])")) xlog("L_NOTICE", "avp(i:1[6]) is set to: $avp(i:1[6])\n");
  if (is_avp_set("$avp(i:1[7])")) xlog("L_NOTICE", "avp(i:1[7]) is set to: $avp(i:1[7])\n");
  if (is_avp_set("$avp(i:1[8])")) xlog("L_NOTICE", "avp(i:1[8]) is set to: $avp(i:1[8])\n");
  if (is_avp_set("$avp(i:1[9])")) xlog("L_NOTICE", "avp(i:1[9]) is set to: $avp(i:1[9])\n");

  # Example 3.
  #
  # This example reads two columns from the usr_preferences table
  # into two "locally defined" AVP's with integer names.
  #
  # It is interesting to note, that just like Example 1, an AVP list
  # is created and can be accessed using an index.
  #
  avp_delete("*");
  if (avp_db_query("SELECT attribute, value 
                    FROM usr_preferences 
                    WHERE username='5551212' 
                    ORDER BY attribute","$avp(i:10);$avp(i:20)")) {

    xlog("L_NOTICE", "avp(i:10[1])=$avp(i:10[1]) avp(i:20[1])=$avp(i:20[1])\n");
    xlog("L_NOTICE", "avp(i:10[2])=$avp(i:10[2]) avp(i:20[2])=$avp(i:20[2])\n");
    xlog("L_NOTICE", "avp(i:10[3])=$avp(i:10[3]) avp(i:20[3])=$avp(i:20[3])\n");

  } else {
    xlog("L_NOTICE", "avp_db_query() returned no records\n");
  };

  # Example 4.
  #
  # This example reads two columns from the usr_preferences table
  # into two "locally defined" AVP's with string names.
  #
  # It is interesting to note, that just like Examples 1 and 3, an AVP list
  # is created and can be accessed using an index.
  #
  avp_delete("*");
  if (avp_db_query("SELECT attribute, value 
                    FROM usr_preferences 
                    WHERE username='5551212' 
                    ORDER BY value","$avp(s:attribute);$avp(s:value)")) {

    xlog("L_NOTICE", "avp(s:attribute[1])=$avp(s:attribute[1]) avp(s:value[1])=$avp(s:value[1])\n");
    xlog("L_NOTICE", "avp(s:attribute[2])=$avp(s:attribute[2]) avp(s:value[2])=$avp(s:value[2])\n");
    xlog("L_NOTICE", "avp(s:attribute[3])=$avp(s:attribute[3]) avp(s:value[3])=$avp(s:value[3])\n");

  } else {
    xlog("L_NOTICE", "avp_db_query() returned no records\n");
  };

  # Example 5.
  #
  # This example reads multiple columns from the "locally defined"
  # user_table table into dynamically created AVP's.
  #
  avp_delete("*");
  if (avp_db_query("SELECT Account_ID, 
                           Country_Code, 
                           NPA, 
                           CallerID_URI, 
                           CallerID_Name, 
                           Call_Forward_Blind 
                    FROM user_table 
                    WHERE username='5551212'")) {

    xlog("L_NOTICE", "avp(i:1)=$avp(i:1)\n");
    xlog("L_NOTICE", "avp(i:2)=$avp(i:2)\n");
    xlog("L_NOTICE", "avp(i:3)=$avp(i:3)\n");
    xlog("L_NOTICE", "avp(i:4)=$avp(i:4)\n");
    xlog("L_NOTICE", "avp(i:5)=$avp(i:5)\n");
    xlog("L_NOTICE", "avp(i:6)=$avp(i:6)\n");

  } else {
    xlog("L_NOTICE", "avp_db_query() returned no records\n");
  };

  # Example 6.
  #
  # This example reads multiple columns from the "locally defined"
  # user_table table into "locally defined" AVP's that can be accessed
  # with string names.
  #
  avp_delete("*");
  if (avp_db_query("SELECT Account_ID, 
                           Country_Code, 
                           NPA, 
                           CallerID_URI, 
                           CallerID_Name, 
                           Call_Forward_Blind 
                    FROM user_table 
                    WHERE username='5551212'", 
                    "$avp(s:Account_ID);
                     $avp(s:Country_Code);
                     $avp(s:NPA);
                     $avp(s:CallerID_URI);
                     $avp(s:CallerID_Name);
                     $avp(s:Call_Forward_Blind)")) {

    xlog("L_NOTICE", "avp(s:Account_ID)=$avp(s:Account_ID)\n");
    xlog("L_NOTICE", "avp(s:Country_Code)=$avp(s:Country_Code)\n");
    xlog("L_NOTICE", "avp(s:NPA)=$avp(s:NPA)\n");
    xlog("L_NOTICE", "avp(s:CallerID_URI)=$avp(s:CallerID_URI)\n");
    xlog("L_NOTICE", "avp(s:CallerID_Name)=$avp(s:CallerID_Name)\n");
    xlog("L_NOTICE", "avp(s:Call_Forward_Blind)=$avp(s:Call_Forward_Blind)\n");

    if (is_avp_set("$avp(s:Account_ID)")) 
        xlog("L_NOTICE", "avp(s:Account_ID) is set to: $avp(s:Account_ID)\n");
    if (is_avp_set("$avp(s:UNDEFINED)"))
        xlog("L_NOTICE", "avp(s:UNDEFINED)  is set to: $avp(s:UNDEFINED)\n");

  } else {
    xlog("L_NOTICE", "avp_db_query() returned no records\n");
  };

  # Example 7.
  #
  # This example shows the usage of avp_db_load().
  # For an explanation of the parms passed to avp_db_load(),
  # please visit: http://www.openser.org/docs/modules/1.1.x/avpops.html#AEN208
  #
  avp_delete("*");
  if (avp_db_load("$fU","a")) {

    xlog("L_NOTICE", "avp(s:Account_ID)=$avp(s:Account_ID)\n");
    xlog("L_NOTICE", "avp(s:Country_Code)=$avp(s:Country_Code)\n");
    xlog("L_NOTICE", "avp(s:NPA)=$avp(s:NPA)\n");
    xlog("L_NOTICE", "avp(s:CallerID_URI)=$avp(s:CallerID_URI)\n");
    xlog("L_NOTICE", "avp(s:CallerID_Name)=$avp(s:CallerID_Name)\n");
    xlog("L_NOTICE", "avp(s:Call_Forward_Blind)=$avp(s:Call_Forward_Blind)\n");

  } else {
    xlog("L_NOTICE", "avp_db_load() returned no records\n");
  };

4. Resulting log messages

Example 1.

avp(i:1[1])=CallerID_Name avp(i:2[1])=John Doe
avp(i:1[2])=CallerID_URI avp(i:2[2])=sip:5551212@test.com
avp(i:1[3])=Call_Forward_Blind avp(i:2[3])=sip:john.doe@test.com

Example 2.

avp(i:1[1]) is set to: CallerID_Name
avp(i:1[2]) is set to: CallerID_URI
avp(i:1[3]) is set to: Call_Forward_Blind
avp(i:1[4]) is set to: Country_Code
avp(i:1[5]) is set to: NPA

Example 3.

avp(i:10[1])=CallerID_Name avp(i:20[1])=John Doe
avp(i:10[2])=CallerID_URI avp(i:20[2])=sip:5551212@test.com
avp(i:10[3])=Call_Forward_Blind avp(i:20[3])=sip:john.doe@test.com

Example 4.

avp(s:attribute[1])=Account_ID avp(s:value[1])=123456789
avp(s:attribute[2])=NPA avp(s:value[2])=212
avp(s:attribute[3])=CallerID_Name avp(s:value[3])=John Doe

Example 5.

avp(i:1)=123456789
avp(i:2)=1
avp(i:3)=212
avp(i:4)=sip:5551212@test.com
avp(i:5)=John Doe
avp(i:6)=sip:john.doe@test.com

Example 6.

avp(s:Account_ID)=123456789
avp(s:Country_Code)=1
avp(s:NPA)=212
avp(s:CallerID_URI)=sip:5551212@test.com
avp(s:CallerID_Name)=John Doe
avp(s:Call_Forward_Blind)=sip:john.doe@test.com

avp(s:Account_ID) is set to: 123456789

Example 7.

avp(s:Account_ID)=123456789
avp(s:Country_Code)=1
avp(s:NPA)=212
avp(s:CallerID_URI)=sip:5551212@test.com
avp(s:CallerID_Name)=John Doe
avp(s:Call_Forward_Blind)=sip:john.doe@test.com