Announcement

**Isomorphic** · 10 Dec 2014, 00:55

So, does the column "province" in the table "CusNeiGroupDS" exist? Your database thinks not.

You may have simply forgotten to set dataSource.tableName in your .ds.xml if your table is not named CusNeiGroupDS.

**zaj** · 10 Dec 2014, 01:19

Thanks for your feedback. you see i dont have a table called "CusNeiGroupDS". This is the name of my ds.xml file which I call from my java class like this

Code:

listGrid.setDataSource(DataSource.get("CusNeiGroupDS"));

This is my raw SQL query which runs perfectly well in my database

Code:

SELECT
    dbo.province.provincename as province,
    dbo.province.capital,
    dbo.province.code,
    dbo.province.telcode,
    dbo.province.taxcode,
    dbo.county.countyname as county,
    dbo.district.districtname as district,
    dbo.zone.alternateName as zone,
    dbo.neighbourhood.alternateName as neigbhour,
    dbo.city.cityname as city,
    dbo.city.taxcode,
    dbo.city.fdocode,
    count(dbo.customer.customeraltname) as countCustomer
FROM
    dbo.county
INNER JOIN
    dbo.province
ON
    (
        dbo.county.provinceID = dbo.province.id)
INNER JOIN
    dbo.district
ON
    (
        dbo.county.id = dbo.district.countyID)
INNER JOIN
    dbo.city
ON
    (
        dbo.district.id = dbo.city.districtID)
INNER JOIN
    dbo.zone
ON
    (
        dbo.city.id = dbo.zone.cityId)
INNER JOIN
    dbo.neighbourhood
ON
    (
        dbo.zone.id = dbo.neighbourhood.zoneId)
INNER JOIN
    dbo.customer
ON
    (
        dbo.neighbourhood.id = dbo.customer.neighbourhoodId)
        Group By dbo.province.provincename,
    dbo.province.capital,
    dbo.province.code,
    dbo.province.telcode,
    dbo.province.taxcode,
    dbo.county.countyname,
    dbo.district.districtname,
    dbo.zone.alternateName,
    dbo.neighbourhood.alternateName,
    dbo.city.cityname,
    dbo.city.taxcode,
    dbo.city.fdocode

**Isomorphic** · 10 Dec 2014, 01:23

So to restate: you're including the $defaultSelectClause in your query, and you have simply called the DataSource "CusNeiGroupDS" and not provided a tableName - so of course the framework is going to generate the name CusNeiGroupDS.province as part of the defaultSelectClause: that's what you configured the system to do.

What were you expecting to happen instead, and why?

**zaj** · 10 Dec 2014, 01:30

Should I remove this $defaultSelectClause from my CusNeiGroupDS.ds.xml file. Actually I was not sure if I needed that $defaultSelectClause.

>>What were you expecting to happen instead, and why?

I was expecting SGWT to create that raw sql which I posted before and return me the columns which I mentioned in the select clause in the Listgrid. Please bear with me.

**zaj** · 10 Dec 2014, 02:45

I removed that default clause and now get this error,data:"Invalid column name 'province'."

Any idea what am I doing wrong.

**Blama** · 10 Dec 2014, 04:06

Originally posted by Isomorphic View Post

You may have simply forgotten to set dataSource.tableName in your .ds.xml if your table is not named CusNeiGroupDS.

Please see the docs.

Best regards,
Blama

**zaj** · 14 Dec 2014, 22:36

Thanks for your feedback. I tried adding the tableName in my field tag and in my datasource primary table,like so.

Code:

<DataSource ID="CusNeiGroupDS" serverType="sql" tableName="province">
	<fields>
		<field name="id" type="integer" />
		<field name="provincename" title="province" type="text"/>
		<field name="capital" title="capital" type="text"/>
		<field name="code" title="code" type="text"/>
		<field name="telcode" title="telcode" type="text"/>
		<field name="taxcode" title="taxcode" type="text" />
		<field name="countyname" title="county" type="text" tableName="county"/>
		<field name="district" title="district" type="text" customSQL="true" />
		<field name="city" title="city" type="text" customSQL="true" />
		<field name="zone" title="zone" type="text" customSQL="true" />
		<field name="neighbour" title="neighbour" type="text" customSQL="true" />
		<field name="taxcodecity" title="taxcodecity" type="text"
			customSQL="true" />
		<field name="fdocode" title="fdocode" type="text" customSQL="true" />
		<!-- <field name="countCustomer" title="countCustomer" type="int" /> -->
	</fields>


	<operationBindings>
		<operationBinding operationId="summary"
			operationType="fetch"
			customFields="district,city,zoneneighbour,taxcodecity,fdocode">
			<selectClause>
				districtname as
				district,
				alternateName as zone,
				alternateName as neigbhour,
				cityname
				as city,
				fdocode
			</selectClause>
			<tableClause>province, county, district, zone, neighbourhood, city
			</tableClause>
			<whereClause>
                province.Id = county.provinceId
            AND district.countyId = county.Id
			AND city.districtId = district.Id
			AND neighbourhood.zoneId = zone.Id
			</whereClause>
		</operationBinding>
	</operationBindings>
</DataSource>
<!-- <operationBindings> <operationBinding operationType="fetch"> <selectClause> 
	province.provincename, province.capital, province.code, province.telcode, 
	province.taxcode, county.countyname as county, district.districtname as district, 
	zone.alternateName as zone, neighbourhood.alternateName as neigbhour, city.cityname 
	as city, city.fdocode </selectClause> <tableClause> province JOIN county 
	ON province.Id = county.provinceId JOIN district ON district.countyId = county.Id 
	JOIN city ON city.districtId = district.Id JOIN zone ON zone.cityId = city.Id 
	JOIN neighbourhood ON neighbourhood.zoneId = zone.Id JOIN customer ON customer.neighbourhoodId 
	= neighbourhood.Id </tableClause> </operationBinding> </operationBindings> -->

Now the cols in the province table are being shown but the col from the county table is not brought and the query which SmartGWt generates looks like this

Code:

 
 SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rowID FROM (SELECT TOP 100 PERCENT  province.id, province.provincename, province.capital, province.code, province.telcode, province.taxcode FROM province WHERE ('1'='1')) x) y WHERE y.rowID BETWEEN 1 AND 75

please help me understand what I am doing wrong or missing.

**Blama** · 14 Dec 2014, 22:55

Hi zaj,

how is the framework supposed to generate the join here (there is no custom operation binding used, which is OK)? No information about FK/PK-relationship.
See in the docs I linked before: primaryKey, foreignKey, includeFrom.
Please make sure that these also appear in your local 3.0p docs, as I don't know if this is already supported in that version.

Best regards,
Blama

**zaj** · 14 Dec 2014, 23:09

Blama,Thanks a lot. In the Datasource doc which you linked I don't see those tags. Did you mean in the DataSourceField class.
Do I need to add the primary key and foreign key of each table in the field tag or in the whereClause. I am confused here.Can you please help me understand it.

>>how is the framework supposed to generate the join here
I am in the impression that this whereClause will tell the framework to join these tables.

<whereClause>
province.Id = county.provinceId
AND district.countyId = county.Id
AND city.districtId = district.Id
AND neighbourhood.zoneId = zone.Id
</whereClause>

**Blama** · 14 Dec 2014, 23:45

Hi zaj,

yes, I meant DataSourceField, sorry for the confusion.

Your table clause is not used as the whole operationBinding is not used (you used operationId="summary" in the ds.xml, but the default is to go to the no-operationId-default-fetch binding, which you commented out). But it should work nevertheless, given that you provided the missing information and given that 3.0 supports FK/PK-lookup).

Missing is:

id: primaryKey="true" (every DS should have a PK, that is also unique in the underlying database/persistence layer)
new field: name="county_id" foreignKey="county.id" (needs a DS named county with a PK-field id (mandatory in this case), make sure to set the correct tableName in the DataSource-tag of this DS as well
countyname: includeFrom="county.countyname", drop the tableName.

The same for all the other joined tables (province, district, city, zone). Note that if these form a tree structure, SmartGWT will generate all necessary joins.
But I'd start small and see if just the join to county works in the first place.

Best regards,
Blama

**zaj** · 15 Dec 2014, 00:26

Blama,thanks once again for your feedbacks. so if I use this method(primary key,foreignKey, includeFrom) then I need not use operationBinding in my DataSource.ds.xml.correct??.

**Blama** · 15 Dec 2014, 00:37

Correct (if these attributes are available in the DataSourceField-docs of your download).

**zaj** · 15 Dec 2014, 00:44

cheers. by the way do you also provide consultancy outside the forum too.

**Blama** · 15 Dec 2014, 00:55

Haha, no.
I'm learning to use the framework myself with the product I'm developing. But I can say so far that I really like it and that there was a solution to almost every use case one can have in an enterprise application, so far. Also, if one uses the skinning capabilities, it even doesn't look like a "Microsoft Windows Explorer enterprise application".

Besides that, if anyone from the Munich/Germany area is interested in an user group meeting, I'd be very happy to reserve a table somewhere downtown for this.

Best regards,
Blama

Announcement

Datasource cannot load data from database

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment