List:MySQL++« Previous MessageNext Message »
From:Warren Young Date:October 15 2008 7:37pm
Subject:Re: Using MySQL++ with lots and lots of data
View as plain text  
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.

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?

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.

> 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.
Thread
Using MySQL++ with lots and lots of dataRick Gutleber13 Oct
  • Re: Using MySQL++ with lots and lots of dataAndrew Sayers14 Oct
  • Re: Using MySQL++ with lots and lots of dataWarren Young14 Oct
    • Re: Using MySQL++ with lots and lots of dataRick Gutleber14 Oct
      • Re: Using MySQL++ with lots and lots of dataWarren Young15 Oct
        • Re: Using MySQL++ with lots and lots of dataRick Gutleber15 Oct
          • Re: Using MySQL++ with lots and lots of dataWarren Young15 Oct
            • Re: Using MySQL++ with lots and lots of dataRick Gutleber15 Oct
              • Re: Using MySQL++ with lots and lots of dataWarren Young15 Oct