Wrong query formed while using sql datasource

[preeti_kanyal]

Although we know from smartgwt documentation (https://www.smartclient.com/smartgwt...ataSource.html) that sql datasource is known to work with MS Sql server versions upto 2017 only yet we tried to use it with our 2019 version and facing some issues.

We are using
smartgwt Power edition version: 6.1-p20190721
MS Sql server version: Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08

First of all do you recommend to use sql datasource with this setup ?


The problem we are facing while trying to use sql ds with MS sql server 2019:

We have the following datasources:

FaceTypeCodeDescMappingSQL.ds.xml >>>

<DataSource ID="FaceTypeCodeDescMappingSQL"
serverType="sql"
tableName="FACETYPE_CODE_DESC_MAPPING"
dropExtraFields="false">
<fields>
<field name="id" type="sequence" hidden="true" primaryKey="true"/>
<field name="code" type="text" length="100"/>
<field name="description" type="text" length="50"/>
<field name="dim_type" type="text" length="50"/>
</fields>
</DataSource>


FaceTypeLocParamsSQL.ds.xml>>>

<DataSource ID="FaceTypeLocParamsSQL"
serverType="sql"
tableName="FACETYPE_LOC_PARAMS"
dropExtraFields="false">
<fields>
<field name="id" type="sequence" hidden="true" primaryKey="true" />
<field name="facetype_mapping_id" type="integer" foreignKey="FaceTypeCodeDescMappingSQL.id" joinType="outer" />
<field name="code" type="text" length="100" includeFrom="FaceTypeCodeDescMappingSQL.code" />
<field name="description" type="text" length="50" includeFrom="FaceTypeCodeDescMappingSQL.description" />
<field name="parameter" type="text" length="5"/>
</fields>
</DataSource>

The code to fetch data:
DataSource ds = DataSource.get("FaceTypeLocParamsSQL");
Criteria crit = new Criteria();
crit.addCriteria("code", "2FNV");
crit.addCriteria("description", "lite");
ds.fetchData(crit, (resp, data, req) -> {
GWT.log("data fetched" + resp.getData());
});

The query that formed in this fetch call does not seem correct.

SELECT FACETYPE_LOC_PARAMS.id, FACETYPE_LOC_PARAMS.facetype_mapping_id, FACETYPE_LOC_PARAMS.parameter, FACETYPE_CODE_DESC_MAPPING.code, FACETYPE_CODE_DESC_MAPPING.description FROM FACETYPE_LOC_PARAMS, FACETYPE_CODE_DESC_MAPPING WHERE (LOWER(FACETYPE_CODE_DESC_MAPPING.code)='2fnv' AND LOWER(FACETYPE_CODE_DESC_MAPPING.description)='lite')
AND FACETYPE_LOC_PARAMS.facetype_mapping_id *= FACETYPE_CODE_DESC_MAPPING.id

This gives the following error: Incorrect syntax near '*='.

But if we use joinType="inner " everything goes well.

Please let us know what is going wrong here and how to fix this.

[Isomorphic]

You are making use of legacy outer join support which has evidently been dropped by Microsoft for SQL 2019 (it has been deprecated for a very long time). As the documentation for joinType says (https://www.smartclient.com/isomorph...Field.joinType), support for outer joins is very limited unless you are using ANSI-style joins. So If you need an outer join, specify useAnsiJoins (https://www.smartclient.com/isomorph...e.useAnsiJoins).

[preeti_kanyal]

Thank you for the answer.

We have another query, as we know from smartgwt documentation (https://www.smartclient.com/smartgwt...ataSource.html) that sql datasource is known to work with MS Sql server versions upto 2017, so is it ok to use it with Ms sql server 2019?

[Isomorphic]

Yes, more recent versions are also supported.

[Sam Squanch]

Oh that is good to know... still just playing around but I am glad there is lots of helpful information in these forums.