Need a little Help with a mysql INSERT

[Speedy2k]

Hi, here is the command line i have in asterisk 1.8 on my test server and this command is working.

exten => 1,n,MYSQL(Query resultid ${connid} INSERT INTO `Users` VALUES (NULL,${ROOM},'Chambre ${ROOM}',${ROOM},'Chambre ${ROOM}',NOW(),NULL,NULL))

But in asterisk 1.4 on my production server this command fail, what is wrong in this command on asterisk 1.4? thanx a lot!

 

[jmullinix]

You may need to escape the blanks and commas.

1,n,MYSQL(Query resultid ${connid} INSERT INTO `Users` VALUES (NULL,${ROOM},'Chambre ${ROOM}',${ROOM},'Chambre ${ROOM}',NOW(),NULL,NULL))

Becomes

1,n,MYSQL(Query resultid ${connid} INSERT INTO\ `Users`\ VALUES\ (NULL,${ROOM}\,'Chambre ${ROOM}'\,${ROOM}\,'Chambre ${ROOM}'\,NOW()\,NULL\,NULL))

 

[Speedy2k]

I have tried what you said and here is the log!

[2011-04-22 15:25:40] VERBOSE[27483] logger.c:     -- Executing [1@room-not-active:3] MYSQL("SIP/1009-00000504", "Query resultid 1 INSERT INTO `Users` VALUES (NULL|100,Chambre 100,100,Chambre 100,NOW(),NULL,NULL)") in new stack
[2011-04-22 15:25:40] WARNING[27483] app_addon_sql_mysql.c: aMYSQL_query: mysql_query failed. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '100,100,Chambre 100,NOW(),NULL,NULL)' at line 1

I don't know exactly what is the problem???

 

[dghundt]

http://pbxinaflash.com/community/threads/asterisk-pbx-and-mysql-syntax.3493/?t=3493

maybe my update examples for a pbxiaf with Asterisk 1.2 would help you.

Try this,
All the single quotes and commas need an escape character. The blanks do not need escape chars.
Does the table users need the special quotes?

Looking at my old code, what about something closer to -

1,n,MYSQL(Query resultid ${connid} INSERT INTO Users VALUES (NULL\,${ROOM}\,\'Chambre ${ROOM}\'\,${ROOM}\,\'Chambre ${ROOM}\'\,NOW()\,NULL\,NULL))

And consider removing the ( ) around your Values too.
Be careful if you paste this with ConfigEdit in FreePbx, a couple years back ConfigEdit would always remove one backslash when you clicked the red bar. Nano was easier. Not sure if this still applies.

As a last resort, you can put it in an AGI script that uses php. There are lots of mysql examples for this.