View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003522 | SymmetricDS | Bug | public | 2018-04-19 15:25 | 2019-03-14 12:48 |
| Reporter | jubi74 | Assigned To | pmarzullo | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.8.26 | ||||
| Target Version | 3.10.0 | Fixed in Version | 3.10.0 | ||
| Summary | 0003522: Table creation Error while initial Load into oracle DB (initial.load.create.first=true) when using Default value SYS_GUID() | ||||
| Description | When creating table through SymmetricDS with column ID RAW(20) Default SYS_GUID() NOT NULL the Default value is quoted 'SYS_GUID()'. Need to remove the quotes for valid table creation with oracle database 11g. Sample Log: 2018-04-18 15:19:00,043 INFO [mpos] [DefaultDatabaseWriter] [mpos-dataloader-2] About to create table using the following definition: <?xml version="1.0"?> <!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database"> <database name="dataextractor" schema="DB"> <table name="CASHMANVOUCHER"> <column name="ID" primaryKey="true" required="true" type="VARBINARY" size="20" default="SYS_GUID()"> <platform-column name="oracle" type="RAW" size="20" default="SYS_GUID()"/> </column> <column name="CSHORTTEXT" type="VARCHAR" size="42"> <platform-column name="oracle" type="VARCHAR2" size="42"/> </column> <column name="LVOUCHER" type="DECIMAL" size="11"> <platform-column name="oracle" type="NUMBER" size="11"/> </column> </table> </database> 2018-04-18 15:19:00,043 INFO [mpos] [OracleDatabasePlatform] [mpos-dataloader-2] Running alter sql: CREATE TABLE "DB"."CASHMANVOUCHER"( "ID" RAW(20) DEFAULT 'SYS_GUID()' NOT NULL, "CSHORTTEXT" VARCHAR2(42), "LVOUCHER" NUMBER(11) ); ALTER TABLE "DB"."CASHMANVOUCHER" ADD CONSTRAINT "CASHMANVOUCHER_PK" PRIMARY KEY ("ID"); 2018-04-18 15:19:00,058 WARN [mpos] [JdbcSqlTemplate] [mpos-dataloader-2] ORA-01465: invalid hex number . Failed to execute: CREATE TABLE "DB"."CASHMANVOUCHER"( "ID" RAW(20) DEFAULT 'SYS_GUID()' NOT NULL, "CSHORTTEXT" VARCHAR2(42), "LVOUCHER" NUMBER(11) ) 2018-04-18 15:19:00,058 ERROR [mpos] [DefaultDatabaseWriter] [mpos-dataloader-2] Failed to alter table using the following xml: <?xml version="1.0"?> | ||||
| Steps To Reproduce | initial load with create first using any table including column with Default value SYS_GUID() | ||||
| Additional Information | works with change in org.jumpmind.db.platform.AbstractDdlBuilder.java ... boolean shouldUseQuotes = !isNull && !TypeMap.isNumericType(typeCode) && !(TypeMap.isDateTimeType(typeCode) && (defaultValueStr.toUpperCase().startsWith("TO_DATE(") || defaultValueStr.toUpperCase().startsWith("SYSDATE") || defaultValueStr.toUpperCase().startsWith("SYSTIMESTAMP") || defaultValueStr.toUpperCase().startsWith("CURRENT_TIMESTAMP") || defaultValueStr.toUpperCase().startsWith("CURRENT_TIME") || defaultValueStr.toUpperCase().startsWith("CURRENT_DATE") || defaultValueStr.toUpperCase().startsWith("CURRENT TIMESTAMP") || defaultValueStr.toUpperCase().startsWith("CURRENT TIME") || defaultValueStr.toUpperCase().startsWith("CURRENT DATE") || defaultValueStr.toUpperCase().startsWith("CURRENT_USER") || defaultValueStr.toUpperCase().startsWith("CURRENT USER") || defaultValueStr.toUpperCase().startsWith("USER") || defaultValueStr.toUpperCase().startsWith("SYSTEM_USER") || defaultValueStr.toUpperCase().startsWith("SESSION_USER") || defaultValueStr.toUpperCase().startsWith("DATE '") || defaultValueStr.toUpperCase().startsWith("TIME '") || defaultValueStr.toUpperCase().startsWith("TIMESTAMP '") || defaultValueStr.toUpperCase().startsWith("INTERVAL '") )) && !(defaultValueStr.toUpperCase().startsWith("N'") && defaultValueStr.endsWith("'")) && !(defaultValueStr.toUpperCase().startsWith("SYS_GUID()")); | ||||
| Tags | No tags attached. | ||||
|
|
Issue still exists in 3.9.14. The issue seems to be that SymmetricDS assumes the default value is a constant. In the case of functions, that's not the case. |
|
|
SYS_GUID Is an Oracle specific function. There now is an implementation of the printDefaultValue in the OracleDdlBuilder class. This class can be enhanced as needed when other functions need to be outside of single quotes. |
|
SymmetricDS: 3.10 04b2fb17 2019-01-25 08:40:17 Philip Marzullo Details Diff |
0003522: Table creation Error while initial Load into oracle DB (initial.load.create.first=true) when using Default value SYS_GUID() |
Affected Issues 0003522 |
|
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java | Diff File | ||
|
SymmetricDS: 3.10 b6d9e04a 2019-01-25 09:26:04 Philip Marzullo Details Diff |
0003522: Table creation Error while initial Load into oracle DB (initial.load.create.first=true) when using Default value SYS_GUID() |
Affected Issues 0003522 |
|
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-04-19 15:25 | jubi74 | New Issue | |
| 2018-10-17 13:46 | hanes | Assigned To | => hanes |
| 2018-10-17 13:46 | hanes | Status | new => confirmed |
| 2018-10-17 13:46 | hanes | Note Added: 0001275 | |
| 2018-10-17 13:46 | hanes | Assigned To | hanes => |
| 2018-10-17 18:24 | hanes | Priority | normal => low |
| 2018-10-17 18:24 | hanes | Target Version | => 3.10.0 |
| 2018-10-29 16:55 | hanes | Priority | low => normal |
| 2019-01-23 15:47 | pmarzullo | Assigned To | => pmarzullo |
| 2019-01-23 15:47 | pmarzullo | Status | confirmed => assigned |
| 2019-01-25 13:43 | pmarzullo | Status | assigned => resolved |
| 2019-01-25 13:43 | pmarzullo | Resolution | open => fixed |
| 2019-01-25 13:43 | pmarzullo | Fixed in Version | => 3.10.0 |
| 2019-01-25 13:43 | pmarzullo | Note Added: 0001388 | |
| 2019-01-25 14:00 | Changeset attached | => SymmetricDS 3.10 04b2fb17 | |
| 2019-01-25 15:00 | Changeset attached | => SymmetricDS 3.10 b6d9e04a | |
| 2019-03-14 12:48 | admin | Status | resolved => closed |