lists65 at gmail.com
2012-Apr-10 00:34 UTC
[asterisk-users] MYSQL INSERT QUESTION IN DIALPLAN
I am not a programmer and I have learned so much from examples and the list.
Perhaps someone could tell me what I am doing wrong in my example below:
I am getting the caller ID and caller name from my local POTS line and I
want to add it into a sql table. I am trying with the following code but
the data never gets put into the table.
Can anyone correct my syntax and tell me what I am doing wrong?
[callerinfo]
exten => s,1,MYSQL(Connect connid localhost myuser mypassword cnam)
exten => s,n,MYSQL(Query resultid ${connid} INSERT INTO `calleridcapture`
(`number`,`name`) VALUES (${CALLERID(num)},${CALLERID(name)})
exten => s,n,MYSQL(Clear ${resultid})
exten => s,n,MYSQL(Disconnect ${connid})
exten => s,n,NoOp(Callerid Name ${CALLERID(name)})
exten => s,n,NoOp(Callerid Number ${CALLERID(num)})
The NoOP does show the correct CALLERID name & number when I test it. The
information just doesn't go into my calleridcapture table in the cnam
database.
Thanks very much for your help
Again I am not a programmer and I am sure my syntax is wrong.
This is Asterisk 1.8.10.0
lists65 at gmail.com wrote:> exten => s,n,MYSQL(Query resultid ${connid} INSERT INTO `calleridcapture` > (`number`,`name`) VALUES (${CALLERID(num)},${CALLERID(name)})Here is an example of one of my inserts: exten => s,n,MYSQL(Query resultid ${connid} INSERT INTO Indianapolis set phone="${CALLERID(number)}" \, flag="YES" \, note="Blacklisted by Tele-Torture - ${TODAY}") Doug
On Mon, 9 Apr 2012, lists65 at gmail.com wrote:> I am getting the caller ID and caller name from my local POTS line and I > want to add it into a sql table. I am trying with the following code > but the data never gets put into the table. > > Can anyone correct my syntax and tell me what I am doing wrong? > > [callerinfo] > exten => s,1,MYSQL(Connect connid localhost myuser mypassword cnam) > exten => s,n,MYSQL(Query resultid ${connid} INSERT INTO `calleridcapture` > (`number`,`name`) VALUES (${CALLERID(num)},${CALLERID(name)}) > exten => s,n,MYSQL(Clear ${resultid}) > exten => s,n,MYSQL(Disconnect ${connid}) > exten => s,n,NoOp(Callerid Name ${CALLERID(name)}) > exten => s,n,NoOp(Callerid Number ${CALLERID(num)}) > > The NoOP does show the correct CALLERID name & number when I test it. > The information just doesn't go into my calleridcapture table in the > cnam database.I'm just a 1.2 Luddite, but I am a reasonably skilled c programmer. I've never used the mysql() application because it seems ugly, limited and 'hackish' to me. If it were me, I'd code it up as an AGI in a 'real' language where you have access to 'real' error codes and messages and you don't need a bunch of quoting hocus-pocus. (Supposedly, the quoting nonsense has gotten better since 1.2.) You say you're not a programmer so that may not be an option for you -- but you got this far :) The first thing I'd do (aside from using verbose() instead of noop()) would be to display the result from each step. If the connection is failing, looking farther is pointless. Don't you need to put single or double quotes around your individual values? Change 'mysql' to 'echo' on your 'select' line and see if the statement is valid at the MySQL command line. Then, I'd drop the backticks in the 'hail-mary' hope that they are confusing mysql() somehow. Then, I'd crack open another beer and reach for a book on c or PHP or Perl. -- Thanks in advance, ------------------------------------------------------------------------- Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST Newline Fax: +1-760-731-3000
>-----Original Message----- >From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of lists65 at gmail.com >Sent: Monday, April 09, 2012 8:34 PM >To: asterisk-users at lists.digium.com >Subject: [asterisk-users] MYSQL INSERT QUESTION IN DIALPLAN>I am not a programmer and I have learned so much from examples and the list. >Perhaps someone could tell me what I am doing wrong in my example below:>I am getting the caller ID and caller name from my local POTS line and I want to add it into a sql table. I am trying with the following code but the data never gets put into the table.>Can anyone correct my syntax and tell me what I am doing wrong?>[callerinfo] >exten => s,1,MYSQL(Connect connid localhost myuser mypassword cnam) exten => s,n,MYSQL(Query resultid ${connid} INSERT INTO `calleridcapture` >(`number`,`name`) VALUES (${CALLERID(num)},${CALLERID(name)}) >exten => s,n,MYSQL(Clear ${resultid}) >exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,NoOp(Callerid Name ${CALLERID(name)}) exten => s,n,NoOp(Callerid Number ${CALLERID(num)})>The NoOP does show the correct CALLERID name & number when I test it. The information just doesn't go into my calleridcapture table in the cnam database.>Thanks very much for your help >Again I am not a programmer and I am sure my syntax is wrong.>This is Asterisk 1.8.10.0 >As the previous two posters alluded, you need to encapsulate your values in quotes. I think you can get by without the backticks, not 100% sure as I've converted from MYSQL to func_odbc. If you're not going to go with Steve's recommendation of AGI, I would highly recommend switching from func_mysql to func_odbc; func_odbc is much more straightforward in my opinion, and you definitely get much better error messages within the CLI as you're watching your code execute. ofps.oreilly.com/titles/9780596517342/asterisk-DB.html is a good resource for setting up odbc. Noah -------------- next part -------------- An embedded and charset-unspecified text was scrubbed... Name: AVG Certification.txt URL: <http://lists.digium.com/pipermail/asterisk-users/attachments/20120410/0c971aea/attachment.txt>