Proper way to get generated key without using cache sync

[brad_c]

We are using v11.0p_2016-10-31/Enterprise Deployment (built 2016-10-31) and have a use case where we need to disable cache sync on an ADD operation in a custom DMI because the SELECT is causing a DB lock due to a large amount of logic being run in different processes that lock different tables used by both processes.

Is this the best way to get the generated key since I have the cache sync disabled? I need to use the generated key in a later step, and I don't need the full record that was inserted into the DB.

            DSRequest addDuplicateItemRequest = new DSRequest("lapModelItemsDS", DataSource.OP_ADD, rpcManager);
            addDuplicateItemRequest.setValues(newItemRecord);
            addDuplicateItemRequest.setOperationProperty("canSyncCache", "false");
            DSResponse response = addDuplicateItemRequest.execute();
            Map m = ((SQLDataSource)response.getDataSource()).getDriver().getJdbcPKs();

[Isomorphic]

Calling a series of undocumented APIs is never going to be the best way to do anything - those APIs aren't supported, so they might be changed or removed as part of a minor, unrelated patch.

Take a look at DataSource.sequenceMode for how you can control the approach used to fetch the keys for an auto-generated primary key.

[Blama]

Hi,

I'm pretty sure I have a similar use case in my code. As far as I remember I just go for the PK field in the DSResponse and this works (again, this is just from my memory).
I use sequence-PKs with no special sequenceMode setting in my .ds.xml or server.properties.

Best regards
Blama

[brad_c]

Isomorphic

The sequenceMode isn't my issue. Since I need to disable the cache sync, the DSResponse contains no data.

What API should I use to get the generated key from the ADD operation I performed without a cache sync?

[Blama]

Hi,

this is the log for an ADD here:

 === 2017-06-21 22:11:58,950 [c-14] DEBUG RPCManager - Processing 1 requests.
  === 2017-06-21 22:11:58,950 [c-14] DEBUG RPCManager - Request #1 (DSRequest) payload: {
      values:{
          SHORTNAME:"ABC",
          NAME:"ABC"
      },
      operationConfig:{
          dataSource:"T_PRODUCTCATEGORY",
          repo:null,
          operationType:"add",
          textMatchStyle:"exact"
      },
      componentId:"isc_ProductProductcategoryList_0",
      appID:"builtinApplication",
      operation:"T_PRODUCTCATEGORY_add",
      oldValues:{
          SHORTNAME:"ABC",
          NAME:"ABC"
      },
      criteria:{
      }
  }
  === 2017-06-21 22:11:58,951 [c-14] INFO  LMSIDACall - Performing 1 operation(s)
  === 2017-06-21 22:11:58,951 [c-14] DEBUG AppBase - [builtinApplication.T_PRODUCTCATEGORY_add] No userTypes defined, allowing anyone access to all operations for this application
  === 2017-06-21 22:11:58,951 [c-14] DEBUG AppBase - [builtinApplication.T_PRODUCTCATEGORY_add] No public zero-argument method named '_T_PRODUCTCATEGORY_add' found, performing generic datasource operation
  === 2017-06-21 22:11:58,952 [c-14] DEBUG AppBase - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
  === 2017-06-21 22:11:58,952 [c-14] DEBUG AppBase - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
  === 2017-06-21 22:11:58,952 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Performing fetch operation with
  outputs: [SHORTNAME, ID] criteria: {SHORTNAME:"ABC",TENANT_ID:1} values: {SHORTNAME:"ABC",TENANT_ID:1}
  === 2017-06-21 22:11:58,952 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
  === 2017-06-21 22:11:58,953 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] 602: Executing SQL query on 'Oracle': SELECT T_PRODUCTCATEGORY.ID, T_PRODUCTCATEGORY.SHORTNAME FROM T_PRODUCTCATEGORY WHERE (LOWER(T_PRODUCTCATEGORY.SHORTNAME)='abc' AND T_PRODUCTCATEGORY.TENANT_ID=1)
  === 2017-06-21 22:11:58,954 [c-14] DEBUG SQLTransaction - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Started new Oracle transaction "1217939084"
  === 2017-06-21 22:11:58,954 [c-14] DEBUG SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Setting DSRequest as being part of a transaction
  === 2017-06-21 22:11:58,954 [c-14] INFO  SQLDriver - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Executing SQL query on 'Oracle' using connection '1217939084': SELECT T_PRODUCTCATEGORY.ID, T_PRODUCTCATEGORY.SHORTNAME FROM T_PRODUCTCATEGORY WHERE (LOWER(T_PRODUCTCATEGORY.SHORTNAME)='abc' AND T_PRODUCTCATEGORY.TENANT_ID=1)
  === 2017-06-21 22:11:58,957 [c-14] INFO  DSResponse - [builtinApplication.T_PRODUCTCATEGORY_add] DSResponse: List with 0 items
  === 2017-06-21 22:11:58,958 [c-14] DEBUG AppBase - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
  === 2017-06-21 22:11:58,958 [c-14] DEBUG AppBase - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
  === 2017-06-21 22:11:58,959 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Performing fetch operation with
  outputs: [ID, NAME] criteria: {TENANT_ID:1,NAME:"ABC"} values: {TENANT_ID:1,NAME:"ABC"}
  === 2017-06-21 22:11:58,959 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
  === 2017-06-21 22:11:58,960 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] 602: Executing SQL query on 'Oracle': SELECT T_PRODUCTCATEGORY.ID, T_PRODUCTCATEGORY.NAME FROM T_PRODUCTCATEGORY WHERE (T_PRODUCTCATEGORY.TENANT_ID=1 AND LOWER(T_PRODUCTCATEGORY.NAME)='abc')
  === 2017-06-21 22:11:58,960 [c-14] DEBUG SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Setting DSRequest as being part of a transaction
  === 2017-06-21 22:11:58,960 [c-14] INFO  SQLDriver - [builtinApplication.T_PRODUCTCATEGORY_add, builtinApplication.null] Executing SQL query on 'Oracle' using connection '1217939084': SELECT T_PRODUCTCATEGORY.ID, T_PRODUCTCATEGORY.NAME FROM T_PRODUCTCATEGORY WHERE (T_PRODUCTCATEGORY.TENANT_ID=1 AND LOWER(T_PRODUCTCATEGORY.NAME)='abc')
  === 2017-06-21 22:11:58,961 [c-14] INFO  DSResponse - [builtinApplication.T_PRODUCTCATEGORY_add] DSResponse: List with 0 items
  === 2017-06-21 22:11:58,962 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add] Performing add operation with
  criteria: {SHORTNAME:"ABC",NAME:"ABC",CREATED_BY:"1",CREATED_AT:new Date(1498075918951),MODIFIED_BY:"1",MODIFIED_AT:new Date(1498075918951)} values: {SHORTNAME:"ABC",NAME:"ABC",CREATED_BY:"1",CREATED_AT:new Date(1498075918951),MODIFIED_BY:"1",MODIFIED_AT:new Date(1498075918951),TENANT_ID:1}
  === 2017-06-21 22:11:58,962 [c-14] DEBUG SQLValuesClause - [builtinApplication.T_PRODUCTCATEGORY_add] Sequences: {ID=__default}
  === 2017-06-21 22:11:58,962 [c-14] DEBUG SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add] Setting DSRequest as being part of a transaction
  === 2017-06-21 22:11:58,962 [c-14] INFO  SQLDriver - [builtinApplication.T_PRODUCTCATEGORY_add] Executing SQL query on 'Oracle' using connection '1217939084': INSERT INTO T_PRODUCTCATEGORY (CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY, NAME, SHORTNAME, TENANT_ID, ID) VALUES (TO_DATE('2017-06-21 22:11:58','YYYY-MM-DD HH24:MI:SS'), '1', TO_DATE('2017-06-21 22:11:58','YYYY-MM-DD HH24:MI:SS'), '1', 'ABC', 'ABC', 1, T_PRODUCTCATEGORY_ID.NextVal)
  === 2017-06-21 22:11:58,972 [c-14] DEBUG SQLDriver - [builtinApplication.T_PRODUCTCATEGORY_add] SequenceMode is not JDBC_DRIVER, skipping search for generated values
  === 2017-06-21 22:11:58,972 [c-14] DEBUG SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add] add operation affected 1 rows
  === 2017-06-21 22:11:58,972 [c-14] DEBUG SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add] Setting DSRequest as being part of a transaction
  [B]=== 2017-06-21 22:11:58,972 [c-14] INFO  SQLDriver - [builtinApplication.T_PRODUCTCATEGORY_add] Executing SQL query on 'Oracle' using connection '1217939084': SELECT T_PRODUCTCATEGORY_ID.CurrVal FROM DUAL[/B]
  [B]=== 2017-06-21 22:11:58,973 [c-14] INFO  SQLDataSource - [builtinApplication.T_PRODUCTCATEGORY_add] primaryKeys: {ID=131}[/B]

Afterwards there is the cacheSync. But with this you'll have the PK in the DSResponse if you omit the cacheSync, IMHO.
As I did not set a sequenceMode, the mode used is native.

Best regards
Blama

[Isomorphic]

You haven't explained anything about how or why cacheSync is causing you issues, but, the sequenceMode setting gives you multiple ways to control how cacheSync occurs in the presence of a field of type "sequence", one of which ("jdbcDriver") is roughly equivalent to the series of unsupported APIs you are trying to call.

So, to spell out our suggestion more explicitly: turn cacheSync back on and try these other modes. If there's still an issue, let us know what specifically you are trying to avoid.

[brad_c]

@Isomorphic,

Apologies for the late reply, I've been on vacation a lot recently...

My datasource is updating/inserting into multiple tables in a single transaction. The cache sync on the last INSERT occurs in the same transaction as the UPDATE/INSERT statements. The SELECT for the cache sync operation joins on multiple views which use multiple tables - one of which may be locked for update/insert by another process.
When these two processes are executed around the same time, both tables may end up locked since they are waiting on each other to release table locks.

Once I disabled the cache sync, the locking issue went away, but I later added code that needs the generated ID in that same process. I couldn't find an API on the response/request/datasource to get the generated ID (besides the undocumented SQLDatasource API in my example above) and disabling cache causes the DSResponse to return null when you call getRecord(). So I need some API call to get the generated ID.

We use SQL Server and are using the default of "jdbcDriver" as the sequenceMode for the Datasource. If I switch it to "native", I get a NPE as seen below. I can't use "none" as then I have to use the cache sync to get the ID.

But I still don't see how changing sequenceMode will help me since I still need an API to get the generated ID.

 === 2017-07-17 21:08:06,896 [7-45] [INFO] SQLDataSource - [builtinApplication.replaceItem, builtinApplication.null] Performing add operation with
  criteria: {sourceId:"APP",q_ast_id:315,modelId:28,releasedFlag:false,advertisedFlag:false,display:"13295-49:Zip Frt W/Quilted Warmer:Maroon",slIncludeFlag:true,loadDate:new Date(1500343681503),itemId:4471,tbdFlag:false,includeFlag:true,lastUpdate:new Date(1500343681503),duplicateFlag:true,sortby:1,createUser:"brad"} values: {sourceId:"APP",q_ast_id:315,modelId:28,releasedFlag:false,advertisedFlag:false,display:"13295-49:Zip Frt W/Quilted Warmer:Maroon",slIncludeFlag:true,loadDate:new Date(1500343681503),itemId:4471,tbdFlag:false,includeFlag:true,lastUpdate:new Date(1500343681503),duplicateFlag:true,sortby:1,createUser:"brad"}
  === 2017-07-17 21:08:06,897 [7-45] [INFO] SQLValuesClause - [builtinApplication.replaceItem, builtinApplication.null] Ignored data for non-existent or included columns: [q_ast_id, display]
  === 2017-07-17 21:08:06,905 [7-45] [DEBUG] SQLDataSource - [builtinApplication.replaceItem, builtinApplication.null] Setting DSRequest as being part of a transaction
  === 2017-07-17 21:08:06,906 [7-45] [INFO] SQLDriver - [builtinApplication.replaceItem, builtinApplication.null] Executing SQL query on 'AppDB' using connection '632041597': INSERT INTO LAP_MDL_ITM (LAP_MDL_ITM_ADVRT_FLG, CRT_USR, LAP_MDL_ITM_DUP_FLG, LAP_MDL_ITM_INCL_FLG, Q_ITM_ID, LAST_UPDATE, LOAD_DATE, LAP_MDL_ID, LAP_MDL_ITM_RLSD_FLG, LAP_MDL_ITM_SL_INCL_FLG, LAP_MDL_ITM_SORT, SRC_ID, LAP_MDL_ITM_TBD_FLG) VALUES (0, 'brad', 1, 1, 4471, '2017-07-17T21:08:01', '2017-07-17T21:08:01', 28, 0, 1, 1, 'APP', 0)
  === 2017-07-17 21:08:06,912 [7-45] [DEBUG] SQLDriver - [builtinApplication.replaceItem, builtinApplication.null] SequenceMode is not JDBC_DRIVER, skipping search for generated values
  === 2017-07-17 21:08:06,912 [7-45] [DEBUG] SQLDataSource - [builtinApplication.replaceItem, builtinApplication.null] add operation affected 1 rows
  === 2017-07-17 21:08:06,914 [7-45] [DEBUG] SQLDataSource - [builtinApplication.replaceItem, builtinApplication.null] Setting DSRequest as being part of a transaction
  === 2017-07-17 21:08:06,914 [7-45] [INFO] SQLDriver - [builtinApplication.replaceItem, builtinApplication.null] Executing SQL query on 'AppDB' using connection '632041597': SELECT SCOPE_IDENTITY()
  === 2017-07-17 21:08:54,588 [7-45] [WARN] RequestContext - dsRequest.execute() failed:
  java.lang.NullPointerException
  at com.isomorphic.sql.SQLServerDriver.fetchLastPrimaryKeys(SQLServerDriver.java:319)
  at com.isomorphic.sql.SQLDataSource.getLastPrimaryKeys(SQLDataSource.java:833)
  at com.isomorphic.sql.SQLDataSource.getLastRow(SQLDataSource.java:664)
  at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:2173)
  at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:449)
  at com.isomorphic.sql.SQLDataSource.executeAdd(SQLDataSource.java:402)

[Isomorphic]

We are looking into this. In the meantime, we are interested in this NPE because it arises in circumstances where the value should never be null. Could you please:

• Share the version numbers of your SQL Server database and JDBC driver
• Let us know the setting of "useGlobalIdentity" (if any) in your server.properties file
• Confirm that the table "LAP_MDL_ITM" has an identity column which is mapped to a DataSource field of type "sequence" which is declared as the DataSource's only primaryKey field (or tell us otherwise)

[Isomorphic]

Actually, this NPE is a known issue when trying to use native techniques to get identity column values with more recent SQL Server JDBC drivers. The solution is to use sequenceMode "jdbcDriver" instead, which is what you were originally doing anyway. We have changed the SQLServerDriver class to log this information out as a warning, rather than dumping an Exception.

We have now added a supported API, DSResponse.getKeys(), which can be used to obtain the generated keys whether or not we go ahead with cache sync. This API will be in builds of 12.0 as of tomorrow, and going forward it is the API you should use. However, since it is a new feature, and you have a workaround in 11.0, we are not intending to backport it to any bugfix branches. Although you are using undocumented/unsupported APIs in your workaround, we see no immediate reason why this would break on the 11.0 release.

[brad_c]

I was just writing a reply with version info. As you stated, we are using a recent version of SQL Server JDBC drivers.
Is the logging change to SQLServerDriver going in version 11.0 ?

Thanks for adding the API for retrieving keys. We will continue using the workaround until we upgrade version

• Microsoft JDBC Driver 6.0 for SQL Server - Jan 2017
• Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (Intel X86)
  • Developer Edition on Windows NT 6.3 <X64> (Build 14393: ) (WOW64)
• useGlobalIdentity is not set in server.properties.
• The id column is a sequence for LAP_MDL_ITM.

[Isomorphic]

Yes, the change to warn about SQL Server and sequenceMode "native" has been ported back as far as SmartClient 10.1 / SmartGWT 5.1