getLastPrimiaryKets() called before valid insert/replace/update operation has been performed

[Isomorphic]

Hi there,

We've got to the bottom of this - the upshot for you is that you should be using the 3-parameter signature of DSRequest creation (which takes an RPCManager), because that's both more efficient and maintains transaction context. With your current approach, you end up unnecessarily allocating an additional DataSource as well as an additional SQLConnection.

This change is better for efficiency and happens to ensure correctness for this case.

Separately, we are also looking at making it so that your current code still works even if inefficiently. Just for context, your code precisely threads the needle between several mechanisms that were designed to ensure backwards compatibility: you had to specifically call executeAdd(), call super(), etc in order to dodge the multiple mechanisms we put in place. Had you done anything else (called dsRequest.execute(), just called super(), etc) your code would have continued to execute as it had before.

Ultimately, it's good that you found this particular pathway, as we can make changes so that even this unusual pathway still has full backwards compatibility.

However, it's no longer relevant to you since by using the correct DSRequest constructor you get both greater efficiency and correct execution.

[tece321]

Do you have an update on this issue?

Why do we have to setDataSource on a DSRequest created by :

DSRequest addRow = new DSRequest(dsName, "add");

when the DSRequest should be initialized with the DataSource?

[tece321]

Could we also ask you to please try your use case with the latest 13.0 build - there have been changes made in this area since April 25, so your issue may already be fixed.

I have tested against release 2023-08-01 and the problem/bug remains. No difference in behavior.

[tece321]

Can you please share the code in beer.server.ProjectSchedules.executeUpdate(), and the .ds.xml file for the corresponding dataSource (presumably ProjectSchedules)?

The code of ProjectSchedules.executeUpdate has been posted in the very first post in this thread. I have posted the DS to support@isomorphic.com. It is BCG_UIProjectChecklist.ds.xm.

Thanks

[Isomorphic]

Can you please share the code in beer.server.ProjectSchedules.executeUpdate(), and the .ds.xml file for the corresponding dataSource (presumably ProjectSchedules)? If you don't want to share them publicly, you can email them to support@isomorphic.com. Could we also ask you to please try your use case with the latest 13.0 build - there have been changes made in this area since April 25, so your issue may already be fixed.

[Isomorphic]

Wow! Thanks for figuring that out. We hadn't gotten all the way into this yet, but that's a big clue.

[eschwegs]

I stumbled across the bug.

In the original code segment I posted, the addRow DSRequest object is instantiated with the contractor that takes the DataSource Name. When I redundantly set the datasource by instance as well, the getLastPrimaryKey issues does not appear.
i.e.

    DSRequest addRow = new DSRequest(dsName, "add");
    addRow.setDataSource(req.getDataSource());    //  This line was not in the original code segment

[tece321]

SmartClient Version: v13.0p_2023-04-25/PowerEdition Deployment (built 2023-04-25)


This is what the server side console log contains:

=== 2023-07-27 11:52:14,209 [-574] DEBUG SQLValuesClause - Sequences: {PK_BCG_UIProjectChecklist=__default}
=== 2023-07-27 11:52:14,308 [-574] INFO SQLDriver - DSRequest has no DSTransaction set, when testing if we should join a transaction - transaction will not be joined
=== 2023-07-27 11:52:14,755 [-574] DEBUG PoolableSQLConnectionFactory - makeObject() created an unpooled Connection '1990777090'
=== 2023-07-27 11:52:14,757 [-574] DEBUG SQLConnectionManager - Borrowed connection '1990777090'
=== 2023-07-27 11:52:14,760 [-574] INFO SQLDriver - Executing SQL query on 'QA' using connection '1990777090': INSERT INTO BCG_UIProjectChecklist (Action, Category, FK_LastModifiedBy, FK_Userid_Accountable, FK_Userid_Responsible, ItemOrder, LastModifiedOn, PercentageComplete) VALUES ('Adding new row T1', 'IO import', 30, '[47705]', '[HW Lead]', '4.11', '2023-07-27T18:51:48', 0)
=== 2023-07-27 11:52:14,826 [-574] DEBUG SQLDataSource - DataSource 11562 acquired SQLDriver instance 731525260 during initialization
=== 2023-07-27 11:52:14,862 [-574] DEBUG SQLDriver - Found 1 sequence(s)/autoGenerated PK field(s): [PK_BCG_UIProjectChecklist]
=== 2023-07-27 11:52:14,869 [-574] DEBUG SQLDriver - Discovered zero or one generated key via JDBC: {PK_BCG_UIProjectChecklist=732714}
=== 2023-07-27 11:52:14,898 [-574] INFO SQLDataSource - primaryKeys: {}
=== 2023-07-27 11:52:14,901 [-574] DEBUG SQLDataSource - Added JDBC-generated values to the list of primaryKeys
=== 2023-07-27 11:52:14,907 [-574] DEBUG SQLDataSource - Gathered all keys. lastPrimaryKeys is now {PK_BCG_UIProjectChecklist=732714}
=== 2023-07-27 11:52:14,908 [-574] DEBUG SQLDataSource - add operation affected 1 rows
=== 2023-07-27 11:52:14,917 [-574] DEBUG SQLDataSource - Running cache sync eagerly
=== 2023-07-27 11:52:14,921 [-574] INFO SQLRefetchStrategy - BCG_UIProjectChecklist: using default operationBinding
=== 2023-07-27 11:52:17,931 [-574] DEBUG DSRequest - About to free up resources for request of type update on DataSource BCG_UIProjectChecklist
=== 2023-07-27 11:52:17,931 [-574] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 11560
=== 2023-07-27 11:52:17,931 [-574] DEBUG SQLDriver - Freeing SQLDriver dbConnection 1990777090 for SQLDriver instance 596708448
=== 2023-07-27 11:52:17,931 [-574] DEBUG SQLConnectionManager - About to close SQLServerConnection with hashcode "1990777090"
=== 2023-07-27 11:52:17,932 [-574] WARN RequestContext - dsRequest.execute() failed:
java.lang.Exception: getLastPrimaryKeys() called before valid insert/replace/update operation has been performed
at com.isomorphic.sql.SQLDataSource.getLastPrimaryKeys(SQLDataSource.java:785)
at com.isomorphic.sql.cachesync.SQLRefetchStrategy.getCacheSyncData(SQLRefetchStrategy.java:39)
at com.isomorphic.sql.SQLDataSource.getLastRow(SQLDataSource.java:719)
at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:2178)
at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1802)
at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:481)
at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:433)
at beer.server.ProjectSchedules.executeUpdate(ProjectSchedules.java:901)
at com.isomorphic.datasource.DataSource.execute(DataSource.java:2848)
at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:658)
at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:555)
at com.isomorphic.application.AppBase.execute(AppBase.java:498)
at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:3297)
at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:230)
at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:123)
at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:178)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669)
at beer.server.NoCache.doFilter(NoCache.java:31)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:263)
at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:91)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at beer.server.DevelopmentAuthenticationFilter.doFilter(DevelopmentAuthenticationFilter.java:51)


Note this log message: === 2023-07-27 11:52:14,907 [-574] DEBUG SQLDataSource - Gathered all keys. lastPrimaryKeys is now {PK_BCG_UIProjectChecklist=732714}

So it seems like the lastPrimaryKeys() has been executed, and a value attained (732714) but we still get this exception:

java.lang.Exception: getLastPrimaryKeys() called before valid insert/replace/update operation has been performed

One other KEY piece of information is that we DON'T see this problem on SmartGWT 12.1 version from Oct. 2022.

[tece321]

SmartClient Version: v13.0p_2023-04-25/PowerEdition Deployment (built 2023-04-25)

Isomorphic:

]It turns out that SCOPE_IDENTITY() is completely broken in all JDBC drivers from Microsoft since one released in 2000 which can no longer be downloaded from Microsoft (yes, really).

Download 2.3, we implemented a workaround there.

This is from back in 2011.

Is it possible that the JDBC or ODBC driver, latest edition, is broken ? I will check with our IT department to see if the JDBC driver has recently been updated on our SQL Servers.


We don't see the problem with older releases of our app running 12.1 SmartGWT from Oct. 2022.

[eschwegs]

Moving from smartGWT12.1 to v13.0p_2023-04-25/ we came across a grid that no longer saves user changes. The UI spits out the Warning pop up : "getLastPrimiaryKeys() called before valid insert/replace/update operation has been performed" . Our ListGrid uses ListGrid.startEditingNew(...) and saveAllEdits() to create a new record, initializing some fields like modified time in the code and user facing fields via standard List Grid cell edit user input.

Has anything changed that would cause that warning?

Also, in the server constructor's executeUpddate routine I'm looking at code that determines that a record is brand new via getValues() PrimaryKey == -1, then creates a new DSRequest to add the base record with a super.executeAdd(). The add uses the serverConstructors executeUpdate methods DSRequest parameter's getOldValues() as the values to add for the new record. The values the user added via the ListGrid row edits are contained in the request's getNewValues(). They would be added next, by a super.executeUpdate() call, if not for the Warning that gets produces from the Add.

i.e.

    @Override
    public DSResponse executeUpdate(DSRequest req) throws Exception {

        if ("vanillaUpdate".equalsIgnoreCase(req.getOperationId())) {
            return super.executeUpdate(req);
        }

        com.isomorphic.datasource.DataSource ds = req.getDataSource();
        String dsName = ds.getName();
        String keyName = ds.getPrimaryKey();

        Map<String, Object> recordMap = req.getValues();

        if (recordMap != null && recordMap.containsKey(keyName) && (Long) recordMap.get(keyName) == -1) {
            recordMap.remove(keyName);

            // There seems to be an issue when adding new Checklist items - specifically
            // with the multiple FK_Userid_* fields
            // Adding transforms to an encoded list, while updates keep the string
            // (correctly) intact.
            // So add in 2 parts:
            // 1. Add the record with just the basics from oldValues( FK_idProject, tabId,
            // etc)
            // 2. Update the remaining fields after you've inserted the record...

            DSRequest addRow = new DSRequest(dsName, "add");
            if ( req.getOldValues() != null) {
                addRow.setValues(req.getOldValues());
            }
            DSResponse addResponse = super.executeAdd(addRow); // esdebug why isn't this working like in 12.1? debug shows it kicks out here.

            List<Map> queryResults = addResponse.getDataList();

            Map map = queryResults.get(0);
            for (Object key : map.keySet()) {
                if (!recordMap.containsKey(key.toString())) {
                    recordMap.put(key.toString(), map.get(key));
                }
            }
            req.setValues(recordMap);
            req.setCriteria(keyName, map.get(keyName));

            return super.executeUpdate(req);
        }
        return super.executeUpdate(req);
    }