[asterisk-users] MYSQL Query --> PAGE

[Forrest Beck]

Well This seems to work.

[macro-pageall]
; Context for paging all devices.
;       This will search the sip table in the realtime database
;       for all phones that start with a number.  That number is
;       passed to this macro as ${ARG1}.
;
;       ARG1 = The first digit of the phones to be paged (US Campus=6,
LS Campus=2)
;       ARG2 = Device for the PA system.  If the user selected to
;               page the PA system.  That will be included.
;
exten => s,1,MYSQL(Connect connid ${realdb_host} ${realdb_user}
${realdb_pass} ${realdb_db})
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ name\ FROM\ sip\
WHERE\ name\ LIKE\ "'${ARG1}%'")
exten => s,3,MYSQL(Fetch fetchid ${resultid} number)
exten => s,4,GoToIf($["${fetchid}" = "1"]?5:8)
exten => s,5,Set(pagedevice=${pagedevice}&SIP/${number})
exten => s,6,NoOp(${number})
exten => s,7,GoToIf($["${fetchid}" = "1"]?3:8)
exten => s,8,Set(pagedevice=${pagedevice:1})
exten => s,9,NoOp(PageDevice ${pagedevice})
exten => s,10,MYSQL(Clear ${resultid})
exten => s,11,MYSQL(Disconnect ${connid})
exten => s,12,GoToIf($["${ARG2}" != ""]?13:14)
exten => s,13,Set(pagedevice=${pagedevice}&${ARG2})
exten => s,14,Set(_ALERT_INFO="RA")
exten => s,15,Page(${pagedevice})
exten => s,16,Hangup()


On 5/8/07, Remco Post <remco at pipsworld.nl> wrote:
> 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
> _______________________________________________
> --Bandwidth and Colocation provided by Easynews.com --
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
>


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