<div>It can get confusing at first but once you get the hang of it, its a breeze.</div>
<div>first take a look at: <a href="http://www.voip-info.org/wiki-Asterisk+cmd+MYSQL">http://www.voip-info.org/wiki-Asterisk+cmd+MYSQL</a></div>
<div>there is plenty of (correct)info and (working)examples on this page.</div>
<div> </div>
<div>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.
</div>
<div>looking at your old query line:</div>
<div>exten => s,n,MYSQL(Query resultid ${connid} SELECT\ password\ FROM\dnislookup\ WHERE\ dnis=\'${IVR-Exten}\')</div>
<div>you have a backslash but no space between FROM and dnislookup. I also dropped the quotes on the dnis=${IVR-Exten}.</div>
<div> </div>
<div>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.
</div>
<div>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.
</div>
<div>this is from your old example:</div>
<div>exten => s,n,MYSQL(Fetch fetchid ${password} password)</div>
<div>the var ${password} right after fetchid is not a resultset and should be corrected to ${resultid}.</div>
<div> </div>
<div>the last parameter - password is the var which will be assigned with the result.</div>
<div>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).</div>
<div> </div>
<div>Joss.</div>
<div><br><br> </div>
<div><span class="gmail_quote">On 4/14/07, <b class="gmail_sendername">Barton Fisher</b> <<a href="mailto:bhfisher@icpage.com">bhfisher@icpage.com</a>> wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Sorry, me again..<br>I'm at a loss as to why your example worked and mine didn't - I was<br>using one of the last examples I found during my searches.
<br>Can you tell me when/why I need to use the escape or quotes? Is there<br>some basic rule to follow? I'm asking because there is<br>a confusing mix of examples on google search and I'm not sure how to know.<br>
<br>Also, if I wish to expand the query to return additional fields (for<br>example online & owner) How would I add these to query and populate the<br>variables?<br><br>Thanks<br><br>Bart<br><br>Yossi Ben Hagai wrote:
<br>> That's the correct syntax:<br>><br>> exten => s,1,Noop()<br>> exten => s,n,MYSQL(Connect connid localhost root passw0rd dax)<br>><br>> exten => s,n,MYSQL(Query resultid ${connid} SELECT\ password\ FROM\
<br>> dnislookup\ WHERE\ dnis=${IVR-Exten})<br>> exten => s,n,MYSQL(Fetch fetchid ${resultid} password)<br>><br>> exten => s,n,MYSQL(Clear ${password})<br>> exten => s,n,MYSQL(Disconnect ${connid})
<br>> exten => s,n,returnpes<br>><br>> On 4/14/07, *Barton Fisher* <<a href="mailto:bhfisher@icpage.com">bhfisher@icpage.com</a><br>> <mailto:<a href="mailto:bhfisher@icpage.com">bhfisher@icpage.com</a>
>> wrote:<br>><br>> Sorry,<br>> From the logs I see:<br>><br>> Apr 13 13:32:06 WARNING[19854] app_addon_sql_mysql.c: Identifier 0,<br>> identifier_type 2 not found in identifier list
<br>> Apr 13 13:32:06 WARNING[19854] app_addon_sql_mysql.c: aMYSQL_fetch:<br>> Invalid result identifier 0 passed<br>><br>> Using this:<br>><br>> exten => s,1,Noop()<br>> exten => s,n,MYSQL(Connect connid localhost root passw0rd dax)
<br>> exten => s,n,MYSQL(Query resultid ${connid} SELECT\ password\ FROM\<br>> dnislookup\ WHERE\ dnis=\'${IVR-Exten}\')<br>> exten => s,n,MYSQL(Fetch fetchid ${password} password)<br>> exten => s,n,MYSQL(Clear ${password})
<br>> exten => s,n,MYSQL(Disconnect ${connid})<br>> exten => s,n,return<br>><br>> Bart<br>><br>> Alex Balashov wrote:<br>> > On Fri, 13 Apr 2007, Barton Fisher said something to this effect:
<br>> ><br>> >> What wrong with this:<br>> ><br>> > Well... what is wrong with it? :-)<br>> ><br>> > I'm not trying to be funny, but, what are the symptoms that it
<br>> > doesn't work? Error output on Asterisk<br>> console? Logs? Anything you<br>> > can provide would be helpful.<br>> ><br>> > -- Alex<br>> ><br>> > --
<br>> > Alex Balashov <<a href="mailto:sasha@presidium.org">sasha@presidium.org</a> <mailto:<a href="mailto:sasha@presidium.org">sasha@presidium.org</a>>><br>> > _______________________________________________
<br>> > --Bandwidth and Colocation provided by <a href="http://Easynews.com">Easynews.com</a><br>> <<a href="http://Easynews.com">http://Easynews.com</a>> --<br>> ><br>> > asterisk-users mailing list
<br>> > To UNSUBSCRIBE or update options visit:<br>> > <a href="http://lists.digium.com/mailman/listinfo/asterisk-users">http://lists.digium.com/mailman/listinfo/asterisk-users</a><br>> ><br>
> ><br>> ><br>> > __________ NOD32 2187 (20070413) Information __________<br>> ><br>> > This message was checked by NOD32 antivirus system.<br>> > <a href="http://www.eset.com">
http://www.eset.com</a><br>> ><br>> ><br>> ><br>><br>><br>><br>> _______________________________________________<br>> --Bandwidth and Colocation provided by <a href="http://Easynews.com">
Easynews.com</a><br>> <<a href="http://Easynews.com">http://Easynews.com</a>> --<br>><br>> asterisk-users mailing list<br>> To UNSUBSCRIBE or update options visit:<br>> <a href="http://lists.digium.com/mailman/listinfo/asterisk-users">
http://lists.digium.com/mailman/listinfo/asterisk-users</a><br>><br>><br><br><br><br></blockquote></div><br>