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
-~----------~----~----~----~------~----~------~--~---

Reply via email to