Issue in the SQL query generated by SGWT

[zaj]

Hello there,

I am using SmartClient Version: v10.0p_2015-05-08/PowerEdition Deployment (built 2015-05-08) with Firefox 26.

I have this datasource file which I am using to populate a treegrid but for some reason SGWT is adding the condition WHERE IS NULL. Why is it so..

 
SELECT expd.id, expd.name, expd.mfk, mepd.id AS didi FR
OM expd LEFT OUTER JOIN mepd ON expd.mfk = mepd.id WHERE (expd.mfk IS NULL)

Here is my datasource file

<DataSource ID="distributorProductDS" serverType="sql" tableName="expd">
	<fields>
		<field name="id" type="sequence" hidden="true" primaryKey="true" />
		<field name="name" title="Product Name" type="ntext" />
		<field name="mfk" title="ecid" foreignKey="importDisExcelMainDS.id" type="integer" joinType="outer"
			required="true"/>
		<field name="didi" includeFrom="importDisExcelMainDS.id" hidden="true" />
	</fields>
</DataSource>

and here is my java code

        DataSource ds = DataSource.get("distributorProductDS");		
		
	Tree employeeTree1 = new Tree();  
        employeeTree1.setModelType(TreeModelType.PARENT);  
        employeeTree1.setNameProperty("name");  
        employeeTree1.setOpenProperty("isOpen");  

		
        final DistributorTreeGrid treeGrid = new DistributorTreeGrid(); 
        treeGrid.setDataProperties(employeeTree1);
        treeGrid.setDataSource(ds);
        treeGrid.addDrawHandler(new DrawHandler() {  
            public void onDraw(DrawEvent event) {  
                treeGrid.fetchData();  
            }  
        });

[zaj]

can somebody tell me why is this so

[zaj]

somebody please tell me what i am missing or doing wrong

[Isomorphic]

Start by providing basic information that always required:

1. Server log file for the request that generates the SQL

2. What type of database is this

3. Settings from server.properties

[zaj]

1. Server log file for the request that generates the SQL

DSREQUEST log

{
dataSource:"distributorProductDS",
operationType:"fetch",
componentId:"isc_DistributorTreeGrid_0",
data:{
mfk:null
},
textMatchStyle:"exact",
resultTree:[ResultTree ID:isc_ResultTree_0 (dataSource: distributorProductDS, created by: isc_DistributorTreeGrid_0)],
callback:{
caller:[ResultTree ID:isc_ResultTree_0 (dataSource: distributorProductDS, created by: isc_DistributorTreeGrid_0)],
methodName:"loadChildrenReply"
},
willHandleError:true,
showPrompt:true,
prompt:"Finding Records that match your criteria...",
oldValues:{
mfk:null
},
requestId:"distributorProductDS$6270",
internalClientContext:{
parentNode:{
name:"/",
isOpen:true,
children:Array[0]
},
relationship:{
childDS:[DataSource ID:distributorProductDS],
parentDS:[DataSource ID:importDisExcelMainDS],
parentIdField:"mfk",
idField:"id"
},
childrenReplyCallback:{
},
fetchCount:1
},
fallbackToEval:false,
progressiveLoading:false,
lastClientEventThreadCode:"MUP9[E0]",
bypassCache:true
}

Server Logs

=== 2015-06-14 08:45:24,688 [9-26] DEBUG DSRequest - Caching instance 63 of DS 'distributorProductDS' from DSRequest.getDataSource()
=== 2015-06-14 08:45:24,688 [9-26] DEBUG DSRequest - Caching instance 63 of DS distributorProductDS
=== 2015-06-14 08:45:24,689 [9-26] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
mfk:null
},
operationConfig:{
dataSource:"distributorProductDS",
repo:null,
operationType:"fetch",
textMatchStyle:"exact"
},
componentId:"isc_DistributorTreeGrid_0",
appID:"builtinApplication",
operation:"distributorProductDS_fetch",
oldValues:{
mfk:null
},
progressiveLoading:false,
resultTreeIdField:"id",
resultTreeParentIdField:"mfk"
}
=== 2015-06-14 08:45:24,689 [9-26] INFO IDACall - Performing 1 operation(s)
=== 2015-06-14 08:45:24,689 [9-26] DEBUG ISCKeyedObjectPool - Borrowing object for 'importDisExcelMainDS'
=== 2015-06-14 08:45:24,689 [9-26] DEBUG PoolableDataSourceFactory - Activated DataSource 52 of type 'importDisExcelMainDS'
=== 2015-06-14 08:45:24,689 [9-26] DEBUG Relation - Caching instance of toDS 'importDisExcelMainDS' in the DSRequest map
=== 2015-06-14 08:45:24,690 [9-26] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-06-14 08:45:24,690 [9-26] DEBUG DeclarativeSecurity - DataSource distributorProductDS is not in the pre-checked list, processing...
=== 2015-06-14 08:45:24,690 [9-26] DEBUG DeclarativeSecurity - Processing security checks for DataSource importDisExcelMainDS, field id
=== 2015-06-14 08:45:24,690 [9-26] DEBUG DeclarativeSecurity - DataSource importDisExcelMainDS is not in the pre-checked list, processing...
=== 2015-06-14 08:45:24,677 [9-20] DEBUG IDACall - Header Name:Value pair: Cookie:GLog=%7B%0D%20%20%20%20trackRPC%3Atrue%2C%20%0D%20%20%20%20pageGUID%3A%22467682A7-E5B0-4E92-8060-A95143B4D14D%22%2C%20
%0D%20%20%20%20priorityDefaults%3A%7B%0D%20%20%20%20%20%20%20%20sgwtInternal%3A1%2C%20%0D%20%20%20%20%20%20%20%20Log%3A4%0D%20%20%20%20%7D%2C%20%0D%20%20%20%20defaultPriority%3A3%2C%20%0D%20%20%20%20l
eft%3A-1543%2C%20%0D%20%20%20%20top%3A-7%2C%20%0D%20%20%20%20width%3A1536%2C%20%0D%20%20%20%20height%3A809%0D%7D; JSESSIONID=fzv7waa7tf6tbhucbenjwgbr; isc_cState=ready
=== 2015-06-14 08:45:24,707 [9-26] DEBUG DeclarativeSecurity - Processing security checks for DataSource importDisExcelMainDS, field id
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Host:127.0.0.1:8888
=== 2015-06-14 08:45:24,707 [9-26] DEBUG DeclarativeSecurity - DataSource importDisExcelMainDS is not in the pre-checked list, processing...
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Content-Length:1239
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Content-Type:application/x-www-form-urlencoded; charset=UTF-8
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Accept-Language:en-US,en;q=0.5
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Pragma:no-cache
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Connection:keep-alive
=== 2015-06-14 08:45:24,707 [9-20] DEBUG IDACall - Header Name:Value pair: Referer:http://127.0.0.1:8888/KaizenBehsa.html?gwt.codesvr=127.0.0.1:9997
=== 2015-06-14 08:45:24,708 [9-26] DEBUG AppBase - [builtinApplication.distributorProductDS_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-06-14 08:45:24,708 [9-20] DEBUG IDACall - Header Name:Value pair: User-Agent:Mozilla/5.0 (Windows NT 6.1; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0
=== 2015-06-14 08:45:24,708 [9-20] DEBUG IDACall - Header Name:Value pair: Cache-Control:no-cache
=== 2015-06-14 08:45:24,708 [9-20] DEBUG IDACall - Header Name:Value pair: Accept-Encoding:gzip, deflate
=== 2015-06-14 08:45:24,708 [9-26] DEBUG AppBase - [builtinApplication.distributorProductDS_fetch] No public zero-argument method named '_distributorProductDS_fetch' found, performing generic datasour
ce operation
=== 2015-06-14 08:45:24,708 [9-20] DEBUG IDACall - session exists: fzv7waa7tf6tbhucbenjwgbr
=== 2015-06-14 08:45:24,708 [9-20] DEBUG IDACall - remote user: null
=== 2015-06-14 08:45:24,708 [9-26] INFO SQLDataSource - [builtinApplication.distributorProductDS_fetch] Performing fetch operation with
criteria: {mfk:null} values: {mfk:null}
=== 2015-06-14 08:45:24,711 [9-26] INFO SQLDataSource - [builtinApplication.distributorProductDS_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause$defaultAnsiJoinClause WHERE
$defaultWhereClause
=== 2015-06-14 08:45:24,711 [9-26] INFO SQLDataSource - [builtinApplication.distributorProductDS_fetch] 63: Executing SQL query on 'SQLServer': SELECT expd.id, expd.name, expd.mfk, mepd.id AS didi FR
OM expd LEFT OUTER JOIN mepd ON expd.mfk = mepd.id WHERE (expd.mfk IS NULL)
=== 2015-06-14 08:45:24,712 [9-26] DEBUG SQLConnectionManager - [builtinApplication.distributorProductDS_fetch] Borrowed connection '16898665'
=== 2015-06-14 08:45:24,712 [9-26] DEBUG SQLDriver - [builtinApplication.distributorProductDS_fetch] About to execute SQL query in 'SQLServer' using connection '16898665'
=== 2015-06-14 08:45:24,712 [9-26] INFO SQLDriver - [builtinApplication.distributorProductDS_fetch] Executing SQL query on 'SQLServer': SELECT expd.id, expd.name, expd.mfk, mepd.id AS didi FROM expd
LEFT OUTER JOIN mepd ON expd.mfk = mepd.id WHERE (expd.mfk IS NULL)

2. What type of database is this

MSSQL 2008

3. Settings from server.properties

see attachment

[Isomorphic]

The TreeGrid is trying to load just the nodes under root. You haven't configured a dataSourceField.rootValue (see docs) for your foreignKey field, and the default is null, so that's what the criteria passed to the server reflects and that's what the SQL query reflects. This is all correct and expected behavior.

You can either:

1. use a rootValue that actually reflects your data

or

2. turn off level-by-level loading and load the whole tree of data at once, by setting treeGrid.loadDataOnDemand to false. This of course is only appropriate for smaller trees.

[zaj]

Hi Isomorhpic,

Thanks for your feedback.

You haven't configured a dataSourceField.rootValue (see docs) for your foreignKey field,

Can you please elaborate on this. You see, I have a db table structure with these tables and I am trying to display the distributor id(for now) as parentID and its products as the child idField.
What I did now is created a new datasource file distributorProductDS and using that to display the treegrid. Now you mentioned that I did not configure rootValue for the foreignKey field. I dont get this.
For e.g. if i do something like this employeeTree1.setRootValue(1);

DataSource ds = DataSource.get("distributorProductDS");		
		
		Tree employeeTree1 = new Tree();  
		employeeTree1.setModelType(TreeModelType.PARENT);  
		employeeTree1.setRootValue(1);  
		employeeTree1.setNameProperty("name");  
		employeeTree1.setIdField("id");  
		employeeTree1.setParentIdField("mfk");  
		employeeTree1.setOpenProperty("isOpen"); 
		
		final DistributorTreeGrid treeGrid = new DistributorTreeGrid(); 
		treeGrid.setDragDataAction(DragDataAction.MOVE);  
		treeGrid.setDataProperties(employeeTree1);
		treeGrid.setDataSource(ds);
		treeGrid.addDrawHandler(new DrawHandler() {  
		    public void onDraw(DrawEvent event) {  
		        treeGrid.fetchData();  
		    }  
		});

<DataSource ID="distributorProductDS" serverType="sql" tableName="expd">
	<fields>
		<field name="id" type="sequence" hidden="true" primaryKey="true" />
		<field name="name" title="Product Name" type="ntext" />
		<field name="mfk" title="Distributor ID" valueField="id" displayField="DisID" foreignKey="importDisExcelMainDS.id"
               required="true"/>
        <field name="DisID" includeFrom="importDisExcelMainDS.id" hidden="true"/>
	</fields>
</DataSource>

then I get this warning,which is obvious,as I am putting all my node under this id foreignKey 1

09:03:58.748 [ERROR] [kaizenbehsa] 09:03:33.372:XRP4:WARN:ResultTree:isc_ResultTree_0 (dataSource: distributorProductDS, created by: isc_DistributorTreeGrid_0):Adding node to tree with id property set to:1. A node with this ID is already present in this Tree - that node will be replaced. Note that this warning may be disabled by setting the reportCollisions attribute to false.

[Isomorphic]

This isn't a question of schema, which you already shared, but what's in your actual data. How can SmartClient select the nodes that should be at root, what value for the foreignKey field do they have? That's your rootValue.

[zaj]

Thanks for your feedback.
The value of the foreignKey field is the mfk col. As shown in the attachment.

[Isomorphic]

Again, it's not a question of schema. The same question we just asked still needs to be answered:

How can SmartClient select the nodes that should be at root, what value for the foreignKey field do they have? That's your rootValue.

[zaj]

My apologies, I dont get it.

lets take an example...there is one distributor with many products...and distributor should be the root node

[Isomorphic]

Still the same question. What criteria will select the root node or nodes? That's what you need to answer, then you know what to use for root value.

Just in case - are you trying to load a tree where some nodes would come from one DataSource and some nodes from a different DataSource? Because if so, your declarations don't make any sense for that use case, and you should re-read the Tree Data Binding overview, focusing on the parts that discuss mixed types of nodes.

[zaj]

Thanks,i will.