View Issue Details

IDProjectCategoryView StatusLast Update
0003955SymmetricDSBugpublic2019-05-20 15:00
ReporterpmarzulloAssigned Topmarzullo 
Prioritynormal 
Status assignedResolutionfixed 
Product Version3.10.2 
Target Version3.10.3Fixed in Version3.10.3 
Summary0003955: BIT from SQLServer comes in as Integer, Postgres needs value converted to String (VARCHAR) to handle it correctly (3.10)
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

Activities

pmarzullo

2019-05-13 13:13

developer   ~0001467

Also fixed in 3.9.23 using issue 0003954.

Related Changesets

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).

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.10 99695c5d

2019-05-13 12:37:16

Philip Marzullo

Details Diff
0003955: Rearrange code to minimize duplication
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.10 7282a842

2019-05-20 14:48:04

Philip Marzullo

Details Diff
0003955: Roll back changes
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

Issue History

Date Modified Username Field Change
2019-05-13 11:58 pmarzullo New Issue
2019-05-13 11:58 pmarzullo Status new => assigned
2019-05-13 11:58 pmarzullo Assigned To => pmarzullo
2019-05-13 11:58 pmarzullo Tag Attached: data sync
2019-05-13 11:58 pmarzullo Tag Attached: data type
2019-05-13 11:58 pmarzullo Tag Attached: dialect: postgresql
2019-05-13 13:00 Changeset attached => SymmetricDS 3.10 99695c5d
2019-05-13 13:00 Changeset attached => SymmetricDS 3.10 8d6dec10
2019-05-13 13:13 pmarzullo Status assigned => resolved
2019-05-13 13:13 pmarzullo Resolution open => fixed
2019-05-13 13:13 pmarzullo Fixed in Version => 3.10.3
2019-05-13 13:13 pmarzullo Note Added: 0001467
2019-05-17 20: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.10)
2019-05-20 14:47 pmarzullo Status resolved => assigned
2019-05-20 15:00 Changeset attached => SymmetricDS 3.10 7282a842