Insert String in HSQLDB

Question

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

So, you don't suppose that the contents of the fields of `emergency` might be of any pertinence at all to the question at hand, do you? — Mike Nakis, Apr 28 '17 at 15:35
And you don't suppose that there would be any point at all in showing us the actual contents of the `sql` string when the error occurs, right? — Mike Nakis, Apr 28 '17 at 15:37

score 4 · Accepted Answer · 2017-04-28T16:11:50.487

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.

now the program already eroors at emergency_id: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: EMERGENCY_ID in statement [INSERT INTO Emergency (emergency_id, status, type_d, type_b, floor_id) Values(?, ?, ?, ?, ?)] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.(Unknown Source) — F. Aker, Apr 28 '17 at 16:06
@F.Aker: I had to guess the column names because you did not show us the table definition. Of course you need to replace them with the correct names of your table. Please don't just blindly copy code you get from the internet. Read it, try to understand it and do the necessary adjustments to your situation. — , Apr 28 '17 at 16:09
@F.Aker: the code in my answer will **not** generated that error — , Apr 28 '17 at 17:15

score 1 · Answer 2 · answered Sep 16 '18 at 11:59

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"

score 1 · Answer 3 · answered Feb 12 '19 at 11:11

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 )

Insert String in HSQLDB

3 Answers3