I try to insert a String into a hsqldb an it gives me this error:
> java.sql.SQLSyntaxErrorException: user lacks privilege or object not
found: S
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)
the column is set to VARCHAR(50) and the sqlstring is build like this:
String sql = "INSERT INTO Emergency Values(" + Counter.emergencyID + ","+
emergency.status +"," + "\""+ emergency.typeD +"\"" + "," + "\""+
emergency.typeB +"\"" + ","+ emergency.floorID + ")";
this ist how i execute the query:
Statement st = null;
st = con.createStatement(); // statements
int i = st.executeUpdate(sql); // run the query
PS: I know i am open to a sqlInjection like this.
EDIT: values are
sql = "INSERT INTO Emergency Values(0,1,"S","IB",1)"
If i change the string to ;
String sql = "INSERT INTO Emergency Values(" + Counter.emergencyID + ","+
emergency.status +","+ emergency.typeD +","+ emergency.typeB +","+
emergency.floorID +")";
the same error occures
Use a PreparedStatement and you won't have problems:
String sql =
"INSERT INTO Emergency (emergency_id, status, type_d, type_b, floor_id) " +
" Values (?, ?, ?, ?, ?)";
Note that I explicitly listed the column names in the insert statement. Not doing that is considered bad coding style.
I had to guess those names as you didn't show us the definition of your table. You have to replace with the correct column names of your table.
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, Counter.emergencyID);
pstmt.setInt(2, emergency.status);
pstmt.setString(3, emergency.typeD);
pstmt.setInt(4, emergency.typeB);
pstmt.setInt(5, emergency.floorID);
int i = pstmt.executeUpdate(sql); // run the query
The root cause of your problem was the incorrect usage of double quotes: ". String constants have to be put in single quotes in SQL. 'foobar' is a string value. Double quotes are used for identifiers "foobar" is e.g. a column name.
Unrelated, but: the use of Counter.emergencyID lets me think that your are generating (or trying to) unique IDs in your application. Don't do that. Use a sequence or identity column in the database. Do it correctly from the beginning. For a single user application this might not make a difference, but there is no way you can implement that correctly and scalable in an application that is used by multiple users at the same time, with concurrent transactions inserting into the same table.
i found the error in @a_horse_with_no_name 's code
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, Counter.emergencyID);
pstmt.setInt(2, emergency.status);
pstmt.setString(3, emergency.typeD);
pstmt.setInt(4, emergency.typeB);
pstmt.setInt(5, emergency.floorID);
int i = pstmt.executeUpdate(sql); // run the query
note the last line, it should be
int i = pstmt.executeUpdate(); // run the query
please refer to HSQLDB cryptic exception message: "feature not supported"
I know the question is old, but I ran into the same problem and found my a solution without using PreparedStatements.
INSERT INTO TypeA (id) VALUES ("Hello");
failed (user lacks privilege or object not found: Hello ), but
INSERT INTO TYPEA (id) VALUES ('Hello');
worked. So it seems like double quotes are not accepted (see also http://www.hsqldb.org/doc/1.8/guide/ch09.html#expression-section )