[asterisk-users] MySQL query from extensions?

[Yossi Ben Hagai]

It can get confusing at first but once you get the hang of it, its a breeze.
first take a look at: http://www.voip-info.org/wiki-Asterisk+cmd+MYSQL
there is plenty of (correct)info and (working)examples on this page.

you have to \escape a space,quote and double quote, comma and backslash - so
you just write down the query the same way as it worked on the query browser
and just prepend and backslash every time you see one of these characters.
looking at your old query line:
exten => s,n,MYSQL(Query resultid ${connid} SELECT\ password\
FROM\dnislookup\ WHERE\ dnis=\'${IVR-Exten}\')
you have a backslash but no space between FROM and dnislookup. I also
dropped the quotes on the dnis=${IVR-Exten}.

the other thing that might cause some confusion is the return var for each
MYSQL(subcommand). to start you have the connect line which after execution
returns the specific connection identifier on ${connid}. now you are ready
to issue a query - the query result will be stored in ${resultid} and it
will the connection id you specifiy - ${connid} in your case.
once you got your resultset stored in ${resultid} you have the fetch line
which is used to assign the resultset into asterisk vars. the return var in
this line is ${fetchid} which lets you know if there is a row available in
your resultset (1=true, 0=false), then you specify the resultset you want to
work on - ${resultid} in your case.
this is from your old example:
exten => s,n,MYSQL(Fetch fetchid ${password} password)
the var ${password} right after fetchid is not a resultset and should be
corrected to ${resultid}.

the last parameter - password is the var which will be assigned with the
result.
to assign additional fields you simply change your query to something like
SELECT password, online, owner FROM... and your fetch to MYSQL(Fetch fetchid
${resultid} password online owner).

Joss.



On 4/14/07, Barton Fisher <bhfisher at icpage.com> wrote:
>
> Sorry, me again..
> I'm at a loss as to why your example worked and mine didn't - I was
> using one of the last examples I found during my searches.
> Can you tell me when/why I need to use the escape or quotes?  Is there
> some basic rule to follow?  I'm asking because there is
> a confusing mix of examples on google search and I'm not sure how to know.
>
> Also, if I wish to expand the query to return additional fields (for
> example online & owner) How would I add these to query and populate the
> variables?
>
> Thanks
>
> Bart
>
> Yossi Ben Hagai wrote:
> > That's the correct syntax:
> >
> > exten => s,1,Noop()
> > exten => s,n,MYSQL(Connect connid localhost root passw0rd dax)
> >
> > exten => s,n,MYSQL(Query resultid ${connid} SELECT\ password\ FROM\
> > dnislookup\ WHERE\ dnis=${IVR-Exten})
> > exten => s,n,MYSQL(Fetch fetchid ${resultid} password)
> >
> > exten => s,n,MYSQL(Clear ${password})
> > exten => s,n,MYSQL(Disconnect ${connid})
> > exten => s,n,returnpes
> >
> > On 4/14/07, *Barton Fisher* <bhfisher at icpage.com
> > <mailto:bhfisher at icpage.com>> wrote:
> >
> >     Sorry,
> >     From the logs I see:
> >
> >     Apr 13 13:32:06 WARNING[19854] app_addon_sql_mysql.c: Identifier 0,
> >     identifier_type 2 not found in identifier list
> >     Apr 13 13:32:06 WARNING[19854] app_addon_sql_mysql.c: aMYSQL_fetch:
> >     Invalid result identifier 0 passed
> >
> >     Using this:
> >
> >     exten => s,1,Noop()
> >     exten => s,n,MYSQL(Connect connid localhost root passw0rd dax)
> >     exten => s,n,MYSQL(Query resultid ${connid} SELECT\ password\ FROM\
> >     dnislookup\ WHERE\ dnis=\'${IVR-Exten}\')
> >     exten => s,n,MYSQL(Fetch fetchid ${password} password)
> >     exten => s,n,MYSQL(Clear ${password})
> >     exten => s,n,MYSQL(Disconnect ${connid})
> >     exten => s,n,return
> >
> >     Bart
> >
> >     Alex Balashov wrote:
> >     > On Fri, 13 Apr 2007, Barton Fisher said something to this effect:
> >     >
> >     >> What wrong with this:
> >     >
> >     >   Well... what is wrong with it?  :-)
> >     >
> >     >   I'm not trying to be funny, but, what are the symptoms that it
> >     > doesn't work?  Error output on Asterisk
> >     console?  Logs?  Anything you
> >     > can provide would be helpful.
> >     >
> >     > -- Alex
> >     >
> >     > --
> >     > Alex Balashov <sasha at presidium.org <mailto:sasha at presidium.org>>
> >     > _______________________________________________
> >     > --Bandwidth and Colocation provided by Easynews.com
> >     <http://Easynews.com> --
> >     >
> >     > asterisk-users mailing list
> >     > To UNSUBSCRIBE or update options visit:
> >     >   http://lists.digium.com/mailman/listinfo/asterisk-users
> >     >
> >     >
> >     >
> >     > __________ NOD32 2187 (20070413) Information __________
> >     >
> >     > This message was checked by NOD32 antivirus system.
> >     > http://www.eset.com
> >     >
> >     >
> >     >
> >
> >
> >
> >     _______________________________________________
> >     --Bandwidth and Colocation provided by Easynews.com
> >     <http://Easynews.com> --
> >
> >     asterisk-users mailing list
> >     To UNSUBSCRIBE or update options visit:
> >       http://lists.digium.com/mailman/listinfo/asterisk-users
> >
> >
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20070413/acb67b33/attachment.htm