View Issue Details

IDProjectCategoryView StatusLast Update
0002356SymmetricDSBugpublic2024-09-11 18:01
Reportergoran Assigned To 
Prioritynormal 
Status closedResolutionwon't fix 
Product Version3.7.19 
Summary0002356: Triggers not created for tables with sql_variant column
DescriptionHi,

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

Tagsdata type, dialect: sql-server

Activities

chenson

2015-08-03 12:30

administrator   ~0000702

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?

goran1

2015-08-03 14:28

reporter   ~0000703

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.

chenson

2015-08-03 14:36

administrator   ~0000704

I was able to get the triggers to work by changing code.

I like your idea. Probably, a pretty significant task though.

emiller

2024-09-11 18:01

developer   ~0002495

The sql_variant data type is not supported. We have seen no interest in this data type since 2015.

Related Changesets

SymmetricDS: master a6dde304

2015-08-03 08:30:50

chenson

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

chenson

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

Issue History

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
2024-09-11 18:01 emiller Status new => closed
2024-09-11 18:01 emiller Resolution open => won't fix
2024-09-11 18:01 emiller Note Added: 0002495