No access to $criteria and other fields in datasource operation

[kevinherrmann]

$criteria.xxxxxx, $currentDate are not getting replaced in a templated operation update.

This is all server-side by the way...

I create a new DSRequest, set it's criteria and then get a SQL exception.

Here is the version info:
SNAPSHOT_v8.3d_2012-06-12/EVAL Deployment (expires 2012.08.11_06.36.16) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Java code:

          HashMap<String, Object> lRecord = new HashMap<String, Object>();

          // TODO Ensure period is for an hour or reject
          EPeriod lPeriod = EPeriod.HOUR;
          lRecord.put("observed_for_period", lPeriod.ordinal());

          // TODO Determine rules for normalizing timestamp, encapsulate them somewhere and do it
          Date lDate = lNeo1Converter.parse(lLine[3]);
          Calendar lOldestDateForAPeriod = Utility.getOldestDateForAPeriod(lPeriod, lDate);
          lRecord.put("observed_for", lOldestDateForAPeriod.getTime());

          Long lMetricDefinitionID = caller.getIDForMetric(lLine[2]);
          if (lMetricDefinitionID == null)
            throw new XInvalidMetricFormat("DataSource could not validate the metric name '" + lLine[2] + "'");
          lRecord.put("metric_definition_id", lMetricDefinitionID);

          lRecord.put("dimension1_name", lDim1Name);
          lRecord.put("dimension1_value", lLine[0]);
          lRecord.put("dimension2_name", lDim2Name);
          lRecord.put("dimension2_value", lLine[1]);

          // Create the criteria before adding the value...we DON'T want to match that
          HashMap<String, Object> lCriteria = new HashMap<String, Object>();
          lCriteria.putAll(lRecord);

          lRecord.put("metric_value", Double.valueOf(lLine[5]));

          ErrorReport lErrorReport = lDS.validate(lRecord, true);
          if (lErrorReport != null)
            throw new XInvalidMetricFormat("DataSource could not validate the record\r\n" + lErrorReport.toString());

          DSRequest lDSR = new DSRequest("metric_copy", DSOperationType.UPDATE.getValue());
          lDSR.setOperationId("updateSame");

          ArrayList<Map<String, Object>> lArrayList = new ArrayList<Map<String, Object>>();
          lArrayList.add(lRecord);
          lDSR.setValues(lArrayList);

          lDSR.setCriteria(lCriteria);

          DSResponse lExecuteUpdate = lDSR.execute();

datasource definition

<DataSource xmlns="http://www.smartclient.com/schema"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.smartclient.com/schema smartclient-datasource.xsd"
  ID="metric_copy" serverType="sql" tableName="metric_copy"
  autoDeriveSchema="true" sparseUpdates="true">
  <fields>
    <field name="name" type="text" tableName="metric_definition" />
  </fields>

  <operationBindings>
    <operationBinding operationType="update"
      operationId="updateSame">
      <whereClause>
        metric_definition_id=$criteria.metric_definition_id
        AND observed_for=$currentDate
        AND $defaultWhereClause
      </whereClause>
    </operationBinding>
  </operationBindings>
</DataSource>

The relevant exception is here:

 WHERE 
metric_definition_id=$criteria.metric_definition_id
        AND observed_for=$currentDate
        AND ('1'='1')
       on db: Mysql threw exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntax
ErrorException: Unknown column '$criteria.metric_definition_id' in 'where clause'

See how the $criteria are not getting replaced with values I set in the Java code?

Here is the whole Log:

=== 2012-08-03 15:56:00,875 [ad-1] INFO  com.isomorphic.sql.SQLDataSource - Deri
ving dataSource metric_copy from table: metric_copy
=== 2012-08-03 15:56:00,889 [main] INFO  com.isomorphic.base.ISCInit - Isomorphi
c SmartClient/SmartGWT Framework initialization called from com.isomorphic.base.
Init
=== 2012-08-03 15:56:00,889 [main] INFO  com.isomorphic.base.ISCInit - Isomorphi
c SmartClient/SmartGWT Framework is already initialized
=== 2012-08-03 15:56:00,943 [ad-1] INFO  com.isomorphic.sql.SQLDSGenerator - Fet
ching column metadata for table: metric_copy
=== 2012-08-03 15:56:00,943 [ad-1] INFO  com.isomorphic.sql.SQLDSGenerator - ===
==========Using catalog: neo
=== 2012-08-03 15:56:01,088 [ad-1] INFO  com.isomorphic.sql.SQLDSGenerator - Fet
ching column metadata for metric_copy complete
=== 2012-08-03 15:56:01,329 [ad-1] WARN  com.isomorphic.sql.SQLDataSource - ds:i
sc.DataSource.create({
    allowAdvancedCriteria:true,
    serverType:"sql",
    generatedBy:"SNAPSHOT_v8.3d_2012-06-12/EVAL Deployment 2012-06-12",
    ID:"metric_copy_inheritsFrom",
    dataSourceVersion:"1",
    fields:[
        {
            name:"id",
            primaryKey:true,
            type:"sequence"
        },
        {
            name:"metric_definition_id",
            type:"integer"
        },
        {
            name:"observed_for",
            type:"datetime"
        },
        {
            name:"observed_for_period",
            type:"integer"
        },
        {
            name:"metric_value",
            type:"float"
        },
        {
            name:"dimension1_name",
            length:255,
            type:"text"
        },
        {
            name:"dimension1_value",
            length:255,
            type:"text"
        },
        {
            name:"dimension2_name",
            length:255,
            type:"text"
        },
        {
            name:"dimension2_value",
            length:255,
            type:"text"
        }
    ]
})

=== 2012-08-03 15:56:01,330 [ad-1] INFO  com.maryville.neo.server.importers.CSVI
mporterCopy - romulus.lab.sl.maryville.com
=== 2012-08-03 15:56:27,511 [ad-1] INFO  com.isomorphic.sql.SQLDataSource - [bui
ltinApplication.updateSame] Performing update operation with
	criteria: {}	values: {metric_definition_id:201,observed_for_period:3,dimension2
_name:"Subunit",metric_value:98.0,dimension2_value:"System",observed_for:new Dat
e(1345366800000),dimension1_value:"romulus.lab.sl.maryville.com",dimension1_name
:"Unit ID"}
=== 2012-08-03 15:56:27,516 [ad-1] INFO  com.isomorphic.sql.SQLWhereClause - [bu
iltinApplication.updateSame] empty condition
=== 2012-08-03 15:56:27,532 [ad-1] WARN  com.isomorphic.sql.SQLDriver - [builtin
Application.updateSame] DSRequest has no RPCManager set when testing if we shoul
d join a transaction - transaction will not be joined
=== 2012-08-03 15:56:27,602 [ad-1] INFO  com.isomorphic.sql.SQLDriver - [builtin
Application.updateSame] Executing SQL update on 'Mysql': UPDATE metric_copy SET 
dimension1_name='Unit ID', dimension1_value='romulus.lab.sl.maryville.com', dime
nsion2_name='Subunit', dimension2_value='System', metric_definition_id=201, metr
ic_value=98.0, observed_for='2012-08-19 03:00:00', observed_for_period=3 WHERE 
        metric_definition_id=$criteria.metric_definition_id
        AND observed_for=$currentDate
        AND ('1'='1')
      
=== 2012-08-03 15:56:27,771 [ad-1] INFO  com.isomorphic.sql.SQLDriver - [builtin
Application.updateSame] Execute of update: UPDATE metric_copy SET dimension1_nam
e='Unit ID', dimension1_value='romulus.lab.sl.maryville.com', dimension2_name='S
ubunit', dimension2_value='System', metric_definition_id=201, metric_value=98.0,
 observed_for='2012-08-19 03:00:00', observed_for_period=3 WHERE 
        metric_definition_id=$criteria.metric_definition_id
        AND observed_for=$currentDate
        AND ('1'='1')
       on db: Mysql threw exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntax
ErrorException: Unknown column '$criteria.metric_definition_id' in 'where clause
' - assuming stale connection and retrying update.

Thanks,
Kevin

[Isomorphic]

In the log you can clearly see the criteria are empty, so it's expected that any $criteria expression would not work.

[kevinherrmann]

In the log you can clearly see the criteria are empty, so it's expected that any $criteria expression would not work.

Yes, I can see that...the question is why.

If you look at the Java code, you can see I populate the DSRequest with a Map using setCriteria before sending.

And, when I call getCriteria, they are still there (not shown in code)

So...why isn't setCriteria sticking through he template processing?

Also, if you read my post, '$currentDate' isn't working either...which I thought was always available.

[Isomorphic]

"update" requests are normally limited to affect a single record specified by primaryKey - you can set use dsResponse.setAllowMultiUpdate(true) to avoid criteria being trimmed off.

We're not seeing any issue with $currentDate malfunctioning but we'll sanity check it.

[Isomorphic]

About $currentDate, this becomes available along with several other context variables (like $session) when you call dsRequest.setRPCManager().

It's true that $currentDate in particular may as well be available even with no RPCManager and we've made this the case for 3.1. For 8.2 just call setRPCManager().

[arojaspor]

I Can't access to $criteria in operationBindings on MySQL

DATASOURCE (Basic Example)
----------------
<operationBindings>
<operationBinding operationType="fetch" operationId="getCitys">
<customSQL>
SELECT City.* FROM City, Country
WHERE City.countryId=Country.countryId.
AND City.countryId=$criteria.countryId
</customSQL>
</operationBinding>
</operationBindings>

Server Log print:
----------------
Mysql threw exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntax
ErrorException: Unknown column '$criteria.countryId' in 'where clause'

[emnetdev]

for anyone else having this issue, it appears that the primary key will be available in the criteria, but all other values added to the DSRequest's criteria can be accessed in an array called "values".

<operationBinding operationType="update" operationId="updateNote" requires="">
        <customSQL>
            UPDATE data_notes SET start_dt = $values.startDt, end_dt = $values.endDt, note = $values.note WHERE id = $values.id
        </customSQL>
 </operationBinding>