Rows affected in mysql

[pradeepktg]

Hi,

How to find number of rows affected in update statement using mysql (not using php mysql).

Thanks,
Pradeep

[bhanu1225]

brief explanation please....Mr Pradeep

[pradeepktg]

Hi bhanu1225,

Thanks! Actually, am trying to update a record say: update tbl_name set name='bhanu1225' where name='pradeep' and id='1';

If the update is success, i want to print a messgae 'successfully updated' in frontend. For this i want to know if a record is updated in database or not. Am using mysql and jsp.

Thanks,

[Atli]

Hi.

The ROW_COUNT function will return the number of rows in a previous INSERT, UPDATE or DELETE statement.

[pradeepktg]

Hi Alti,
row_count() is working properly in command prompt, but whereas in query browser,it always shows -1 value even after records are updated successfully.

I would be better if u can give sample code on how to get row_count value in front end.

Thanks,

[Atli]

Ok.

It seems query browser executes queries on separate connections, or something like that, so when you execute a INSERT/UPDATE/DELETE and then a SELECT ROW_COUNT(); query, the data from the latter will no longer be available to the function.

An easy fix is to just create a wrapper procedure.
For example:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`rc_exec`$$
CREATE PROCEDURE `test`.`rc_exec` (IN command VarChar(60000))
BEGIN
	SET @query = command;
	PREPARE stmt FROM @query;
	EXECUTE stmt;

	SELECT ROW_COUNT() AS 'Affected rows';
END$$

DELIMITER ;

Which you could then call like:

CALL rc_exec("
	INSERT INTO myTable(First, Second)
	VALUES  ('Testing First', 'Testing Second'),
                ('Testing First again', 'Teststing Second again');");

Which returns a single field for 'Affected rows' with the value 2.

[pradeepktg]

Ok. Thanks Atli ! Is there any other options like mysql_affected_rows() as in php???

[Atli]

The ROW_COUNT function is the mysql_affected_rows equivalent in MySQL.

Keep in mind that the query browser is just a development tool. It doesn't execute queries the same way a PHP or JSP script would. They wouldn't need the procedure to use the ROW_COUNT function.

For example, this would print "3":

<?php
$inResult = mysql_query("INSERT INTO tbl VALUES()()()");
$rcResult = mysql_query("SELECT ROW_COUNT()");
$count = mysql_result($rcResult, 0, 0);
echo $count;
?>

And I'm sure the JSP equivalent for that would to.
And in languages like those, you don't actually have to use the ROW_COUNT function. They have functions like the mysql_num_rows function.

The ROW_COUNT function isn't really mean to be used like this.
It's more aimed at procedures and such.