[Zope] MySQL and Zope struggles

[Baker, Dennis [sjmr]]

What about one sql method:

select @noteid:=note_id from artist where <dtml-sqltest artist_id type=int>
<dtml-var sql_delimiter> 
update note set notes =<dtml-sqlvar notes type=string>
     where note_id = @noteid



-----Original Message-----
From: Richard Moon [mailto:richard@dcs.co.uk]
Sent: Monday, October 16, 2000 2:48 AM
To: zope@zope.org
Subject: [Zope] MySQL and Zope struggles


I'm struggling to migrate an application from Zope/PostgreSQL to Zope MySQL 
(Why ? - because I've got a kind offer of free hosting if I use MySQL).

MySQL offers a limited set of features and is missing, among other things, 
the ability to use subqueries - so for example in PostgreSQL you can say

update note set notes =<dtml-sqlvar notes type=string>
     where note_id =
     (select note_id from artist where <dtml-sqltest artist_id type=int>)

and in MySQL you can't.

If you were working in a traditional programming environment you could 
overcome this by splitting the above into two parts - a select to retrieve 
the value of note_id from the artist table followed by an update of the 
note table using the returned value of note-id.

For example

select note-id into note-id-var from artist where artist-id = 23;
update note set notes = 'asdasda' where note-id = note-id-var;

The problem is that in Zope I believe you can't use a returned value within 
an SQL Method,  so the above code would fail. The only way I can see to do 
the above is to have two separate SQL Methods, one for the select, 
returning the note-id-var and another for the update. This is very clumsy.

I was wondering if anyone could tell me if there was a better way.

Many thanks


Richard

Richard Moon
richard@dcs.co.uk



_______________________________________________
Zope maillist  -  Zope@zope.org
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists - 
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope-dev )