Oops, my mistake. Three quotes is right. This works for me:
drop table if exists foobar;
create table foobar (name varchar(20));
insert into foobar (name) values ('''steve''');
Furthermore, if this is being generated by Java code, then you shouldn't be
doing it with string concatenation.
You should be doing this:
Not only does this let the JDBC driver take care of the work of escaping
the single quotes at the beginning and end of the string, it is also safer
against SQL Injection attacks.
// assuming url contains the correct url for your H2 database...
final Connection connection = DriverManager.getConnection(url);
PreparedStatement statement =
connection.prepareStatement("insert into foobar (name) values (?)");
statement.setString(1, "'Steve'");
statement.executeUpdate();
On Friday, 27 July 2012 12:03:20 UTC+2, Steve McLeod wrote:
>
> I think you need to use just two single quotes at the beginning and end.
>
> Insert into PAYEES (NAME) values(''Starbucks'')
>
> On Thursday, 26 July 2012 05:57:23 UTC+2, sandstones wrote:
>>
>> I am dealing with some data that is a bit dirty, but I would still expect
>> this to work. I am trying to insert a text string that is 'Starbucks' WITH
>> a single quote at the start and at the end.
>>
>> The SQL statement I generate is
>> Insert into PAYEES (NAME) values('''Starbucks''')
>> (its not that obvious in google's font, but there are 3 single quotes
>> before and after Starbucks). According to Oracle, that is the way they do
>> it.
>>
>> In H2 I getand error of:
>> SQLState: 42001
>> Error Code: 42001
>> Message: Syntax error in SQL statement "INSERT INTO PAYEES (NAME)
>> VALUES(NULL'StarbucksNULL'[*]) "; expected "[, ::, *, /, %, +, -, ||, ~,
>> !~, NOT, LIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
>> Insert into PAYEES (NAME) values(NULL'StarbucksNULL') [42001-168]
>>
>> Is there a way to do this, or do I need to start checking all the fields
>> to clean up?
>> And who knows, someone might want the quote.
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/r4zIASxkZScJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
