[Zope] MySQL and Zope struggles

[administrator@consotec.de]

--------------1DD2510B41FE
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I don't know if the following link can solve your problem,
but maybe it gives you an idea:
http://www.zope.org/Members/Roug/new_record_with_subrecords
(How-To: Creating a new record with subrecords in MySQL)
 Arno


> 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 =3D<dtml-sqlvar notes type=3Dstring>
>      where note_id =3D
>      (select note_id from artist where <dtml-sqltest artist_id type=3Dint>)
> 
> 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 =3D 23;
> update note set notes =3D 'asdasda' where note-id =3D 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 )
> 
> 
> To: zope@zope.org

--------------1DD2510B41FE--