View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003954SymmetricDSBugpublic2019-05-13 15:542019-06-25 17:58
Reporterpmarzullo Assigned Topmarzullo  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.9.20 
Target Version3.9.21Fixed in Version3.9.21 
Summary0003954: BIT from SQLServer comes in as Integer, Postgres needs value converted to String (VARCHAR) to handle it correctly (3.9)
DescriptionWhen SQLServer has a BIT column defined, and is being synchronized to PostgreSql BIT column, the value comes in as a Integer.

Postgres driver converts the value to a BOOLEAN, and then can't insert the value into the BIT column.

Catch the value and change it to a VARCHAR, and Postgres driver is fine with that.
Steps To ReproduceSQLServer table:
CREATE TABLE "dbo"."philwithbit"
(
   id bigint PRIMARY KEY NOT NULL,
   isscheduled tinyint NOT NULL
)
GO
CREATE UNIQUE INDEX philwithbit_pk ON "dbo"."philwithbit"(id)
GO

PostgreSql table:
CREATE TABLE "public"."philwithbit"
(
   id bigint PRIMARY KEY NOT NULL,
   isscheduled bool DEFAULT '000'::"bit" NOT NULL
)
;
CREATE UNIQUE INDEX philwithbit_pk ON "public"."philwithbit"(id)
;

Master/Client Symmetric configuration
Create trigger on philwithbit
Link trigger and default router for server to client synchronization

SQlServer insert:
insert into philwithbit values(1,'true');

Synch should fail with:
org.jumpmind.db.sql.SqlException: ERROR: column "isscheduled" is of type bit but expression is of type boolean
  Hint: You will need to rewrite or cast the expression.
  Position: 69
Tagsdata sync, data type, dialect: postgresql

Relationships

related to 0003844 closedpmarzullo "Failed to execute: CREATE TABLE" in PostgreSQL 9.6 (BIT data type with default) 

Activities

pmarzullo

2019-05-13 16:01

developer   ~0001466

Also fixed in 3.10.3 on issue 0003955

pmarzullo

2019-05-20 18:30

developer   ~0001497

This code change needs to be rolled back.

The solution is to define the column in Postgres as a boolean instead of a bit(1) when the SQL Server column is defined as a bit.

Related Changesets

SymmetricDS: 3.9 a143391b

2019-05-13 12:00:17

Philip Marzullo

Details Diff 		0003954: Change incoming Integer to Varchar for Postgres BIT data type Affected Issues
0003954
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File

SymmetricDS: 3.10 8d6dec10

2019-05-13 12:03:23

Philip Marzullo

Details Diff 		0003955: Change incoming Integer to Varchar for Postgres BIT data type
(fixed in 3.9.23 on issue 0003954 also).		 Affected Issues
0003954, 0003955
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File

SymmetricDS: 3.9 f931d2d1

2019-05-20 14:32:23

Philip Marzullo

Details Diff 		0003954: Roll back the changes. Affected Issues
0003954
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2019-05-13 15:54 pmarzullo New Issue
2019-05-13 15:54 pmarzullo Status new => assigned
2019-05-13 15:54 pmarzullo Assigned To => pmarzullo
2019-05-13 15:54 pmarzullo Tag Attached: data sync
2019-05-13 15:54 pmarzullo Tag Attached: data type
2019-05-13 15:54 pmarzullo Tag Attached: dialect: postgresql
2019-05-13 16:01 pmarzullo Status assigned => resolved
2019-05-13 16:01 pmarzullo Resolution open => fixed
2019-05-13 16:01 pmarzullo Fixed in Version => 3.9.21
2019-05-13 16:01 pmarzullo Note Added: 0001466
2019-05-13 17:00 Changeset attached => SymmetricDS 3.9 a143391b
2019-05-13 17:00 Changeset attached => SymmetricDS 3.10 8d6dec10
2019-05-18 00:17 elong Summary BIT from SQLServer comes in as Integer, Postgres needs value converted to String (VARCHAR) to handle it correctly => BIT from SQLServer comes in as Integer, Postgres needs value converted to String (VARCHAR) to handle it correctly (3.9)
2019-05-18 00:18 elong Relationship added related to 0003844
2019-05-20 18:30 pmarzullo Note Added: 0001497
2019-05-20 18:31 pmarzullo Status resolved => assigned
2019-05-20 18:32 pmarzullo Status assigned => resolved
2019-05-20 19:00 Changeset attached => SymmetricDS 3.9 f931d2d1
2019-06-25 17:58 admin Status resolved => closed