H2 - (Quite) long INSERT failing with error 42000

Question

H2 in-memory - INSERT - Error 42000

Tried versions 1.4.196, 1.4.197, 1.4.199.

I also tried to execute INSERT on H2 server (local) : also failed

The line giving the error: (sorry but for security reasons I cannot produce more) :

    INSERT INTO tb_ae (server,  record_id,  ...) 
    SELECT ... 
    FROM   vw_ofch_prepal_delta,   vw_ab_bie  
    WHERE  bie_tp IN ('S[*]') AND is_most_recent = 1;

The statement is 4.004 characters long.

The error is pointed by H2 as [*] (this is not part of the statement).

Cheney

Answer 1

H2 does not set the error mark [*] inside character string literals. It means that most likely you have a misplaced ' character somewhere earlier. It can be an unclosed string literal, unescaped ' character in some string literal (if a string literal contains the ' character it should be written with two such characters as ''), or a stray ' character.

Kumler

Many thanks for your quick answer which was definitely helpful :-) Sorry for the mess but I was reading SQL statements from an Excel file and coming from a "double quotes" version of those statements that worked well in github.com/vorburger/MariaDB4j , but with memory leak under Eclipse, which is the reason why I switched to H2. Then I had to put single quotes everywhere and Excel removes a single quote as the first character of a text cell, what happened to be the case for me. Thanks to you I reviewed my SQL and found this out. Many thanks ! :-) – Cheney

Answer 2

According to H2 error code documentation, there is syntax error in your query.

/**
 * The error with code <code>42000</code> is thrown when
 * trying to execute an invalid SQL statement.
 * Example:
 * <pre>
 * CREATE ALIAS REMAINDER FOR "IEEEremainder";
 * </pre>
 */
public static final int SYNTAX_ERROR_1 = 42000;

As your query is too long and not fully copied. I advised to review query again or may be review from someone else. You should create small alias for table names and use alias with every column name to avoid syntax error.

Ammon

Answer 3

This can also happen if you mistakenly wrote an insert like

insert into `my_table` (`id`, `name`)
    values (1, 'ENTITY_1'),
    values (2, 'ENTITY_2');

Careless copy-and-paste of "values" twice might not be obvious to the eye.

Acclimate