List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 11 1999 5:03pm
Subject:How to obtain "rows matched" value?
View as plain text  
>>>>> "Erhhung" == Erhhung Yuan <erhhung@stripped> writes:

Erhhung> I don't know why my posts to myodbc@stripped never show up,
Erhhung> but here I go again:

Erhhung> I have a function that, given a struct, sets a unique record
Erhhung> into the database regardless if that record already exists--
Erhhung> pretty standard action.  So the function looks like:

Erhhung> void SetInfo(const MyData& data)
Erhhung> {
Erhhung> ...
Erhhung> // I'm using MySql++ syntax...
Erhhung> query << "update myTbl set ... where ...";
Erhhung> query.execute();

Erhhung> if (query.affected_rows() == 0)
Erhhung> {
Erhhung> ...
Erhhung> query << "insert into myTbl ...";
Erhhung> query.execute();
Erhhung> }
Erhhung> }

Erhhung> Now, my problem is that if the update statement finds the
Erhhung> existing record *but the column values are the same*, the
Erhhung> value of affected_rows() is still 0, causing the insert
Erhhung> statement to execute and produce duplicate primary key
Erhhung> violation.

Erhhung> There is only one insert the first time and many updates
Erhhung> thereafter, so I usually want to execute the update first
Erhhung> and be complete with just one query.  I can always delete
Erhhung> the record first or do a select to check for the existence
Erhhung> but then those approaches both incur two queries for each
Erhhung> SetInfo call.

Erhhung> What I want is the "rows matched" value that I see MySql
Erhhung> command processor reports when I do an update query, but
Erhhung> I don't see the equivalent API call. Is there an equally
Erhhung> efficient way to do what I want to do?

Hi!

There is many ways to solve this.

- Use REPLACE instead of UPDATE / INSERT
- Use the 'mysql_info()' call to get the 'rows matched' value.
- put into your my.cnf file:

[your_prog_name]
return-found-rows

(You have of course to execute
mysql_options(mysql_struct, MYSQL_READ_DEFAULT_GROUP,"your_prog_name");

in this case)

- Invoke mysql_real_connect() with the CLIENT_FOUND_ROWS as the last
  argument.

Regards,
Monty

PS: Sorry for the long delay before replying, but I just come back from
    a 2 weeks vacation trip.

Thread
How to obtain "rows matched" value?Erhhung Yuan22 Oct
  • How to obtain "rows matched" value?Michael Widenius11 Nov