[asterisk-users] MYSQL Query --> PAGE

[Lee Jenkins]

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