Bug in Velocity serverCondition with non-Integer fields $dataSource.fetchByID(pk)

[Blama]

Hi Isomorphic,

I think I hit a bug when it comes to Velocity-expressions using non-integer fields of a DataSource.

I want to make sure for two date fields (start and end) that start<=end. I enforce this in the DB and would like to have a validator for it as well.

I read http://www.smartclient.com/smartgwte...erverCondition in detail (therefore no use of "dsRequest.oldValues") and think there is something wrong with "fetchById".
I use the following t_user.ds.xml:

<DataSource dbName="Oracle" tableName="T_USER" ID="T_USER" dataSourceVersion="1" serverType="sql">
	<fields>
		<field primaryKey="true" hidden="true" name="ID" type="sequence"></field>
		<field hidden="true" name="TENANT_ID" title="TENANT_ID" type="integer"></field>
		<field foreignKey="V_USER_MODIFIED_BY.ID" name="MODIFIED_BY" title="Geändert von" type="integer"></field>
		<field name="MODIFIED_AT" title="Geändert am" type="date"></field>
		<field foreignKey="T_COMPANY.ID" name="BELONGSTO_COMPANY_ID" title="Firma" type="integer"></field>
		<field foreignKey="T_DISTRIBUTOR.ID" name="BELONGSTO_DISTRIBUTOR_ID" title="Distributor" type="integer"></field>
		<field foreignKey="T_RESELLER.ID" name="BELONGSTO_RESELLER_ID" title="Reseller" type="integer"></field>
		<field name="ANREDE" title="Anrede" length="10" type="text" escapeHTML="true"></field>
		<field name="GIVENNAME" title="Vorname" length="30" type="text" escapeHTML="true"></field>
		<field name="SURNAME" title="Nachname" length="30" type="text" escapeHTML="true" required="true"></field>
		<field name="LOGINNAME" title="Loginname" length="20" type="text" escapeHTML="true" required="true">
			<validators>
				<validator type="isUnique" errorMessage="Der Benutzername ist schon vergeben!"></validator>
			</validators>
		</field>
		<field name="FUNCTION" title="Funktion" length="50" type="text" escapeHTML="true" required="true"></field>
		<field name="BCRYPTHASH" title="Passwort" length="60" type="text" escapeHTML="true"></field>
		<field name="PASSWORD1" customSelectExpression="''" title="Passwort" length="60" type="text" escapeHTML="true" canEdit="true"></field>
		<field name="PASSWORD2" customSelectExpression="''" title="Passwortwiederholung" length="60" type="text" escapeHTML="true" canEdit="true">
			<validators>
				<validator type="matchesField" otherField="PASSWORD1" errorMessage="Passwort und Passwortwiederholung müssen übereinstimmen!"></validator>
			</validators>
		</field>
		<field name="GUELTIGBIS" title="Login gültig bis" type="date" canEdit="true"></field>
		<field name="GUELTIGVON" title="Login gültig von" type="date" required="true" canEdit="true">
			<validators>
				<validator type="serverCustom">
					<serverCondition><![CDATA[
					("$!record.GUELTIGBIS" == "" && "$value" <= "$dataSources.T_USER.fetchById($record.ID).GUELTIGBIS")
					||  ("$!record.GUELTIGBIS" != "" && "$value" <= "$!record.GUELTIGBIS")
					]]></serverCondition>
					<errorMessage>Bitte beachten Sie, dass das Startdatum nicht kleiner als das Enddatum sein darf!</errorMessage>
				</validator>
			</validators>
		</field>
		<!-- Validator hinzufügen -->
		<field name="FEHLERHAFT" title="Anzahl fehlerhafter Logins" type="integer" required="true"></field>
		<field name="LASTLOGIN" title="letztes Login am" type="datetime"></field>
		<field name="EMAIL" title="eMail" length="50" type="text" escapeHTML="true">
			<validators>
				<validator type="regexp"
					expression="^[a-zA-Z0-9._%+-]+@(?:[a-zA-Z0-9-]+\.)+(?:[a-zA-Z]{2}|[cC][oO][mM]|[oO][rR][gG]|[nN][eE][tT]|[eE][dD][uU]|[gG][oO][vV]|[mM][iI][lL]|[bB][iI][zZ]|[iI][nN][fF][oO]|[mM][oO][bB][iI]|[nN][aA][mM][eE]|[aA][eE][rR][oO]|[aA][sS][iI][aA]|[jJ][oO][bB][sS]|[mM][uU][sS][eE][uU][mM])$"
					errorMessage="Bitte geben Sie eine gültige Emailadresse ein."></validator>
			</validators>
		</field>
		<field name="FIRMENNAME"
			customSelectExpression="CASE WHEN c.name IS NULL AND d.name IS NULL THEN r.name WHEN c.name IS NULL AND r.name IS NULL THEN d.name WHEN d.name IS NULL AND r.name IS NULL THEN c.name END">
		</field>
		<field name="ROLELIST" title="zugeordnete Rollen" type="text" escapeHTML="true" canEdit="false" tableName="rl" />
	</fields>
	<serverObject lookupStyle="new" className="com.lmscompany.lms.server.worker.T_USER" />
	<operationBindings>
		<operationBinding operationType="fetch"
			outputs="ID, TENANT_ID, MODIFIED_BY, MODIFIED_AT, BELONGSTO_COMPANY_ID, BELONGSTO_DISTRIBUTOR_ID, BELONGSTO_RESELLER_ID, ANREDE, GIVENNAME, SURNAME, LOGINNAME, FUNCTION, GUELTIGVON, GUELTIGBIS, FEHLERHAFT, LASTLOGIN, EMAIL, FIRMENNAME">
			<tableClause>
				t_user
				LEFT OUTER JOIN t_company c
				ON t_user.belongsto_company_id = c.ID
				LEFT OUTER JOIN t_distributor d
				ON
				t_user.belongsto_distributor_id = d.ID
				LEFT OUTER JOIN t_reseller r
				ON t_user.belongsto_reseller_id = r.ID
				LEFT OUTER JOIN
				(SELECT user_id,
				listagg(r.shortname, ', ') WITHIN GROUP(
				ORDER BY r.shortname) AS rolelist
				FROM t_user_role ur
				INNER JOIN t_role r
				ON ur.role_id = r.ID
				GROUP BY
				ur.user_id
				) rl ON t_user.ID = rl.user_id
			</tableClause>
		</operationBinding>
		<operationBinding operationType="fetch" operationId="getUserIdForLoginname"
			outputs="ID, LOGINNAME, GIVENNAME, SURNAME, FIRMENNAME, ROLELIST, TENANT_ID, BELONGSTO_COMPANY_ID, BELONGSTO_DISTRIBUTOR_ID, BELONGSTO_RESELLER_ID">
			<tableClause>
				t_user
				LEFT OUTER JOIN t_company c
				ON t_user.belongsto_company_id = c.ID
				LEFT OUTER JOIN t_distributor d
				ON
				t_user.belongsto_distributor_id = d.ID
				LEFT OUTER JOIN t_reseller r
				ON t_user.belongsto_reseller_id = r.ID
				LEFT OUTER JOIN
				(SELECT user_id,
				listagg(r.shortname, ', ') WITHIN GROUP(
				ORDER BY r.shortname) AS rolelist
				FROM t_user_role ur
				INNER JOIN t_role r
				ON ur.role_id = r.ID
				GROUP BY
				ur.user_id
				) rl ON t_user.ID = rl.user_id
			</tableClause>
		</operationBinding>
		<operationBinding operationType="fetch" operationId="fetchTabname" outputs="ID, GIVENNAME, SURNAME, LOGINNAME" />
		<operationBinding operationType="add">
			<values fieldName="MODIFIED_BY" value="$session.authenticatedUserID" />
			<values fieldName="MODIFIED_AT" value="$currentDate" />
			<values fieldName="TENANT_ID" value="$session.authenticatedUserTenantID" />
		</operationBinding>
		<operationBinding operationType="update">
			<values fieldName="MODIFIED_BY" value="$session.authenticatedUserID" />
			<values fieldName="MODIFIED_AT" value="$currentDate" />
			<values fieldName="TENANT_ID" value="$session.authenticatedUserTenantID" />
		</operationBinding>
	</operationBindings>
</DataSource>

For

$dataSource.fetchById($record.ID).GUELTIGBIS

and

$dataSources.T_USER.fetchById($record.ID).GUELTIGBIS

I get the following error:

=== 2013-08-19 20:30:34,067 [ec-9] DEBUG Velocity - Null reference [template 'CustomValidator', line 2, column 115] : $dataSource.fetchById($record.ID).GUELTIGBIS cannot be resolved.
=== 2013-08-19 20:30:34,067 [ec-9] ERROR Velocity - Left side of '>=' operation is not a Number at CustomValidator[line 2, column 113]

I did some tests "debugging" by changing the ds.xml and reloading and come to the conclusion that the error is most likely related to non-Integer fields in the ds.xml.

For example this (useless) validator works:

			<validators>
				<validator type="serverCustom">
					<serverCondition><![CDATA[
					("$!record.GUELTIGBIS" == "" && "3" != "$dataSource.fetchById($record.ID).FEHLERHAFT")
					||  ("$!record.GUELTIGBIS" != "" && "$value" <= "$!record.GUELTIGBIS")
					]]></serverCondition>
					<errorMessage>Bitte beachten Sie, dass das Startdatum nicht kleiner als das Enddatum sein darf!</errorMessage>
				</validator>
			</validators>

My environment:
Firefox 23.0.1
Tomcat 7.0.42
SmartGWT v9.0p_2013-08-17/EVAL Deployment 2013-08-17
Oracle 11g
Table DDL:

CREATE TABLE t_user
  (
    id                       INTEGER NOT NULL ,
    tenant_id                INTEGER NOT NULL ,
    modified_by              INTEGER ,
    modified_at              DATE DEFAULT SYSDATE NOT NULL ,
    belongsto_company_id     INTEGER ,
    belongsto_distributor_id INTEGER ,
    belongsto_reseller_id    INTEGER ,
    anrede                   VARCHAR2 (10 CHAR) ,
    givenname                VARCHAR2 (30 CHAR) ,
    surname                  VARCHAR2 (30 CHAR) NOT NULL ,
    loginname                VARCHAR2 (20 CHAR) NOT NULL ,
    FUNCTION                 VARCHAR2 (50 CHAR) NOT NULL ,
    bcrypthash               VARCHAR2 (60 CHAR) NOT NULL ,
    gueltigvon               DATE NOT NULL ,
    gueltigbis               DATE ,
    fehlerhaft               INTEGER DEFAULT 0 NOT NULL ,
    lastlogin                DATE ,
    email                    VARCHAR2 (50 CHAR)
  );

Could you check if there is something wrong here for you, too.
Also I noticed that I will (most likely) get a 2nd problem after this one is solved as the $value handed over to Velocity for a date is "TO_DATE('....', 'YYYY-MM-DD HH24:MI:SS')". ">" and "<"-text-comparison would lead to the correct result here, but I think (?) that Velocity will complain about me wanting to compare strings and not numbers (see 2nd error above "Left side of '>=' operation is not a Number").

Therefore it would be great if you also could extend the example
http://www.smartclient.com/smartgwte...ation_velocity with a check for "wanted delivery date >= today" or something like this in order to show the correct usage here. I noticed that the explanation in http://www.smartclient.com/smartgwte...Validator.html is now way more verbose, but I think that this additional example would really help a lot.

Thank you,
Blama

[Isomorphic]

fetchById() is just doing a fetch against your DataSource by literally calling the Java method DataSource.fetchById(). So you can see right in your server log whether this method succeeded and what went wrong if it failed.

[Blama]

solved (with example)

Hi Isomorphic,

thanks for the reply. I was able to solve my use-case.
Reason for the error was the value in the DB being NULL. (For readers: For NULL-Handling in Velocity see http://wiki.apache.org/velocity/CheckingForNull).
When a field may be NULL, we have a problem in general if the field is a number or a date and we want to compare this field:

• As soon as you want to compare with "<" or ">", you must not use the embracing double-quotes, as this tells Velocity to handle it as String (Strings only support "== and "!=" comparisons)
• If you don't use embracing double-quotes you'll get syntax errors after the $!dataSource.fetchById($record.PK) resolves to NULL. eg:
  

  ("$!dataSource.fetchById($record.PK).NULLFIELD" != "" && 10 < $!dataSource.fetchById($record.PK).NULLFIELD)
  ===>
  ("" != "" && 10 < )
  ===> Syntax error

The solution is to use #if directives from VTL (http://velocity.apache.org/engine/de...nce-guide.html).
My working validator (START <= END or END == NULL) now looks like this:

			<validators>
				<validator type="serverCustom">
					<serverCondition><![CDATA[
					  #if(         "$!record.GUELTIGBIS" != "" )
					    #set( $gb = $!record.GUELTIGBIS.getTime() )
					  #elseif(     "$!dataSource.fetchById($record.ID).GUELTIGBIS" != "" )
					    #set( $gb = $!dataSource.fetchById($record.ID).GUELTIGBIS.getTime() )
					  #else
					    #set( $gb = -1 )
					  #end
					  $value.getTime() <= $gb || $gb == -1]]></serverCondition>
					<errorMessage>Bitte beachten Sie, dass das Startdatum nicht kleiner als das Enddatum sein darf!</errorMessage>
				</validator>
			</validators>

(See http://forums.smartclient.com/showthread.php?t=18337 for the "getTime()")

Best regards,
Blama