Mysql: INSERT INTO table

Asterisk Asterisk Support
1

Hi,

I try to insert some data in a table from the dialplan, but it seems that something is wrong since no row is created after the exection of the following:

exten => s,n,Read(number|beep|10)
exten => s,n,MYSQL(Connect connid 127.0.0.1 aaa aaa aaa)
exten => s,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ client\ set\ uniqueid=${UNIQUEID},number=${number})
exten => s,n,MYSQL(Disconnect ${connid})

I created the database “aaa” with both username and password “aaa” and within it the table “client” that has 2 fields “uniqueid” and “number”.

Any idea what may cause this problem?

Thanks in advance,

Daniel

2

looks more like an update statement than an insert !

INSERT INTO tablename (field1, field2) VALUES (‘value1’, ‘value2’);

3

Thanks for your reply. From the dialplan the syntax is different (I think). Look at voip-info.org/wiki/view/Asterisk+cmd+MYSQL

in the asterisk log file there is no error message. Is there any Mysql error log file? if so, where is it located?

Thanks,

Daniel

4

From the mysql manual it says that the alternate syntax

insert into x set a=b,c=d

is acceptable.

Check for error messages in /var/log/mysql to begin with.

How are you checking to see if the record has been added? If using browser did you refresh the access to the db? It might be there all along but waiting to be discovered…

5

you learn something new every day … i too many years, i’ve never written an insert statement like that … the pedant in me :smiley:

just been playing with this, it turns out you should escape the ‘,’ too !! try exten => s,n,Read(number|beep|10) exten => s,n,MYSQL(Connect connid 127.0.0.1 aaa aaa aaa) exten => s,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ client\ set\ uniqueid=${UNIQUEID}\,number=${number}) exten => s,n,MYSQL(Disconnect ${connid})

6

Thanks again for your replies.

colbec,

I use phpmyadmin to check if the records are actually added. When I insert a record to the table from the prompt, I see directly it is added by refreshing phpmyadmin. I do the same after executing the dialplan, but no new record added.

I do not have the file /var/log/mysql. I just have /var/log/mysqld.log in which nothing related to the execution of the code.

baconbuttie,

I added the “” after uniqueid=${UNIQUEID}, but still doesn’t work.

Any other idea?

Daniel

7

Do you have a directory /var/log/mysql inside which there might be a file which contains info about errors logged? Otherwise you might want to check where your install of mysql has been instructed to store this info (mysql.cnf/my.ini?). Do you have anything in /var/log/messages?

8

when i was playing with this (away from the office now), i was getting errors seen in the /var/log/asterisk/full log … but nothing in the CLI itself with verbosity set at 10.

according to the log, the “,” was being interpreted as a “|” (like other Asterisk applications/functions).

after the Clear and Close i also had a PlayDTMF($MYSQL_STATUS}) to read out the result … was always 0 (success) regardless !

9

Strangely, i do not have the directory /var/log/mysql and I cannot find any error log file related to mysql in any directories.

There is nothing related to the code in var/log/messages.

baconbuttie,

I have also looked at /var/log/asterisk/full and noticed the “|” issue, but it is not treated as an error.

Any other ideas?

Daniel

10

post the log output here, let’s have a look

11

here’s the log. Note that I removed the “” after ${UNIQUEID} in the INSERT command. Otherwise, in the log file you have “|” instead of “,”.

Oct 13 10:58:42 VERBOSE[6400] logger.c: – Executing Goto(“SIP/3001-108e”, “Leave-Number|s|1”) in new stack
Oct 13 10:58:42 VERBOSE[6400] logger.c: – Goto (Leave-Number,s,1)
Oct 13 10:58:42 VERBOSE[6400] logger.c: – Executing Playback(“SIP/3001-108e”, “after-the-tone”) in new stack
Oct 13 10:58:42 DEBUG[6400] channel.c: Scheduling timer at 160 sample intervals
Oct 13 10:58:42 VERBOSE[6400] logger.c: – Playing ‘after-the-tone’ (language ‘en’)
Oct 13 10:58:43 DEBUG[6400] channel.c: Scheduling timer at 0 sample intervals
Oct 13 10:58:43 DEBUG[6400] channel.c: Scheduling timer at 0 sample intervals
Oct 13 10:58:43 VERBOSE[6400] logger.c: – Executing Read(“SIP/3001-108e”, “number|beep|10”) in new stack
Oct 13 10:58:43 VERBOSE[6400] logger.c: – Accepting a maximum of 10 digits.
Oct 13 10:58:43 DEBUG[6400] channel.c: Scheduling timer at 160 sample intervals
Oct 13 10:58:43 VERBOSE[6400] logger.c: – Playing ‘beep’ (language ‘en’)
Oct 13 10:58:44 DEBUG[6400] channel.c: Scheduling timer at 0 sample intervals
Oct 13 10:58:44 DEBUG[6400] channel.c: Scheduling timer at 0 sample intervals
Oct 13 10:58:54 VERBOSE[6400] logger.c: – User entered '022455526’
Oct 13 10:58:54 VERBOSE[6400] logger.c: – Executing MYSQL(“SIP/3001-108e”, “Connect connid 127.0.0.1 aaa aaa aaa”) in new stack
Oct 13 10:58:54 VERBOSE[6400] logger.c: – Executing MYSQL(“SIP/3001-108e”, “Query resultid 1 INSERT INTO client set uniqueid=asterisk-6308-1160729883.0,number=022455526”) in new stack
Oct 13 10:58:54 VERBOSE[6400] logger.c: – Executing MYSQL(“SIP/3001-108e”, “Disconnect 1”) in new stack
Oct 13 10:58:54 VERBOSE[6400] logger.c: – Executing Playback(“SIP/3001-108e”, “goodbye”) in new stack
Oct 13 10:58:54 DEBUG[6400] channel.c: Scheduling timer at 160 sample intervals
Oct 13 10:58:54 VERBOSE[6400] logger.c: – Playing ‘goodbye’ (language ‘en’)
Oct 13 10:58:55 DEBUG[6400] channel.c: Scheduling timer at 0 sample intervals
Oct 13 10:58:55 DEBUG[6400] channel.c: Scheduling timer at 0 sample intervals
Oct 13 10:58:55 VERBOSE[6400] logger.c: – Executing Hangup(“SIP/3001-108e”, “”) in new stack
Oct 13 10:58:55 VERBOSE[6400] logger.c: == Spawn extension (Leave-Number, s, 7) exited non-zero on ‘SIP/3001-108e’

Daniel

12

and what does MySQL return if you attempt that exact query in phpMyAdmin ?

edit : here’s what i used :exten => 313,1,Answer() exten => 313,2,Wait(1) exten => 313,3,Read(number|beep|10) exten => 313,4,MYSQL(Connect connid localhost username password test) exten => 313,5,MYSQL(Query resultid ${connid} INSERT\ INTO\ testaa\ SET\ `uniqueid`='${UNIQUEID}'\,`number`=${number}) exten => 313,6,MYSQL(Clear ${resultid}) exten => 313,7,MYSQL(Disconnect ${connid}) exten => 313,8,SayDigits(${MYSQL_STATUS}) exten => 313,9,Hangup()

13

FWIW bacon’s code works fine for me in 1.4 beta 2 on SuSe.

How have you defined your fields in the database? Should some of the values in your sql statment be quoted for char type fields?

14

baconbuttie,

I tried the query with appropriate syntax (Mysql) in phpMyAdmin and the record is actually created in the table.

I also tried your code; it doesn’t work for me. MYSQL_STATUS is 0. Is it working for you?

colbec,

I defined both fields as VARCHAR(15).

Daniel

15

Hmmm, I just tried inserting a record into char fields without quotes and it worked fine. My first test was using int fields. Latest theory shot to nothing.

Is it time to consider whether your version of asterisk-addons is suitable for your version of asterisk? One of the caveats on compile is to ensure that you upgrade the addons at the same time as upgrading the asterisk server. What versions are you using? Do you get any notifys when asterisk starts up?

16

yes, i can’t check again now 'cos i’m off-site, but i was doing the same as you … checking with phpMyAdmin … and the code i’ve pasted is what i used when the record went in … except the username and password has been changed.

it’s not a permissions issue for the user you’re connecting with is it ?

17

Thanks guys, I found the problem. It couldn’t insert records with ‘uniqueid’=’${UNIQUEID}’. The correct syntax is ‘uniqueid’=’"${UNIQUEID}"’. It seems that without " ", the ${UNIQUEID} (that is something like asterisk-2688-1160986278.32) cannot be converted into VARCHAR. Starange.

Daniel