[Zope] Error, _mysql_exceptions. 'ON DUPLICATE KEY

Wayne Connolly Wayne Connolly <wayne@c-media.com.au>
Wed, 02 Jul 2003 01:43:52 +1000


This is a multi-part message in MIME format.

--Boundary_(ID_aEGcu2sIa6kHixIyFi3vUQ)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

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>

--Boundary_(ID_aEGcu2sIa6kHixIyFi3vUQ)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=Content-Type content=text/html;charset=ISO-8859-1>
<META content="MSHTML 6.00.2600.0" name=GENERATOR></HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Thanks Kevin,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I found a work around after i discovered this 
myself.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>basically it is to create a select, update, and 
insert method.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Then i test the select with python script 
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial 
size=2>---------------------------------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2>#!/usr/bin/python<BR>import string<BR>REQUEST = 
container.REQUEST<BR>res=context.SelectMethod()<BR>if 
len(res)==0:<BR>&nbsp;&nbsp; res=context.InsertMethod()<BR>else:<BR>&nbsp;&nbsp; 
res=context.UpdateMethod()
<DIV><FONT face=Arial 
size=2>---------------------------------------------------------</FONT></DIV></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>By this fashion if a record doesnt exist then i 
perform what is insert, else i do an update.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I think this is a bit clumsy and i hope to put all 
sql statements into the script to stop multiple external calls.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Hope this helps others.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Cheers,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Wayne</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=ltr 
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV 
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B> 
  <A title=khcarlso@bellsouth.net href="mailto:khcarlso@bellsouth.net">Kevin 
  Carlson</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>To:</B> <A title=wayne@c-media.com.au 
  href="mailto:wayne@c-media.com.au">Wayne Connolly</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>Cc:</B> <A title=zope@zope.org 
  href="mailto:zope@zope.org">zope@zope.org</A> </DIV>
  <DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, July 02, 2003 1:31 
  AM</DIV>
  <DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [Zope] Error, 
  _mysql_exceptions. 'ON DUPLICATE KEY</DIV>
  <DIV><BR></DIV>This is a mysql error message that has nothing to do with 
  zope.&nbsp; I don't believe that mysql supports the 'ON DUPLICATE KEY' 
  syntax...<BR><BR>Wayne Connolly wrote:<BR>
  <BLOCKQUOTE cite=mid004301c33f12$69bf93f0$c9698690@XP type="cite">
    <META content="MSHTML 6.00.2600.0" name=GENERATOR>
    <STYLE></STYLE>

    <DIV><FONT face=Arial size=2>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?</FONT></DIV>
    <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
    <DIV>
    <DIV><FONT face=Arial size=2><FONT face=Arial 
    size=2>--------------------------------------------------------------------</FONT></FONT></DIV></DIV>
    <DIV><STRONG>Error, <EM>_mysql_exceptions.ProgrammingError</EM>:</STRONG> 
    (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") </DIV>
    <DIV><FONT face=Arial 
    size=2>--------------------------------------------------------------------</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=Arial size=2>the ZSQL used is below</FONT></DIV>
    <DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=Arial 
    size=2>--------------------------------------------------------------------</FONT></DIV></DIV>
    <DIV><FONT face=Arial size=2>insert into 
    charts<BR>&nbsp;(title,<BR>&nbsp;artist,<BR>&nbsp;genre,<BR>&nbsp;album,<BR>&nbsp;plays)<BR>&nbsp;VALUES 
    (&lt;!--#sqlvar title type=string --&gt; ,<BR>&nbsp;&nbsp;&lt;!--#sqlvar 
    artist type=string --&gt; ,<BR>&nbsp;&nbsp;&lt;!--#sqlvar genre type=string 
    --&gt; ,<BR>&nbsp;&nbsp;&lt;!--#sqlvar album type=string --&gt; 
    ,<BR>&nbsp;&nbsp;plays=1<BR>&nbsp;&nbsp;)</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=Arial size=2>ON DUPLICATE KEY</FONT></DIV>
    <DIV>&nbsp;</DIV>
    <DIV><FONT face=Arial size=2>update charts set 
    <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; plays=plays + 
    1<BR>&nbsp;where title=&lt;dtml-sqlvar title type=nb&gt; and 
    artist=&lt;dtml-sqlvar artist 
type=nb&gt;</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>

--Boundary_(ID_aEGcu2sIa6kHixIyFi3vUQ)--