Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    Bug in TextMatchStyle.SUBSTRING, Enhancement suggestion for TextMatchStyle

    Hi Isomorphic,

    the SQL generated for a ComboBoxItem with TextMatchStyle.SUBSTRING is e.g.
    Code:
    t_reseller.name LIKE '%mysearchstring%' ESCAPE '\' AND t_reseller.name IS NOT NULL
    while the javadoc reads "test for case-insenstive substring match".
    The generated SQL for Oracle does not include a UPPER()/LOWER() for the comparison, making the search case-sensitive.

    Either the JavaDoc or the choose of the Operator is wrong here (I think it's the Operator).

    Enhancement: Make it possible for the user to decide if he or she wants the case-sensitive or case-insensitive versions of TextMatchStyle.EXACT, TextMatchStyle.STARTS_WITH and TextMatchStyle.SUBSTRING.

    Best regards,
    Blama
    Tags: None
    #2
    Not reproducible. The SQL shown is for MySQL, not Oracle, and in MySQL case-insensitive is the default. The Oracle SQL includes lowercasing of the column value.

    The operator used by ComboBoxItem is already correct, and all the behaviors you are looking for are available via the different OperatorIds you can use with AdvancedCriteria.

    Comment

      #3
      Hi Isomorphic,

      I'm pretty sure I'm using Oracle :)
      Where can the error otherwise be? My server.properties excerpt is:
      Code:
      sql.defaultDatabase: Oracle
sql.useAnsiJoins: true

sql.Oracle.database.type: oracle
sql.Oracle.autoJoinTransactions: true
sql.Oracle.database.supportsSQLLimit: false

sql.Oracle.interface.credentialsInURL: true
sql.Oracle.interface.type: dataSource

sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.driver.serverName: localhost
sql.Oracle.driver.portNumber: 1521
sql.Oracle.driver.databaseName: xe
sql.Oracle.driver.user: xxx
sql.Oracle.driver.password: xxx

sql.Oracle.driver.driverType: thin
sql.Oracle.driver.networkProtocol: tcp
sql.Oracle.driver.context:
      Everything else regarding the DB connection works as expected. I'm investigating further here.

      Regarding the enhancement suggestion:
      Yes, you can with setPickListFilterCriteriaFunction. What I meant was adding to TextMatchStyle as convenience API. But I'm fine with setPickListFilterCriteriaFunction, just wanted to let you know it's not there - but if TextMatchStyle.SUBSTRING (and perhaps TextMatchStyle.STARTS_WITH) are case-insensitive, this should be OK for the most use cases. The rest can use setPickListFilterCriteriaFunction.

      Best regards,
      Blama

      Comment

        #4
        Hi Isomorphic,

        I just saw that the code generated for the HSQLDB engine in the BuiltInDS sample is using LOWER().

        For my Oracle this is definitely not the case. I tried to remove my subclassed SQLDataSource, which didn't change anything.

        The only thing I could imagine is that my settings somehow render SmartGWT to use MySQL settings and that the syntax differences are so little that I didn't hit an error, yet.

        Could you have a look at my server.properties (now whole file attached) and see if you find anything suspicious?
        As I saw in the Admin Console that in the column "Database Type" "oracle" is written, I really doubt that something is wrong here, but you never know.

        I attached a screenshot of the Admin Console.

        Best regards,
        Blama

        server.properties:
        Code:
        # The webRoot directory:
# the directory that the servlet engine regards as the place where applications 
# that use the servlet engine should be installed.  Generally, it is safe to leave
# this at the default setting of __AUTODETECT__.  When the SmartClient server is
# started, it logs a message to stdout telling you the autodetected path to your
# webRoot.  If this path is not your actual webRoot, then you'll want to override
# this config parameter here.
#
# Valid values: 
#
# 1. Absolute path to the webRoot directory
#
# 2. Special token:  __AUTODETECT__
#    When this token is used, SmartClient attempts to auto-detect the webRoot using
#    standard servlet APIs.  This may or may not work - depending on your
#    container type and deployment type.  For example, WAR/EAR deployments
#    on some containers never make it to disk, and so the container refuses
#    to provide the webRoot path.
#  
#    If SmartClient cannnot detect the webRoot, it sets the webRoot to
#    __USE_CONTAINER__ (see below).
#
# 3.  Special token: __USE_CONTAINER__
#     When this token is used, SmartClient uses standard servet APIs for accessing
#     filesystem resources.  This is slower than direct file access and, since
#     the servlet APIs provide no mechanism for writing to disk, means that some
#     development tools like the FileAssembler will not work.
#
#webRoot: __AUTODETECT__
webRoot: C:/Program Files/Apache Software Foundation/Tomcat 7.0/webapps/lms

# Set this to the GWT module name.
gwtModuleName: lms

# if you've moved the isomorphic directory from its default location in webRoot,
# set the root-relative path to it here
#
# For example, if in your deployment the 'isomorphic' dir is in /foo/bar, then set
# then you'll need to set this to foo/bar/isomorphic
isomorphicPathRootRelative: $gwtModuleName/sc


# -------------- PICK DATABASE TO USE --------------------
#
# The SmartClient SDK ships with examples that use a database as the persistence
# layer.  By default, the SDK uses a built-in version of HSQLDB, but you can
# specify a different database to use here.

# which database do you want to use?  HSQLDB is enabled by default.
# sql.defaultDatabase: HSQLDB

# If you want to use Mysql instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: Mysql

# If you want to use Oracle instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
sql.defaultDatabase: Oracle
sql.useAnsiJoins: true

sql.Oracle.database.type: oracle
sql.Oracle.autoJoinTransactions: true
sql.Oracle.database.supportsSQLLimit: false

sql.Oracle.interface.credentialsInURL: true
sql.Oracle.interface.type: dataSource

sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.driver.serverName: localhost
sql.Oracle.driver.portNumber: 1521
sql.Oracle.driver.databaseName: xe
sql.Oracle.driver.user: xxx
sql.Oracle.driver.password: xxx

sql.Oracle.driver.driverType: thin
sql.Oracle.driver.networkProtocol: tcp
sql.Oracle.driver.context: 

#ADMIN:
sql.OracleAdmin.database.type: oracle
sql.OracleAdmin.autoJoinTransactions: true
sql.OracleAdmin.database.supportsSQLLimit: false

sql.OracleAdmin.interface.credentialsInURL: true
sql.OracleAdmin.interface.type: dataSource

sql.OracleAdmin.driver: oracle.jdbc.pool.OracleDataSource
sql.OracleAdmin.driver.serverName: localhost
sql.OracleAdmin.driver.portNumber: 1521
sql.OracleAdmin.driver.databaseName: xe
sql.OracleAdmin.driver.user: yyy
sql.OracleAdmin.driver.password: yyy

sql.OracleAdmin.driver.driverType: thin
sql.OracleAdmin.driver.networkProtocol: tcp
sql.OracleAdmin.driver.context: 


# If you want to use Postgres instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: PostgreSQL

# If you want to use DB2 instead, uncomment the following line
# and comment all other sql.defaultDatabase definitions
#sql.defaultDatabase: DB2

# -------------- SETTINGS FOR HSQLDB --------------------

#sql.HSQLDB.database.type: hsqldb
#sql.HSQLDB.interface.type: driverManager

#sql.HSQLDB.driver: org.hsqldb.jdbcDriver
#sql.HSQLDB.driver.url: jdbc:hsqldb:hsql://localhost/isomorphic


# -------------- LOADING APP AND DATASOURCE DEFINITIONS --------------------

# Where the system looks for DataSource definition files ([dataSourceId].ds.xml or
# [dataSourceID].ds.js).  It's useful to put all your DataSources in one 
# directory since DataSources are frequently shared between applications.  
# "project.datasources" is also where the DataSource Importer tool looks 
# for available DataSources.
project.datasources: $webRoot/ds, $webRoot/types  
project.ui: $webRoot/shared/ui
project.apps: $webRoot/shared/app

# -------------- Other settings --------------------
# The setting RPCManager.enabledBuiltinMethods enables or disables the BuiltInRPCs - RPC calls
# that are built into the SmartClient Server.  The setting below reflects the framework default
# of enabling only those RPCs that are typically needed in an application.
# 
# See the JavaDoc for com.isomorphic.rpc.BuiltinRPC and com.isomorphic.tools.BuiltinRPC for all
# available builtinRPCs and their behavior.
# 
# Note that many of the BuiltinRPCs are designed for use by tools such as Visual Builder, and
# provide services such as direct access to the file system (for load and save of screens) that
# would be unsafe to expose to untrusted users.
#
RPCManager.enabledBuiltinMethods: getLogNames, getPdfObject, xmlToJS, uploadProgressCheck, exportClientData, downloadClientExport, setAttributes

#See http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/ServerScript.html
script.defaultLangauge: "java"

#See http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/serverds/DataSource.html#requiresAuthentication
#See http://www.smartclient.com/smartgwtee/javadoc/com/smartgwt/client/docs/serverds/OperationBinding.html#requiresRole
authentication.defaultRequired: true
        Attached Files

        Comment

          #5
          We can't reproduce this, trying the use case you describe with a real Oracle back end. Can you please post the server logs from around that snippet of generated SQL you posted - we are particularly interested in seeing the serialized request that went over the wire (this will appear in the logs a page or two before the dumped SQL statement)

          Comment

            #6
            Hi Isomorphic,

            I had a suspicion after this thread.

            Turns out the problem might be related. My DMI, that inserts additional Criteria is
            Code:
            	public DSResponse fetchChildResellers(DSRequest dsRequest, HttpServletRequest servletRequest) throws Exception {
		ArrayList<Long> validResellers = new ArrayList<Long>();
		validResellers.add(1L);
		validResellers.add(2L);

		if (validResellers != null && validResellers.size() > 0) {
			dsRequest.addToCriteria("ID", DefaultOperators.InSet.getID(), validResellers);
		}
		return dsRequest.execute();
	}
            If I comment out the dsRequest.addToCriteria(...), the generated SQL contains the LOWER(): "WHERE (LOWER (t_reseller.name) LIKE '%soft%' ESCAPE '\')".

            If I somewhere (either DMI or SQLDataSource subclass) do dsRequest.addToCriteria(...), the generated clause is: "(t_reseller.name LIKE '%soft%' ESCAPE '\' AND t_reseller.name IS NOT NULL)".

            For reference, the client-request is:
            Code:
            === 2014-01-23 17:10:19,280 [ec-4] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        NAME:"soft"
    },
    operationConfig:{
        dataSource:"T_RESELLER",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    sortBy:[
        "NAME"
    ],
    componentId:"isc_PickListMenu_1",
    appID:"builtinApplication",
    operation:"fetchChildResellers",
    oldValues:{
        NAME:"soft"
    },
    progressiveLoading:true
}
            Best regards,
            Blama

            Comment

              #7
              Please see also this post. The bugs are definitely related.

              Best regards,
              Blama

              Comment

                #8
                A fix for this has been committed for 9.0, 9.1 and 10.0 and should be present in nightly builds as of tomorrow (Saturday). Please try your use case and let us know how you get on.

                Comment

                  #9
                  Hi Isomorphic,

                  in my real world use case this is fixed now, thank you (SNAPSHOT_v9.1d_2014-01-25).

                  The one related to http://forums.smartclient.com/showthread.php?t=29234 is still there in some cases. I'll reply there.

                  Best regards,
                  Blama

                  Comment

                  Previous template Next
                  Working...
                  X