glassfish
  1. glassfish
  2. GLASSFISH-3698

SQL NativeQuery with EntityResult does not work with PostgreSQL

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 9.1pe
    • Fix Version/s: 9.1peur1
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: Linux

    • Issuezilla Id:
      3,698

      Description

      Query q = em.createNativeQuery("SELECT * FROM entitytable", EntityClass.class);
      List<EntityClass> result = q.getResultList();

      Gives the following exception with PostgreSQL:

      Exception [TOPLINK-6044] (Oracle TopLink Essentials - 2.0 (Build b58f-fcs
      (09/06/2007))): oracle.toplink.essentials.exceptions.QueryException
      Exception Description: The primary key read from the row [DatabaseRecord(
      => 1
      => 1
      => 1
      => 2
      => 1
      => 1
      => 1
      => O.S.:-,
      => null
      => null
      => null
      => 2007-09-26 20:01:58.327
      => 2007-09-26 20:01:58.327)] during the execution of the query was
      detected to be null. Primary keys must not contain null.
      Query: ReadAllQuery(com.vesseltracker.wateropt.entity.ShipAction)
      at
      oracle.toplink.essentials.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:823)
      at
      oracle.toplink.essentials.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:366)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:386)
      at
      oracle.toplink.essentials.queryframework.ObjectBuildingQuery.registerIndividualResult(ObjectBuildingQuery.java:318)
      at
      oracle.toplink.essentials.queryframework.ReadAllQuery.registerResultInUnitOfWork(ReadAllQuery.java:511)
      at
      oracle.toplink.essentials.queryframework.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:346)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:724)
      at
      oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478)

        Activity

        Hide
        batzee added a comment -

        1. did help, now I can execute the query.
        However, as the table was generated by toplink's schema generation, this still
        might be a bug? As the field names are annotated in uppercase, toplink should
        generate uppercase SQL fields, at least for case sensitive databases like
        PostgreSQL.

        2. did not help:
        String query = "SELECT " +
        "a.id as \"ID\",a.ship_id as \"SHIP_ID\",a.datasource_id as
        \"DATASOURCE_ID\",a.type as \"TYPE\",a.agent_company_id as
        \"AGENT_COMPANY_ID\",a.port_id as \"PORT_ID\",a.berth_id as
        BERTH_ID,a.berth_info as \"BERTH_INFO\",a.starboard as
        \"STARBOARD\",a.tugboat_company_id as
        \"TUGBOAT_COMPANY_ID\",a.boatmen_company_id as
        \"BOATMEN_COMPANY_ID\",a.time_updated as \"TIME_UPDATED\",a.time_created as
        \"TIME_CREATED\"" +
        " FROM shipaction a";

        results in the same error.

        Show
        batzee added a comment - 1. did help, now I can execute the query. However, as the table was generated by toplink's schema generation, this still might be a bug? As the field names are annotated in uppercase, toplink should generate uppercase SQL fields, at least for case sensitive databases like PostgreSQL. 2. did not help: String query = "SELECT " + "a.id as \"ID\",a.ship_id as \"SHIP_ID\",a.datasource_id as \"DATASOURCE_ID\",a.type as \"TYPE\",a.agent_company_id as \"AGENT_COMPANY_ID\",a.port_id as \"PORT_ID\",a.berth_id as BERTH_ID,a.berth_info as \"BERTH_INFO\",a.starboard as \"STARBOARD\",a.tugboat_company_id as \"TUGBOAT_COMPANY_ID\",a.boatmen_company_id as \"BOATMEN_COMPANY_ID\",a.time_updated as \"TIME_UPDATED\",a.time_created as \"TIME_CREATED\"" + " FROM shipaction a"; results in the same error.
        Hide
        tware added a comment -

        I just spoke to someone with a little more PostgreSQL knowledge than me.

        Apparently, PostGresSQL will, by default, convert the field names in the create
        table statement to Lower case when we send table creation SQL. i.e. We send the
        SQL in the case you provide, but PostGreSQL changes it.

        I am told that if you do everything in lower case, you should avoid the problem.

        I am going to mark this issue as a duplicate of:

        https://glassfish.dev.java.net/issues/show_bug.cgi?id=600

        I believe the fix for that issue will solve your problem. Let me know if that
        is a problem.

            • This issue has been marked as a duplicate of 600 ***
        Show
        tware added a comment - I just spoke to someone with a little more PostgreSQL knowledge than me. Apparently, PostGresSQL will, by default, convert the field names in the create table statement to Lower case when we send table creation SQL. i.e. We send the SQL in the case you provide, but PostGreSQL changes it. I am told that if you do everything in lower case, you should avoid the problem. I am going to mark this issue as a duplicate of: https://glassfish.dev.java.net/issues/show_bug.cgi?id=600 I believe the fix for that issue will solve your problem. Let me know if that is a problem. This issue has been marked as a duplicate of 600 ***
        Hide
        batzee added a comment -

        I think the real way to solve this issue is to put doublequotes around the field
        names when generating the DDL:

        CREATE TABLE "ALARMT" (
        NOTIFICATIONID VARCHAR(255) NOT NULL,
        DISCRIMINATORVALUE VARCHAR(255) NOT NULL, NotificationId VARCHAR(255), PRIMARY
        KEY (NOTIFICATIONID, DISCRIMINATORVALUE))

        Show
        batzee added a comment - I think the real way to solve this issue is to put doublequotes around the field names when generating the DDL: CREATE TABLE "ALARMT" ( NOTIFICATIONID VARCHAR(255) NOT NULL, DISCRIMINATORVALUE VARCHAR(255) NOT NULL, NotificationId VARCHAR(255), PRIMARY KEY (NOTIFICATIONID, DISCRIMINATORVALUE))
        Hide
        batzee added a comment -

        I think the real way to solve this issue is to put doublequotes around the field
        names when generating the DDL:

        CREATE TABLE "ALARMT" (
        "NOTIFICATIONID" VARCHAR(255) NOT NULL,
        "DISCRIMINATORVALUE" VARCHAR(255) NOT NULL,
        "NotificationId" VARCHAR(255),
        PRIMARY KEY ("NOTIFICATIONID", "DISCRIMINATORVALUE"))
        );

        Then postgres will not put the field names to lowercase and everyone will be happy.

        Maybe you can also post this comment to GLASSFISH-600 since I do not have the
        possiblility to post there.

        Show
        batzee added a comment - I think the real way to solve this issue is to put doublequotes around the field names when generating the DDL: CREATE TABLE "ALARMT" ( "NOTIFICATIONID" VARCHAR(255) NOT NULL, "DISCRIMINATORVALUE" VARCHAR(255) NOT NULL, "NotificationId" VARCHAR(255), PRIMARY KEY ("NOTIFICATIONID", "DISCRIMINATORVALUE")) ); Then postgres will not put the field names to lowercase and everyone will be happy. Maybe you can also post this comment to GLASSFISH-600 since I do not have the possiblility to post there.
        Hide
        tware added a comment -

        Setting back to duplicate - since I still believe GLASSFISH-600 will solve the issue.

        I have posted a reference to this bug in GLASSFISH-600.

        Please be very explicit about why you are changing the status if you change it
        again.

            • This issue has been marked as a duplicate of 600 ***
        Show
        tware added a comment - Setting back to duplicate - since I still believe GLASSFISH-600 will solve the issue. I have posted a reference to this bug in GLASSFISH-600 . Please be very explicit about why you are changing the status if you change it again. This issue has been marked as a duplicate of 600 ***

          People

          • Assignee:
            tware
            Reporter:
            batzee
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: