Syntax error in SQL statement for DB2

[Alexey Kalugin]

Hi everyone.

We're using H2 database for unit testing on our project. The production database is IBM Db2. However when we run DB2-specific SQL queries against H2 we get SQL syntax error: it looks like that H2 doesn't recognize "WITH UR" (with uncommitted read) DB2-specific SQL construction. Can you please let us know if this issue is known for you and whether it has already been fixed?

Best regards, Alexey.

[Noel Grandin]

This has been fixed but not yet released.

You could try testing with the nightly automated build.

[Alexey Kalugin]

Thanks for the response Noel. Can you please let me know how can I "try testing with the nightly automated build"?
Thank you.

[Noel Grandin]

The automated build links are here:

http://h2database.com/html/build.html#automated

[Alexey Kalugin]

I took the latest automated build having followed the link (http://www.h2database.com/automated/h2-latest.jar) however that didn't help me as the issue still persists. Can you please let me knew which build will the issue be resolved in?

On Mon, Dec 23, 2013 at 2:21 PM, Noel Grandin <noelg...@gmail.com> wrote:

The automated build links are here:

http://h2database.com/html/build.html#automated

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/I9R1bBS2WRw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[Thomas Mueller]

Hi,

Did you use the IBM DB2 mode? To do that, append ";MODE=DB2" to the database URL. If it still doesn't work, could you post a reproducible test case?

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Alexey Kalugin]

Thomas, here are the files needed for the reproduction of the problem. If you delete "with ur" grammar statement at the method DriverDaoService.getAllDrivers() the test DriverTestH2.testGetAllfDrivers() will be completed successfully.

Regards, Alexey

[Alexey Kalugin]

Hello H2 group.

Can you please let me know if there's any update on the issue raised by me? Or perhaps you're in need of some additional information from my side regarding the problem?
Thank you.

[litailang]

Hi,

This is a bug. 

My test case was not enough. I wrote a patch for this issue.

litailang

2013/12/27 Alexey Kalugin <kalugin...@gmail.com>

[Noel Grandin]

Thank you, this patch has been committed.

[Alexey Kalugin]

Hi H2 Team.

I've taken the latest automated build however the issue still persists. Can you please react on that fact?
Thank you.

[Noel Grandin]

On 2014-01-16 10:43, Alexey Kalugin wrote:
>
> I've taken the latest automated build however the issue still persists. Can you please react on that fact?
> Thank you.
>

You need to enable DB2 mode for this syntax to work.
i.e. your database url needs to look something like:

jdbc:h2:C:/temp/noel1;mode=db2

[Alexey Kalugin]

That's my database url:
<property name="hibernate.connection.url">jdbc:h2:mem:test;MODE=DB2;DB_CLOSE_DELAY=-1</property>

However h2 still doesn't recognise "with UR" syntax feature of DB2.
Thanks.

[Noel Grandin]

On 2014-01-16 12:20, Alexey Kalugin wrote:
> That's my database url:
> <property name="hibernate.connection.url">jdbc:h2:mem:test;MODE=DB2;DB_CLOSE_DELAY=-1</property>
>
> However h2 still doesn't recognise "with UR" syntax feature of DB2.
> Thanks.
>

I used your test case and it worked for me. So I'm sorry, but there isn't anything more I can do.

[Alexey Kalugin]

What's the jar file you've run my test case against?
Thank you.

[Noel Grandin]

On 2014-01-16 13:14, Alexey Kalugin wrote:
> What's the jar file you've run my test case against?
> Thank you.

I'm running against latest SVN.

[Alexey Kalugin]

Is it possible to obtain the jar file from the latest SVN?

[Noel Grandin]

On 2014-01-16 13:25, Alexey Kalugin wrote:
> Is it possible to obtain the jar file from the latest SVN?
>

Not without downloading the source and compiling it yourself.

But I just did this:

(1) I downloaded the latest jar from http://www.h2database.com/automated/h2-latest.jar

(2) I ran the H2 console from the command line
java -cp h2-latest.jar org.h2.tools.Console

(3) I connected to a DB called

jdbc:h2:mem:test;MODE=DB2;DB_CLOSE_DELAY=-1

(4) I executed the command
create table if not exists Driver (
ID int identity primary key,
FirstName varchar,
LastName varchar,
BirthDate date
)

(5) I executed the command
select * from Driver with ur

And it all worked.

At this point in time I suspect that your problem is in Hibernate, not H2.

[Alexey Kalugin]

I'm trying to run my tests with the jar provided by you but have got an exception: "junit.framework.AssertionFailedError: Exception in constructor: testDriverDataLoaded (java.lang.UnsupportedClassVersionError: org/h2/tools/RunScript : Unsupported major.minor version 51.0
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(Unknown Source)
    at java.security.SecureClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.access$000(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClassInternal(Unknown Source)
    at testing.DriverTestH2.<init>(DriverTestH2.java:32)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at junit.framework.TestSuite.createTest(TestSuite.java:68)
    at junit.framework.TestSuite.addTestMethod(TestSuite.java:310)
    at junit.framework.TestSuite.addTestsFromTestCase(TestSuite.java:153)
    at junit.framework.TestSuite.<init>(TestSuite.java:132)
    at org.junit.internal.runners.JUnit38ClassRunner.<init>(JUnit38ClassRunner.java:72)
    at org.junit.internal.builders.JUnit3Builder.runnerForClass(JUnit3Builder.java:11)
    at org.junit.runners.model.RunnerBuilder.safeRunnerForClass(RunnerBuilder.java:59)
    at org.junit.internal.builders.AllDefaultPossibilitiesBuilder.runnerForClass(AllDefaultPossibilitiesBuilder.java:26)
    at org.junit.runners.model.RunnerBuilder.safeRunnerForClass(RunnerBuilder.java:59)
    at org.junit.internal.requests.ClassRequest.getRunner(ClassRequest.java:26)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.<init>(JUnit4TestReference.java:33)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestClassReference.<init>(JUnit4TestClassReference.java:25)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestLoader.createTest(JUnit4TestLoader.java:48)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestLoader.loadTests(JUnit4TestLoader.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:452)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
)
    at junit.framework.Assert.fail(Assert.java:57)
    at junit.framework.TestCase.fail(TestCase.java:227)
    at junit.framework.TestSuite$1.runTest(TestSuite.java:100)
    at junit.framework.TestCase.runBare(TestCase.java:141)
    at junit.framework.TestResult$1.protect(TestResult.java:122)
    at junit.framework.TestResult.runProtected(TestResult.java:142)
    at junit.framework.TestResult.run(TestResult.java:125)
    at junit.framework.TestCase.run(TestCase.java:129)
    at junit.framework.TestSuite.runTest(TestSuite.java:255)
    at junit.framework.TestSuite.run(TestSuite.java:250)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:84)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

junit.framework.AssertionFailedError: Exception in constructor: testGetAllfDrivers (java.lang.UnsupportedClassVersionError: org/h2/tools/RunScript
    at testing.DriverTestH2.<init>(DriverTestH2.java:32)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at junit.framework.TestSuite.createTest(TestSuite.java:68)
    at junit.framework.TestSuite.addTestMethod(TestSuite.java:310)
    at junit.framework.TestSuite.addTestsFromTestCase(TestSuite.java:153)
    at junit.framework.TestSuite.<init>(TestSuite.java:132)
    at org.junit.internal.runners.JUnit38ClassRunner.<init>(JUnit38ClassRunner.java:72)
    at org.junit.internal.builders.JUnit3Builder.runnerForClass(JUnit3Builder.java:11)
    at org.junit.runners.model.RunnerBuilder.safeRunnerForClass(RunnerBuilder.java:59)
    at org.junit.internal.builders.AllDefaultPossibilitiesBuilder.runnerForClass(AllDefaultPossibilitiesBuilder.java:26)
    at org.junit.runners.model.RunnerBuilder.safeRunnerForClass(RunnerBuilder.java:59)
    at org.junit.internal.requests.ClassRequest.getRunner(ClassRequest.java:26)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.<init>(JUnit4TestReference.java:33)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestClassReference.<init>(JUnit4TestClassReference.java:25)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestLoader.createTest(JUnit4TestLoader.java:48)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestLoader.loadTests(JUnit4TestLoader.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:452)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
)
    at junit.framework.Assert.fail(Assert.java:57)
    at junit.framework.TestCase.fail(TestCase.java:227)
    at junit.framework.TestSuite$1.runTest(TestSuite.java:100)
    at junit.framework.TestCase.runBare(TestCase.java:141)
    at junit.framework.TestResult$1.protect(TestResult.java:122)
    at junit.framework.TestResult.runProtected(TestResult.java:142)
    at junit.framework.TestResult.run(TestResult.java:125)
    at junit.framework.TestCase.run(TestCase.java:129)
    at junit.framework.TestSuite.runTest(TestSuite.java:255)
    at junit.framework.TestSuite.run(TestSuite.java:250)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:84)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

"

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/I9R1bBS2WRw/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

On 2014-01-16 14:03, Alexey Kalugin wrote:
> Unsupported major.minor version 51.0

Did you try googling to see what the problem might be?

[Alexey Kalugin]

Thanks for your advice. I've googled and resolved the problem with "Unsupported major.minor version 51.0".
However there appeared new problem - it looks like that H2 doesn't recognise "with ur" DB2 syntax in INSERT statements. For example please consider the statement "insert into Driver(FirstName, LastName, BirthDate) values('SomeName', 'SomeSurname', '2002-11-15') with ur". Please let me know if you need more additional information from my side.

Thx.

[Noel Grandin]

On 2014-01-16 14:39, Alexey Kalugin wrote:
> However there appeared new problem - it looks like that H2 doesn't recognise "with ur" DB2 syntax in INSERT statements.
> For example please consider the statement "insert into Driver(FirstName, LastName, BirthDate) values('SomeName',
> 'SomeSurname', '2002-11-15') with ur".

A fix for this has been committed to SVN. It should be in the automated build jar by tomorrow.

[Alexey Kalugin]

Thanks for the update. I'll check tomorrow :)

[Alexey Kalugin]

Hi H2 team.

I've run my tests against the latest h2.jar file and it looks like that the issue with "UR" syntax construction of DB2 has been resolved. Can you let me know if H2 has started to support another isolation levels syntax constructions of DB2 (such as RR, CS and RS)?
Thank you.

[Noel Grandin]

On 2014-01-17 13:46, Alexey Kalugin wrote:
> I've run my tests against the latest h2.jar file and it looks like that the issue with "UR" syntax construction of DB2
> has been resolved. Can you let me know if H2 has started to support another isolation levels syntax constructions of DB2
> (such as RR, CS and RS)?

Why don't you test it yourself and see?

[Alexey Kalugin]

I've checked. Another isolation levels syntax constructions are supported as well.

Thank you for your help.