0

I'm using a PreparedStatement to Insert a row into my remote database. I'm getting an issue where after I finish preparing my PreparedStatement, and use its .executeUpdate() method, I do not get anything returned. No exception, value, or error of any sort. My database is working perfectly fine as I can still make queries to it.

I've logged statements in my code to see where the issue is:

as soon as I call int i = statement.executeUpdate(); I never get anything returned. The exception

catch (SQLException e) {
    System.out.println("Error: " + e);
    return null;
}

is also never called.

I have also tried using a timeout for the PreparedStatement of 10 seconds

statement.setQueryTimeout(10);

but unfortunately does not display any timeout errors. Any idea why this is happening?


String query = "INSERT INTO appointments(Title, Description, Location, Type, Start, End, " +
                "Created_By, Last_Updated_By, Customer_ID, User_ID, Contact_ID) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DriverManager.getConnection(Constants.CONNECTION_URL);

            statement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

            System.out.println("Called here");
            //Have tried setting a timeout as well, but it doesn't do anything
            statement.setQueryTimeout(10);

            statement.setString(1, appointment.getTitle());
            statement.setString(2, appointment.getDescription());
            statement.setString(3, appointment.getLocation());
            statement.setString(4, appointment.getType());
            statement.setString(5, appointment.getStartDateAndTime());
            statement.setString(6, appointment.getEndDateAndTime());
            statement.setString(7, LoginManager.getUsername());
            statement.setString(8, LoginManager.getUsername());
            statement.setInt(9, appointment.getCustomer().getCustomerId());
            statement.setInt(10, LoginManager.getUserId());
            statement.setInt(11, appointment.getContact().getContactId());
            
            //This Println is never called for some reason.
            System.out.println("Called here two.");

            int i = statement.executeUpdate();
         
            //Never finishes beyond this point
            //Exception is never called
            System.out.println("ExecuteUpdate results: " + i);

            if (i > 0) {
                //success
                resultSet = statement.getGeneratedKeys();
                System.out.println("Success: " + resultSet.getInt(1));
                if (resultSet.next()) {
                    appointment.setAppointmentId(resultSet.getInt(1));
                }
                return appointment;
            } else {
                return null;
            }
        } catch (SQLException e) {
            System.out.println("Error: " + e);
            return null;
        } finally {
            closeConnection(connection, statement, resultSet);
        }
    }
public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
DIRTY DAVE
  • 2,523
  • 2
  • 20
  • 83
  • I'm just going to guess that your connection URL is wrong. Do you know if you are successfully making a connection to the database? Or are you certain it is the insert statement? – Sam Orozco Aug 26 '21 at 23:10
  • The connect URL is working fine. If just tried making it invalid by adding a random letter and it threw an error. ```com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.``` Yes the insert statement it's an insert statement. It also works fine when I hardcode it. it might be something to do with the prepared statement? – DIRTY DAVE Aug 26 '21 at 23:13
  • So you're getting "Called Here" println? – Sam Orozco Aug 26 '21 at 23:15
  • yup that is being called. ```System.out.println("ExecuteUpdate results: " + i)``` is not. – DIRTY DAVE Aug 26 '21 at 23:16
  • Can you put a println just above `int i = statement.executeUpdate();` and make sure that is being called? or if you can debug make sure that the code execution is actually being hung up on that line. – Sam Orozco Aug 26 '21 at 23:18
  • Yeah it's not being called before the ```.executeUpdate()```. So it's something wrong with the ```statement.set```? – DIRTY DAVE Aug 26 '21 at 23:22
  • Yeah I would imagine one of your statement.set is calling a remote service or something like that. But I would dig in there. – Sam Orozco Aug 26 '21 at 23:23
  • it's not calling a remote service. The login manager class is just returning a static string ```private static String username = "test"; public static String getUsername() { return username; }``` It works fine when I hardcode the queries values. So is it because of a null ```statement.set``` value? But if it is, wouldn't that throw an error – DIRTY DAVE Aug 26 '21 at 23:26
  • First you need to debug and figure out which `set` statement is actually causing the issue and go from there. I agree, I would think setting a null value would throw an exception or be handled fine. – Sam Orozco Aug 26 '21 at 23:27
  • The test values I've used are ```Appointment{appointmentId=0, title='', description='', location='', type='', startDateAndTime='2021-08-26 01:00:00', endDateAndTime='2021-08-27 01:04:00', lastUpdatedBy='test', customer=null, contact=Contact{contactId=2, contactName='Daniel Garcia', contactEmail='dgarcia@company.com'}, user=null}``` but none of them throw an error. If I use ```null``` for all it throws an error. I don't know why this happens :/ – DIRTY DAVE Aug 26 '21 at 23:34
  • Ok but can you either add print statements or debug to determine which line is actually causing application to stop? – Sam Orozco Aug 26 '21 at 23:36
  • 2
    `customer=null` so `appointment.getCustomer().getCustomerId()` is probably throwing a `NullPointerException` – Tim Moore Aug 26 '21 at 23:39
  • 1
    @TimMoore that's probably right and you're only catching a SQlException so a NPE will just be swallowed and hit the finally block. – Sam Orozco Aug 26 '21 at 23:43
  • Tim is correct! https://i.imgur.com/w6UPavW.png logged everything..the ```.getCustomerId()``` was throwing a NPE. ```will just be swallowed and hit the finally block. ``` didn't know that would happen – DIRTY DAVE Aug 26 '21 at 23:46
  • It will be thrown to the calling method and up the stack until something catches it or the thread is exited. Since we haven’t seen the calling code, we can’t know, but if nothing is being logged then there must be a caller that is catching exceptions and ignoring them. This is bad practice for this very reason: it makes debugging much more difficult. – Tim Moore Aug 26 '21 at 23:47
  • ```This is bad practice for this very reason``` using a ```SQLException``` instead of ```Exception```? or did you mean the overall quality of the code. I'm still quite new so I don't know what's the best practices for making my code above better/clean :d – DIRTY DAVE Aug 26 '21 at 23:56
  • 1
    @DIRTYDAVE I mean catching an exception and ignoring it is a bad practice, but I don't think it's happening in the code above. In the code above, it catches `SQLException` and logs `"Error: " + e`. This is better than nothing, as the error is at least reported. However, it doesn't print the stack trace, which is useful debugging information. What I'm really talking about is that some caller of this code must be catching the `NullPointerException` and does nothing with it. Otherwise, I would have expected you to see the `NullPointerException` stack trace printed on the console. – Tim Moore Aug 27 '21 at 01:04

0 Answers0