adfemg
  1. adfemg
  2. ADFEMG-117

Entity Object "Refresh on Insert" doesn't work with MySQL AUTO_INCREMENT primary key

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Incomplete
    • Labels:
      None
    • Environment:

      11.1.2.3.0

      Description

      I have a MySQL table with an AUTO_INCREMENT primary key column and a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP ON UPDATE. This means that MySQL will automatically provide a primary key on insert, and that the timestamp column will be auto updated on insert and update.

      My ADF BC project uses SQL92 mapping. Because the RETURNING clause is not SQL92, ADF attempts to query back the record when I select "Refresh on Insert" for my timestamp column. But because the ID is provided by MySQL, ADF doesn't know the ID value and fails with JBO-28203: The updated row cannot be refreshed beacuse its key values are unset or modified by database triggers.

      I belive ADF should be able to handle this by querying LAST_INSERT_ID().

        Activity

        Hide
        johnsivertsen added a comment -

        According to http://www.oracle.com/technetwork/developer-tools/jdev/multidatabaseapp-085183.html
        Refresh on insert or refresh on update does not work for entity object when not using Oracle database.
        Quote:
        Don't select refresh on insert or refresh on update for entity object attributes
        These options generate Oracle-specific SQL and should not be used.

        The above document is from jan 2010. Don't know if it is outdated?

        // John

        Show
        johnsivertsen added a comment - According to http://www.oracle.com/technetwork/developer-tools/jdev/multidatabaseapp-085183.html Refresh on insert or refresh on update does not work for entity object when not using Oracle database. Quote: Don't select refresh on insert or refresh on update for entity object attributes These options generate Oracle-specific SQL and should not be used. The above document is from jan 2010. Don't know if it is outdated? // John
        Hide
        anandmanikutty added a comment -

        There are wheels within wheels. The issue is the following :
        "These options generate Oracle-specific SQL"

        If the other database supports the Oracle-specific SQL in question, then the feature would still work.

        Show
        anandmanikutty added a comment - There are wheels within wheels. The issue is the following : "These options generate Oracle-specific SQL" If the other database supports the Oracle-specific SQL in question, then the feature would still work.
        Hide
        vesterli added a comment -

        I don't see how the JDBC driver can affect the issue - root cause is that ADF generates SQL including RETURNING clause which will only work in Oracle.

        Environment:
        5.6.10 MySQL Community Server
        JDEVADF_11.1.2.3.0_GENERIC_120914.0223.6276.1
        mysql-connector-java-5.1.23-bin.jar

        Test case:
        create table sev1 (
        sev1_id int primary key auto_increment
        , sev1_desc varchar(80)
        , last_update_date timestamp default current_timestamp
        );

        Build default EO, VO, AM on this (SQL92/Java).
        For sev1_id in EO set
        PrimaryKey="true"
        RetrievedOnInsert="true"
        IsUpdateable="while_insert"

        For last_update_date in EO set
        RetrievedOnInsert="true"

        Both sev1_id and last_update_date have Mandatory unchecked.

        Run BC tester, insert new row with only sev1_desc column value.
        Expect new record to be saved with auto ID and auto last_update_date.
        What happens is "(oracle.jbo.RowNotFoundException) JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers."

        BC tester log below. Extract from log:
        [103] INSERT INTO sakila.sev1(sev1_desc) VALUES
        [106] BaseSQLBuilder.doRefreshSQL, Executing Select for Refresh-on-insert or update attributes
        [107] Warning: Attempting to use current primary key attributes for refresh SQL.
        [108] ******* Found no UniqueKey attribute when a PK attribute is marked for refresh on Entity:Sev1
        [109] Built select: 'SELECT sev1_id, last_update_date FROM sakila.sev1'
        [110] Executing Refresh SQL...SELECT sev1_id, last_update_date FROM sakila.sev1 WHERE sev1_id=?
        [111] General failure in select : JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers.

        ADF tells me it can't handle the situation with an auto-generated ID and another refreshOnInsert column. As I see it, on Oracle ADF uses the RETURNING clause. On MySQL, ADF could call LAST_INSERT_ID(), but apparently doesn't.

        My suggestion is that you implement a LAST_INSERT_ID call when database is MySQL.

        Best regards

        Sten Vesterli

        BTW, looking forward to JDev 12c!

        ----------
        Full BC tester log:
        [94] INFO: No app def in BindingContext for: adfFacesContext
        [95] INFO: mDCRefMap lookup failed. Does the cpx have a dataControlUsages 'dc' entry? adfFacesContext
        [96] INFO: No app def in BindingContext for: adfFacesContext
        [97] INFO: mDCRefMap lookup failed. Does the cpx have a dataControlUsages 'dc' entry? adfFacesContext
        [98] BaseSQLBuilder: setSavepoint 'BO_SP' ignored
        [99] BaseSQLBuilder Executing DML ... (Insert)
        [100] Executing DML...
        [101] Warning: BaseSQLBuilder does not support RETURNING (key-cols) clause....
        [102] Warning: BaseSQLBuilder does not support RETURNING (cols) clause....
        [103] INSERT INTO sakila.sev1(sev1_desc) VALUES
        [104] Insert binding param 1: blabla
        [105] Fetch RefreshOnInsert attributes
        [106] BaseSQLBuilder.doRefreshSQL, Executing Select for Refresh-on-insert or update attributes
        [107] Warning: Attempting to use current primary key attributes for refresh SQL.
        [108] ******* Found no UniqueKey attribute when a PK attribute is marked for refresh on Entity:Sev1
        [109] Built select: 'SELECT sev1_id, last_update_date FROM sakila.sev1'
        [110] Executing Refresh SQL...SELECT sev1_id, last_update_date FROM sakila.sev1 WHERE sev1_id=?
        [111] General failure in select : JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers.
        [112] Close prepared statement for refresh SQL
        [113] BaseSQLBuilder: rollbackToSavepoint 'BO_SP' ignored
        [114] DCBindingContainer.reportException :oracle.jbo.RowNotFoundException
        [115] oracle.jbo.RowNotFoundException: JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers.
        at oracle.jbo.server.BaseSQLBuilderImpl.doRefreshSQL(BaseSQLBuilderImpl.java:3424)
        at oracle.jbo.server.EntityImpl.doRefreshSQL(EntityImpl.java:10273)
        at oracle.jbo.server.EntityImpl.internalRefreshSQL(EntityImpl.java:10419)
        at oracle.jbo.server.BaseSQLBuilderImpl.doEntityDML(BaseSQLBuilderImpl.java:489)
        at oracle.jbo.server.EntityImpl.doDML(EntityImpl.java:8518)
        at oracle.jbo.server.EntityImpl.postChanges(EntityImpl.java:6775)
        ...
        ----------

        Show
        vesterli added a comment - I don't see how the JDBC driver can affect the issue - root cause is that ADF generates SQL including RETURNING clause which will only work in Oracle. Environment: 5.6.10 MySQL Community Server JDEVADF_11.1.2.3.0_GENERIC_120914.0223.6276.1 mysql-connector-java-5.1.23-bin.jar Test case: create table sev1 ( sev1_id int primary key auto_increment , sev1_desc varchar(80) , last_update_date timestamp default current_timestamp ); Build default EO, VO, AM on this (SQL92/Java). For sev1_id in EO set PrimaryKey="true" RetrievedOnInsert="true" IsUpdateable="while_insert" For last_update_date in EO set RetrievedOnInsert="true" Both sev1_id and last_update_date have Mandatory unchecked. Run BC tester, insert new row with only sev1_desc column value. Expect new record to be saved with auto ID and auto last_update_date. What happens is "(oracle.jbo.RowNotFoundException) JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers." BC tester log below. Extract from log: [103] INSERT INTO sakila.sev1(sev1_desc) VALUES [106] BaseSQLBuilder.doRefreshSQL, Executing Select for Refresh-on-insert or update attributes [107] Warning: Attempting to use current primary key attributes for refresh SQL. [108] ******* Found no UniqueKey attribute when a PK attribute is marked for refresh on Entity:Sev1 [109] Built select: 'SELECT sev1_id, last_update_date FROM sakila.sev1' [110] Executing Refresh SQL...SELECT sev1_id, last_update_date FROM sakila.sev1 WHERE sev1_id=? [111] General failure in select : JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers. ADF tells me it can't handle the situation with an auto-generated ID and another refreshOnInsert column. As I see it, on Oracle ADF uses the RETURNING clause. On MySQL, ADF could call LAST_INSERT_ID(), but apparently doesn't. My suggestion is that you implement a LAST_INSERT_ID call when database is MySQL. Best regards Sten Vesterli BTW, looking forward to JDev 12c! ---------- Full BC tester log: [94] INFO: No app def in BindingContext for: adfFacesContext [95] INFO: mDCRefMap lookup failed. Does the cpx have a dataControlUsages 'dc' entry? adfFacesContext [96] INFO: No app def in BindingContext for: adfFacesContext [97] INFO: mDCRefMap lookup failed. Does the cpx have a dataControlUsages 'dc' entry? adfFacesContext [98] BaseSQLBuilder: setSavepoint 'BO_SP' ignored [99] BaseSQLBuilder Executing DML ... (Insert) [100] Executing DML... [101] Warning: BaseSQLBuilder does not support RETURNING (key-cols) clause.... [102] Warning: BaseSQLBuilder does not support RETURNING (cols) clause.... [103] INSERT INTO sakila.sev1(sev1_desc) VALUES [104] Insert binding param 1: blabla [105] Fetch RefreshOnInsert attributes [106] BaseSQLBuilder.doRefreshSQL, Executing Select for Refresh-on-insert or update attributes [107] Warning: Attempting to use current primary key attributes for refresh SQL. [108] ******* Found no UniqueKey attribute when a PK attribute is marked for refresh on Entity:Sev1 [109] Built select: 'SELECT sev1_id, last_update_date FROM sakila.sev1' [110] Executing Refresh SQL...SELECT sev1_id, last_update_date FROM sakila.sev1 WHERE sev1_id=? [111] General failure in select : JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers. [112] Close prepared statement for refresh SQL [113] BaseSQLBuilder: rollbackToSavepoint 'BO_SP' ignored [114] DCBindingContainer.reportException :oracle.jbo.RowNotFoundException [115] oracle.jbo.RowNotFoundException: JBO-28203: The updated row cannot be refreshed because its key values are unset or modified by database triggers. at oracle.jbo.server.BaseSQLBuilderImpl.doRefreshSQL(BaseSQLBuilderImpl.java:3424) at oracle.jbo.server.EntityImpl.doRefreshSQL(EntityImpl.java:10273) at oracle.jbo.server.EntityImpl.internalRefreshSQL(EntityImpl.java:10419) at oracle.jbo.server.BaseSQLBuilderImpl.doEntityDML(BaseSQLBuilderImpl.java:489) at oracle.jbo.server.EntityImpl.doDML(EntityImpl.java:8518) at oracle.jbo.server.EntityImpl.postChanges(EntityImpl.java:6775) ... ----------
        Hide
        anandmanikutty added a comment - - edited

        The following information was provided by Sten Vesterli (talk of 7/3) regarding the repro:

        • This is a generic ADF issue. (Based on reading the java.net issue, it was not clear whether the issue was an ADF Essentials or not. Upon speaking with Sten, it is clear that the issue is not an ADF Essentials-only one.)
        • The problem was found when going thru the UI, i.e, the XML was not directly edited.

        ~
        I have since verified that the issue repros in the latest JDEVADF code base (verified next day). This issue is being actively tracked by development & PM (bug entered into bug database). Please contact me for bug-related information.
        -+

        Show
        anandmanikutty added a comment - - edited The following information was provided by Sten Vesterli (talk of 7/3) regarding the repro: This is a generic ADF issue. (Based on reading the java.net issue, it was not clear whether the issue was an ADF Essentials or not. Upon speaking with Sten, it is clear that the issue is not an ADF Essentials-only one.) The problem was found when going thru the UI, i.e, the XML was not directly edited. ~ I have since verified that the issue repros in the latest JDEVADF code base (verified next day). This issue is being actively tracked by development & PM (bug entered into bug database). Please contact me for bug-related information. -+
        Hide
        chriscmuir added a comment -

        Bug 16947080.

        As the MySQL environment is not something I can personally track or verify, and I maintain the ADF EMG issue tracking system which has no SLA, I'm going to close this issue here. For customers to pursue they'll need to follow this up through the normal SR channels.

        CM.

        Show
        chriscmuir added a comment - Bug 16947080. As the MySQL environment is not something I can personally track or verify, and I maintain the ADF EMG issue tracking system which has no SLA, I'm going to close this issue here. For customers to pursue they'll need to follow this up through the normal SR channels. CM.

          People

          • Assignee:
            Unassigned
            Reporter:
            vesterli
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: