usage of "runscript" feature

[Yuqiang Fan]

Hi,

I am confused on how to use the feature "runscript".

On documentation, it says "runscrip" feature has quirks_mode/variable_binary/from_1x options, and these options can import scripts made by 1.4.200 into recent version.

Thus, I tried to store scripts into "init_table.sql", and executed: runscript from 'classpath:mybatis/sql/init_table.sql' from_1x; On my init_table.sql, I used "value" as a column name without putting backticks around, and this usage is supported on version 1.4.200, and not working on my current version 2.0.206 (because now "value" is a reserved key word). Unfortunately, the program still threw me the exception that tells there is syntax error / bad grammar. Thus, I am not sure on how to use this "runscript" feature.

My goal is not changing any codes that I wrote for 1.4.200, and run the scripts on 2.0.206, and I know there are some points that work on older version but not work on current one (For example: I have to surroud backticks on value for new version while for older version I don't have to).

Please advise, thank you in advance.

Yuqiang

[Evgenij Ryazanov]

Hello.

FROM_1X flag of RUNSCRIPT command allows keywords to be used as identifiers only in the imported script. This option doesn't change behavior of database after data import.

If your application tries to misuse VALUE as unquoted identifier it needs to be fixed anyway, you need to quote it as "VALUE". If you cannot fix your application right now, you can add ;NON_KEYWORDS=VALUE to JDBC URL of H2.

Please also note that H2 2.0 is very different from H2 1.* and doesn't provide bug-for-bug compatibility.

[Yuqiang Fan]

Thank you fro replying, and it does work that your suggestion on using non_keywords settings, this helps because I don't have to surround backticks for keywords like "key" and "value".

While, I was not trying to look for a bug-for-bug compatibility, I have encountered some cases that worked on 1.4.200 either MySql or Oracle mode, but not worked on 2.0.206 under a specific mode. For example, under 1.4.200 either Oracle or Mysql , I can use

"create table users(
id int(10) not null primary key auto_increment,
name varchar(30 byte) default null,
password varchar(30) default null,
value varchar(30) default null,
date date(8) default null
);"

Under 2.0.206, 

for oracle mode int(10) needs to be int; auto_increment is no longer supported; date(8) needs to be date

for mysql mode int(10) still works; auto_increment is supported; date(8) needs to be date;  varchar(30 byte) needs to be varchar/varchar(30)

And these cases are not bugs, but compatibilities under different modes;

Is there any mean like "runscript" to get rid of these  incompatibilities? Thank you again.

Best regards,

Yuqiang