Hsqldb single quote character

Question

How can I insert single quote character to Hsqldb table? Escape character doesn't work for the problem.

Answer 1

The question is old, but I found the answer by fredt but it led me astray because it doesn't actually do what the original poster requested which is insert a single quote. Instead it inserts escap'd which is not what I wanted to do.

This may have been the only way in the past (Fred would know better than me), but the easiest way in hsqldb to insert a single quote now is to just make a prepared statement in java (p_insert) and set the String:

p_insert.setString(1,"'"); p_insert.executeUpdate();

This will actually put a single quote in the database, at least in version 2.3.2. To retrieve it from the database, you will need to use double single quotes in your SELECT statement.

Answer 2

There are two ways.

1. Use two single quotes. For example INSERT INTO T VALUES 'escap''d'

2. Use a Unicode string, which can contain a Unicode escape. For example INSERT INTO T VALUES U&'escap\0027d'

Both examples insert the string escap'd into the table.