asterisk users - Feb 2005 - using the MYSQL command to insert a record

I am trying to use the MYSQL command to insert a
record into a database and I can't seem to get it to
work. I can do an UPDATE with no problem.
Here is the line in my dialplan
exten => s,12,MYSQL(QUERY resultid ${connid} INSERT
INTO `member` ( `id` , `member_num` , `active` )VALUES
('',${number}' , '1'))

Does anyone have an example of an INSERT INTO that I
could look at?

Thanks 
Robert Howard


		
__________________________________ 
Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 
http://my.yahoo.com

On Wed, 2005-02-02 at 21:02 -0800, Robert Howard wrote:
> I am trying to use the MYSQL command to insert a
> record into a database and I can't seem to get it to
> work. I can do an UPDATE with no problem.
> Here is the line in my dialplan
> exten => s,12,MYSQL(QUERY resultid ${connid} INSERT
> INTO `member` ( `id` , `member_num` , `active` )VALUES
> ('',${number}' , '1'))
Try
exten => s,12,MYSQL(QUERY resultid ${connid} INSERT
INTO member \( id \, member_num \, active \)VALUES
\(''\,'${number}' \, 1\))

Some of those \'s might not be needed, or you might need to also escape the
' as well.

Of course, maybe you can't do an insert from a query .... you may need 
to look at the source code for more informaiton.

Regards,
Adam

-- 
 -- 
Adam Goryachev
Website Managers
Ph:  +61 2 8304 0000                        adam@websitemanagers.com.au
Fax: +61 2 9345 4396                        www.websitemanagers.com.au

In article <20050203050228.44097.qmail@web31009.mail.mud.yahoo.com>,
Robert Howard <mrroberthoward@yahoo.com> wrote:
> I am trying to use the MYSQL command to insert a
> record into a database and I can't seem to get it to
> work. I can do an UPDATE with no problem.
> Here is the line in my dialplan
> exten => s,12,MYSQL(QUERY resultid ${connid} INSERT
> INTO `member` ( `id` , `member_num` , `active` )VALUES
> ('',${number}' , '1'))
> 
> Does anyone have an example of an INSERT INTO that I
> could look at?
Firstly, backticks around table and column names are not needed unless
the name has weird characters in it.

Secondly, the whole query needs single quotes around it.

Thirdly, single quotes within the query need \-escaping.

Here's a working example which updates a database on hangup:

exten => h,1,MYSQL(Connect conn localhost username password dbname)
exten => h,2,MYSQL(Query res ${conn} 'INSERT INTO
responses(adv_id,resp_callerid,resp_file,resp_created)
VALUES(${advertid},\'${CALLERIDNUM}\',\'${RECORDED_FILE}\',NOW())')
exten => h,3,MYSQL(Disconnect ${conn})

Hope this helps!
Cheers
Tony
-- 
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org