4

I'm trying to create a view on H2SQL, but i cannot find the correct syntax.

I'm using:

CREATE VIEW dbo.Log
AS
SELECT * FROM dbo.MyTable

And i receive the error:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE VIEW DBO.LOG "; expected "COMMENT, (, AS";

What is the right syntax?

renanleandrof
  • 6,699
  • 9
  • 45
  • 67
  • try to add semicolon at last after dbo.MyTable; – HaveNoDisplayName Dec 05 '14 at 13:39
  • can you add full view code also, because your error message says different what you have in view – HaveNoDisplayName Dec 05 '14 at 13:42
  • THe code is exactly this one. I have a bigger select, but i reduced it to this simple line and it still doesn't work. – renanleandrof Dec 05 '14 at 13:43
  • 1
    Based on the error it seems like it's trying to parse `CREATE VIEW dbo.Log ` as an independent statement. Not sure what would cause this. Seeing the actual code might help. – SamYonnou Dec 05 '14 at 13:43
  • When i reduce the code in one line it works fine! Is it a bug? – renanleandrof Dec 05 '14 at 13:44
  • Try putting that in one line as `CREATE VIEW Log AS SELECT * FROM MyTable` without the schema? – chridam Dec 05 '14 at 13:45
  • 1
    @Renalf Might be a bug. Are you using the H2 Console to execute the queries? I've never used it myself but from the screenshots I see on their website all of the statements seem to be on one line. I would suggest using something like DBVisualizer instead if that is the case. – SamYonnou Dec 05 '14 at 13:50
  • It's definetely a bug. In the console it works fine, but im running it in a RUNSCRIPT at the connection, and it doesn't support multiline view... it has to be all in one line! =/ – renanleandrof Dec 08 '14 at 12:21
  • Similar issue has been resolved in this question: https://stackoverflow.com/questions/32953037/spring-mvc-hibernate-encoding-multi-line-import-sql – Milan Batica May 16 '19 at 08:27

1 Answers1

8

It's a bug on this version of h2 (1.4.182).

When running a CREATE VIEW from a RUNSCRIPT command, it doesn't handle well the line-breaks (\n) on the file. I resolved adding a comment (--) before each line break.

renanleandrof
  • 6,699
  • 9
  • 45
  • 67
  • Adding a comment `--` didn't solve the problem for me. So I had to re-format a statement into one line and it worked. Thank you for the tip! Can you also include into your answer a link to this bug in H2 issue tracker? I can't find it anywhere. – naXa stands with Ukraine May 14 '18 at 13:14