How to set a connection parameter?

I'd like to set the Found Rows=true connection parameter so that when I issue an update statement via the query window it will return the number of rows that matched the where clause rather than the number of rows modified. (These two are different when updating a record to a value that is the same as already on that record.) This parameter is supported by the DevArt MySql connector that I'm using but I can't figure out how to set connection string parameters for HeidiSql.

Anyone have any ideas?

That's the CLIENT_FOUND_ROWS (2) flag which must be passed to mysql_real_connect(). There is no way to do that currently. If I would make that a session setting, I would break HeidiSQL's grid operations, which rely on the right number of affected rows to work correctly.

What's the point in preferring CLIENT_FOUND_ROWS?

anse,
Thanks for your response. The reason that I would like this setting as an option is that I use HeidiSql to test out sql before embedding that sql in my application. My application connects to MySql with CLIENT_FOUND_ROWS set so that is knows if the update command issued matched rows or not. In the application this is more important than whether the value supplied in the update happen to match the values that were already in teh database and resulted in no actual record change. Since I use HeidiSql to write and test the sql first before incorporating it in my app, it'd be super helpful if the info returned for an update statement executed in the query window matched what I get in my app. So I would really like it if Heidi returned a number that indicated the number of rows that matched the where clause rather than the number actually modified. I'd be happy to give up the grid operations for that but I can see that many users wouldn't make that trade. Is there a way for you to provide a field where a user could just specify additional connection string values that get incorporated in the login. Then if CLIENT_FOUND_ROWS is set there by the user, they won't have grid opperations. But they will have the behavior they might need to match their other MySql environments. Would that be possible?

Well, not having grid operations means "grid updates will crash left and right". Also other stuff may depend on the affected rows, but that's more a gutt feeling than a fact. What about a registry hack for passing additional CLIENT_* flags? You would need to know exactly what you add there, but once you know that CLIENT_FOUND_ROWS has the value "2", you're done.

hm. what about using a simple

SELECT Count(*) FROM xxx WHERE where_cond

instead ?

Yes, of course. A simple

SELECT FOUND_ROWS();

after your update/insert should do it as well.

That's a great suggestion. However it'd be nice not to have to run a select statement with every update statement if HeidiSql could just return the found rows when executing update statements.

When a update statement is ran in the query window, it returns somethign like:
/* 2 rows affected, 0 rows found. Duration for 1 query: 0.031 sec. */

It seems that it always shows 0 rows found. My problem could be solved if this part of the output showed the correct value for found rows for the update statement. Is there an easy way to fix that?

When a update statement is ran in the query window, it returns something like:
/* 2 rows affected, 0 rows found. Duration for 1 query: 0.031 sec. */

It would be super helpful if instead of 0 rows found it could show an accurate representation of the number of rows that matched the where clause of the update statement. It seems that the current display of 0 rows found is a bug. Can this be fixed?

If you say that's a bug than you express that the API function mysql_affected_rows() has a bug (which is not true). If you need the number of rows which matched your WHERE clause than you would need an API function like mysql_matched_rows() or so, which does not exist. HeidiSQL calls mysql_affected_rows() and displays the result in that output line "x rows affected...".

Think I misread your comment. "0 rows found" is always displayed when the query did not return a result. I can, however, check if the API function throws errors or not when I check mysql_found_rows() also when the query did not return a result.

Btw, your HeidiSQL is very old, isn't it? I can see that by your SQL log output.

anse, thanks for the response.
I'm using HeidiSql 7.0.0.4053

To provide a very concrete example.
If I have a table called simple and execute this statement

update simple set code = 'hello5' where simpleId > 7;

in the query window which matches 3 records but where 2 records already have the code field set to hello5, HeidiSql returns the following:

/* 1 rows affected, 0 rows found. Duration for 1 query: 0.031 sec. */

as a developer, it'd be _super_ helpful if it could also tell me that 3 records matched. Is that possible?

I'm not sure mysql_found_rows() does exactly what you want, but I can test that as mentioned above. In the meantime, please update your HeidiSQL to the latest build, so we roughly talk about the same revision.

Eh, typo, it's not mysql_found_rows(), but mysql_num_rows().

No, does not work. mysql_num_rows() needs a non-empty result parameter, which we do not have when the query did not return a result. If I call it with an empty (null/nil) parameter, I get an "Access violation at address 6DC1C264 in module 'libmysql.dll'".

That's how it's documented on http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html :

mysql_num_rows() is intended for use with statements that return a result set, such as SELECT. For statements such as INSERT, UPDATE, or DELETE, the number of affected rows can be obtained with mysql_affected_rows().

Passing CLIENT_FOUND_ROWS flag does what you want. Then, mysql_affected_rows() returns the number of matching rows. HeidiSQL still displays it as "Affected rows: x", and if I fix that to display "Affected rows: 0 Found rows: x", grid editing is impossible, due to checks of Heidi's affected rows variable. Leaving that number in the "Affected rows" sentence would be wrong, as "found rows" are not what "affected" expresses.

anse,
Thanks again for looking into this for me. It sounds like there may not be a good way to get heidisql to return the rows that matched the update statement. Rows affected is awesome, but it'd be so helpful to see rows that matched as well. The case that is the most troubling for me is when I'm testing a piece of sql that I know should update one row and when I run it in the query window it says 0 rows affected. I'm left not knowing if the issue is a) my update statement's where clause is incorrect or b) the statement is fine but the row that matched already has the value set to what I was trying to set it to. I think you can probably empathize from a development point of view, this is a bummer.

Not a bummer for me, I'm living several years with that MySQL fact. You get affected rows, and if your update contains values which are already present in the row, affected rows is 0, so you know the number of physically changed rows. That's good, isn't it? Even more important than to know what rows matched your WHERE clause, in an UPDATE query.

Knowing the number of rows affected is awesome. But knowing the number of rows that matched the where clause can be equally important. Otherwise when rows affected is 0 it might indicate an improperly constructed where clause. As a programmer, if I see rows affected = 0, and know that rows matched was 1. Then all is good. but if rows affected = 0 and rows matched = 0 for a row that I know exists, I'm alerted to the fact that my where clause has an issue I need to fix.

I still love heidisql, it's a great product. I was just wondering if there was a easy way to have it show rows matched in addtion to rows affected. It may just be the nature of mysql that such a feature can't be easily implemented. anse, I appreciate your time and thought on this.

No problem. Thanks for your immediate feedback!