>>>>> "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> // I'm using MySql++ syntax...
Erhhung> query << "update myTbl set ... where ...";
Erhhung> if (query.affected_rows() == 0)
Erhhung> query << "insert into myTbl ...";
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> 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?
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:
(You have of course to execute
in this case)
- Invoke mysql_real_connect() with the CLIENT_FOUND_ROWS as the last
PS: Sorry for the long delay before replying, but I just come back from
a 2 weeks vacation trip.