Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I'm trying to get the primary auto incremented key from one table and store this in another using MySQL connector and JDBC. Although its giving me this error -

'statement.executeupdate() cannot issue statements that produce result sets.'

I think its something to do with the storing of the integer variable but I'm not too sure.


public void  insertIntoWorkoutLogs(String field_setNumber, String field_repNumber, String field_weightAmount) {
              try{
                      Class.forName("com.mysql.cj.jdbc.Driver");
                      Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","");
                      Statement statement =connection.createStatement();

                      String insert ="INSERT INTO `workout`.`workoutlogs`" + " (`SetNumber`, `RepNumber` , `WeightAmount`)"
                              + "VALUES('" +field_setNumber+"','"+field_repNumber+"','"+field_weightAmount+"')";
                      statement.executeUpdate(insert);

                      int workoutID = insertQueryGetId("SELECT workoutID FROM workout");

                      String insert2 ="INSERT INTO `workout`.`workoutlogs`" + " (`WorkoutID`)"
                              + "VALUES('" +workoutID+"')";
                      statement.executeUpdate(insert2);

                      connection.close();

              }catch(Exception e) {
                      System.out.println(e);}
      }

      public int insertQueryGetId(String query) throws ClassNotFoundException, SQLException {

              Class.forName("com.mysql.cj.jdbc.Driver");
              Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","");
              Statement statement =connection.createStatement();

              int workoutID=0;
              int result=-1;

              try {
                      workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

                      ResultSet rs = statement.getGeneratedKeys();
                      if (rs.next()){
                              result=rs.getInt(1);
                      }
                      rs.close();

                      statement.close();
              } catch (Exception e) {
                      e.printStackTrace();
              }
              return result;
      }


What I have tried:

I've tried using statement for this but I'm thinking it may have to be prepared statement for it to work but I'm not sure about this so would like some guidance.
Posted
Updated 17-Nov-22 1:39am

1 solution

Quote:
C#
insertQueryGetId("SELECT workoutID FROM workout")
...
workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
You're passing a SELECT query to the executeUpdate method. The error clearly tells you that this is not supported.

As per the documentation[^], you need to pass the actual INSERT statement to this method, specifying the RETURN_GENERATED_KEYS option, and then read the returned keys. There is an example of how to do this in the documentation.

But you have a much bigger problem: Your code is almost certainly vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900