Hi,
> The SQL statement I generate is
> Insert into PAYEES (NAME) values('''Starbucks''')
I'm quite sure that's not true. I believe the SQL statement you
generate is the one that is listed in the error message:
Insert into PAYEES (NAME) values(NULL'StarbucksNULL')
And obviously, this statement is illegal.
Regards,
Thomas
On Fri, Jul 27, 2012 at 12:10 PM, Steve McLeod <[email protected]> wrote:
> 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.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
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.
