[asterisk-users] MYSQL INSERT QUESTION IN DIALPLAN

[lists65 at gmail.com]

-----Original Message-----
From: asterisk-users-bounces at lists.digium.com
[mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Steve Edwards
Sent: Monday, April 09, 2012 9:43 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [asterisk-users] MYSQL INSERT QUESTION IN DIALPLAN

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

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to
Asterisk? Join us for a live introductory webinar every Thurs:
               http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users


Thanks for your help and comments.  Again I am not a programmer but you did
give me some direction and I will see if I can find some examples that will
work for my needs.  I have seen AGI before and you are right it does seem to
work very well.

Thanks for your help and comments.  I hope to figure this out.  The SQL is
new to me but I really like the database stuff.