This issue is present in our code based on SmartClient Version: v10.0p_2015-10-19/PowerEdition Deployment (built 2015-10-19), but I have reproduced it in the BuiltInDS sample of the latest nightly build of SmartClient Version: v10.0p_2016-01-21/PowerEdition Deployment (built 2016-01-21).
The issue is that for any fetch request satisfying both of the following generates incorrect SQL that fails:
For example, If we do the following to the BuiltInDS project:
which goes on to generate the following error:
Clearly those highlighted references to employeeTable.itemName should instead refer to supplyItem.itemName. Although in my example I have included the supplyItem fields using foreignKey and includeFrom attributes, the problem was first noticed on a datasource that uses the inheritsFrom attribute at the datasource level and manually joins two tables in a custom operation binding. I have used more correct methods in my example, and it is worth observing that both mechanisms fail in the same way.
To reproduce, make the following changes to BuiltInDS project, construct the required filter with the FilterBuilder component and click on the Filter button:
employees.ds.xml
BuiltInDS.java
The issue is that for any fetch request satisfying both of the following generates incorrect SQL that fails:
- Request is filtered using criteria containing a operator that accepts a datasource field value for the RHS operand (e.g. EQUALS_FIELD, CONTAINS_FIELD, etc.)
- The field specified in the RHS operand is included or imported from another datasource.
For example, If we do the following to the BuiltInDS project:
- Change the datasource definition of employees.ds.xml to:
- Have a foreign key relationship with supplyItem.ds.xml
- Include the supplyItem.itemName and supplyItem.SKU fields.
- Filter the grid by (for example):
- Name "matches other field" Item
Code:
[INDENT]SELECT COUNT(*) FROM supplyItem, employeeTable WHERE (((employeeTable.Email IS NULL AND [B]employeeTable.itemName[/B] IS NULL) OR (employeeTable.Email = [B]employeeTable.itemName[/B] AND employeeTable.Email IS NOT NULL AND [B]employeeTable.itemName[/B] IS NOT NULL))) AND employeeTable.userOrder = supplyItem.itemID[/INDENT]
Code:
[INDENT]HSQLDB threw exception: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: EMPLOYEETABLE.ITEMNAME - assuming stale connection and retrying query.[/INDENT]
To reproduce, make the following changes to BuiltInDS project, construct the required filter with the FilterBuilder component and click on the Filter button:
employees.ds.xml
Code:
<DataSource
ID="employees"
serverType="sql"
tableName="employeeTable"
recordName="employee"
testFileName="/examples/shared/ds/test_data/employees.data.xml"
titleField="Name"
>
<fields>
<field name="userOrder" title="userOrder" type="integer" canEdit="false" hidden="true"[B] foreignKey="supplyItem.itemID"[/B] />
<field name="Name" title="Name" type="text" length="128" />
<field name="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true"/>
[B] <field includeFrom="supplyItem.itemName"/>
<field includeFrom="supplyItem.SKU"/>[/B]
<field name="ReportsTo" title="Manager" type="integer" required="true"
foreignKey="employees.EmployeeId" rootValue="1" detail="true"/>
<field name="Job" title="Title" type="text" length="128"/>
<field name="Email" title="Email" type="text" length="128"/>
<field name="EmployeeType" title="Employee Type" type="text" length="40"/>
<field name="EmployeeStatus" title="Status" type="text" length="40"/>
<field name="Salary" title="Salary" type="float"/>
<field name="OrgUnit" title="Org Unit" type="text" length="128"/>
<field name="Gender" title="Gender" type="text" length="7">
<valueMap>
<value>male</value>
<value>female</value>
</valueMap>
</field>
<field name="MaritalStatus" title="Marital Status" type="text" length="10">
<valueMap>
<value>married</value>
<value>single</value>
</valueMap>
</field>
</fields>
</DataSource>
BuiltInDS.java
Code:
package com.smartgwt.sample.client;
import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.Record;
import com.smartgwt.client.types.SelectionStyle;
import com.smartgwt.client.types.SortArrow;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.Label;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.form.DynamicForm;
import com.smartgwt.client.widgets.form.FilterBuilder;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.grid.ListGridRecord;
import com.smartgwt.client.widgets.grid.events.RecordClickEvent;
import com.smartgwt.client.widgets.grid.events.RecordClickHandler;
import com.smartgwt.client.widgets.layout.HLayout;
import com.smartgwt.client.widgets.layout.VStack;
import com.smartgwt.client.widgets.viewer.DetailViewer;
/**
* Entry point classes define <code>onModuleLoad()</code>.
*/
public class BuiltInDS implements EntryPoint {
private ListGrid boundList;
private DynamicForm boundForm;
private IButton saveBtn;
private DetailViewer boundViewer;
private IButton newBtn;
private FilterBuilder fb;
/**
* This is the entry point method.
*/
public void onModuleLoad() {
KeyIdentifier debugKey = new KeyIdentifier();
debugKey.setCtrlKey(true);
debugKey.setKeyName("D");
Page.registerKey(debugKey, new PageKeyHandler() {
public void execute(String keyName) {
SC.showConsole();
}
});
ListGrid grid = new ListGrid();
grid.setLeft(20);
grid.setTop(75);
grid.setWidth(130);
grid.setLeaveScrollbarGap(false);
grid.setShowSortArrow(SortArrow.NONE);
grid.setCanSort(false);
grid.setFields(new ListGridField("dsTitle", "Select a DataSource"));
grid.setData(new ListGridRecord[]{
new DSRecord("Animals", "animals"),
new DSRecord("Office Supplies", "supplyItem"),
new DSRecord("Employees", "employees")}
);
grid.setSelectionType(SelectionStyle.SINGLE);
grid.addRecordClickHandler(new RecordClickHandler() {
public void onRecordClick(RecordClickEvent event) {
DSRecord record = (DSRecord) event.getRecord();
bindComponents(record.getDsName());
}
});
grid.draw();
VStack vStack = new VStack();
vStack.setLeft(175);
vStack.setTop(75);
vStack.setWidth("70%");
vStack.setMembersMargin(20);
Label label = new Label();
label.setContents("<ul>" +
"<li>select a datasource from the list at left to bind to these components</li>" +
"<li>click a record in the grid to view and edit that record in the form</li>" +
"<li>click <b>New</b> to start editing a new record in the form</li>" +
"<li>click <b>Save</b> to save changes to a new or edited record in the form</li>" +
"<li>click <b>Clear</b> to clear all fields in the form</li>" +
"<li>click <b>Filter</b> to filter (substring match) the grid based on form values</li>" +
"<li>click <b>Fetch</b> to fetch records (exact match) for the grid based on form values</li>" +
"<li>double-click a record in the grid to edit inline (press Return, or arrow/tab to another record, to save)</li>" +
"</ul>");
vStack.addMember(label);
boundList = new ListGrid();
boundList.setHeight(200);
boundList.setCanEdit(true);
boundList.addRecordClickHandler(new RecordClickHandler() {
public void onRecordClick(RecordClickEvent event) {
Record record = event.getRecord();
boundForm.editRecord(record);
saveBtn.enable();
boundViewer.viewSelectedData(boundList);
}
});
vStack.addMember(boundList);
fb = new FilterBuilder();
vStack.addMember(fb);
boundForm = new DynamicForm();
boundForm.setNumCols(6);
boundForm.setAutoFocus(false);
// vStack.addMember(boundForm);
HLayout hLayout = new HLayout(10);
hLayout.setMembersMargin(10);
hLayout.setHeight(22);
saveBtn = new IButton("Save");
saveBtn.addClickHandler(new ClickHandler() {
public void onClick(ClickEvent event) {
boundForm.saveData();
if (!boundForm.hasErrors()) {
boundForm.clearValues();
saveBtn.disable();
}
}
});
hLayout.addMember(saveBtn);
newBtn = new IButton("New");
newBtn.addClickHandler(new ClickHandler() {
public void onClick(ClickEvent event) {
boundForm.editNewRecord();
saveBtn.enable();
}
});
hLayout.addMember(newBtn);
IButton clearBtn = new IButton("Clear");
clearBtn.addClickHandler(new ClickHandler() {
public void onClick(ClickEvent event) {
boundForm.clearValues();
saveBtn.disable();
}
});
hLayout.addMember(clearBtn);
IButton filterBtn = new IButton("Filter");
filterBtn.addClickHandler(new ClickHandler() {
public void onClick(ClickEvent event) {
boundList.filterData(fb.getCriteria());
saveBtn.disable();
}
});
hLayout.addMember(filterBtn);
IButton fetchBtn = new IButton("Fetch");
fetchBtn.addClickHandler(new ClickHandler() {
public void onClick(ClickEvent event) {
boundList.fetchData(boundForm.getValuesAsCriteria());
saveBtn.disable();
}
});
hLayout.addMember(fetchBtn);
vStack.addMember(hLayout);
boundViewer = new DetailViewer();
vStack.addMember(boundViewer);
vStack.draw();
}
private void bindComponents(String dsName) {
DataSource ds = DataSource.get(dsName);
boundList.setDataSource(ds);
boundViewer.setDataSource(ds);
boundForm.setDataSource(ds);
fb.setDataSource(ds);
boundList.fetchData();
newBtn.enable();
saveBtn.disable();
}
}
Comment