Custom Update Example?

[mandrachek]

I have a datasource, that includes a reference to another row in the same table. I've got the select working ok, but I haven't been able to find a concrete example of how to setup and operationBinding for updates and deletes that helps me out in this scenario.

<DataSource 
	schema="dbo"
	dbName="SQLServer"
	tableName="L_DATA"
	ID="L_DATA"
	dataSourceVersion="1"
	serverType="sql"
	autoDeriveSchema="true"
>
	<fields>
		<field sqlType="integer" primaryKey="true" sqlLength="10" name="DATA_ID" type="sequence" hidden="true"></field>
		<field sqlType="char" sqlLength="1" name="ASP" length="1" type="text" title="PREFIX" width="48"></field>
		<field sqlType="varchar" sqlLength="7" name="CODE" length="7" type="text" title="CODE" width="96"></field>
		<field sqlType="varchar" sqlLength="80" name="TITLE" length="80" type="text" width="288"></field>
		<field sqlType="varchar" sqlLength="20" name="SHORT_TITLE" length="20" type="text" width="120"></field>
		<field sqlType="integer" sqlLength="10" name="TRANS_ID" type="integer" hidden="true" canHide="false"></field>
 		<field sqlType="varchar" sqlLength="7" name="TRANSTO" customSelectExpression="L_DATA_TRANS.PREFIX + L_DATA_TRANS.CODE" title="Transfer To" width="120"></field>
	</fields>
	<operationBindings>
		<operationBinding operationType="fetch">
			<tableClause>
			    L_DATA
			    LEFT OUTER JOIN L_DATA AS L_DATA_TRANS ON L_DATA.TRANS_ID = L_DATA_TRA S.DATA_ID
			</tableClause>
		</operationBinding>
	</operationBindings>
</DataSource>

The TRANSTO field should be text, not a SelectItem or ComboBoxItem, which is why I didn't set it to the displayField for the TRANS_ID field, and why it's set to hidden, with canHide set to false (this should prevent the column from being made visible right?). There's lots of possible combinations, and I don't want to use a picklist. So, on add or update, I need to take the text entered in the TRANSTO field to look up the TRANS_ID, and use that value, based on the composite key (PREFIX + CODE) contained in the TRANSTO.

Can somebody help me out with this, or point me to relevant documentation and/or code samples?

[mandrachek]

FYI, when attempting to change the value of that field, I get the following message (including typo "opertaion") in a dialog:

"Insert, update or replace operation is not a full query; check DataSource configuration (operationBinding, valuesClause);check submitted parameters (valuesClause, command) for opertaion."

In the console, I get:

=== 2013-01-29 18:45:32,674 [sor3] DEBUG AppBase - [builtinApplication.L_DATA_update] No public zero-argument method named '_L_DATA_update' found, performing generic datasource operation
=== 2013-01-29 18:45:32,675 [sor3] INFO  SQLDataSource - [builtinApplication.L_DATA_update] Performing update operation with
	criteria: {DATA_ID:1}	values: {DATA_ID:1,TRANSTO:"XYZ"}
=== 2013-01-29 18:45:32,675 [sor3] INFO  SQLValuesClause - [builtinApplication.L_DATA_update] Ignoring value for TRANSTO since it has a customSelectExpression or customSQLExpression but no customUpdateExpression
=== 2013-01-29 18:45:32,675 [sor3] WARN  SQLDataSource - [builtinApplication.L_DATA_update] Insert, update or replace operation is not a full query; check DataSource configuration (operationBinding, valuesClause);check submitted parameters (valuesClause, command) for opertaion.
=== 2013-01-29 18:45:32,676 [sor3] WARN  RequestContext - dsRequest.execute() failed: 
java.lang.Exception: Insert, update or replace operation is not a full query; check DataSource configuration (operationBinding, valuesClause);check submitted parameters (valuesClause, command) for opertaion.
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1314)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:306)
	at com.isomorphic.sql.SQLDataSource.executeUpdate(SQLDataSource.java:254)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1383)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:726)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2227)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:216)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:173)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:138)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:74)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
	at com.isomorphic.js.JSSyntaxScannerFilter.doFilter(JSSyntaxScannerFilter.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:259)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
	at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
	at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
	at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
	at java.lang.Thread.run(Thread.java:680)

I'm not sure, but I don't think a customUpdateExpression would work for what I need to do (split the value apart, lookup id, set id value).

[Isomorphic]

We haven't actually run this, but it seems like something along these lines will do what you want (assuming that the PREFIX part of TRANSTO is 1 char long):

<field sqlType="integer" sqlLength="10" name="TRANS_ID" type="integer" hidden="true" canHide="false" customUpdateExpression="(select TRANS_ID from L_DATA where PREFIX = $values.TRANSTO.substring(0, 1) and CODE = $values.TRANSTO.substring(1))"></field>

Incidentally, the canHide property just controls whether a user can remove a field from a ListGrid using the customization UI that pops up when a grid header is right-clicked.

[rasape]

Hi Mandrachek,

Did you managed to get the update work? I'm facing the same problem and I'm really going crazy!!!