Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Bug: If length of the criteria field>4000

    Hi Isomorphic,

    please take a look at this test case.
    If I use a field with length greater than 4000, in criteria, then I have a query like this:
    Code:
    SELECT COUNT(*) FROM T_TEST WHERE ('1'='1' OR '1'='1')
    In my test case it happens when I click on the button "Fetch data content".

    When I click on button "Fetch data subject", it works as it should:
    Code:
    SELECT COUNT(*) FROM T_TEST WHERE ((LOWER(T_TEST.SUBJECT) LIKE LOWER('%mail%') ESCAPE'\'  AND T_TEST.SUBJECT IS NOT NULL) OR (LOWER(T_TEST.SUBJECT) LIKE LOWER('%4%') ESCAPE'\'  AND T_TEST.SUBJECT IS NOT NULL))
    Oracle script
    Code:
    CREATE TABLE t_test
        (
         id INTEGER  NOT NULL ,
         mail_subject VARCHAR2 (100 CHAR) ,
         mail_content VARCHAR2 (100 CHAR) ,
         modified_by INTEGER  NOT NULL ,
         modified_at DATE DEFAULT SYSDATE  NOT NULL
        )
    ;
    
    ALTER TABLE t_test
        ADD CONSTRAINT PK_test PRIMARY KEY ( id ) ;
    
    CREATE SEQUENCE T_TEST_ID
        NOCACHE
        ORDER ;
    
    CREATE OR REPLACE TRIGGER t_test_BI
    BEFORE INSERT ON t_test
    FOR EACH ROW
    WHEN (NEW.id IS NULL)
    BEGIN
        SELECT T_TEST_ID.NEXTVAL INTO :NEW.id FROM DUAL;
    END;
    /
    
    INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 1', 'Column mail content 1', 1);
    INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 2', 'Column mail content 1', 1);
    INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 3', 'Column mail content 1', 1);
    INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 4', 'Column mail content 1', 1);
    INSERT INTO T_TEST(mail_subject, mail_content, modified_by) VALUES ('Column mail subject 5', 'Column mail content 1', 1);
    COMMIT;
    T_TEST.ds.xml
    Code:
    <DataSource
        ID="T_TEST" dbName="Oracle"
        serverType="sql"
        tableName="T_TEST"
    >
           <fields>
            <field name="MAIL_CONTENT" length="10000" type="text" escapeHTML="true" />
            <field name="SUBJECT" length="200" type="text" escapeHTML="true" />
        </fields>
        <operationBindings>
            <operationBinding operationType="fetch" />
        </operationBindings>
    </DataSource>
    BuiltInDS.java
    Code:
    package com.smartgwt.sample.client;
    
    import com.google.gwt.core.client.EntryPoint;
    import com.smartgwt.client.Version;
    import com.smartgwt.client.core.KeyIdentifier;
    import com.smartgwt.client.data.AdvancedCriteria;
    import com.smartgwt.client.data.Criterion;
    import com.smartgwt.client.types.OperatorId;
    import com.smartgwt.client.util.Page;
    import com.smartgwt.client.util.PageKeyHandler;
    import com.smartgwt.client.util.SC;
    import com.smartgwt.client.widgets.Button;
    import com.smartgwt.client.widgets.IButton;
    import com.smartgwt.client.widgets.Window;
    import com.smartgwt.client.widgets.events.ClickEvent;
    import com.smartgwt.client.widgets.events.ClickHandler;
    import com.smartgwt.client.widgets.grid.ListGrid;
    import com.smartgwt.client.widgets.grid.ListGridField;
    import com.smartgwt.client.widgets.layout.VLayout;
    
    public class BuiltInDS extends VLayout implements EntryPoint {
        private IButton recreateBtn;
    
        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();
                }
            });
    
            setWidth100();
            setHeight100();
    
            recreateBtn = new IButton("Recreate");
            recreateBtn.addClickHandler(new ClickHandler() {
                @Override
                public void onClick(ClickEvent event) {
                    new MyWindow().show();
                }
            });
            addMember(recreateBtn);
            new MyWindow().show();
            draw();
        }
    
        private class MyWindow extends Window {
            public MyWindow() {
                setWidth(600);
                setHeight(300);
                setMembersMargin(0);
                setModalMaskOpacity(70);
                setTitle(" (" + Version.getVersion() + "/" + Version.getSCVersionNumber() + ")");
                setShowMinimizeButton(false);
                setIsModal(true);
                setShowModalMask(true);
                centerInPage();
                final String value = "mail 4";
    
                final ListGrid lg = new ListGrid() {
                    {
                        setDataSource("T_TEST");
                        setShowFilterEditor(true);
    
                        ListGridField mailContentLGF = new ListGridField("MAIL_CONTENT");
                        ListGridField subjectLGF = new ListGridField("SUBJECT");
                        setFields(mailContentLGF, subjectLGF);
                    }
                };
    
                Button fetchContentButton = new Button("Fetch data content");
                fetchContentButton.addClickHandler(new ClickHandler() {
                    @Override
                    public void onClick(ClickEvent event) {
                        String[] s = value.split(" ");
                        AdvancedCriteria finalCriteria = new AdvancedCriteria(OperatorId.OR);
                        for (int i = 0; i < s.length; i++) {
                            AdvancedCriteria ad = new AdvancedCriteria(new Criterion("MAIL_CONTENT", OperatorId.ICONTAINS, s[i]));
                            finalCriteria.appendToCriterionList(ad);
                        }
                        lg.fetchData(finalCriteria);
                    }
                });
    
                Button fetchSubjectButton = new Button("Fetch data subject");
                fetchSubjectButton.addClickHandler(new ClickHandler() {
                    @Override
                    public void onClick(ClickEvent event) {
                        String[] s = value.split(" ");
                        AdvancedCriteria finalCriteria = new AdvancedCriteria(OperatorId.OR);
                        for (int i = 0; i < s.length; i++) {
                            AdvancedCriteria ad = new AdvancedCriteria(new Criterion("SUBJECT", OperatorId.ICONTAINS, s[i]));
                            finalCriteria.appendToCriterionList(ad);
                        }
                        lg.fetchData(finalCriteria);
                    }
                });
    
                addItem(lg);
                addItem(fetchContentButton);
                addItem(fetchSubjectButton);
            }
        }
    }
    Using v11.1p_2018-12-03/PowerEdition Deployment (built 2018-12-03) and Oracle DB,

    Best regards
    Pavo
    Last edited by pavo123; 14 Dec 2018, 03:01.

    #2
    The problem here is that Oracle has limitations where the LIKE operator doesn't work normally on longer text fields (>4000) - it only searches the first 4000.

    We're looking at what's best to do in the framework here - perhaps provide settings for the various possibilities - but in the meantime, you can get around this immediately using the customSQL features, eg customSelectExpression.

    Comment


      #3
      Limitation of 4000 characters length causing field being ignored in criteria is removed. The fix will be available for download in nightly builds since Jan 8 (tomorrow). Let us know please how it worked for you.

      Comment


        #4
        Hi Isomorphic,

        it works fine for me, thank you!

        Best regards
        Pavo

        Comment

        Working...
        X