You can actually nest ZSQL methods in a couple of ways. First of al, I sometimes find it easiest to use a dtml-let to set up the namespace for the second query, when doing what you are doing eg: <dtml-in qry1> <dtml-let value1=var1 value2=var2> <dtml-in qry2> ... </dtml-in> </dtml-let> </dtml-in This has always worked for me. There should be know need to pass parameters in explicitly, since dtml-let should put them in the namespace. HOWEVER, depending on your needs, this is sometime easier. ZSQLMethod # 1 (id is qry1) SELECT (value1, value2) from table1 ZSQLMethod # 2 (id is qry2) INSERT INTO table2 values <dtml-in qry1> <dtml-unless sequence-start>,</dtml-unless> (<dtml-var value1>, <dtml-var value2>) </dtml-in> Notice that I am calling the first query directly from the second query, which is about as close to nested SELECT statements as you can get in MySQL. --sam Jorge Magalhaes wrote:
On Fri, 7 Apr 2000 Daniel.Weber@SEMATECH.Org wrote:
A couple of methods pop into my head:
1) At the point the data is available, use a <dtml-in> to call an insert procedure once for every exam score.
This is my first problem: make the tuple list!!!
my table in the db have two fields (student_name and student_id)
my first query are
select student_id as ID from students;
in DTML method i tried:
<form action="send"> <table> <dtml-in "firstquery()"> <tr> <td><dtml-var ID></td> <td><input type="TEXT" name="scor"></td> </tr> <td><input type="submit" value="Score it"></td> </table> </form>
my second query is:
insert into scores (student_id, score) values (<dtml.sqlvar IDE type=int>, <dtml-sqlvar SCORE type=int)
my send method
<dtml-in "firstquery()"> <dtml-call "secondquery(IDE = ID, SCORE= scor)"> </dtml-in>
don't work !!!!!!!!!!!!!!!!!
bad news
Thanks for your attention
2) Pass the list of (exam_score, student) in as a list of tuples to an sql query and use a <dtml-in> combined with a <dtml-var sql_delimiter> between each insert, such as:
<dtml-in exam_scores> INSERT INTO EXAM_DB VALUES (<dtml-sqlvar score type=?>, <dtml-sqlvar student type=nb>) <dtml-unless sequence-end><dtml-var sql_delimiter></dtml-unless> </dtml-in>
passing the data in in a usable format could be a challenge if you have two lists instead of a list of tuples. i don't know what the maximum size of an insert is - you may be better of with (1) if the insert size is large.
HTH...
-----Original Message----- From: Jorge Magalhaes [mailto:root@predict.telepac.pt] Sent: Thursday, April 06, 2000 5:49 PM To: zope@zope.org Subject: [Zope] multiple inserts and MySQLDA
Hi Zopiis:
How i can build a DTML method for making multiple inserts in MySQL db. For example, inserting a exames scores for a class with 20 students.
My problem isn't make the sql query, but call the query for each pair (student_id, score).
I would like fetched the student_id and the student name from the db. How i can do it?
I find in this newsgroup some posts about the Product MySQLDA1.4. I have some queries for select integers and y d'ont have the related problems. For that i have changed the type INT(2), for example, to INT
If a is defined a INT(2)
for the query SELECT a FROM T1, the result is (23L)
i make
ALTER TABLE t1 CHANGE a a INT;
after this modifications for the query
SELECT a FROM T1, the result is (23)
It's Ok
Have a nice day. -- +------------------------------------------------------------- ---------+ | Jorge Magalhães Email: jmagalhaes@eng.uminho.pt | | Quinta do Calvário, bloco 14 Telemóvel: 966172200 | | 2º Direito Trás Fax: + 351 253 516007 | | 5400 Chaves Phone: + 351 276 333008 | | Portugal Email: magalhaes.jorge@netc.pt | +------------------------------------------------------------- ---------+
_______________________________________________ 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 )
_______________________________________________ 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 )