[Zope] multiple inserts and MySQLDA

[Sam Gendler]

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 )