Optimistic concurrency

[tmoes]

Hello,

what is the best way to realize "Optimistic Concurrency" at >SQL Datasource<.
I don't no where and how i can manipulate the sql where condition with oldValue.

1) I cant find the "where condition" in com.isomorphic.datasource.DSRequest (see javaclass)

2) I cant find a equivalent to $values like $oldvalues for <whereClause> in DS

I tried

..ds.xml

<operationBinding operationType="update">  
  <serverObject className="com.payfits.server.ds.DataSourceAccess" lookupStyle="new" />  
  <serverMethod>updateRecord</serverMethod>
</operationBinding>

and javaclass:

 
public DSResponse updateRecord(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
	
	//where can i manipulate the sql "where conditions"???
	dsRequest.getOldValues().get("technicalfieldlastupdate");
	
	//Call Database
	DSResponse response = dsRequest.execute();
	
	// no row update, means a OptimisticLockException
	if (response.getAffectedRows() == 0) {
		response.addError("technicalfieldlastupdate", "OptimisticLockException!!!!");
		response.setProperty("technicalfieldlastupdate", "OptimisticLockException!!!!");
		response.setFailure();
	}
	
	return response;
}

with smartGWTee 2.2 eval and >SQL Datasource<

Thanks for your help.
Cheers

[Isomorphic]

Not really sure what you're trying to accomplish, but take a look at this sample for modifying the DSRequest via Java code before the SQL statements are formed.

[tmoes]

I know this example, but there is nothing in the whereClause with an oldValue.
The user may only update when the record was not changed even in the meantime.

How can i set/modify the whereClause within DMI javaclass?

[Isomorphic]

If you mean that you want to check that the record is unchanged before saving changes, use new DSRequest(), populate the criteria of the new DSRequest with the oldValues from the current DSRequest, execute() the new DSRequest. If it hits a record, the values haven't changed.

If you mean something else, try to explain more clearly what you want.

[tmoes]

you are right, i want to check if the record is unchanged before saving changes.

Did I understand correctly that I should use my "check field e.g. timestamp" as a criteria?

// timestamp to check
dsRequest.setCriteria("technicalfieldlastupdate", dsRequest.getOldValues().get("technicalfieldlastupdate"));

this criteria has unfortunately no effect on the whereClause in my case.

[Isomorphic]

No. First of all, you need to do new DSRequest, second, criteria is set as a Map, not a single field. Please spend some time with the JavaDoc, then post if things are not clear.

[tmoes]

Hi Isomorphic,

now i created a new com.isomorphic.datasource.DSRequest in DMI Class, e.g. copy from current DSRequest .
But i cant find the oldValue (timestamp) at whereClause! there is only the primarykey inside.

INFO SQLDriver - [builtinApplication.null] Executing SQL update on 'Mysql': UPDATE payfits.B2B_BH SET useridCreator='test' WHERE (B2B_BH.pkid='1')

What is wrong in my implementation? I have found nothing in the Javadoc to.

thanks for your help

public DSResponse updateRecord(DSRequest dsRequest, HttpServletRequest servletRequest, RPCManager rpcManager) throws Exception {
		
	DSRequest dsRequest2 = new DSRequest();
	dsRequest2.setRPCManager(rpcManager);
	dsRequest2.setDataSource(dsRequest.getDataSource());
	dsRequest2.setDataSourceName(dsRequest.getDataSourceName());
	dsRequest2.setComponentId(dsRequest.getComponentId());
	dsRequest2.setAppID(dsRequest.getAppID());
	dsRequest2.setOperationConfig(dsRequest.getOperationConfig());
	dsRequest2.setOperationType(DataSource.OP_UPDATE);
	
	// set the oldValue to criteria
	dsRequest2.setCriteria(dsRequest.getOldValues());
	
	//set the values
	dsRequest2.setValues(dsRequest.getValues());

	dsRequest2.setRequestContext(dsRequest.getRequestContext());
	dsRequest2.setBeenThroughDMI(dsRequest.getBeenThroughDMI());
	dsRequest2.setBeenThroughValidation(dsRequest.getBeenThroughValidation());

	//execute call
	DSResponse response = dsRequest2.execute();
	
	// no row update, means a OptimisticLockException
	if (response.getAffectedRows() == 0) {
		response.addError("technicalfieldlastupdate", "OptimisticLockException!!!!");
		response.setProperty("technicalfieldlastupdate", "OptimisticLockException!!!!");
		response.setFailure();
	}
	
	return response;
}

[Isomorphic]

??

You want to do a *fetch* before you allow the default *update* to proceed, right?

Your code shows an update only, no fetch.. basically it doesn't seem to make any sense.

[tmoes]

Actually I only wanted to perform an update NO fetch before. Would you recommend that?

usecase:
Step 1) user fetch all records from database into listgrid
Step 2) user modified some fields
Step 3) a update request sends to server (dmi)
Step 3 - a) when timestamp are equal = update record
Step 3 - b) when timestamp not equal = (getAffectedRows() == 0) = errorMessage

aktually:
UPDATE payfits.B2B_BH SET useridCreator='test' WHERE (B2B_BH.pkid='1')

favored:
UPDATE payfits.B2B_BH SET useridCreator='test' WHERE (B2B_BH.pkid='1' AND B2B_BH.technicalfieldlastupdate='2010-04-05 05:00:00')

[tmoes]

i found it:

dsRequest2.setAllowMultiUpdate(true);

With this code I get the desired whereClause. All criteria parameter as whereCause.

Is this approach wrong?

Thanks

[Isomorphic]

Yes, that approach is wrong. If all you want to do is add your "lastupdate" field to the criteria, you are doing something basically identical to the example you were linked to in our first response.

Delete everything you've written and follow that example. You just need to modify the criteria of the existing request to include a value from oldValues, nothing more.

You are also calling many internal APIs. Refer to the JavaDoc for which methods are documented and supported. Other methods, even though marked with the Java "public" qualifier, are not supported.

[tmoes]

ok, maybe I wanted it too complicated. :-)
now i check if the record is unchanged (fetch first) before saving changes.

Hopefully I have now uses only "official" methods. :-)
Thanks for your patience


INFO:

public DSResponse updateRecord(DSRequest dsRequestUPDATE, HttpServletRequest servletRequest) throws Exception {
	DSResponse response = null;
	
	// for FETCH first
	DSRequest dsRequestFETCH = new DSRequest();
	dsRequestFETCH.setDataSource(dsRequestUPDATE.getDataSource());
	dsRequestFETCH.setDataSourceName(dsRequestUPDATE.getDataSourceName());
	dsRequestFETCH.setOperationType(DataSource.OP_FETCH);
	Map<String, Object> criteria = new HashMap<String, Object>();
	
	//Lastupdate Field
	criteria.put("technicalfieldlastupdate", dsRequestUPDATE.getOldValues().get("technicalfieldlastupdate"));
	//Primary Key
	criteria.put("pkid", dsRequestUPDATE.getValues().get("pkid"));
	dsRequestFETCH.setCriteria(criteria);

	// execute Fetch Operation first
	DSResponse responseFETCH = dsRequestFETCH.execute();

	// if record identical = update
	if (responseFETCH.getRowCount() > 0) {
//		dsRequestUPDATE.setFieldValue("useridLastupdate", servletRequest.getRemoteUser());
		dsRequestUPDATE.setFieldValue("technicalfieldlastupdate", new Date());
		//execute update call
		response = dsRequestUPDATE.execute();
	}else{
		// no row found, means a OptimisticLockException
		response = new DSResponse();
		response.addError("technicalfieldlastupdate", "OptimisticLockException!!!!");
		response.setProperty("technicalfieldlastupdate", "OptimisticLockException!!!!");
		response.setFailure();
	}
	
	return response;
}

[Isomorphic]

You're still overcomplicating things. Given that you have an explicit last updated field, you can just do a single update by adding the last update as criteria, with no fetch. Very nearly a one-liner.

[tmoes]

I like it when it is more complicated :-).......

My implementation still has an advantage: I could return the new values to the client, or perform a forced update, or or or (It is an implementation for the future)

:-)

I love this Framwork, very good work.

[Isomorphic]

True, but keep in mind, you could also postpone the fetch of the old values so that it's only done when the update fails - this would be more efficient.