Thanks Kevin,

I found a work around after i discovered this myself.

basically it is to create a select, update, and insert method.

Then i test the select with python script

---------------------------------------------------------

#!/usr/bin/python
import string
REQUEST = container.REQUEST
res=context.SelectMethod()
if len(res)==0:
res=context.InsertMethod()
else:
res=context.UpdateMethod()

---------------------------------------------------------

By this fashion if a record doesnt exist then i perform what is insert, else i do an update.

I think this is a bit clumsy and i hope to put all sql statements into the script to stop multiple external calls.

Hope this helps others.

Cheers,

Wayne

----- Original Message -----

From: Kevin Carlson

To: Wayne Connolly

Cc: zope@zope.org

Sent: Wednesday, July 02, 2003 1:31 AM

Subject: Re: [Zope] Error, _mysql_exceptions. 'ON DUPLICATE KEY

This is a mysql error message that has nothing to do with zope. I don't believe that mysql supports the 'ON DUPLICATE KEY' syntax...

Wayne Connolly wrote:

Hi, i have been playing round with this insert/update script on zope/mysql and keep recieving the following error...any idea's on how to get rid of this would be muchly appreciated?

--------------------------------------------------------------------

Error, _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax near 'ON DUPLICATE KEY update charts set \n plays=plays + 1\n\twhere title='wayne'' at line 14")

--------------------------------------------------------------------

the ZSQL used is below

--------------------------------------------------------------------

insert into charts
(title,
artist,
genre,
album,
plays)
VALUES ( ,
   ,
   ,
   ,
  plays=1
  )

ON DUPLICATE KEY

update charts set
        plays=plays + 1
where title=<dtml-sqlvar title type=nb> and artist=<dtml-sqlvar artist type=nb>