[asterisk-users] MYSQL INSERT QUESTION IN DIALPLAN

[Steve Edwards]

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