Warren Young wrote:
> Rick Gutleber wrote:
>> However, that leads me to another question. I was all ready to start
>> knocking this thing out, but I realized each of the insert( ),
>> replace( ), etc calls in class Query puts a single query into the
>> stream which gets executed when you call execute( ). But now we are
>> creating a situation where one call to insert( ) could result in
>> multiple queries. We are doing so, among other things, to avoid
>> exceeding the maximum packet size. If I call execute( ) with, which
>> in turn calls mysql_real_query( ) which requires multi-statement
>> execution to be turned on to process multiple statements at once. I
>> would guess that maximum packet size would apply to the compound
>> statement not each individual one.
>
> First, there's no reason for multi-statements. An insert with
> multiple values() blocks is not multi-statements. It's just a single
> statement inserting multiple rows. See Query::insert(Iter, Iter) for
> the syntax. Note the lack of semicolons in the SQL.
I understand that. However if the query string exceeds the policy's
limit, then the bulk INSERT would have to done in chunks. That's what I
mean by multiple statements.
>
> Second, this flavor of insert() will insert immediately, without
> requiring an execute() call. In effect, it calls execute()
> internally, as many times as necessary to insert the entire
> container. Perhaps it should be called insertfrom() to avoid confusion?
I think that's a good idea. I didn't consider that because nothing else
does execute( ) internally, but that is the most obvious thing to do.
It should be called something a little different because it acts
different than every other flavor of insert( ). I'll use that name
unless I can think of something better.
> It occurs to me that you should probably wrap the whole thing in a
> transaction. If you're halfway through the container and an INSERT
> fails, you shouldn't leave the DB with half a container's worth of
> data inserted. The BEGIN statement should be wrapped in a
> NoExceptions block so it doesn't break insert[from]() if the DB engine
> isn't capable of transactions. If the BEGIN succeeds, set a flag that
> tells you to call ROLLBACK or COMMIT -- no sense trying to call them
> if the BEGIN failed. If an INSERT fails, roll back the transaction
> and throw a BadQuery, just as execute() would for a single-row
> insert. Remember to save the error you get from the INSERT before
> calling ROLLBACK, as that will reset the last-error code.
Makes sense to me. Thanks for the detail.
>> Query::insert( Iter & it, Policy policy ) {
>
> I think you should pass the policy object as a pointer, so you can
> pass 0 to get default behavior, and so you can pass a pointer to a
> static instance where that is sensible. Some policy objects will
> probably carry state that gets changed by the insertion process, and
> so shouldn't be static. Others will never change, so there's no point
> creating identical copies again and again.
That's reasonable. I'm not a big fan of pointers when references will
do, and would probably have provided a stock do-nothing Policy for those
purposes, but I'm happy to go with the pointer. Consistency in
libraries is very important to me, so I will place emphasis on doing
things the way they are done elsewhere in MySQL++.
Thanks for all your feedback and help, I think I am now equipped to do
some coding! Woot!
Rick
