Berch Berch - 4 years ago 94
SQL Question

Unable to set data to textField from tableView - on Mouseclick and Up and Down Arrow keys (H2 Database)

I am a recent user of the h2 database, I need some assistance with the SQL syntax.

I'm able to retrieve data from the h2 dB and set it into JavaFX tableView, On performing the mouseclick or buttonpress action (Up & Down arrows) the intended behaviour is to display the current row of data from the tableView into the textfields, below is the code.

I'm getting the following exception:

Invalid value "1" for parameter "parameterIndex" [90008-193]


I'm certain this exception is due to SQL grammar unique to the H2 database, as the placeholder
(' "+slnoField.getText()+" ' ")
works fine in other databases. Please could you suggest the correct syntax or a solution. Many thanks.

@FXML
public void UpdateTable(){
data.clear();
try
{
conn = lrconn.getDatabaseConnection();
String sql = "SELECT * from APP_TABLE ;
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while(rs.next())
{

data.add(new TestPOJO(
rs.getString("SLNO"),
rs.getString("NAME")
));
Table.setItems(data);
}
pst.close();
rs.close();
}
catch(Exception e1)
{
e1.printStackTrace();
}

Table.setOnMouseClicked((MouseEvent me) ->{
try{
conn = lrconn.getDatabaseConnection();
TestPOJO user = (TestPOJO)Table.getSelectionModel().getSelectedItem();
String sql = "SELECT * from APP_TABLE where SLNO =' "+slnoField.getText()+" ' ";
pst = conn.prepareStatement(sql);
pst.setString(1, user.getSLNO());
rs = pst.executeQuery();

while(rs.next()){
slnoField.setText(rs.getString("SLNO"));
nameField.setText(rs.getString("NAME"));
}
rs.close();
pst.close();
}catch(SQLException ex){
Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
}

}
);

Table.setOnKeyReleased((KeyEvent e) ->{
if(e.getCode() == KeyCode.UP || e.getCode() == KeyCode.DOWN){
try{
TestPOJO user = (TestPOJO)Table.getSelectionModel().getSelectedItem();
String sql = "SELECT * from APP_TABLE where SLNO =' "+slnoField.getText()+" ' ";
pst = conn.prepareStatement(sql);
pst.setString(1, user.getSLNO());
rs = pst.executeQuery();
while(rs.next()){
slnoField.setText(rs.getString("SLNO"));
nameField.setText(rs.getString("NAME"));

catch(IOException | SQLException ex){
Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
);
}

Answer Source

You're not using the PreparedStatement correctly. Place ? at locations where you want to insert parameters in the query string. You don't seem to add one of those:

String sql = "SELECT * from APP_TABLE where SLNO = ?";
pst = conn.prepareStatement(sql);
pst.setString(1, user.getSLNO());
rs = pst.executeQuery();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download