Cayenne
  1. Cayenne
  2. CAY-1084

openbase does not like our join syntax :(

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 3.0
    • Fix Version/s: None
    • Component/s: Database integration
    • Labels:
      None
    • Environment:
      OpenBase 10.0.11

      Description

      Hello.

      Just tried the junit tests on OpenBase and it did not work very well. A lot of the problems is from the join syntax that OpenBase seem to have problems with.

      INFO: SELECT t0.COLLECTION_TO_MANY_ID, t0.ID FROM COLLECTION_TO_MANY_TARGET t0 JOIN COLLECTION_TO_MANY t1 ON (t0.COLLECTION_TO_MANY_ID = t1.ID) WHERE t1.ID = ? [bind: 1->ID:1]
      INFO: *** error.
      java.sql.SQLException: SQL ERROR - [position 72, near 'JOIN' in '_TARGET t0 JOIN COLLECTIO'] comma or JOIN syntax expected but not found in from clause.
      SQL: SELECT t0.COLLECTION_TO_MANY_ID, t0.ID FROM COLLECTION_TO_MANY_TARGET t0 JOIN COLLECTION_TO_MANY t1 ON (t0.COLLECTION_TO_MANY_ID = t1.ID) WHERE t1.ID = 1
      at com.openbase.net.c.a(Unknown Source)
      at com.openbase.jdbc.c.a(Unknown Source)
      at com.openbase.jdbc.c.a(Unknown Source)
      at com.openbase.jdbc.i.executeQuery(Unknown Source)
      at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:73)

      This looks like the documentation for select statements including join. The "JOIN" keyword is not shown at all.
      http://www.openbase.com/help/KnowledgeBase/400_OpenBaseSQL/401_SelectStatements.html

        Activity

        Hide
        Andrus Adamchik added a comment -

        I suspected some of the DB's won't like the new syntax.... Actually I think the current mechanism is flexible enough to generate "legacy" joins per adapter.

        OpenBase seems to even support outer joins, via a really weird syntax (from the link you posted) :

        "OpenBase supports left outer joins, meaning that records from tables specified first are included even when they cannot be matched with records from tables on the right. An outer join is specified by using the * operator in place of the = operator in the WHERE statement. Here is an example:

        SELECT mov.TITLE, mov.CATEGORY, stu.NAME FROM MOVIE mov, STUDIO stu WHERE mov.STUDIO_ID * stu.STUDIO_ID "

        Show
        Andrus Adamchik added a comment - I suspected some of the DB's won't like the new syntax.... Actually I think the current mechanism is flexible enough to generate "legacy" joins per adapter. OpenBase seems to even support outer joins, via a really weird syntax (from the link you posted) : "OpenBase supports left outer joins, meaning that records from tables specified first are included even when they cannot be matched with records from tables on the right. An outer join is specified by using the * operator in place of the = operator in the WHERE statement. Here is an example: SELECT mov.TITLE, mov.CATEGORY, stu.NAME FROM MOVIE mov, STUDIO stu WHERE mov.STUDIO_ID * stu.STUDIO_ID "
        Hide
        Tore Halset added a comment -

        So how do I start to "generate "legacy" joins per " this adapter???

        Show
        Tore Halset added a comment - So how do I start to "generate "legacy" joins per " this adapter???
        Hide
        Tore Halset added a comment - - edited

        ..Probably by subclassing JoinStack and also make it possible for the JoinStack to output stuff after "WHERE" in the SelectTranslator.

        Show
        Tore Halset added a comment - - edited ..Probably by subclassing JoinStack and also make it possible for the JoinStack to output stuff after "WHERE" in the SelectTranslator.
        Hide
        Mike Kienenberger added a comment -

        In the past, OpenBase has been very good about changing syntax to fit in with standards.

        You might want to drop a line on the openbase dev mailing list.

        Show
        Mike Kienenberger added a comment - In the past, OpenBase has been very good about changing syntax to fit in with standards. You might want to drop a line on the openbase dev mailing list.
        Hide
        Tore Halset added a comment -

        I have joined the list, but is not able to post as I do not know what the mail adr is. Really strange. Will do.

        Added support for the current OpenBase join syntax. It still fails some tests as the outer joins behaves a little different. It looks like checking for null does not work if you want rows that does not have a related row on the right side of the join. or so.

        Will keep this one open a little while to see what happens..

        Show
        Tore Halset added a comment - I have joined the list, but is not able to post as I do not know what the mail adr is. Really strange. Will do. Added support for the current OpenBase join syntax. It still fails some tests as the outer joins behaves a little different. It looks like checking for null does not work if you want rows that does not have a related row on the right side of the join. or so. Will keep this one open a little while to see what happens..

          People

          • Assignee:
            Tore Halset
            Reporter:
            Tore Halset
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development