From: Warren Young
Date: August 10 2007 2:51pm
Subject: Re: Using Result, Query::store, Query::store_next and Connection::opt_multi_statements
 with INSERT, UPDATE, DELETE etc.
List-Archive: http://lists.mysql.com/plusplus/6908
Message-Id: <46BC7B7B.4040303@etr-usa.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

James Vanns wrote:
> I don't think I suggested it was a major 'problem'. Just a minor
> inconvenience ;)

Then please excuse my misread of your report of something "this major" 
being changed in the context of a problem report.

> With regards to the Query::success thing, naturally I use try {}
> catch {} blocks for MySQL++ exceptions but I can't check the return
> status of a Result object - cos there isn't one! 

I don't see how there can be no Result object.  It's not throwing an 
exception, so the function does return, so there should be an empty 
result set.  In bool context, an empty Result is false.  There's your 
"end of results test" right there.

This method should continue to work in v3 as well.

> As far as the 'affected rows' thing is concerned I'm dead serious.
> Both with this version and the one I used previous (2.1) I can do
> this:
> 
> query << "UPDATE blah;DELETE foo;INSERT whatever;SELECT something";
> 
> run the algorithm previously posted and have 0 rows affected returned
> by Connection::affected_rows(). Running the exact same statements
> through a mysql shell against the same result set - it returns 2 rows
> affected for the update, 1 for delete etc.

I don't think you're comparing apples to apples.

The mysql program knows how to interpret semicolons on its own.  Indeed, 
it absolutely requires them to terminate a query.  I would guess that it 
is sending off four separate queries to the server in your example, so 
it can deal with the results individually.  This ability to interpret 
semicolons predates the multiquery feature in MySQL, so they'd have to 
back-pedal to use multiqueries here.  There's no good reason for them to 
do that.

MySQL++ does not interpret the SQL query in any way.  It just ships the 
string off to the server, via the C API.  There's no reason to suspect 
the C API can do semicolon interpretation, either; nor should it, 
because that would obviate the multiquery feature.

Therefore, semicolons mean something different in each case, which 
explains why the results are different.

It is possible that if you call affected_rows() between each 
store{_next}() call, you could get different values each time.  If that 
doesn't work, the only way to get individual row counts is to issue 
separate queries.

> Sometimes it even manages to return UINT_MAX ;) This is on an Intel
> 32-bit Fedora 6 machine. The documentation suggests it returns a type
> of 'my_ulonglong'. This is simply an unsigned long long? 

I'm sure it varies depending on the platform.  See the C API header files.

> http://dev.mysql.com/doc/refman/5.0/en/mysql-affected-rows.html
> 
> is confusing - it says a -1 can be returned even though the type is
> an unsigned integer! 

I guess you don't understand two's complement integer representation, 
then.  Read this, and I think you will understand where your UINT_MAX is 
coming from:

	http://en.wikipedia.org/wiki/Two%27s_complement