MySQL->Core: Option to receive affected rows?

Hi,

I have a question which I think I already could answer myself with no.

Is there a possibility to get the mysql_affected_rows-return on queries doing “UPDATE” ?

I’ve seen that the connection to mysql is done with mysql_real_connect, and the connection flag is 0.

To return “found rows” instead of only “changed rows” the flag has to be “2” (CLIENT_FOUND_ROWS).

I fear, I also understood it correctly, that for non-blocking queries (Execute, PExecute) it might be not possible to get the result.

For normal queries, Even for normal Queries I can not get the resulting “numRows” as it is only returned with entries in the resultset (so for “SELECT”-statements).

What do I want to achieve/Why I’m asking for this.

In some places there is “REPLACE INTO” used, which does: lookup entry, compare unique keys, if it hits, deletes old entry and inserts new.

But as long as only one unique key is compared, it would be gaining more performance having:

Update XYZ

→ row affected (changed OR found - latter if fieldvalues are the same)?

YES

→ everything ok

NO

→ insert XYZ

It’s more performant as all already existant entries just get updated instead of getting a “is it there?”-check.

I’ve also already seen that there is some usage of “Insert … on duplicate key update” within boss-scripts. As each table with only one unique key performs better with the “on duplicate key” method, I just want to know if there’d be some use to transform those queries or if there is a reason why it is that way.

bye

GWR

You can add the MySQL C API method mysql_affected_rows(MYSQL*) for that. Just add it to the execution methods. It will also work for asyncrhonous, non-blocking, queries if you add it to the task’s Execute method, (ie: BasicStatementTask::Execute)

Just to check my assumptions:

I’ll have to change the way mysql_real_connect(…,flags) is atm - from 0 to 2.

Are there parts of the code relying on getting only changed rows than also “found” rows?

Second problem, I didn’t catch everything what you have replied to my question. So for clarifying:

for example I want to get the amount of affected rows for the following part (eg to check whether there was something deleted):

(item.cpp)


void Item::DeleteRefundDataFromDB()

{

    CharacterDatabase.PExecute("DELETE FROM item_refund_instance WHERE item_guid = '%u'", GetGUIDLow());

}

How would I achieve that without adding too much overhead with too many new variables, getters… ?

Sorry for asking a bit “amateurish” questions but assuming you know more about the code it may be wiser to ask u instead of struggling with it for hours with finding nothing at the end.

Thanks in advance,

bye

GWR

EDIT:

AdhocStatement.cpp

→ bool BasicStatementTask::Execute()

returns only numRows if containing results (which is not the case if doing “update” or “delete”) - else ResultSet(null), like written in my first post.

mysql_affected_rows works for INSERT, UPDATE, SELECT, etc. See http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html

It is currently not called, you’d have to add it. And then you can use logging methods to print the return value for analysing.

That’s why I’ve asked whether the change mysql_real_connect’s params will have unwanted effect on some other queries done by the core.

Like I’ve written above, current param uses flag = 0, which only returns changed rows, flag should be CLIENT_FOUND_ROW to also return “found” rows (update values are equal to rows fields).

To be honest, atm I have to clue where to add the part you described as I have written, that for “normal” ResultSets the rows are returned while “updates/deletes” get a ResultSet(null) returned.

So it would be way more kind of you to give a kind of code snippet instead of describing ways whose path is covered by the fog of my missing knowledge /emoticons/default_biggrin.png.

As I’m not that firm with TCs code I wont change standard returns to achieve an effect which then might break something else.

bye

GWR

Hmm still no answer, I’ll just wait for my changes until I got a helpful answer (to avoid posting trash /emoticons/default_biggrin.png).

Just to add, as it concerns a commit by Machiavelli and he will most likely read my post here:

https://github.com/TrinityCore/TrinityCore/commit/6618655d042c377be38f4395af42c4defe245ed8

At the end my changes won’t be that huge like the ones of others, but 10 small changes might be equal to one

big.

bye

GWR