Runscript utility throws an exception when encountering a single quote

[Harpal Grover]

Hi there,

I am running an sql script loaded into a Reader object through the
RunScript utility. When it tries to execute the following statement
with a single quote in someone's name:

INSERT INTO USER(1,'RYAN O''GRADY');

where RYAN O'GRADY is escaped by adding a second single quote. I never
run into a problem when using a prepared statement with jdbc code.
However when running the query through the runscript utility i get an
SQLException. I am currently using the H2 2008-01-18 build. Has this
issue been addressed in new builds?

Thanks

Harpal Grover

[Thotheolh]

Version 1.0.73

I tested yours in my database since I have a user table in one of my
personal databases.

Script: test.sql

insert into user('RYAN O''GRADY','ABC','Manager');

My java application using JDBC connection to my H2 database throws:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement INSERT
INTO USER('RYAN O''GRADY'[*],'ABC','Manager') ; expected identifier;
SQL statement:
insert into user('RYAN O''GRADY','ABC','Manager') [42001-73]
at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSyntaxError(Message.java:139)
at org.h2.command.Parser.readColumnIdentifier(Parser.java:
2383)
at org.h2.command.Parser.parseColumnList(Parser.java:747)
at org.h2.command.Parser.parseInsert(Parser.java:824)
at org.h2.command.Parser.parsePrepared(Parser.java:366)
at org.h2.command.Parser.parse(Parser.java:288)
at org.h2.command.Parser.parse(Parser.java:260)
at org.h2.command.Parser.prepareCommand(Parser.java:232)
at org.h2.engine.Session.prepareLocal(Session.java:284)
at org.h2.engine.Session.prepareCommand(Session.java:245)
at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1015)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.tools.RunScript.process(RunScript.java:203)
at org.h2.tools.RunScript.process(RunScript.java:179)
at org.h2.tools.RunScript.process(RunScript.java:254)
at org.h2.tools.RunScript.execute(RunScript.java:243)

When I tested on the web console:

runscript from 'C:\Documents and Settings\Owner\Desktop\test.sql'

runscript from 'C:\Documents and Settings\Owner\Desktop\test.sql';
Syntax error in SQL statement INSERT INTO USER('RYAN
O''GRADY'[*],'ABC','Manager') ; expected identifier; SQL statement:
insert into user('RYAN O''GRADY','ABC','Manager') [42001-73]
42001/42001 (Help)

[Thomas Mueller]

Hi,

> insert into user('RYAN O''GRADY','ABC','Manager');

This is not the correct syntax. Try:
insert into user(name, x, y) values('RYAN O''GRADY','ABC','Manager');
or
insert into user values('RYAN O''GRADY','ABC','Manager');
See also: http://www.h2database.com/html/grammar.html#insert

Regards,
Thomas

[Harpal Grover]

Hi,

Sorry, the above statement was not written properly in my original
post. The actual statement indeed which is causing me grief is as you
have written it Thomas:

insert into user(name, x, y) values('RYAN O''GRADY','ABC','Manager');

When I use a PreparedStatement object and fill in the prepared
variables with values, the statement executes above with no problem.

String sql = "INSERT INTO USER(NAME,X,Y) VALUES (?,?,?);
PreparedStatement ps = c.prepareStatment(sql);

ps.setString(1,'RYAN O''GRADY');
etc...

But for some weird reason i cannot get it to execute when running from
the RunScript class.

[Harpal Grover]

Hello again,

It looks like my issue with the RunScript class has dissappeared. It
seems to be something else which is causing this issue, but for some
strange reason the engine is bubbling up the Ryan O'Grady statement as
the issue.

I will look into this further.

Thanks
Harpal