[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> </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> </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> </DIV>
<DIV><FONT face=Arial size=2>Then i test the select with python script
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </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> res=context.InsertMethod()<BR>else:<BR>
res=context.UpdateMethod()
<DIV><FONT face=Arial
size=2>---------------------------------------------------------</FONT></DIV></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </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> </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> </DIV>
<DIV><FONT face=Arial size=2>Hope this helps others.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Cheers,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Wayne</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </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. 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> </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> </DIV>
<DIV><FONT face=Arial size=2>the ZSQL used is below</FONT></DIV>
<DIV>
<DIV> </DIV>
<DIV><FONT face=Arial
size=2>--------------------------------------------------------------------</FONT></DIV></DIV>
<DIV><FONT face=Arial size=2>insert into
charts<BR> (title,<BR> artist,<BR> genre,<BR> album,<BR> plays)<BR> VALUES
(<!--#sqlvar title type=string --> ,<BR> <!--#sqlvar
artist type=string --> ,<BR> <!--#sqlvar genre type=string
--> ,<BR> <!--#sqlvar album type=string -->
,<BR> plays=1<BR> )</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>ON DUPLICATE KEY</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>update charts set
<BR> plays=plays +
1<BR> where title=<dtml-sqlvar title type=nb> and
artist=<dtml-sqlvar artist
type=nb></FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
--Boundary_(ID_aEGcu2sIa6kHixIyFi3vUQ)--