Question re:DB package interaction with MySQL

Ben XO wrote:
> There is a very slight "gotcha" with the
2nd insert syntax: it's very easy
> to make a query so long that it fails (mysql gives an
error about
> "max_packet exceeded" I think) so you have
to make sure you limit the size
> of your insert.
> 

Yes, it's not very hard to hit the default limit.  It's
fairly trivial to get the configured max_packet size from
the database, and break an insert up into several smaller
queries, however.

> Well you are combining two separate methods. How should
autoPrepare() 
> know how many rows you want to insert?

Admittedly, it may not be practical to use autoPrepare() in
this context (I only used it in my example for simplicity's
sake), but as far as I can discern, there's no practical
way to use the DB package to efficiently insert large
numbers of rows, using autoPrepare() OR prepare().

I'd imagine at least a few of the other databases other
than MySQL suffer from the same problem, as well.

Slightly off topic, but is MDB2 the database abstraction
package of choice now?

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php

On 2/24/06, Frank F <frankbooksku.com> wrote:
> Ben XO wrote:
> > There is a very slight "gotcha" with
the 2nd insert syntax: it's very easy
> > to make a query so long that it fails (mysql gives
an error about
> > "max_packet exceeded" I think) so you
have to make sure you limit the size
> > of your insert.
> >
>
> Yes, it's not very hard to hit the default limit. 
It's fairly trivial to get the configured max_packet size
from the database, and break an insert up into several
smaller queries, however.
>
> > Well you are combining two separate methods. How
should autoPrepare()
> > know how many rows you want to insert?
>
> Admittedly, it may not be practical to use
autoPrepare() in this context (I only used it in my example
for simplicity's sake), but as far as I can discern,
there's no practical way to use the DB package to
efficiently insert large numbers of rows, using
autoPrepare() OR prepare().
>

That's because it's a prepared query....prepared queries
are supposed
to be...prepared...then executed. It doesn't make sens to
have a
variable length query and have it prepared. That's just not
how it
works.

You *could* make a multi-insert wrapper around it by giving
it the
precise # of params and the entire insert SQL I suppose. Or
perhaps
this could be added to autoInsert.

> I'd imagine at least a few of the other databases
other than MySQL suffer from the same problem, as well.
>
> Slightly off topic, but is MDB2 the database
abstraction package of choice now?
>

Absolutely. I suggest not wasting time adding new features
to DB when
MDB2 is pretty much a better choice any way you look at it.

--
Justin Patrin

-- 
PEAR Development Mailing List (http://pear.php.net/)
To unsubscribe, visit: http://www.php.net/unsub
.php