[Blama]

Hi Isomorphic,

thanks for the heads up and all the best to your colleague.

Best regards
Blama

[Isomorphic]

I'm "the guy" and I'm well and back to work. THANK YOU for you patience.

We agree that iLike should only be applied to iContains (and similar) operators, so this appears to be a clear regression, which is now fixed and will be available for download in nightly builds since Oct 8 (tomorrow). Let us know please how it worked for you.

[Blama]

Hi Isomorphic,

welcome back, great you are well again.
I just retested with v12.0p_2021-11-04 and unfortunately this is not fixed. The issue from #7 is still apparent.
This is a real issue for us, as we also use RESTHandler and not only BatchUploader that make use of importStrategy="display"-fields, meaning that an ISO-code of 'AT' (we have lower-case 'at' in the DB) is no longer accepted and API-Requests that worked before will fail.

The issue is like in #7:
Lookup with the new sql.postgresql.useILike: true

SELECT ... FROM T_COUNTRY JOIN ... WHERE (T_COUNTRY.ISO_3166_1_ALPHA_2 = 'AT')

Lookup without this setting:

SELECT ... FROM T_COUNTRY JOIN ... WHERE (LOWER(T_COUNTRY.ISO_3166_1_ALPHA_2) = 'at')

Best regards
Blama

[Isomorphic]

This could not be reproduced on our end. After the fix BatchUpload lookUp code results in LOWER(field)=loweredValue comparison despite the sql.postgresql.useILike setting. You've already provided all the details, but something must be different or we've missed something. Could you please share complete details of your current setup? Thank you.

[Blama]

Hi Isomorphic,

it took some time to get it working, but please try this BuiltInDS based testcase (12.0p_2021-11-04):

server.properties (+add postgresql-42.2.6.jar to class path):

sql.defaultDatabase: myDB
sql.useAnsiJoins: true
sql.postgresql.useILike: false
sql.pool.enabled: false

sql.myDB.database.type: postgresql
sql.myDB.interface.type: driverManager
sql.myDB.driver: org.postgresql.Driver
sql.myDB.driver.url: jdbc:postgresql://localhost:5432/mydb?user=xxx&password=xxx
sql.myDB.pingTest: select 1
sql.myDB.autoJoinTransactions: true
sql.myDB.database.supportsSQLLimit: true

animals.ds.xml:

<DataSource
    ID="animals"
    serverType="sql"
    tableName="animals"
>
    <fields>
        <field name="commonName" title="Animal" type="text"/>
        <field name="scientificName" title="Scientific Name" type="text" primaryKey="true" required="true"/>

        <field name="lifeSpan" importStrategy="display" displayField="employeeName" uploadFieldName="lifeSpan" foreignKey="employees.EmployeeId" required="true" />
        <field name="employeeName" includeFrom="employees.Name" />

        <field name="status" title="Endangered Status" type="text">
            <valueMap>
                <value>Threatened</value>
                <value>Endangered</value>
                <value>Not Endangered</value>
                <value>Not currently listed</value>
                <value>May become threatened</value>
                <value>Protected</value>
            </valueMap>
        </field>
        <field name="diet" title="Diet" type="text"/>
        <field name="information" title="Interesting Facts" type="text" length="1000"/>
        <field name="picture" title="Picture" type="image" detail="true"
               imageURLPrefix="/isomorphic/system/reference/inlineExamples/tiles/images/"/>
    </fields>
    <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.Animals" />
    <operationBindings>
        <operationBinding operationType="custom" operationId="addApi" serverMethod="addApi" />
    </operationBindings>
</DataSource>

Animals.java:

package com.smartgwt.sample.server.listener;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isomorphic.datasource.DSRequest;
import com.isomorphic.datasource.DSResponse;
import com.isomorphic.datasource.DataSource;
import com.isomorphic.tools.DataImport;

public class Animals {

    @SuppressWarnings("unchecked")
    public DSResponse addApi(DSRequest dsRequest, HttpServletRequest servletRequest, HttpServletResponse servletResponse) throws Exception {
        DataImport dataImporter = new DataImport();
        dataImporter.setPopulateDisplayFields(true);

        Map<String, Object> dataImporterResult = dataImporter.importDataSourceRecord(dsRequest.getValues(), dsRequest.getDataSourceName());

        DSRequest addRequest = new DSRequest(dsRequest.getDataSourceName(), DataSource.OP_ADD, dsRequest.getRPCManager());
        addRequest.setValues(dataImporterResult);
        return addRequest.execute();
    }
}

POST API request to http://127.0.0.1:8888/builtinds/sc/RESTHandler (I use Advanced REST Client, but many tools will do the job):

<request>
    <dataSource>animals</dataSource>
    <operationType>custom</operationType>
    <operationId>addApi</operationId>
    <data>
        <commonName>Animal 2</commonName>
        <scientificName>Animal 2</scientificName>
        <lifeSpan>Test</lifeSpan>
        <status>Max</status>
    </data>
</request>

Now, the lookup query generated depends on the sql.postgresql.useILike setting.
You don't need the actual animals and employeeTable tables in the DB, as we are only interested in what the 1st SQL statement looks like (=it may fail).

sql.postgresql.useILike:false:

SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE (LOWER(employeeTable.Name)='test')

sql.postgresql.useILike:true:

SELECT employeeTable.userOrder, employeeTable.Name, employeeTable.EmployeeId, employeeTable.Job, employeeTable.Email, employeeTable.EmployeeType, employeeTable.EmployeeStatus, employeeTable.Salary, employeeTable.OrgUnit, employeeTable.Gender, employeeTable.MaritalStatus FROM employeeTable WHERE (employeeTable.Name='Test')

For me, it does not differ if I use clientside BatchUploader or serverside RESTHandler, but the RESTHandler test is easier to create.

Best regards
Blama

[Isomorphic]

Apologies, a mistake was made while porting to earlier versions and 12.0 did not have the changes. This is fixed now and available in the latest nightly build. Let us know please how it worked for you.

[Blama]

Hi Isomorphic,

thanks, I retested using v12.0p_2021-11-21 and it seems to work in the testcase and in my application as expected with sql.postgresql.useILike: true.

Best regards
Blama

[Blama]

Hi Isomorphic,

I noticed that this feature never made it into the docs - a search for useILike does not bring up any results in the SmartClient 12.1 docs. I think this should definitely be in the docs, as it will help a lot when using Postgres.

Best regards
Blama

[Isomorphic]

We've added docs for useILike to the SQL settings overview, thank you for noticing it was missing.