CODEWITHSUNDEEP
javahsqldb
F. Aker
F. Aker

Reputation: 27

Insert String in HSQLDB

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

Upvotes: 1

Views: 2879

Answers (3)

taranion
taranion

Reputation: 671

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 )

Upvotes: 1

Eric Yu
Eric Yu

Reputation: 27

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"

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 4

Related Questions