ace7 ace7 - 10 days ago 6
SQL Question

UCAExc 3.0.7 unexpected token Errors

I'm working on a project that wants me to integrate a Microsoft Access database into a Java program. I have successfully connected to the database however my SQL statements are not updating the DB. I have defined variables that read user input and use that information as the conditional for the WHERE statement. I have taken parts of the first query out and have gotten positive results but the whole statement refuses to cooperate. What do I need to change about the first query to make it run?

result = statement.executeQuery("SELECT slipNumber FROM Slip WHERE (slipOpen = -1 & slipLength >= " + boatLengthdub + "& slipDepth >= " + boatDepthdub + ")"+ "LIMIT 1" );

statement.executeQuery("INSERT INTO Slip (slipOpen, boatID) VALUES (0," + boatIDdub + ")");

System.out.println("Have a" + result);

Answer

You appear to be trying to use the ampersand character (&) where you should be using the SQL keyword AND. You also should be using PreparedStatement objects to perform parameterized queries, e.g.,

String sql = 
        "SELECT slipNumber FROM Slip " + 
        "WHERE slipOpen = -1 AND slipLength >= ? AND slipDepth >= ? " +
        "LIMIT 1";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDouble(1, boatLengthdub);  // assuming that they are Double values
ps.setDouble(2, boatDepthdub);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
    System.out.println(rs.getInt("slipNumber"));
} else {
    System.out.println("Not found.");
}