List:MySQL++« Previous MessageNext Message »
From:Ana Johnson Date:July 6 2011 12:06am
Subject:Re: ssqls and extend insert syntax? how?
View as plain text  
excellent!   Thank you William.

I incorporated this version in my program.  it's working like a charm.
 you know...  its very cool actually.  I don't know how I missed so
much while reading the manual.  I guess the first time I read through
section 5 I was looking for different information.  The more I mess
with ssqls structures, the more I appreciate them.

So, I read the fine manual again, and discovered Query::insertfrom()
which appears to do everything I want to do.  I like the design,
especially the InsertPolicy aspect.  I tried to make it work, but I
seem to have an older version of the library installed...  which so
far doesn't have insertfrom().  Oh well.  My server's default
max_packet_size is 16MB, and if I hit that limit then I have bigger
problems with which to contend.  so, Query::insert(begin, end) it is.
I will perhaps download the latest version to else it has to offer.

My second attempt involved filling a std::set with my ssqls
structs....  then passing the set::begin() and set::end() iterators to
the insert.  I ran into a compile time error.  Just out of curiosity,
I modified query.h a little, and found that this tiny change is enough
to make it work.

------------------------------------------
--- query.h	2011-07-05 16:43:30.000000000 -0700
+++ query1.h	2011-07-05 16:44:19.000000000 -0700
@@ -1006,7 +1006,8 @@
 				first->field_list() << ") VALUES (" <<
 				first->value_list() << ')';

-		Iter it = first + 1;
+		Iter it = first;
+		it++;
 		while (it != last) {
 			MYSQLPP_QUERY_THISPTR << ",(" << it->value_list() << ')';
 			++it;
------------------------------------------

that's all.  it appears, I think(?), that the "first + 1" is enough to
force the requirement of a random access iterator.  replacing it with
++ enables the use of the less feature-full iterator associated with a
std::set.

All the same...  I certainly am not in the habit of altering library
files.  And, I bet there's a better way to do what I was trying to do
anyway.

std::set is, however, definitely the best option for storing rows
prior to insertion, for my programming purposes at least.  The
std::set features are compelling enough, and the library change is
inconsequential enough, that I am actually considering sticking with
this little change.  I do not want the headache that will come with
maintenance.  hm....  a conundrum.  I think I'll download the latest
version of the library to see if it will work better for me.

Thanks!!!

- Ana


On Sat, Jul 2, 2011 at 3:01 PM, Kemet <kemet@stripped> wrote:
> Hi Ana,
>
> Better have a look at section 5.4 ! First you have the example of inserting
> a single row (which you are probably using atm),
> then it shows how to use the same "query.insert" statement to insert many
> rows at once by using a vector instead of a single ssqls variable.
>
> So you're example from below would be:
>
> sql_create_3( mieeff, ...)  // like you typed it
>
> vector <mieeff> vLotsOfData;    // here we store all the data to
> insert
>
> // populate the vector
> vLotsOfData.pushback( mieeff( radius1, wavelength1, qext1) ); // first row
> to insert
> vLotsOfData.pushback( mieeff( radius2, wavelength2, qext2) ); // second row
> ... or any other way to add elements to the vector
>
> // now do 1 big insert at once:
> mysqlpp::Query q = conn.query();
> q.insert(vLotsOfData.begin(), vLotsOfData.end());    // all elements of
> our
> vector, but you can also do a more limited range
> q.execute();
>
> and if the query could become very long, you have the "insertfrom" discussed
> at the end of section 5.4.
>
> Hope this helps !
> William
>
>
> ----- Original Message ----- From: "Ana Johnson" <anajilly@stripped>
> To: <plusplus@stripped>
> Sent: Saturday, July 02, 2011 1:37 AM
> Subject: Re: ssqls and extend insert syntax? how?
>
>
> although I believe it may be bad form to reply to my own post...
>
> I found an answer. although my specific question isn't addressed
> directly in the manual, the answer is there in the section labeled
> "5.5. Harnessing SSQLS Internals".
> referencing my own question... the answer (that I came up with
> anyway... there may be a better one) goes something like this:
>
> sql_create_3( mieeff, // this is the name of the struct and the table
> 3, 3,
> sql_bigint, radius,
> sql_bigint, wavelength,
> sql_double, qext /* .... */ )
>
> mieeff effc( 1,2,3 );
> mysqlpp::Query q = conn.query();
>
> q << "insert into mieeff (" << effc.field_list() << ") values ("
> <<
> effc.value_list() << ")";
>
> effcl[n] = yourmagic();
>
> for( /* all values in my list ... */ )
> {
> q << ",(" << effcl.value_list() << ")";
> // there must be *some* concern about the length of the command.
> if( q.str().size() > whatever )
> break;
> }
>
> q.execute(); // pseudo code. I don't remember the method name.
>
>
> On Fri, Jul 1, 2011 at 2:55 PM, Ana Johnson <anajilly@stripped> wrote:
>>
>> Hi.
>> I'm using the ssqls structures to setup my sql statements. I like it.
>> I believe I'm calling too many insert commands though. I want to switch to
>> using mysql's extended insert syntax. I haven't found anything in the
>> mysql++ documentation about this server/protocol capability, or about how to
>> use it through the mysql++ library. (I haven't found any google results
>> either.)
>> to be clear, by "extended insert syntax" I mean: "insert into blah (c1,c2)
>> values (1,2),(1,3),(1,9823),(23,8923)"
>> Is there support for mysql's extended insert syntax in mysql++? How would
>> I use it? Do I need to switch to method which does not use the ssqls
>> structures?
>> Thanks!!
>> - Ana
>
> --
> MySQL++ Mailing List
> For list archives: http://lists.mysql.com/plusplus
> To unsubscribe:    http://lists.mysql.com/plusplus?unsub=1
>
>
> --
> MySQL++ Mailing List
> For list archives: http://lists.mysql.com/plusplus
> To unsubscribe:    http://lists.mysql.com/plusplus?unsub=1
>
>
Thread
ssqls and extend insert syntax? how?Ana Johnson2 Jul
  • Re: ssqls and extend insert syntax? how?Ana Johnson2 Jul
    • Re: ssqls and extend insert syntax? how?Kemet3 Jul
      • Re: ssqls and extend insert syntax? how?Ana Johnson6 Jul
        • Re: ssqls and extend insert syntax? how?Warren Young6 Jul
          • Re: ssqls and extend insert syntax? how?Ana Johnson6 Jul
            • Re: ssqls and extend insert syntax? how?Warren Young7 Jul
              • Re: ssqls and extend insert syntax? how?Ana Johnson8 Jul
                • Re: ssqls and extend insert syntax? how?Warren Young8 Jul