[OpenSER-Users] Direct (Postgres) SQL queries.

[Mik Cheez]

I use this to connect to a SQL2000 box using FreeTDS and the Sybase Perl 
DB module.  My servers handle a very high load with virtually no 
latency.  I had to go this route our routing is broken down as far as 10 
digits domestic (US) and international is just crazy.

You can look to see if the Postgres DB module allows for persistent 
connections, so you don't have to worry about opening and closing for 
each DB call.

Alex Balashov wrote:
> 
> Thank you, this is very interesting;  I was not aware.
> 
> I imagine there is a perceptible performance impact that comes with 
> calling an external script, though, in that it has to open a new
> database connection handle each time and so on and so forth.  Thus,
> presumably it would be worth it only for situations in which the
> database interaction is sufficiently complex that it warrants
> outboarding it as opposed to trying to entertain it within the
> OpenSER config logic.
> 
> I've gotten around this requirement for the most part and kept things 
> clean by using Postgres stored procedures extensively, so there is 
> considerable incentive at this point to keep the operations 
> monolithicised within OpenSER.
> 
> However, this provides me with a very good conceptual foundation for
> situations where extensive IPC is required.
> 
> Thank you very much for that suggestion!
> 
> On Fri, 5 Oct 2007, Mik Cheez wrote:
> 
>> Try using Perl (to query the database and set an AVP value), and 
>> AVPops (to modify the URI or whatever).
>>
>> In your perl script (sets avp alias 39 to the value in $outuri:
>>     OpenSER::AVP::add(39, "$outuri");
>>
>> In your openser config file:
>>
>> modparam("perl", "filename", "script_name.pl") # enables perl script
>> avp_aliases="ouri=i:39" # define avp alias 39 (alias ouri)
>>
>> route {
>>     ..
>>     ..
>>     perl_exec("script_name.pl");
>>     avp_pushto("$ru","$avp(ouri)"; # set your URI to ouri
>> }
>>
>> Of course, you'll have to setup your perl script to do a meaningful 
>> database query.  There are some good samples in the source code to 
>> understand the perl scripts.
>>
>> Alex Balashov wrote:
>>>
>>> Is there a better way to talk to a database directly than to use 
>>> avp_db_query()?   I need to use my own schemas;  I have a platform
>>> that absolutely does not lend itself to storing data in the avpops'
>>> schema.  Luckily, avp_db_query() saved the day.  Is there anything
>>> even easier, or is this the definitive and methodologically correct
>>> way to go?
>>>
>>> -- 
>>> Alex Balashov
>>> Evariste Systems
>>> Web    : http://www.evaristesys.com/
>>> Tel    : +1-678-954-0670
>>> Direct : +1-678-954-0671
>>>
>>> _______________________________________________
>>> Users mailing list
>>> Users at openser.org
>>> http://openser.org/cgi-bin/mailman/listinfo/users
>>>
>>>
>>
> 
> -- 
> Alex Balashov
> Evariste Systems
> Web    : http://www.evaristesys.com/
> Tel    : +1-678-954-0670
> Direct : +1-678-954-0671
> 
>