Hi,
Thanks! I will fix this in the next release. Thanks for the patch! I
will not directly apply it, because I want to re-use the existing
findSequence method, but basically my solution matches yours. I will
also fix CURRVAL.
Regards,
Thomas
On Wed, Aug 12, 2009 at 6:52 PM, Mike Kienenberger<mkien...@gmail.com> wrote:
>
> We're using Eclipselink with Oracle for our application, but we use H2
> for testing.
>
> For the most part, it appears we can use the Eclipselink
> OraclePlatform during our tests once we specify
>
> ;SCHEMA_SEARCH_PATH=schema1,schema2,schema3,etc
>
> to adjust for the inability to use the oracle "CREATE SYNONYM
> some_table FOR schema1.some_table;" command.
>
> However, fetching the nextval for a sequence appears to be ignoring
> the SCHEMA_SEARCH_PATH.
>
> ie,
>
> SELECT my_seq.NEXTVAL FROM DUAL;
>
> fails (stack trace at end), but
>
> SELECT schema1.my_seq.NEXTVAL FROM DUAL
>
> succeeds.
>
> It is desirable for us to use the same Platform class (since H2
> provides Oracle compatibility) as there is no easy way to specify an
> alternate persistence.xml file for JPA/EclipseLink, so switching
> between running a test, and running the application requires manually
> editing the persistence.xml file each time.
>
> The code in readSequence() in org.h2.command.Parser indicates that
> sequences were intended to be SCHEMA_SEARCH_PATH-aware.
>
> From the stack trace, it looks more like an sql parsing error. This
> is using H2-1.1.111
>
> Setting a breakpoint for org.h2.command.Parser.readColumnIdentifier
> (Parser.java:2513) show that currentTokenType expects IDENTIFIER (2)
> but is KEYWORD (1)
>
> In Parser.readTermObjectDot(String) line: 2125, objectName =
> 'NAVIGATION_HISTORY_SEQ'
>
> Apparently the line above at 2121 failed to detect
> 'NAVIGATION_HISTORY_SEQ' as a sequence.
>
> Expression expr = readWildcardOrSequenceValue(null,
> objectName);
> if (expr != null) {
> return expr;
> }
>
> Stepping through the readWildcardOrSequenceValue() code, the sequence
> is searched for in the default schema (PUBLIC) rather than using the
> SCHEMA_SEARCH_PATH.
>
> if (readIf("NEXTVAL")) {
> Sequence sequence = database.getSchema(schema).findSequence
> (objectName);
>
> I created the following patch, and it appears to fix the problem.
> And I'm now able to use the OraclePlatform for Eclipselink with my H2-
> based tests.
>
> ### Eclipse Workspace Patch 1.0
> #P h2-1.1.111
> Index: src/main/org/h2/command/Parser.java
> ===================================================================
> --- src/main/org/h2/command/Parser.java (revision 1403)
> +++ src/main/org/h2/command/Parser.java (working copy)
> @@ -2104,6 +2104,15 @@
> if (sequence != null) {
> return new SequenceValue(sequence);
> }
> + String[] schemaNames = session.getSchemaSearchPath();
> + for (int i = 0; schemaNames != null && i <
> schemaNames.length; i++) {
> + Schema s = database.getSchema(schemaNames[i]);
> + Sequence sequence2 = s.findSequence(objectName);
> + if (sequence2 != null) {
> + return new SequenceValue(sequence2);
> + }
> + }
> +
> } else if (readIf("CURRVAL")) {
> Sequence sequence = database.getSchema
> (schema).findSequence(objectName);
> if (sequence != null) {
>
>
> Syntax error in SQL statement SELECT NAVIGATION_HISTORY_SEQ.NEXTVAL
> FROM[*] DUAL ; expected identifier; SQL statement:
> SELECT navigation_history_seq.NEXTVAL FROM DUAL [42001-111]
> 42001/42001 (Help)
> org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement SELECT
> NAVIGATION_HISTORY_SEQ.NEXTVAL FROM[*] DUAL ; expected identifier; SQL
> statement:
> SELECT navigation_history_seq.NEXTVAL FROM DUAL [42001-111]
> at org.h2.message.Message.getSQLException(Message.java:107)
> at org.h2.message.Message.getSQLException(Message.java:118)
> at org.h2.message.Message.getSyntaxError(Message.java:143)
> at org.h2.command.Parser.readColumnIdentifier(Parser.java:2513)
> at org.h2.command.Parser.readTermObjectDot(Parser.java:2125)
> at org.h2.command.Parser.readTerm(Parser.java:2228)
> at org.h2.command.Parser.readFactor(Parser.java:1847)
> at org.h2.command.Parser.readSum(Parser.java:1834)
> at org.h2.command.Parser.readConcat(Parser.java:1807)
> at org.h2.command.Parser.readCondition(Parser.java:1675)
> at org.h2.command.Parser.readAnd(Parser.java:1655)
> at org.h2.command.Parser.readExpression(Parser.java:1647)
> at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:
> 1562)
> at org.h2.command.Parser.parseSelectSimple(Parser.java:1592)
> at org.h2.command.Parser.parseSelectSub(Parser.java:1497)
> at org.h2.command.Parser.parseSelectUnion(Parser.java:1342)
> at org.h2.command.Parser.parseSelect(Parser.java:1330)
> at org.h2.command.Parser.parsePrepared(Parser.java:392)
> 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:409)
> at org.h2.engine.Session.prepareCommand(Session.java:370)
> at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
> 1042)
> at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
> at org.h2.server.web.WebThread.getResult(WebThread.java:1723)
> at org.h2.server.web.WebThread.query(WebThread.java:1286)
> at org.h2.server.web.WebThread.process(WebThread.java:444)
> at org.h2.server.web.WebThread.processRequest(WebThread.java:186)
> at org.h2.server.web.WebThread.process(WebThread.java:241)
> at org.h2.server.web.WebThread.run(WebThread.java:196)
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
