Can anyone see the mistake in the SQL Command i am using to update a record in a local DB using JDBC?

Question

I am trying to update a DB Table at my college using JDBC and JAVAFx. I have tried everything to get the SQL Update command to work.

I have a table where the player_id is the foreign key from the table - Player -, and is a child table to its parent namely - Player and Game Information -.

I have set up the schema correctly for both tables as per instructions and am trying to update a record in the 'Player' table based off player_id since it is the foreign key for the 'Player'.

I understand this is very rudimentary as a command and have researched 'preparedstatements' in Java, but i was asked to do it this way. The codebelow basically collects a player_id via an input dialogue for which the record is being updated and all the columns are nullable except for the player_id. The code i have is as follows :

dbConnect();

JFrame frame;
frame = new JFrame();

int searchP_id = Integer.parseInt(JOptionPane.showInputDialog(frame, "Please enter the ID of the Player you would like to update"));

String sql = "UPDATE player SET" +" first_name= '" + first_name + "'," + " last_name= '" + last_name + "'," + " address= '" + address + "'," + " postal_code= '" + postal_code + "'," + " province= '" + province + "'," + " phone_number= '" + phone_number + "'" + " WHERE player_id =" + searchP_id + ";" ;

statement.executeUpdate(sql);

if (statement != null) {
    //Close Statement
    statement.close();
}

I tried verifying that i have the command right as per sql statements and when i run this within sqlDeveloper - it updates the record.

I have a controller class that basically runs these commands as part of a method that runs during an actionevent onUpdatePlayerButtonClick.

I tried checking he syntax and cannot see the problem, but the only error i get whenever running this line is - << Caused by: Error : 933, Position : 128, Sql = UPDATE player SET first_name= '', last_name= '', address= '', postal_code= '', province= '', phone_number= '' WHERE player_id =1;, OriginalSql = UPDATE player SET first_name= '', last_name= '', address= '', postal_code= '', province= '', phone_number= '' WHERE player_id =1;, Error Msg = ORA-00933: SQL command not properly ended >>.

I tried reasearching the error code online and it said that there is a clause added that shouldnot be there and might be casuing the problem -

this is a simple UPDATE query so where could it be going wrong ? The DB is getting connected to fine and the Insert command i have within my utility class works too.

Thank you for any help provided !

Answer 1

Your query is not readable and you should use parameters in your JDBC statement.

dbConnect();

JFrame frame;
frame = new JFrame();

int searchP_id = Integer.parseInt(JOptionPane.showInputDialog(frame, "Please enter the ID of the Player you would like to update"));

String sql = "UPDATE player" + 
        " SET first_name = ?, last_name = ?, address = ?," + 
        " postal_code = ?, province = ?, phone_number = ?" +
        " WHERE player_id = ?";

PreparedStatement preparedStatement =
        connection.prepareStatement(sql);

preparedStatement.setString(1, "the_first_name");
preparedStatement.setString(2, "the_last_name");
preparedStatement.setString(3, "the_address");
preparedStatement.setString(4, "the_postal_code");
preparedStatement.setString(5, "the_province");
preparedStatement.setString(6, "the_phone_number");
preparedStatement.setLong(7, "the_player_id");

preparedStatement.executeUpdate(sql);

if (preparedStatement != null) {
    //Close Statement
    preparedStatement.close();
}

Take look here for more details on parameters with JDBC.