1

I would count the number of rows affected by the query provided as a prepared statement in java passed to a MySql engine.

Given the following sample queries passed to a prepared statement, I need to be able to count the total number of rows affected by all the queries.

//insert data into table X ;
//update records of table X ;

The queries are separated by ";" which is part of MySql syntax to support several CRUD operations over a particular PreparedStatement object. It seems when the "executeUpdate()" method is invoked, that is only the number of rows affected by the first query, i.e., insert to the table, gets returned. Do I miss something I was supposed to provide to get the number of total affected rows in such that query?

Here is the sample real code I am working on:

insert into Activity
select * from (select ?, ?, ?, ?) as temp
where not exists(select * from Activity where ActivityName=?);
update Activity
set EmployeeeName=?, DepartmentName=?, roleId=? 
where ActivityName=?;

I expect the minimum of 1 as the output while get the 0 instead.

Vahid133
  • 13
  • 4
  • Tell us what have you done so far to resolve it? Tell us how have you analyzed or debugged it. – mentallurg Sep 03 '19 at 20:42
  • You can check this link. https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records – Sambit Sep 03 '19 at 20:44
  • You have to enable auditing in database level. – Sambit Sep 03 '19 at 20:46
  • Why not run the SQL statements one at a time? You aren't gaining anything by running them in a batch. – Bill Karwin Sep 03 '19 at 21:07
  • @mentallurg I tried several solutions. Firstly tried to reorder queries and then debug the program but finally did not get any useful feedback. Afterwards, I came up with checking various methods of prepared statement class that could be used for executing the query and seemed still getting the same result. Actually as it obvious in the query, one operation (insert or update) at a time is desired . I tried with "if" statement but could not find clear solution for in MySql. – Vahid133 Sep 05 '19 at 05:39
  • @BillKarwin Actually, I came up with the idea you mentioned, right before posting the question. To do so, I just wanted to use "if" statement to run a query at a time but could not find a clear solution for in mysql. I think you are right and I should go for your suggestion. – Vahid133 Sep 05 '19 at 05:46

1 Answers1

0

  1. Check what returns select * from Activity where ActivityName=?.

  2. Check what returns select ... where ... (select ...).

After your explanation of your goal in the comments there are other solutions to add values that don't exists yet.

  1. Define column ActivityName is a primary key (if not done yes). Then don't check anything, don't do any select, just do insert:
    try {
      // statement to insert like
      // insert into Activity (ActivityName, EmployeeeName, DepartmentName, roleId)
      //     values (?, ?, ?, ?)
    } catch ... {
      // If the exception is caused by primary key violation,
      // then such ActivityName already existed and we can ignore this exception
    }

Why not check first if such ActvityName already exists? Because if you have many requests in parallel from the same user or from many other users, then in the time between your check and between inserting new value some other request can insert this value, and you get primary key violation exception. That's why you need try/catch any way. That's why just do insert without any check and use proper try/catch.

mentallurg
  • 4,967
  • 5
  • 28
  • 36
  • (1) checks whether the targeted activity (which its name is sent as a prepared statement parameter) already exists in the "Activity" table or not. (2) returns the values should be inserted into the "Activity" table (in case (1) returns no row). In query in the post sometimes I just need to update an individual activity rather than the insertion! – Vahid133 Sep 07 '19 at 06:51
  • By *check* I meant not *explain me*. I meant *execute this SQL **manually** and check the result: is it that what you expected or not; and iif this is not what you expected, it means your SQL is wrong*. And since you have not not the the whole SQL, but small pieces, it is easier to understand what the problem is. – mentallurg Sep 07 '19 at 08:59
  • I have extended the answer after your comment. – mentallurg Sep 07 '19 at 09:20
  • Thanks for the extension. The point you mentioned regarding using _try/catch block_ in case of many users is really helpful. – Vahid133 Sep 07 '19 at 13:30
  • OK. If it is helpful, would you mark the answer as helpful? – mentallurg Sep 07 '19 at 13:31
  • 1
    I marked your answer as helpful. My reputation is under 14, so I just could mark the answer as helpful. – Vahid133 Sep 07 '19 at 17:42