View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0002180SymmetricDSBugpublic2015-02-05 15:432022-08-02 18:59
Reporterdavemelo Assigned Toelong  
Priorityhigh 
Status closedResolutionfixed 
Target Version3.14.0Fixed in Version3.14.0 
Summary0002180: H2 timestamp format creates conflict against ORACLE timestamp format
Description
When H2 traslate data from Oracle timestamp columns, loses precission in timestamp. For example, if I have a Timestamp in Oracle like 2015-01-30 09:03:31.0214578, when it is downloaded to H2 Timestamp column, the value is
2015-01-30 09:03:31.021. When i make a change in that row from H2 database, it makes an SYM_DATA and the old value still 2015-01-30 09:03:31.021. When i make a syncronization, i have a conflict, because timestamp values are different between H2 and Oracle, when in real are the same date.

Tagsconflict manager

Relationships

related to 0005239 closedelong Conflict resolution doesn't work if timestamp is in PK and different fractional second precision between databases 

Activities

davemelo

2015-02-05 15:47

reporter   ~0000659

Is probably that for traslate the information, symmetrics for doing the "to_string" of the column values are using the SimpleDateFormat, and in this way you only can format 3 decimals in date.

The problem could be for conflict detection, that i have to use the same pattern (SimpleDateFormat) and not use SQL compare pattern.

davemelo

2015-02-05 17:42

reporter   ~0000660

I think this class(OracleDmlStatement) has the problem:

    @Override
    public void appendColumnEquals(StringBuilder sql, Column[] columns, boolean[] nullValues, String separator) {
        for (int i = 0; i < columns.length; i++) {
            if (columns[i] != null) {
                if (nullValues[i]) {
                    sql.append(quote).append(columns[i].getName()).append(quote).append(" is NULL")
                            .append(separator);
                } else if (columns[i].getMappedTypeCode() == -101) {
                    sql.append(quote).append(columns[i].getName()).append(quote)
                            .append(" = TO_TIMESTAMP_TZ(?, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')")
                            .append(separator);
                } else if (columns[i].getJdbcTypeName().toUpperCase().contains(TypeMap.GEOMETRY)) {
                    sql.append(quote).append(columns[i].getName()).append(quote).append(" = ")
                            .append("SYM_WKT2GEOM(?)").append(separator);
                } else {
                    sql.append(quote).append(columns[i].getName()).append(quote).append(" = ?")
                            .append(separator);
                }
            }
        }

When is detecting a conflict with IGNORE and the Conflict Type is USE_OLD_DATA, , when is building the update sql sentence, if a TIMESTAMP (code 93 not -101) comes, there are using the code:

sql.append(quote).append(columns[i].getName()).append(quote).append(" = ?")
                            .append(separator);

The trigger captures sym_data from timestamp data always with 3 digits: decode(:new."GUCW_TIMESTAMP", null, '', concat(concat('"',to_char(:new."GUCW_TIMESTAMP", 'YYYY-MM-DD HH24:MI:SS.FF3')),'"')),

Because of that, for detects conflicts you need to make the same comparations between the timestams , with the sames 3 digits.

In the problem case example, it will compare 2015-01-30 09:03:31.0214578 with
2015-01-30 09:03:31.021, that always will cause a conflict, and it´s not true.

davemelo

2015-02-05 18:08

reporter   ~0000661

The resolution could be, for 93 column Type:

we convert timestamp column to char with same precission

timestamp --> to_char (3)
char data --> to_timestamp (9) --> to_char (3)
and compare same resolution.

The final code:

else if (columns[i].getMappedTypeCode() == Types.TIMESTAMP) {
                    sql.append("TO_CHAR(" + quote + columns[i].getName() + quote + ", 'YYYY-MM-DD HH24:MI:SS.FF3')")
                            .append(" = TO_CHAR(TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS.FF9'), 'YYYY-MM-DD HH24:MI:SS.FF3')")
                            .append(separator);
                }

davemelo

2015-02-06 10:04

reporter   ~0000662

The last solution was wrong, bacause that method is used for compute de SET statement and the WHERE statatement. And the ? param that is passed when is a TIMESTAMP is a Timestamp Object value. So, the right solution would be:

else if (columns[i].getMappedTypeCode() == Types.TIMESTAMP) {
                    //In case of WHERE part
                    if (separator.equals(" and "))
                    {
                    sql.append("TO_CHAR(" + quote + columns[i].getName() + quote + ", 'YYYY-MM-DD HH24:MI:SS.FF3')")
                            .append(" = TO_CHAR(?, 'YYYY-MM-DD HH24:MI:SS.FF3')")
                            .append(separator);
                    }else
                    {
              //in case of SET part..
                        sql.append(quote).append(columns[i].getName()).append(quote).append(" = ?")
                        .append(separator);
                    }
                }

Issue History

Date Modified Username Field Change
2015-02-05 15:43 davemelo New Issue
2015-02-05 15:47 davemelo Note Added: 0000659
2015-02-05 17:42 davemelo Note Added: 0000660
2015-02-05 18:08 davemelo Note Added: 0000661
2015-02-06 10:04 davemelo Note Added: 0000662
2019-04-23 16:47 elong Tag Attached: conflict manager
2022-08-02 18:59 elong Relationship added related to 0005239
2022-08-02 18:59 elong Assigned To => elong
2022-08-02 18:59 elong Status new => closed
2022-08-02 18:59 elong Resolution open => fixed
2022-08-02 18:59 elong Fixed in Version => 3.14.0