View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002356 | SymmetricDS | Bug | public | 2015-07-30 22:53 | 2019-04-23 19:07 |
| Reporter | goran | Assigned To | |||
| Priority | normal | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.7.19 | ||||
| Summary | 0002356: Triggers not created for tables with sql_variant column | ||||
| Description | Hi, I'm using SQL Server Express 2014 sync-triggers can't create triggers for tables containing sql_variant type column. Process MsSqlSymmetricDialect builds next expression for the sql_variant type column: .....replace(replace(convert(varchar(0),inserted."obj") ,'\','\\')........ which cause following error: org.jumpmind.db.sql.SqlException: Line 24: Length or precision specification 0 is invalid. It seams that varchar(0) cause problem. I'm new to SymmetricsDS and don't have information how it's behave in the previous versions. | ||||
| Steps To Reproduce | -SQL Server 2014 -Create a table with a column of sql_variant type -Configure sync for that table -execute symadmin sync-triggers | ||||
| Tags | data type, dialect: sql-server | ||||
|
|
I was able to get the trigger to create, but SymmetricDS does not have the capability to communicate the data type that is stored so it can properly be stored as the correct type in the target database. I can check in a version that always stores the target as binary, but I am guessing that won't meet your needs if you are using the sql_variant type in the first place. Can you describe your use case? Do you have any ideas on how to know what the proper type is for storage? |
|
|
I wasn't able to create triggers for the table at all if the table contain sql_variant column. Error prevent creating triggers for that table. I have 3 types of values (int, decimal, nvarchar) stored in sql_variant field (I'm using it in the EAV model). I'll try to get rid of sql_variant and use separated fields for every type I need. Idea to support sql_variant: Trigger could determine datatype of source sql_variant by use of SQL_VARIANT_PROPERTY function, then somehow encode source data type together with value in the SymmetricDS storage. At target database, if datatype is sql_variant, using decoded datatype and source value, cast value as proper datatype so it can be stored at destination in the same datatype as it is in the source database (table). It should work if source and target database are SQL server. |
|
|
I was able to get the triggers to work by changing code. I like your idea. Probably, a pretty significant task though. |
|
SymmetricDS: master a6dde304 2015-08-03 08:30:50 Details Diff |
0002356: Triggers not created for tables with sql_variant column |
Affected Issues 0002356 |
|
| mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mssql/MsSqlDdlReader.java | Diff File | ||
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mssql/MsSql2008DdlBuilder.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/model/ColumnTypes.java | Diff File | ||
|
SymmetricDS: 3.7 6c05e7c8 2015-08-03 08:30:50 Details Diff |
0002356: Triggers not created for tables with sql_variant column |
Affected Issues 0002356 |
|
| mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mssql/MsSqlDdlReader.java | Diff File | ||
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mssql/MsSql2008DdlBuilder.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/model/ColumnTypes.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-07-30 22:53 | goran | New Issue | |
| 2015-08-03 12:30 | chenson | Note Added: 0000702 | |
| 2015-08-03 13:00 | chenson | Changeset attached | => SymmetricDS master a6dde304 |
| 2015-08-03 14:28 | goran1 | Note Added: 0000703 | |
| 2015-08-03 14:36 | chenson | Note Added: 0000704 | |
| 2015-08-13 15:00 | chenson | Changeset attached | => SymmetricDS 3.7 6c05e7c8 |
| 2019-04-23 19:07 | elong | Tag Attached: data type | |
| 2019-04-23 19:07 | elong | Tag Attached: mssql | |
| 2019-04-23 20:49 | admin | Tag Renamed | mssql => dialect: mssql |
| 2019-04-24 12:50 | admin | Tag Renamed | dialect: mssql => dialect: sql-server |