View Issue Details

IDProjectCategoryView StatusLast Update
0001286SymmetricDSBugpublic2014-10-27 11:52
ReportersngkwAssigned Tochenson 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.3.6 
Target Version3.6.11Fixed in Version3.6.11 
Summary0001286: Failed to create trigger when table contains timestamp with time zone column in Oracle
DescriptionSimilar to 0000615: When a timestamp with time zone column is part of a primary key or part of a table w.out a primary key, then the trigger fails to create.

But even we have a primary key, it still fails.

We are using Oracle 11g and 11g driver.
TagsNo tags attached.

Activities

chenson

2013-06-19 08:03

administrator   ~0000278

What is the error?

chenson

2013-06-19 09:19

administrator   ~0000279

Can a timestamp with time zone be part of a primary key?

I get this:

Error: ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

SQLState: 99999
ErrorCode: 2329
Position: 103

sngkw

2013-06-19 22:19

reporter  

trigger.jpg (120,058 bytes)
trigger.jpg (120,058 bytes)

sngkw

2013-06-19 22:22

reporter   ~0000286

The log shows the following:

11:54:11 AM CST ERROR [TriggerRouterService] [server-job-19] Failed to retrieve tables for trigger with id of VAT
11:54:11 AM CST ERROR [TriggerRouterService] [server-job-19] Could not find any database tables matching 'VAT' in the datasource that is configured
11:54:11 AM CST INFO [TriggerRouterService] [server-job-19] Done synchronizing triggers


These are the column definitions in VAT, ID is primary key

ID NUMBER(38,0)
CODE VARCHAR2(20 CHAR)
CREATED_BY VARCHAR2(300 CHAR)
CREATED_DATE DATE
MODIFIED_BY VARCHAR2(300 CHAR)
MODIFIED_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE

chenson

2013-06-22 15:00

administrator   ~0000287

This happens when the table can't be found in the database.

sngkw

2013-06-23 23:01

reporter   ~0000289

It is able to find the table by Auto Create trigger function.
But when I try to link with router, exception occurs.

When I changed the data type from TIMESTAMP(6) WITH LOCAL TIME ZONE to TIMESTAMP(6).
The exception does not occur.

snpe

2013-07-23 22:48

reporter   ~0000353

Oracle JDBC driver returns SQL type -102 for column type "TIMESTAMP(6) WITH LOCAL TIME ZONE".
SymmetricDS handles SQL type -101 (TIMESTAMP(6) WITH TIME ZONE), but not SQL type -102.

Attached is a patch.

I have tested with Oracle 9 (it's the same in newer versions), Oracle JDBC 11 and MySQL 5.5.

Oracle table:

create table test(
ID NUMBER(38) not null,
CODE VARCHAR2(20),
CREATED_BY VARCHAR2(300),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR2(300),
MODIFIED_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE,
MODIFIED_DATE2 TIMESTAMP(6) WITH TIME ZONE,
MODIFIED_DATE3 TIMESTAMP,
PRIMARY KEY(ID)
)

MySQL table:

create table test(
ID INTEGER NOT NULL,
CODE VARCHAR(20),
CREATED_BY VARCHAR(300),
CREATED_DATE DATE,
MODIFIED_BY VARCHAR(300),
MODIFIED_DATE TIMESTAMP,
MODIFIED_DATE2 TIMESTAMP,
MODIFIED_DATE3 TIMESTAMP,
PRIMARY KEY(ID)
)

snpe

2013-07-23 22:49

reporter  

oracleTimestampltz.diff (11,213 bytes)
Index: symmetric-oracle/src/main/java/org/jumpmind/symmetric/io/data/writer/OracleBulkDatabaseWriter.java
===================================================================
--- symmetric-oracle/src/main/java/org/jumpmind/symmetric/io/data/writer/OracleBulkDatabaseWriter.java	(revision 7565)
+++ symmetric-oracle/src/main/java/org/jumpmind/symmetric/io/data/writer/OracleBulkDatabaseWriter.java	(working copy)
@@ -200,6 +200,8 @@
                 return "varchar(4000)";
             case Types.DATE:
             case Types.TIME:
+            case OracleTypes.TIMESTAMPLTZ:
+                return "timestamp with local time zone";
             case OracleTypes.TIMESTAMPTZ:
                 return "timestamp with time zone";
             case Types.TIMESTAMP:
@@ -227,6 +229,7 @@
             case Types.DATE:
             case Types.TIME:
             case OracleTypes.TIMESTAMPTZ:
+            case OracleTypes.TIMESTAMPLTZ:
             case Types.TIMESTAMP:
                 return String.format("%s_%s_t", procedurePrefix, "timestamp").toUpperCase();
             case Types.NUMERIC:
Index: symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriterConflictResolver.java
===================================================================
--- symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriterConflictResolver.java	(revision 7565)
+++ symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriterConflictResolver.java	(working copy)
@@ -247,7 +247,7 @@
         
         Date loadingTs = null;
         Date existingTs = null;
-        if (column.getMappedTypeCode() == -101) {
+        if (column.getMappedTypeCode() == -101 || column.getMappedTypeCode() == -102) {
             // Get the existingTs with timezone
             String existingStr = writer.getTransaction().queryForObject(sql, String.class,
                     objectValues);            
Index: symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java
===================================================================
--- symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java	(revision 7565)
+++ symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java	(working copy)
@@ -36,6 +36,7 @@
         geometryColumnTemplate = "case when $(tableAlias).\"$(columnName)\" is null then to_clob('') else '\"'||replace(replace(SDO_UTIL.TO_WKTGEOMETRY($(tableAlias).\"$(columnName)\"),'\\','\\\\'),'\"','\\\"')||'\"' end";
         numberColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', '\"'||cast($(tableAlias).\"$(columnName)\" as number("+symmetricDialect.getTemplateNumberPrecisionSpec()+"))||'\"')" ;
         datetimeColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.FF3')),'\"'))" ;
+        dateTimeWithLocalTimeZoneColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char(cast($(tableAlias).\"$(columnName)\" as timestamp), 'YYYY-MM-DD HH24:MI:SS.FF')),'\"'))" ;
         dateTimeWithTimeZoneColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')),'\"'))" ;
         timeColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS')),'\"'))" ;
         dateColumnTemplate = "decode($(tableAlias).\"$(columnName)\", null, '', concat(concat('\"',to_char($(tableAlias).\"$(columnName)\", 'YYYY-MM-DD HH24:MI:SS')),'\"'))" ;
Index: symmetric-db/src/main/java/org/jumpmind/db/model/TypeMap.java
===================================================================
--- symmetric-db/src/main/java/org/jumpmind/db/model/TypeMap.java	(revision 7565)
+++ symmetric-db/src/main/java/org/jumpmind/db/model/TypeMap.java	(working copy)
@@ -88,6 +88,8 @@
     public static final String TIMESTAMP     = "TIMESTAMP";
 
     public static final String TIMESTAMPTZ     = "TIMESTAMPTZ";
+    
+    public static final String TIMESTAMPLTZ     = "TIMESTAMPLTZ";
 
     /** The string representation of the {@link java.sql.Types#TINYINT} constant. */
     public static final String TINYINT       = "TINYINT";
@@ -150,6 +152,7 @@
         registerJdbcType(Types.VARBINARY,     VARBINARY,     JdbcTypeCategoryEnum.BINARY);
         registerJdbcType(Types.VARCHAR,       VARCHAR,       JdbcTypeCategoryEnum.TEXTUAL);
         registerJdbcType(-101,                TIMESTAMPTZ,   JdbcTypeCategoryEnum.DATETIME);
+        registerJdbcType(-102,                TIMESTAMPLTZ,   JdbcTypeCategoryEnum.DATETIME);
 
         // only available in JDK 1.4 and above:
         if (PlatformUtils.supportsJava14JdbcTypes())
Index: symmetric-db/src/main/java/org/jumpmind/db/model/Column.java
===================================================================
--- symmetric-db/src/main/java/org/jumpmind/db/model/Column.java	(revision 7565)
+++ symmetric-db/src/main/java/org/jumpmind/db/model/Column.java	(working copy)
@@ -654,7 +654,7 @@
     }
     
     public boolean isTimestampWithTimezone() {
-        return jdbcTypeCode == -101 || (jdbcTypeName != null && jdbcTypeName.equals("timestamptz"));
+        return jdbcTypeCode == -101 || jdbcTypeCode == -102 || (jdbcTypeName != null && jdbcTypeName.equals("timestamptz"));
     }
     
     public boolean containsJdbcTypes() {
Index: symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDmlStatement.java
===================================================================
--- symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDmlStatement.java	(revision 7565)
+++ symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDmlStatement.java	(working copy)
@@ -43,6 +43,9 @@
                 if (columns[i].getMappedTypeCode() == -101) {
                     sql.append("TO_TIMESTAMP_TZ(?, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')")
                             .append(",");
+                } else if (columns[i].getMappedTypeCode() == -102) {
+                    sql.append("CAST(TO_TIMESTAMP_TZ(?, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') AS TIMESTAMP WITH TIME ZONE)")
+                    .append(",");
                 } else if (columns[i].getJdbcTypeName() != null && columns[i].getJdbcTypeName().toUpperCase().contains(TypeMap.GEOMETRY)) {
                     sql.append("SYM_WKT2GEOM(?)").append(",");
                 } else {
@@ -63,7 +66,7 @@
                 if (nullValues[i]) {
                     sql.append(quote).append(columns[i].getName()).append(quote).append(" is NULL")
                             .append(separator);
-                } else if (columns[i].getMappedTypeCode() == -101) {
+                } else if (columns[i].getMappedTypeCode() == -101 || columns[i].getMappedTypeCode() == -102) {
                     sql.append(quote).append(columns[i].getName()).append(quote)
                             .append(" = TO_TIMESTAMP_TZ(?, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')")
                             .append(separator);
Index: symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java
===================================================================
--- symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java	(revision 7565)
+++ symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java	(working copy)
@@ -34,6 +34,7 @@
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
+import java.util.TimeZone;
 
 import org.apache.commons.codec.binary.Base64;
 import org.apache.commons.codec.binary.Hex;
@@ -501,7 +502,14 @@
                         try {
                             return Timestamp.valueOf(value);
                         } catch (IllegalArgumentException ex) {
-                            return FormatUtils.parseDate(value, FormatUtils.TIMESTAMP_PATTERNS);
+							try {
+								return FormatUtils.parseDate(value, FormatUtils.TIMESTAMP_PATTERNS);
+							} catch (Exception e) {
+								int split = value.lastIndexOf(" ");
+								return FormatUtils.parseDate(value.substring(0, split).trim(),
+										FormatUtils.TIMESTAMP_PATTERNS,
+										TimeZone.getTimeZone(value.substring(split).trim()));
+							}
                         }
                     } else if (type == Types.TIME) {
                         return FormatUtils.parseDate(value, FormatUtils.TIME_PATTERNS);
Index: symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java
===================================================================
--- symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java	(revision 7565)
+++ symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java	(working copy)
@@ -76,6 +76,8 @@
     protected String dateColumnTemplate;
 
     protected String dateTimeWithTimeZoneColumnTemplate;
+    
+    protected String dateTimeWithLocalTimeZoneColumnTemplate;
 
     protected String geometryColumnTemplate;
 
@@ -667,6 +669,11 @@
                                             .isNotBlank(this.dateTimeWithTimeZoneColumnTemplate)) {
                                 templateToUse = this.dateTimeWithTimeZoneColumnTemplate;
                                 break;
+                            } else if (column.getMappedType().equals(TypeMap.TIMESTAMPLTZ)
+                                    && StringUtils
+                                    		.isNotBlank(this.dateTimeWithLocalTimeZoneColumnTemplate)) {
+                            	templateToUse = this.dateTimeWithLocalTimeZoneColumnTemplate;
+                            	break;
                             }
 
                         }
Index: symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java
===================================================================
--- symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java	(revision 7565)
+++ symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java	(working copy)
@@ -499,6 +499,8 @@
             obj = rs.getTimestamp(index);
         } else if (className != null && "oracle.sql.TIMESTAMPTZ".equals(className)) {
             obj = rs.getString(index);
+        } else if (className != null && "oracle.sql.TIMESTAMPLTZ".equals(className)) {
+            obj = rs.getString(index);
         } else if (className != null && className.startsWith("oracle.sql.DATE")) {
             String metaDataClassName = metaData.getColumnClassName(index);
             if ("java.sql.Timestamp".equals(metaDataClassName)
@@ -882,7 +884,7 @@
     }
 
     protected int verifyArgType(Object arg, int argType) {
-        if (argType == -101 || argType == Types.OTHER) {
+        if (argType == -101 || argType == -102 || argType == Types.OTHER) {
             return SqlTypeValue.TYPE_UNKNOWN;
         } else if ((argType == Types.INTEGER && arg instanceof BigInteger) ||
                 (argType == Types.BIGINT && arg instanceof BigDecimal)) {
oracleTimestampltz.diff (11,213 bytes)

chenson

2013-07-24 12:50

administrator   ~0000355

ah. timestamp with "local" timezone. somehow i missed the local last time i looked at this issue. thanks for the patch.

Related Changesets

SymmetricDS: master f454c5e8

2014-10-26 20:10:26

chenson

Details Diff
0001286: Failed to create trigger when table contains timestamp with time zone column in Oracle
0001286
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/oracle/OracleTriggerTemplate.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/model/TypeMap.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/AbstractDatabasePlatform.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File
mod - symmetric-oracle/src/main/java/org/jumpmind/symmetric/io/data/writer/OracleBulkDatabaseWriter.java Diff File

Issue History

Date Modified Username Field Change
2013-06-18 02:51 sngkw New Issue
2013-06-19 08:03 chenson Note Added: 0000278
2013-06-19 09:19 chenson Note Added: 0000279
2013-06-19 20:39 chenson Project SymmetricDS Pro => SymmetricDS
2013-06-19 22:19 sngkw File Added: trigger.jpg
2013-06-19 22:22 sngkw Note Added: 0000286
2013-06-22 15:00 chenson Note Added: 0000287
2013-06-23 23:01 sngkw Note Added: 0000289
2013-07-23 22:48 snpe Note Added: 0000353
2013-07-23 22:49 snpe File Added: oracleTimestampltz.diff
2013-07-24 12:50 chenson Note Added: 0000355
2013-07-24 12:51 chenson Target Version => 3.5.1
2014-10-26 20:11 chenson Fixed in Version => 3.6.11
2014-10-26 20:11 chenson Target Version 3.5.1 => 3.6.11
2014-10-26 20:11 chenson Status new => resolved
2014-10-26 20:11 chenson Resolution open => fixed
2014-10-26 20:11 chenson Assigned To => chenson
2014-10-26 21:00 chenson Changeset attached => SymmetricDS trunk r8837
2014-10-26 21:00 chenson Changeset attached => SymmetricDS 3.6 r8838
2014-10-27 11:52 chenson Status resolved => closed
2015-07-30 21:49 chenson Changeset attached => SymmetricDS master f454c5e8