using hsqldb and preparedStatement gives me this Error:
java.sql.SQLSyntaxErrorException: parameter marker not allowed
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
I tried:
String sql = "INSERT INTO Emergency (Id, status, typed, typeb, floor,
locationX, locationY) Values(CAST(? AS INT), CAST(? AS INT), CAST(? AS
VARCHAR(50)), CAST(? AS VARCHAR(50)), CAST(? AS INTEGER), CAST(? AS
INTEGER), CAST(? AS INTEGER))";
And:
String sql = "INSERT INTO Emergency (Id, status, typed, typeb, floor,
locationX, locationY) Values(?,?,?,?,?,?,?)";
same error.
EDIT: more code
try {
con = DriverManager.getConnection(
"jdbc:hsqldb:file:hsqldb; shutdown=true", "root", "");
Statement stmt = con.createStatement();
// Alle Kunden ausgeben
//double help = ((number * pow(2.0, j)) - 1);
String sql = "INSERT INTO Emergency (Id, status, typed, typeb, floor, locationX, locationY) Values(CAST(? AS INT), CAST(? AS INT), CAST(? AS VARCHAR(50)), CAST(? AS VARCHAR(50)), CAST(? AS INTEGER), CAST(? AS INTEGER), CAST(? AS INTEGER))";
//Id INTEGER, status INTEGER, typeD VARCHAR(50) , typeB VARCHAR(50), floor INTEGER, locationX INTEGER, locationY INTEGER
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, Counter.emergencyID);
pstmt.setInt(2, emergency.status);
pstmt.setString(3, emergency.typeD);
pstmt.setString(4, emergency.typeB);
pstmt.setInt(5, emergency.floorID);
pstmt.setInt(6, emergency.locationXY[0]);
pstmt.setInt(7, emergency.locationXY[1]);
Statement st = null;
st = con.createStatement(); // statements
int i = st.executeUpdate(sql); // run the query
Counter.emergencyID++;
if (i == -1) {
System.out.println("db error : " + sql);
}
st.close();
System.out.println("Eintrag in DB");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
I am adding all the values and then execute it.
can someone explain me what is goning wrong here? haven't found anything usefull search.
You don't need to cast your types, you just need to know how PreparedStatement work.
You are using Statement and Prepared Statement, but you don't need to Statement, you need just to execute Prepared Statement, so instead use this :
String sql = "INSERT INTO Emergency (Id, status, typed, typeb, floor,
locationX, locationY) Values(?,?,?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, Counter.emergencyID);
pstmt.setInt(2, emergency.status);
pstmt.setString(3, emergency.typeD);
pstmt.setString(4, emergency.typeB);
pstmt.setInt(5, emergency.floorID);
pstmt.setInt(6, emergency.locationXY[0]);
pstmt.setInt(7, emergency.locationXY[1]);
int i = pstmt.executeUpdate();//execute pstmt no need to st