Datasource Velocity question;

[tece321]

Hi,

I haven't yet tried this on 13.0. We are still on "SmartClient Version: v12.1p_2021-10-22/PowerEdition Deployment (built 2021-10-22)"

I have a custom SQL query update. I have it working but don't understand why I had to use the solution I came upon, and why my first attempt didn't work.

Here's what I coded first in the datasource: (the Current_Ptr column is a tiny Int in SQL, and boolean in the DS field definition, because it makes the java code look nice.).

<operationBinding operationType="update" operationId="Update_CP">
               <customSQL>
               UPDATE MyTable
               SET Current_Ptr = #if($values.Current_Ptr == "true") '1'  #else  '0' #end
               WHERE PK_Install = $criteria.PK_Install
               </customSQL>
           </operationBinding>

This OP generated this SQL query:

Update MyTable
SET Current_Ptr = '1'

when $values.Current_Ptr had the value 'true' and

Update MyTable
SET Current_Ptr = '0'

when $values.Current_Ptr did not have the value 'true'

Note the WHERE clause is completely missing.

I then recoded the Velocity expression as:

<operationBinding operationType="update" operationId="Update_CP">
               <customSQL>
               UPDATE MyTable
               SET Current_Ptr = #if($values.Current_Ptr == "true") '1',
               Current_Ptr_1 = '1' WHERE PK_Install = $criteria.PK_Install
               #else '0', Current_Ptr_1 = '0', Current_Ptr_2 = 0 WHERE PK_Install = $criteria.PK_Install
               #end
               </customSQL>
           </operationBinding>

This produced the correct SQL query of:

Update MyTable
SET Current_Ptr = '1', Current_Ptr_1 = '1'
WHERE PK_Install = 38123945

The WHERE clause is in the query. It produces the correct query for the 'true' and not 'true' cases.

??? What gives? Something I'm doing wrong in the first case?

Yes I know how to automatically generate the correct SQL in smartGWT, without using customSQL. But it turns out adding just this Operation ID to the datasource is the simplest fix in this case.

[Isomorphic]

We’ll take a look, but just as a quick take, does it solve the issue to put the Velocity directives on separate lines?

[tece321]

Didn't try that. I have plenty of previous examples in our code base of NOT having to put the Velocity #if, #else #end on separate lines.

[Isomorphic]

This could not be reproduced on our end. Both examples produce correct SQL, i.e. the WHERE clause is present. Could you double check this please? If the problem is still present we would need a full use case, probably based on one of the showcase samples.