Cannot determine why this SQL statement won't execute (Derby)

Question

My table is set up like this and seems to get created properly. (no exceptions are thrown)

    String sqlString = String.format(
            "CREATE TABLE %s(%s BIGINT, 
            %s VARCHAR(%d), 
            %s VARCHAR(%d), 
            %s VARCHAR(%d), 
            %s INT, 
            %s INT, 
            %s BIGINT, 
            PRIMARY KEY (%s))",
                //
                TABLE_NAME, //
                Column.ID.name, //
                Column.MAKE.name, Column.MAKE.length, //
                Column.MODEL.name, Column.MODEL.length, //
                Column.SERIAL_NUMBER.name, Column.SERIAL_NUMBER.length, //
                Column.YEAR.name, //
                Column.MILEAGE.name, //
                Column.CUSTOMER_ID.name, //
                Column.ID.name);

I am trying to insert the following:

String sqlString = String.format(//
            "INSERT INTO %s values(?, ?, ?, ?, ?, ?, ?)", TABLE_NAME);
    boolean result = execute(sqlString, //
            motorcycle.getId(), // long
            motorcycle.getMake(), // String
            motorcycle.getModel(), // String
            motorcycle.getSerialNumber(), // String
            motorcycle.getYear(), // int
            motorcycle.getMileage(), // int
            motorcycle.getCustomerId()); //long

execute(sqlString, args[]) returns false, but no exceptions are thrown. derby.log has no information about what's going wrong either.

Am I using the wrong SQL data types?

Edit:

The execute(sqlString, args[]) method is in the Dao super-class and works fine for the other sub-classes.

The code is as follows:

 protected boolean execute(String preparedStatementString, Object... args) throws SQLException {
        LOG.debug(preparedStatementString);
        boolean result = false;
        PreparedStatement statement = null;
        try {
            Connection connection = Database.getConnection();
            statement = connection.prepareStatement(preparedStatementString);
            int i = 1;
            for (Object object : args) {
                statement.setObject(i, object);
                i++;
            }

            statement.executeUpdate();
            result = true;
        } finally {
            close(statement);
        }

        return result;
    }

Answer 1

From the PreparedStatement javadoc for execute(String sql) :

Returns:

true if the first result is a ResultSet object; false if it is an update count or there are no results

Most likely your table creation succeded, you're just getting false because there's no result to read.

As a_horse_with_no_name mentions in the comments, using PreparedStatement.executeUpdate() would be more appropriate for this kind of statement.

Answer 2

I encountered a similar problem but mine was that the error was that the table doesn't exist and my code was meant to create the table if it doesn't exist so after some research I found out that after the create=true I needed to upgrade the database i.e. upgrade=true.

This is my code:

import java.sql.*;
import javax.swing.JOptionPane;

public final class DatabaseHandler {
private static DatabaseHandler handler;

private static final String DB_URL = 
"jdbc:derby:BookData;create=true;upgrade=true"; 
//connection to database
private static Connection conn = null;
//for creeat insert and oda mySql statements
private static Statement stm = null;

public DatabaseHandler() {
    createconnection();
    setupbookTable();
}



void createconnection(){
  try {

  Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
  System.out.println("Dis is a database connection error "+e);
      conn = DriverManager.getConnection(DB_URL);
  } catch (Exception e) {
  System.out.println("Dis is a database connection error "+e);
  }
 }

 void setupbookTable()
 {
  String TABLE_NAME = "BOOK";
  try {
      //use d object of connection to create a statement
      stm = conn.createStatement();
      //checks if table exist evytime d app is run
      DatabaseMetaData dmb = conn.getMetaData();
      ResultSet tables = dmb.getTables(null, null, TABLE_NAME.toUpperCase(), 
     null);
      if(tables.next())
      {
          System.out.println("Table" +TABLE_NAME+" already Exists");
      }
      else
      {
          stm.execute("CREATE TABLE " + TABLE_NAME + "("
                  +" id varchar(200) primary key,\n"
                  +" title varchar(200),\n"
                  +" writer varchar(200),\n"
                  +" printer varchar(200),\n"
                  +" isAvail boolean default true"
                  + ")");
      }

  } catch (SQLException e) {
      System.err.println(e.getMessage() +"---Setup Database");
  }
  finally
  {  
  }
}
public ResultSet execQuery(String query)
  {
      ResultSet result;
      try {
          stm = conn.createStatement();
          result = stm.executeQuery(query);
      }catch(SQLException ex)
      {
          System.out.println("Error in Database:execQuery" +ex.getLocalizedMessage());
          return null;
      }finally
      {
      }
      return result;
  }
 public boolean execAction(String qu)
{
   try {
       stm = conn.createStatement();
       stm.execute(qu);
       return true;
   } catch (SQLException e) {
       JOptionPane.showMessageDialog(null, "Error occured " +e.getMessage(), 
  " Error", JOptionPane.ERROR_MESSAGE);
       System.out.println("Exception at execQuery:dataHandler" 
  +e.getLocalizedMessage());
       return false;
   }
   finally
   {

   }
 }
}

I also created an n exec method for the Actions and Query and this is how you used it:

  public class FXMLbookController implements Initializable {

  private Label label;
  @FXML
  private JFXTextField title;
  @FXML
  private JFXTextField id;
  @FXML
  private JFXTextField writer;
  @FXML
  private JFXTextField printer;
  @FXML
   private JFXButton save;
  @FXML
  private JFXButton cancel;
  DatabaseHandler database;

  @Override
  public void initialize(URL url, ResourceBundle rb) {
     database = new DatabaseHandler();
}    

@FXML
private void addBook(ActionEvent event) {
    String bookId = id.getText();
    String bookTitle = title.getText();
    String bookPublisher = printer.getText();
    String bookAuthor = writer.getText();
    if(bookId.isEmpty() || bookTitle.isEmpty() || bookPublisher.isEmpty() || 
    bookAuthor.isEmpty())
    {
        JOptionPane.showMessageDialog(null, "No field must be left empty", 
  "Error Occured", JOptionPane.ERROR_MESSAGE);
        return;
     }
    else
    {
        /*+" id varchar(200) primary key,\n"
                  +" title varchar(200),\n"
                  +" writer varchar(200),\n"
                  +" printer varchar(200),\n"
                  +" intcode varchar(100),\n"
                  +" isAvail boolean default true"*/
        String quIn = "INSERT INTO BOOK VALUES("+
                "'" + bookId+"'," +
                "'" + bookTitle+"',"+
                "'" +bookAuthor+ "',"+
                "'" + bookPublisher+"',"+
                "'" +"true"+"'"+
                ")";
        System.err.println(quIn);
        if(database.execAction(quIn))
        {
            JOptionPane.showMessageDialog(null, "Success", "Success", 
    JOptionPane.INFORMATION_MESSAGE);
        }
        else
        {
            JOptionPane.showMessageDialog(null, "Error: ", " Not inserted", 
  JOptionPane.ERROR_MESSAGE);
        };
    }
}

@FXML
private void cancel(ActionEvent event) {
}

}