[Zope] multiple inserts and MySQLDA

[Sam Gendler]

Actually, MySQL allows the insertion of multiple records in a single
insert statement, and it is orders of magnitude faster than looping in
dtml while executing a sql query on every iteration.  Just do:

INSERT INTO table (field1, field2) VALUES (value1, value2), (value3,
value4), (value5, value6),...

Each set of values must be wrapped in parentheses, and there should NOT
be parenthese around the entire list of value sets.

Obviously, you have to build the list with a dtml-in statement, although
you could possibly trick dtml-sql into doing it for you by using a list
of tuples and setting the multiple variable.  I don't know about that
one.

--sam

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.
> 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 )