[Zope] MySQL and Zope struggles

[Richard Moon]

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