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

1 Jul 2003

      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 (<!--#sqlvar title type=string --> ,
      <!--#sqlvar artist type=string --> ,
      <!--#sqlvar genre type=string --> ,
      <!--#sqlvar album type=string --> ,
      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>