Override Update with call to Stored Proc

Smart GWT Technical Q&A
1

Hi. I have a data source defined with an override on the UPDATE call to use a stored proc, like so:

<DataSource 
	dbName="mydb"
	tableName="mytbl"
	ID="myDS"
	dataSourceVersion="1"
	generatedBy="SC_SNAPSHOT-2009-12-17/EVAL Deployment 2009-12-17"
	serverType="sql"
>
	<fields>
		<field name="wssSourceID" type="integer"></field>
		<field name="watchID" type="integer"></field>
	</fields>
	
	<operationBindings>
		<operationBinding operationType="update" allowMultiUpdate="true">
			<customSQL>call spUpdateWSSWatchSources(_watchID, wssSourceIDs)</customSQL>
		</operationBinding>
	</operationBindings>
</DataSource>

I manually serialize my wssSourceIDs as a comma-delimited string, and let my stored proc do the rest. When I pass the values in from the DB’s query window, I have no issues using the same data as what I see in the debug log (the exception I get with SmartGWT is at the end):

=== 2010-05-16 20:09:44,209 [l0-5] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
    },
    values:{
        _watchID:"63231",
        wssSourceIDs:"7,9,10,11,12"
    },
    operationConfig:{
        dataSource:"myDS",
        operationType:"update"
    },
    appID:"builtinApplication",
    operation:"myDS_update",
    oldValues:{
        _watchID:"63231",
        wssSourceIDs:"7,9,10,11,12"
    }
}
=== 2010-05-16 20:09:44,209 [l0-5] INFO  IDACall - Performing 1 operation(s)
=== 2010-05-16 20:09:44,209 [l0-5] DEBUG AppBase - [builtinApplication.myDS_update] No userTypes defined, allowing anyone access to all operations for this application
=== 2010-05-16 20:09:44,225 [l0-5] DEBUG AppBase - [builtinApplication.myDS_update] No public zero-argument method named '_myDS_update' found, performing generic datasource operation
=== 2010-05-16 20:09:44,225 [l0-5] INFO  SQLDataSource - [builtinApplication.myDS_update] Performing update operation with
	criteria: {}	values: {_watchID:"63231",wssSourceIDs:"7,9,10,11,12"}
=== 2010-05-16 20:09:44,225 [l0-5] INFO  SQLValuesClause - [builtinApplication.myDS_update] Ignored data for non-existent columns: [_watchID, wssSourceIDs]
=== 2010-05-16 20:09:44,225 [l0-5] INFO  SQLWhereClause - [builtinApplication.myDS_update] empty condition
=== 2010-05-16 20:09:44,490 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.myDS_update] Returning pooled Connection
=== 2010-05-16 20:09:44,506 [l0-5] INFO  SQLDriver - [builtinApplication.myDS_update] Executing SQL update on 'mydb': call spUpdateWSSWatchSources(_watchID, wssSourceIDs)
=== 2010-05-16 20:09:44,537 [l0-5] INFO  SQLDriver - [builtinApplication.myDS_update] Execute of update: call spUpdateWSSWatchSources(_watchID, wssSourceIDs) on db: mydb threw exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '_watchID' in 'field list' - assuming stale connection and retrying update.
=== 2010-05-16 20:09:44,803 [l0-5] DEBUG PoolableSQLConnectionFactory - [builtinApplication.myDS_update] Returning pooled Connection
=== 2010-05-16 20:09:44,834 [l0-5] WARN  RequestContext - dsRequest.execute() failed: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '_watchID' in 'field list'

Am I building my customSQL incorrectly? I am trying to just take the value of 2 fields with those names, and use them as parameters, like so:

Record record = new Record();
record.setAttribute("_watchID", watchID);
record.setAttribute("wssSourceIDs", buf.toString());
DataSource.get("myDS").updateData(record);
2

If you want part of your <customSQL> to be aubstitued with dynamic params, use a leading $ (so $_watchID)

3

Thanks. Unfortunately, it didn’t work:

=== 2010-05-17 10:34:36,022 [l0-3] INFO  SQLDriver - [builtinApplication.myDS_update] Executing SQL update on 'mydb': call spUpdateWSSWatchSources($_watchID, $wssSourceIDs)
=== 2010-05-17 10:34:36,115 [l0-3] INFO  SQLDriver - [builtinApplication.myDS_update] Execute of update: call spUpdateWSSWatchSources($_watchID, $wssSourceIDs) on db: mydb threw exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '$_watchID' in 'field list' - assuming stale connection and retrying update.
=== 2010-05-17 10:34:36,381 [l0-3] DEBUG PoolableSQLConnectionFactory - [builtinApplication.myDS_update] Returning pooled Connection
=== 2010-05-17 10:34:36,412 [l0-3] WARN  RequestContext - dsRequest.execute() failed: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '$_watchID' in 'field list'

Here is my updated customSQL:

<customSQL>call spUpdateWSSWatchSources($_watchID, $wssSourceIDs)</customSQL>

The docs lead me to believe that I used $ for objects, like $criteria, not necessarily for a single attribute.

P.S. I also tried removing the _ to no avail.

4

Sorry, yes the docs are correct. So $values._watchID

5

That did it – thank you!

-Ken