Workaround for "default from systimestamp to SYSTIMESTAMP"

[Breck_Carter]

The following error dialog appeared in the MobiLink version 12 synchronization model wizard when used with an Oracle consolidated database:

Differences from the closest known version (12.0.0):
Table 'ML_RA_DEPLOYED_TASK' has changed:
 Column 'ASSIGNMENT_TIME' has changed:
  default from systimestamp to SYSTIMESTAMP

The syncora script was manually edited (see later) and a successful workaround was to change two default values back from SYSTIMESTAMP to systimestamp like they were in the original script.

I am guessing there is a string data comparison somewhere in the MobiLink code which should be case-insensitive because the string data default value was originally coded as a keyword rather than a literal, but it is (incorrectly) being treated as in a case-sensitive fashion.

[at this point, my head hurts... it's a keyword... no, it's a string value... stop, you're both right]

Here's what the original syncora.sql script contains (only these two tables say default systimestamp, all the others have default SYSTIMESTAMP... it doesn't matter to Oracle, but it seems to matter to MobiLink):

create table ml_ra_deployed_task (
    task_instance_id               number( 20 ) not null,
    aid                            integer not null,
    task_id                        number( 20 ) not null,
    assignment_time                timestamp default systimestamp not null,
    state                          varchar2( 4 ) default 'P' not null,
    previous_exec_count            number( 20 ) default 0 not null,
    previous_error_count           number( 20 ) default 0 not null,
    previous_attempt_count         number( 20 ) default 0 not null,
    reported_exec_count            number( 20 ) default 0 not null,
    reported_error_count           number( 20 ) default 0 not null,
    reported_attempt_count         number( 20 ) default 0 not null,
    last_modified                  timestamp not null,
    unique( aid, task_id ),
    primary key( task_instance_id ), 
    constraint ml_ra_deployed_aid foreign key( aid ) references ml_ra_agent( aid ),
    constraint ml_ra_deployed_tid foreign key( task_id ) references ml_ra_task( task_id )
)
/

create table ml_ra_event (
    event_id                       number( 20 ) not null,
    event_class                    varchar2( 4 ) not null,
    event_type                     varchar2( 8 ) not null,
    aid                integer null,
    task_id                number( 20 ) null,
    command_number                 integer null,
    run_number                     number( 20 ) null,
    duration                       integer null,
    event_time                     timestamp not null,
    event_received                 timestamp default systimestamp not null,
    result_code                    number( 20 ) null,
    result_text                    clob null,
    primary key (event_id) 
)
/

Here is what the manually-edited script contains...

CREATE TABLE &&fwm_ml..ml_ra_deployed_task (
    task_instance_id               NUMBER(20) NOT NULL,
    aid                            INTEGER NOT NULL,
    task_id                        NUMBER(20) NOT NULL,
    assignment_time                TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    state                          VARCHAR2(4) DEFAULT 'P' NOT NULL,
    previous_exec_count            NUMBER(20) DEFAULT 0 NOT NULL,
    previous_error_count           NUMBER(20) DEFAULT 0 NOT NULL,
    previous_attempt_count         NUMBER(20) DEFAULT 0 NOT NULL,
    reported_exec_count            NUMBER(20) DEFAULT 0 NOT NULL,
    reported_error_count           NUMBER(20) DEFAULT 0 NOT NULL,
    reported_attempt_count         NUMBER(20) DEFAULT 0 NOT NULL,
    last_modified                  TIMESTAMP NOT NULL,
    CONSTRAINT ml_ra_deployed_aid FOREIGN KEY(aid) REFERENCES &&fwm_ml..ml_ra_agent(aid),
    CONSTRAINT ml_ra_deployed_tid FOREIGN KEY(task_id) REFERENCES &&fwm_ml..ml_ra_task(task_id)
) TABLESPACE &&fwm_ml_data
/

CREATE TABLE &&fwm_ml..ml_ra_event (
    event_id                       NUMBER(20) NOT NULL,
    event_class                    VARCHAR2(4) NOT NULL,
    event_type                     VARCHAR2(8) NOT NULL,
    aid                INTEGER NULL,
    task_id                NUMBER(20) NULL,
    command_NUMBER                 INTEGER NULL,
    run_NUMBER                     NUMBER(20) NULL,
    duration                       INTEGER NULL,
    event_time                     TIMESTAMP NOT NULL,
    event_received                 TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    result_code                    NUMBER(20) NULL,
    result_text                    CLOB NULL
) 
LOB (result_text) STORE AS (TABLESPACE &&fwm_ml_clob DISABLE STORAGE IN ROW)
/

[Former Member]

It correctly detected a difference from the ML system setup that would result from using the unmodified syncora.sql, though in this case the difference doesn't matter.

To answer the question, you should be able to click OK and continue anyway (at least you can when deploying a sync model).

The algorithm to check the ML system setup doesn't try to understand column defaults, it just uses the default value from the ODBC metadata in a case-sensitive string comparison. The comparison needs to be case sensitive for any column defaults that are strings.

[Breck_Carter]

I'll let you know how the client responds.

[Former Member]

Forgot to put a smiley or sheepish grin on the first sentence! By "correctly" I just meant that a change to syncora.sql was detected, but I agree that it was not a DDL change (so ideally shouldn't have been detected).

[VolkerBarth]

@Breck: It seems to fit to the issue: What exactly have you edited? The SQLA diff doesn't show any change (in my eyes, at least) but claims there's one...