problem with oracle function in customSelectExpression

[Developer12145]

Hi Isomorphic,

please see this testcase:

<DataSource
    ID="T_TEST"
    serverType="sql"
    tableName="T_TEST"
    dbName="Oracle"
> 
    <fields>
        <field name="ID"              title="ID"                     type="integer"/>
        <field name="dateAsText"      title="dateAsText"            type="text" />
        <field name="curDate"         title="curDate"               type="datetime" customSelectExpression="fn_convert_date(dateAsText)" />
    </fields>

    <operationBindings>
        <operationBinding operationType="fetch" />
    </operationBindings>
</DataSource>

create table t_test (
id integer,
dateAsText varchar2(50 CHAR)
);

insert into t_test( id, dateAsText) values (1, 'Thu, 11 Apr 2019 16:52:28 +0200');

create or replace FUNCTION fn_convert_date ( datestring IN VARCHAR2 ) RETURN DATE
    DETERMINISTIC
    IS
BEGIN
    DECLARE
        dateconverted TIMESTAMP WITH TIME ZONE := NULL;
    BEGIN
        IF
            datestring IS NULL
        THEN
            RETURN NULL;
        END IF;

        BEGIN
            dateconverted := to_timestamp_tz(datestring,'DY,DD MM YYYY HH24:MI:SS TZHTZM');
            RETURN TO_DATE(
                TO_CHAR(
                    dateconverted AT TIME ZONE 'Europe/Berlin',
                    'YYYY-MON-DD HH24:MI:SS'
                ),
                'YYYY-MON-DD HH24:MI:SS'
            );
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END;
    RETURN NULL;
END fn_convert_date;

# 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__

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

# 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
# Publish the JDBC endpoint for the defaultDatabase via JNDI at the specified path for use by
# other frameworks such as Spring, Hibernate, etc.
# Note that we do not use the java:comp or java:comp/env prefix because that
# subtree is read-only on Tomcat (and possibly other containers) due to security
# considerations
sql.defaultDatabase.jndi.publish.path: isomorphic/jdbc/defaultDatabase


# 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

# 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


sql.defaultDatabase: Oracle
sql.useAnsiJoins: true

# Apache Commons DBCP Database connection pooling
sql.pool.enabled: true
sql.pool.minIdle: 1
sql.pool.maxActive: 10
sql.pool.whenExhaustedAction: block
sql.pool.testWhileIdle: true
sql.pool.timeBetweenEvictionRunsMillis: 15000
sql.pool.minEvictableIdleTimeMillis: 29000

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.pingTest: select 1 from dual

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: BUILTINDS
sql.Oracle.driver.password: BUILTINDS

sql.Oracle.driver.driverType: thin
sql.Oracle.driver.networkProtocol: tcp
sql.Oracle.driver.context: 
sql.Oracle.useUTCDateTimes: false
# If you want to use Postgres instead, uncomment the fol

# -------------- 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:file:$webRoot/WEB-INF/db/hsqldb/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
project.project: $webRoot/shared/ui
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: getPdfObject, xmlToJS, uploadProgressCheck, exportClientData, downloadClientExport, setAttributes

# Note: modulesDir is only used with the loadISC and loadModules JSP tags; if
# you intend to use those tags, do not change this setting
modulesDir: modules/

<!DOCTYPE html>

<html>
  <head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
    <!--                                           -->
    <!-- Any title is fine                         -->
    <!--                                           -->
    <title>BuiltInDS</title>

    <!-- IMPORTANT : You must set the variable isomorphicDir to [MODULE_NAME]/sc/ so that the SmartGWT resource are 
      correctly resolved -->    
    <script> var isomorphicDir = "builtinds/sc/"; </script>

    <!--                                           -->
    <!-- This script loads your compiled module.   -->
    <!-- If you add any GWT meta tags, they must   -->
    <!-- be added before this line.                -->
    <!--                                           -->      
    <script type="text/javascript" language="javascript" src="builtinds/builtinds.nocache.js"></script>

    <!-- The following script is required if you're running (Super)DevMode and are using module
         definitions that contain <script> tags.  Normally, this script is loaded automatically
         by builtinds.nocache.js above, but this isn't possible when (Super)DevMode is running.
         Note: it should not create any issue to always load it below (even if already loaded). -->
    <script type="text/javascript" language="javascript" src="builtinds/loadScriptTagFiles.js"></script>

  </head>

  <!--                                           -->
  <!-- The body can have arbitrary html, or      -->
  <!-- you can leave the body empty if you want  -->
  <!-- to create a completely dynamic UI.        -->
  <!--                                           -->
  <body>

    <!--load the datasources-->
   <!--  <script src="builtinds/sc/DataSourceLoader?dataSource=supplyItem,animals,animals2,employees"></script> -->
    <script src="builtinds/sc/DataSourceLoader?dataSource=T_TEST"></script>

    <!-- OPTIONAL: include this if you want history support -->
    <iframe src="javascript:''" id="__gwt_historyFrame" tabIndex='-1' style="position:absolute;width:0;height:0;border:0"></iframe>

  </body>
</html>

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.layout.HLayout;
import com.smartgwt.client.widgets.layout.VStack;

/**
 * Entry point classes define <code>onModuleLoad()</code>.
 */
public class BuiltInDS implements EntryPoint {
    private ListGrid boundGrid;

    /**
     * This is the entry point method.
     */
    public void onModuleLoad() {
        KeyIdentifier debugKey = new KeyIdentifier();
        debugKey.setCtrlKey(true);
        debugKey.setKeyName("D");
        DefaultAppearanceSetter.setDefaultAppearance();
        Page.registerKey(debugKey, new PageKeyHandler() {
            public void execute(String keyName) {
                SC.showConsole();
            }
        });

        VStack vStack = new VStack();
        vStack.setLeft(175);
        vStack.setTop(75);
        vStack.setHeight(500);
        vStack.setWidth("70%");
        vStack.setMembersMargin(20);
        boundGrid = new ListGrid() {
            {
                setDataSource("T_TEST");
            }
        };

        boundGrid.setWidth100();
        boundGrid.setHeight100();
        HLayout h = new HLayout();
        h.setHeight100();
        vStack.addMember(boundGrid);
        vStack.draw();
        boundGrid.fetchData();
    }

}

and place the "ojdbc6.jar" here: smartgwtpower-12.0p\samples\built-in-ds\war\WEB-INF\lib

When I let run the above BuiltInDS.java, I would expect that i get: ID, dateAsText and curDate, but I only get ID and dateAsText, curDate stays empty in the grid.
Its unexpected, because the query that is created executes as expected against the database, please see this generated query from the log:

Query: SELECT T_TEST.ID, T_TEST.dateAsText, fn_convert_date(dateAsText) AS curDate FROM T_TEST WHERE ('1'='1')

even so, I don`t get the value shown in the grid.

I also cannot use virtual columns right now, because we are moving to postgres, what does not support those. Thats why the customSelectExpression is declared in the .ds.xml

I tries as type in curDate the following: text, date, datetime (just to exclude that as cause)

Any help here would be highly appreciated.

Setup: SmartClient Version: v12.0p_2019-04-26/PowerEdition Deployment (built 2019-04-26)
Database: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Thanks in Advance,
Kind Regards

[Isomorphic]

It's not clear that we're going to be able to help here, as this could be an Oracle issue. We are just executing the query in the standard JDBC way, then traversing the results in the standard JDBC way - if the data isn't there, there's nothing we can do on our end.

So, things to check:

1. you mention only that the data isn't there in the grid. What about in the Developer Console? What about if you inspect the DSResponse in a DMI? Do you see anything (including nulls) or just no property at all?

2. try executing the same query via JDBC - do you see the data in the ResultSet returned?

[Blama]

Hi Isomorphic,

I'll step in as I saw the problem as well:
The problem is that the generated query is correct.
If you run it in SQL Developer you'll get the data as expected.
Nevertheless the data is not in the response send to the client (=field not in the response in Developer Console RPC Tab, while all other fields are there as expected).
So it seems the problem is that you somehow don't get the data from the response.

Does this already help? You should see the same problem with the testcase from Developer12145.

Best regards
Blama

[Isomorphic]

That helps answer the first half of #1 above. There's still the second half of #1, and #2.

[Developer12145]

As it turns out, testing with JDBC gives us the same problem. So its not framework-related.