MySQL

Rows affected in mysql

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2009

Posts: 11

Reputation: pradeepktg is an unknown quantity at this point

Solved Threads: 0

pradeepktg pradeepktg is offline

Offline

Newbie Poster

Rows affected in mysql

Jun 16th, 2009

Hi,

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

Thanks,
Pradeep

•

Join Date: Dec 2008

Posts: 51

Reputation: bhanu1225 is an unknown quantity at this point

Solved Threads: 1

bhanu1225

Offline

Junior Poster in Training

Re: Rows affected in mysql

Jun 18th, 2009

brief explanation please....Mr Pradeep

Bhanu
Choose to experience peace rather than conflict

•

Join Date: Jun 2009

Posts: 11

Reputation: pradeepktg is an unknown quantity at this point

Solved Threads: 0

pradeepktg pradeepktg is offline

Offline

Newbie Poster

Re: Rows affected in mysql

Jun 18th, 2009

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,

•

Join Date: May 2007

Posts: 433

Reputation: Atli is on a distinguished road

Solved Threads: 56

Atli

Offline

Posting Pro in Training

Re: Rows affected in mysql

Jun 18th, 2009

Hi.

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

Please do not ask for help in a PM. Use the forums.
And use [code] tags!

•

Join Date: Jun 2009

Posts: 11

Reputation: pradeepktg is an unknown quantity at this point

Solved Threads: 0

pradeepktg pradeepktg is offline

Offline

Newbie Poster

Re: Rows affected in mysql

Jun 19th, 2009

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,

•

Join Date: May 2007

Posts: 433

Reputation: Atli is on a distinguished road

Solved Threads: 56

Atli

Offline

Posting Pro in Training

Re: Rows affected in mysql

Jun 19th, 2009

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:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
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 ;
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:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
CALL rc_exec("
	INSERT INTO myTable(First, Second)
	VALUES  ('Testing First', 'Testing Second'),
                ('Testing First again', 'Teststing Second again');");
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.

Last edited by Atli; Jun 19th, 2009 at 5:00 am.

Please do not ask for help in a PM. Use the forums.
And use [code] tags!

•

Join Date: Jun 2009

Posts: 11

Reputation: pradeepktg is an unknown quantity at this point

Solved Threads: 0

pradeepktg pradeepktg is offline

Offline

Newbie Poster

Re: Rows affected in mysql

Jun 19th, 2009

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

•

Join Date: May 2007

Posts: 433

Reputation: Atli is on a distinguished road

Solved Threads: 56

Atli

Offline

Posting Pro in Training

Re: Rows affected in mysql

Jun 19th, 2009

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":

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<?php
$inResult = mysql_query("INSERT INTO tbl VALUES()()()");
$rcResult = mysql_query("SELECT ROW_COUNT()");
$count = mysql_result($rcResult, 0, 0);
echo $count;
?>
<?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.

Please do not ask for help in a PM. Use the forums.
And use [code] tags!