OFBiz
  1. OFBiz
  2. OFBIZ-1321

SQL creation has changed format in ver 4.0

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: Release Branch 4.0
    • Fix Version/s: Release Branch 4.0
    • Component/s: framework
    • Labels:
      None

      Description

      I update from the latest 4.0 release
      all of a suddent the SQL has under scores where it did not before.
      example
      viewprofile.bsh

      Method Invocation delegator.findByAnd : at Line: 86 : in file: component://party/webapp/partymgr/WEB-INF/actions/party/viewprofile.bsh : delegator .findByAnd ( "PartyNoteView" , UtilMisc .toMap ( "targetPartyId" , partyId ) , UtilMisc .toList ( "-noteDateTime" ) )

      now produces
      Target exception: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT PN.PARTY_ID AS TARGET_PARTY_ID, ND.NOTE_ID AS NOTE_ID, ND.NOTE_NAME AS NOTE_NAME, ND.NOTE_INFO AS NOTE_INFO, ND.NOTE_DATE_TIME AS NOTE_DATE_TIME, ND.NOTE_PARTY AS NOTE_PARTY FROM public.PARTY_NOTE PN INNER JOIN public.NOTE_DATA ND ON PN.NOTE_ID = ND.NOTE_ID WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC (ERROR: column "target_party_id" does not exist)

        Activity

        BJ Freeman created issue -
        Hide
        BJ Freeman added a comment -

        think this may be related to the
        svn commit: r575396 - in /ofbiz/trunk/framework: entity/ entity/config/
        entity/dtd/ entity/src/org/ofbiz/entity/config/
        entity/src/org/ofbiz/entity/connection/
        entity/src/org/ofbiz/entity/jdbc/
        entity/src/org/ofbiz/entity/transaction/ geronimo/src/org/...

        Show
        BJ Freeman added a comment - think this may be related to the svn commit: r575396 - in /ofbiz/trunk/framework: entity/ entity/config/ entity/dtd/ entity/src/org/ofbiz/entity/config/ entity/src/org/ofbiz/entity/connection/ entity/src/org/ofbiz/entity/jdbc/ entity/src/org/ofbiz/entity/transaction/ geronimo/src/org/...
        Hide
        David E. Jones added a comment -

        The table and column names have most certainly always had the underscores in them, since day 1 of OFBiz over 6 years ago.

        There is definitely something wrong, but this isn't it. I'd recommend looking at the database you are using and the settings in the entityengine.xml file, especially the alias-view-columns attribute (which should generally be set to true).

        Show
        David E. Jones added a comment - The table and column names have most certainly always had the underscores in them, since day 1 of OFBiz over 6 years ago. There is definitely something wrong, but this isn't it. I'd recommend looking at the database you are using and the settings in the entityengine.xml file, especially the alias-view-columns attribute (which should generally be set to true).
        David E. Jones made changes -
        Field Original Value New Value
        Assignee David E. Jones [ jonesde ]
        Status Open [ 1 ] Closed [ 6 ]
        Resolution Invalid [ 6 ]
        Hide
        BJ Freeman added a comment -

        I appreciate you input.
        However if you look at the data you will see it is having this problem.
        1/2 an hour before I did the update, it was working fine.
        Now I am probably wrong about why it went south on me.
        but it is a fact it did and I copied the files from the latest SVN for ver 4.0
        I am using the same Entityengine.xml that was working until I updated to the latest version.
        I make a copy then replace it after checking against the one that I got from the SVN.

        So as far as I am concern something is not right. And has todo with the files in ver 4.0

        Show
        BJ Freeman added a comment - I appreciate you input. However if you look at the data you will see it is having this problem. 1/2 an hour before I did the update, it was working fine. Now I am probably wrong about why it went south on me. but it is a fact it did and I copied the files from the latest SVN for ver 4.0 I am using the same Entityengine.xml that was working until I updated to the latest version. I make a copy then replace it after checking against the one that I got from the SVN. So as far as I am concern something is not right. And has todo with the files in ver 4.0
        Hide
        Adam Heath added a comment -

        582338 works fine for me. I took the line you pasted from the code, ran it, then looked at the generated sql from the postgresql.log, and got this:
        ==
        2007-10-09 14:53:41 CDT LOG: statement: SELECT PN.PARTY_ID AS TARGET_PARTY_ID, ND.NOTE_ID AS NOTE_ID, ND.NOTE_NAME AS NOTE_NAME, ND.NOTE_INF
        O AS NOTE_INFO, ND.NOTE_DATE_TIME AS NOTE_DATE_TIME, ND.NOTE_PARTY AS NOTE_PARTY FROM public.PARTY_NOTE PN INNER JOIN public.NOTE_DATA ND ON
        PN.NOTE_ID = ND.NOTE_ID WHERE (PN.PARTY_ID = 1234) ORDER BY NOTE_DATE_TIME DESC;
        ==

        Do you have local changes? There are no changes from 582338 to HEAD that touch the code that generates these queries.

        Also, please give the exact svn version you were running before, and after. And run svn status to see if you have local changes. And do ant clean/ant at the top level.

        Show
        Adam Heath added a comment - 582338 works fine for me. I took the line you pasted from the code, ran it, then looked at the generated sql from the postgresql.log, and got this: == 2007-10-09 14:53:41 CDT LOG: statement: SELECT PN.PARTY_ID AS TARGET_PARTY_ID, ND.NOTE_ID AS NOTE_ID, ND.NOTE_NAME AS NOTE_NAME, ND.NOTE_INF O AS NOTE_INFO, ND.NOTE_DATE_TIME AS NOTE_DATE_TIME, ND.NOTE_PARTY AS NOTE_PARTY FROM public.PARTY_NOTE PN INNER JOIN public.NOTE_DATA ND ON PN.NOTE_ID = ND.NOTE_ID WHERE (PN.PARTY_ID = 1234) ORDER BY NOTE_DATE_TIME DESC; == Do you have local changes? There are no changes from 582338 to HEAD that touch the code that generates these queries. Also, please give the exact svn version you were running before, and after. And run svn status to see if you have local changes. And do ant clean/ant at the top level.
        Hide
        BJ Freeman added a comment -

        I do not modify svn Code.
        I have other modules where I pull everything I need and do it there.
        this error:
        I used the svn partymgr to find list of parties
        then I selected one.
        Note, the is a production system, as of (svn550867) and the DB is postgresql.
        part of the test is to create a party id with svn550867 (6/26/07)
        then
        update to svn577416 (9/19/07), which I did yesterday.
        click on any partyID

        Note: I have a standard batch that moves from this SVN folder where I have the ver 4.0 checked out to the clients folder.
        this is then compiled
        everything but source code that is required to run ofbiz is then zipped and uploaded to the server.

        This has been my standard procedure for two years.

        I use the restart script to restart ofbiz on a Linux box.

        Show
        BJ Freeman added a comment - I do not modify svn Code. I have other modules where I pull everything I need and do it there. this error: I used the svn partymgr to find list of parties then I selected one. Note, the is a production system, as of (svn550867) and the DB is postgresql. part of the test is to create a party id with svn550867 (6/26/07) then update to svn577416 (9/19/07), which I did yesterday. click on any partyID Note: I have a standard batch that moves from this SVN folder where I have the ver 4.0 checked out to the clients folder. this is then compiled everything but source code that is required to run ofbiz is then zipped and uploaded to the server. This has been my standard procedure for two years. I use the restart script to restart ofbiz on a Linux box.
        Hide
        Adam Heath added a comment -

        Look at revision 528862, done by David Jones, on April 14. This is before your last merge point, but I bet you didn't install the change in the entityengine.xml config file the last time you did an update.

        This change changes postgres to not alias column views; but as you noticed, this does not work. The setting needs to be changed back to be true.

        Or, maybe it depends on the version of postgres you are running. I am running against 8.1. Which version are you running?

        Show
        Adam Heath added a comment - Look at revision 528862, done by David Jones, on April 14. This is before your last merge point, but I bet you didn't install the change in the entityengine.xml config file the last time you did an update. This change changes postgres to not alias column views; but as you noticed, this does not work. The setting needs to be changed back to be true. Or, maybe it depends on the version of postgres you are running. I am running against 8.1. Which version are you running?
        Hide
        BJ Freeman added a comment -

        David did comment on this above.
        it does not matter which way I have it, I still get ERROR: column "target_party_id" does not exist)
        you will notice that only one is not found

        PostgreSQL version 7.4.13 (With schemas)

        <datasource name="localpostgres"
        helper-class="org.ofbiz.entity.datasource.GenericHelperDAO"
        schema-name="public"
        field-type-name="postgres"
        check-on-start="true"
        alias-view-columns="flase"
        add-missing-on-start="true"
        use-fk-initially-deferred="false"
        join-style="ansi"
        use-binary-type-for-blob="true">
        <!-- use this attribute to make the EntityListIterator more effective for pgjdbc 7.5devel and later:
        result-fetch-size="50"
        -->
        <read-data reader-name="seed"/>
        <read-data reader-name="demo"/>
        <read-data reader-name="ext"/>
        <inline-jdbc
        jdbc-driver="org.postgresql.Driver"
        jdbc-uri="jdbc:postgresql://127.0.0.1:5432/dbname"
        jdbc-username="dbuser"
        jdbc-password="dbpassword"
        isolation-level="ReadCommitted"
        pool-minsize="2"
        pool-maxsize="20"/>
        <!-- <jndi-jdbc jndi-server-name="default" jndi-name="java:comp/env/jdbc/localpostgres" isolation-level="ReadCommitted"/>-->
        <Unable to render embedded object: File (-- <jndi-jdbc jndi-server-name="default" jndi-name="comp/env/jdbc/xa/localpostgres" isolation-level="ReadCommitted"/> --> <) not found.-- Orion Style JNDI name -->
        <Unable to render embedded object: File (-- <jndi-jdbc jndi-server-name="localweblogic" jndi-name="PostgresDataSource"/> --> <) not found.-- Weblogic Style JNDI name -->
        <Unable to render embedded object: File (-- <jndi-jdbc jndi-server-name="default" jndi-name="jdbc/localpostgres" isolation-level="ReadCommitted"/> --> <) not found.-- JRun4 Style JNDI name -->
        <!-- <tyrex-dataSource dataSource-name="localpostgres" isolation-level="ReadCommitted"/> -->
        </datasource>

        Target exception: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT PN.PARTY_ID AS TARGET_PARTY_ID, ND.NOTE_ID AS NOTE_ID, ND.NOTE_NAME AS NOTE_NAME, ND.NOTE_INFO AS NOTE_INFO, ND.NOTE_DATE_TIME AS NOTE_DATE_TIME, ND.NOTE_PARTY AS NOTE_PARTY FROM public.PARTY_NOTE PN INNER JOIN public.NOTE_DATA ND ON PN.NOTE_ID = ND.NOTE_ID WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC (ERROR: column "target_party_id" does not exist)

        Show
        BJ Freeman added a comment - David did comment on this above. it does not matter which way I have it, I still get ERROR: column "target_party_id" does not exist) you will notice that only one is not found PostgreSQL version 7.4.13 (With schemas) <datasource name="localpostgres" helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" schema-name="public" field-type-name="postgres" check-on-start="true" alias-view-columns="flase" add-missing-on-start="true" use-fk-initially-deferred="false" join-style="ansi" use-binary-type-for-blob="true"> <!-- use this attribute to make the EntityListIterator more effective for pgjdbc 7.5devel and later: result-fetch-size="50" --> <read-data reader-name="seed"/> <read-data reader-name="demo"/> <read-data reader-name="ext"/> <inline-jdbc jdbc-driver="org.postgresql.Driver" jdbc-uri="jdbc:postgresql://127.0.0.1:5432/dbname" jdbc-username="dbuser" jdbc-password="dbpassword" isolation-level="ReadCommitted" pool-minsize="2" pool-maxsize="20"/> <!-- <jndi-jdbc jndi-server-name="default" jndi-name="java:comp/env/jdbc/localpostgres" isolation-level="ReadCommitted"/>--> < Unable to render embedded object: File (-- <jndi-jdbc jndi-server-name="default" jndi-name="comp/env/jdbc/xa/localpostgres" isolation-level="ReadCommitted"/> --> <) not found. -- Orion Style JNDI name --> < Unable to render embedded object: File (-- <jndi-jdbc jndi-server-name="localweblogic" jndi-name="PostgresDataSource"/> --> <) not found. -- Weblogic Style JNDI name --> < Unable to render embedded object: File (-- <jndi-jdbc jndi-server-name="default" jndi-name="jdbc/localpostgres" isolation-level="ReadCommitted"/> --> <) not found. -- JRun4 Style JNDI name --> <!-- <tyrex-dataSource dataSource-name="localpostgres" isolation-level="ReadCommitted"/> --> </datasource> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT PN.PARTY_ID AS TARGET_PARTY_ID, ND.NOTE_ID AS NOTE_ID, ND.NOTE_NAME AS NOTE_NAME, ND.NOTE_INFO AS NOTE_INFO, ND.NOTE_DATE_TIME AS NOTE_DATE_TIME, ND.NOTE_PARTY AS NOTE_PARTY FROM public.PARTY_NOTE PN INNER JOIN public.NOTE_DATA ND ON PN.NOTE_ID = ND.NOTE_ID WHERE (TARGET_PARTY_ID = ?) ORDER BY NOTE_DATE_TIME DESC (ERROR: column "target_party_id" does not exist)
        Hide
        Adam Heath added a comment -

        ==
        alias-view-columns="flase"
        ==
        framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java: this.aliasViews = "true".equals(datasourceElement.getAttribute("alias-view-columns"));
        ==
        So, that setting is false, so the columns are not aliased. Your config needs to be changed.
        The default config shipped by ofbiz also needs to be changed.

        Show
        Adam Heath added a comment - == alias-view-columns="flase" == framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java: this.aliasViews = "true".equals(datasourceElement.getAttribute("alias-view-columns")); == So, that setting is false, so the columns are not aliased. Your config needs to be changed. The default config shipped by ofbiz also needs to be changed.
        Hide
        BJ Freeman added a comment -

        adam as I stated before
        I have set
        alias-view-columns="false"
        and
        alias-view-columns="true"
        with not change.
        so I will dig further into DatasourceInfo.java
        and find out why
        Thanks .

        Show
        BJ Freeman added a comment - adam as I stated before I have set alias-view-columns="false" and alias-view-columns="true" with not change. so I will dig further into DatasourceInfo.java and find out why Thanks .
        Hide
        BJ Freeman added a comment -

        Ok will close this one.
        it is operator error.
        going back over the issue I saw what I did.
        alias-view-columns="flase"
        instead of
        alias-view-columns="false"
        the code looks for True and if does not find it
        then defaults to true state.

        will open another issue for trunk once I have a util to check for parmaters strings
        checkfor(Listofparms, expected)
        then it will return true if expected found and throw and exception if non of the parmlist is found.

        Show
        BJ Freeman added a comment - Ok will close this one. it is operator error. going back over the issue I saw what I did. alias-view-columns="flase" instead of alias-view-columns="false" the code looks for True and if does not find it then defaults to true state. will open another issue for trunk once I have a util to check for parmaters strings checkfor(Listofparms, expected) then it will return true if expected found and throw and exception if non of the parmlist is found.
        Gavin made changes -
        Workflow jira [ 12414580 ] OFbiz Workflow [ 12504780 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Closed Closed
        5h 33m 1 David E. Jones 09/Oct/07 19:21

          People

          • Assignee:
            David E. Jones
            Reporter:
            BJ Freeman
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development