asterisk users - May 2007 - MYSQL Query --> PAGE

I have all my SIP users in a realtime database.  I would like to use
MySQL command to query the database and use the results from the query
to page all the phones found in the query.

The results from the MySQL query will be multiple rows of extension:
Something like:

mysql> Select extension from sip where extension like '6%'
6001
6002
6003
ex....

I need to put all the results into a variable that would equal something like:

SIP/6001&SIP/6002&SIP/6003

I have setup a couple basic MYSQL Query's for my dialplan.  Mostly
just looking up a DID to Extension Mapping for setting callerid on
outbound and inbound calls.

How does asterisk handle the multiple results.  Is there a way to loop
until there are no more rows?

Something like Set(devices=${devices}&${newrow_result})

I looked at the example on
http://www.voip-info.org/wiki/view/Asterisk+cmd+MYSQL but that doesn't
seem to be accurate.

Thanks all!!

-- 
***
Forrest Beck
IAXTEL: 17002871718
jonforrest.beck@gmail.com

Forrest Beck wrote:
> I have all my SIP users in a realtime database.  I would like to use
> MySQL command to query the database and use the results from the query
> to page all the phones found in the query.
> 
> The results from the MySQL query will be multiple rows of extension:
> Something like:
> 
> mysql> Select extension from sip where extension like '6%'
> 6001
> 6002
> 6003
> ex....
> 
> I need to put all the results into a variable that would equal something
> like:
> 
> SIP/6001&SIP/6002&SIP/6003
> 
> I have setup a couple basic MYSQL Query's for my dialplan.  Mostly
> just looking up a DID to Extension Mapping for setting callerid on
> outbound and inbound calls.
> 
> How does asterisk handle the multiple results.  Is there a way to loop
> until there are no more rows?
> 
> Something like Set(devices=${devices}&${newrow_result})
> 
> I looked at the example on
> http://www.voip-info.org/wiki/view/Asterisk+cmd+MYSQL but that doesn't
> seem to be accurate.
> 
> Thanks all!!
> 
What I've done in postgresql is to build an pl/pgsql procedure that
returns the desired dialstring. So the procedure does the select and
then concats them.


-- 

Remco Post

"I didn't write all this code, and I can't even pretend that all of
it
makes sense." -- Glen Hattrup

Forrest Beck wrote:
> 
> How does asterisk handle the multiple results.  Is there a way to loop
> until there are no more rows?
> 
I don't use Realtime in Asterisk personally so I'm not sure if it 
implements it or not, but I agree that being able to iterate over a 
ResultSet is a pretty basic need.  I think I remember AEL2 being able to 
do that.  rushowr put together a nice collection of AEL2 scripts (link 
below) that probably has something in it you could use.  I know he uses 
MySQL a lot in is dialplans.  You could also use an AGI/FastAGI to do 
something like that.

If you don't mind a small FastAGI listener running and you don't mind 
Pascal, you could check out AsterPas (link below) which does support 
doing that with MySQL, FirebirdSQL and Sqlite databases and its free 
(though not open source).  It's still considered beta, but we're using 
it ourselves quite a bit without problems.

Also, there is Astersk Java (link below) which looks dynamite if you're 
more familiar with or prefer Java.

Personally, I like the idea of pushing non-asterisk operations out of 
Asterisk so AGI/FastAGI is my preference.  Many also seem to advocate 
using AEL2 which is pretty powerful and easy.

Asterisk Java:
http://asterisk-java.org

AsterPas:
http://www.datatrakpos.com/pos/datatalk/asterpas.aspx

SKeMAEL AEL2 Scripts from

With AsterPas, you could do something like the following:

{uses sqldb}
program BuildMyCrazyDialString;
Var
rowset: TDTRowset;
sDial: string;
begin

with SQLDB do
    begin
    SetProp('sqltype', 'sql');
    SetProp('Connection', 'MyRealtimeDBConn');
    SetProp('sql', 'SELECT xtenNumber FROM my_extensions_table WHERE
' +
       'my_field = ' + AGI.GetVariable('MyGroupID'));

    if (CreateRowSet('xtens')) then
       begin
       rowset := GetRowSet('xtens');
       while (not rowset.eof) do
          begin
          if (sDial = '') then
             sDial = 'SIP/' + rowset.AsString('xtenNumber')
          else
             sDial := sDial '&' + 'SIP/' +
rowset.AsString('xtenNumber');
          rowset.Next;
          end;
       RemoveRowset('xtens');
       end;

    end;

// push out the result to the CLI
AGI.Noop('The DialString is: ' + sDial);

// set a dialplan variable for use when the FastAGI exits
AGI.SetVariable('DialStrReturn', sDial);

end.


-- 

Warm Regards,

Lee

Lee Jenkins wrote:
> Forrest Beck wrote:
> 
>>
>> How does asterisk handle the multiple results.  Is there a way to loop
>> until there are no more rows?
>>
> 
Sorry, I forgot the last link for the AEL2 scripts:

http://sourceforge.net/projects/aelscriptlib/


-- 

Warm Regards,

Lee