Oracle: When updating "date" field, time is also updated

[pavo123]

Hi Isomorphic,

I found a minor bug which occurs when updating "date" field in Oracle DB (using PostgreSQL works fine).

ds.xml:

<field name="DATE_FIELD" type="date">

serverObject:

        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put("DATE_FIELD", new Date());

        DSRequest updateReq = new DSRequest("DS", DataSource.OP_UPDATE);
        updateReq.setValues(valueMap);
        updateReq.setAllowMultiUpdate(true);
        updateReq.execute();

Query:

UPDATE DS SET DATE_FIELD=TO_DATE('2019-05-23 17:22:49','YYYY-MM-DD HH24:MI:SS')....

Although the type of the field is "date", time is also transmitted.

Docs about it: DateFormatAndStorage

Best regards
Pavo

[Isomorphic]

This isn't happening for us.. the only way we can think of that you could get into this state is if you had customized sql.defaultDateFormat in server.properties, and make it into a datetime format.

[pavo123]

Hi Isomorphic,

that is not the reason, I don't use sql.defaultDateFormat in server.properties and I have the same settings for PostgreSQL DB, and it works fine there.
Using v12.0p_2019-05-23/PowerEdition Deployment (built 2019-05-23).

Maybe this test case will help you.

T_CHAT_PROGRESS.ds.xml

<DataSource xmlns="lmscompany/ds" xmlns:fmt="lmscompany/fmt" dbName="Oracle" tableName="T_CHAT_PROGRESS" ID="T_CHAT_PROGRESS" serverType="sql"
     serverOnly="false">
    <fmt:bundle basename="com.lmscompany.lms.server.i18n.DSXMLResources-utf8" encoding="utf-8" />
    <fields>
        <field primaryKey="true" hidden="true" name="ID" type="sequence" />
        <field name="LASTOPEN" type="date" />
    </fields>
    <serverObject lookupStyle="new" className="com.smartgwt.sample.server.listener.TEST"/>
    <operationBindings>
        <operationBinding operationType="add" />
        <operationBinding operationType="update" />
        <operationBinding operationType="validate" />
        <operationBinding operationType="fetch" />
    </operationBindings>
</DataSource>

TEST.java

package com.smartgwt.sample.server.listener;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

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

public class TEST {

    public DSResponse fetch(DSRequest request, HttpServletRequest servletRequest) throws Exception {

        Map<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put("LASTOPEN", new Date());

        DSRequest updateReq = new DSRequest("T_CHAT_PROGRESS", DataSource.OP_UPDATE);
        updateReq.setValues(valueMap);
        updateReq.setAllowMultiUpdate(true);
        return updateReq.execute();
    }

}

server.properties

webRoot: __AUTODETECT__
gwtModuleName: builtinds
isomorphicPathRootRelative: $gwtModuleName/sc

sql.defaultDatabase: Oracle

sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.driver.url: jdbc:oracle:thin:@localhost:1521/XE
sql.Oracle.driver.user: TEST001
sql.Oracle.driver.password: TEST001


project.datasources: $webRoot/ds
project.project: $webRoot/shared/ui
project.ui: $webRoot/shared/ui
project.apps: $webRoot/shared/app
modulesDir: modules/

BuiltInDS.java

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;

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

    /**
     * This is the entry point method.
     */
    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();
            }
        });

        final ListGrid grid = new ListGrid();
        grid.setDataSource("T_CHAT_PROGRESS");
        grid.fetchData();

        grid.draw();
    }

}

BuiltInDS.html

       ........
    <!--load the datasources-->
    <script src="builtinds/sc/DataSourceLoader?dataSource=T_CHAT_PROGRESS"></script>
     ................

Definition of the field in Oracle DB:

"LASTOPEN" DATE CONSTRAINT "NNC_CHAT_PROGRESS_LASTOPEN" NOT NULL ENABLE

Query:

=== 2019-05-24 09:41:15,673 [6-36] INFO  SQLDriver - [builtinApplication.null] Executing SQL query on 'Oracle' using connection '518240316': UPDATE T_CHAT_PROGRESS SET LASTOPEN=TO_DATE('2019-05-24 09:40:59','YYYY-MM-DD HH24:MI:SS') WHERE ('1'='1')

Best regards
Pavo

[Isomorphic]

This is fixed and will be available for download in nightly builds since June 5 (tomorrow).