Announcement

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

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

    Hi Isomorphic,

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

    ds.xml:
    Code:
    <field name="DATE_FIELD" type="date">
    serverObject:
    Code:
            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:
    Code:
    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

    #2
    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.

    Comment


      #3
      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
      Code:
      <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
      Code:
      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
      Code:
      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
      Code:
      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
      Code:
             ........
          <!--load the datasources-->
          <script src="builtinds/sc/DataSourceLoader?dataSource=T_CHAT_PROGRESS"></script>
           ................
      Definition of the field in Oracle DB:
      Code:
      "LASTOPEN" DATE CONSTRAINT "NNC_CHAT_PROGRESS_LASTOPEN" NOT NULL ENABLE
      Query:
      Code:
      === 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

      Comment


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

        Comment

        Working...
        X