Characters of NVARCHAR(MAX) column improperly decoded

[prakul323]

I am trying to read Sqlserver column has MAX(nvarchar) as string which contains RAW JSON while reading this i am getting Some chinese character between json

thats issue i noticed on Prod

[mp911de]

I moved the issue from the SPI project into the driver project for SQL Server.

If you would like us to spend some time helping you to diagnose the problem, please spend some time describing it and, ideally, providing a minimal sample that reproduces the problem.

Ideally, please provide a schema, the data and a bit of code to reproduce the issue.

[prakul323]

I am using saving json like in SQL server as MAX(nvarchar) coloumn

{ "obj1":[ { "A":"a", "B":"b", "C":"c", "D":"d" }, { "E":"e", "F":"f", "G":"g", "H":"h" } ], "obj2":[ { "A":"a", "B":"b", "C":"c", "D":"d" }, { "E":"e", "F":"f", "G":"g", "H":"h" } ] }

So while reading String with entity class as string i facing some issuea and values coming like

{ "obj1":[ { "A":"a", "B":"b", "C":"c", "D"�愀椀氀猀∀㨀嬀笀∀愀最攀渀琀䌀漀搀攀∀㨀∀　　㄀㄀㌀㌀∀Ⰰ∀搀攀愀氀攀爀䌀漀搀攀∀㨀嬀∀bj2 B":"b", "C":"c", "D":"d" }, { "E":"e", "F":"f", "G":"g", "H":"h" } ]

I tried both reactive repository and database client both with WEBFLUX

[mp911de]

Please provide a schema, the data, and a bit of code to reproduce the issue.

A JSON object dump isn't helpful in particular. If you require further guidance on how to report bugs, please see https://www.chiark.greenend.org.uk/~sgtatham/bugs.html for reference.

[prakul323]

POM Dependencies:

org.springframework.boot.experimental spring-boot-starter-data-r2dbc org.springframework.boot spring-boot-starter-webflux io.r2dbc r2dbc-mssql io.r2dbc r2dbc-postgresql io.r2dbc r2dbc-pool org.springframework spring-jdbc org.springframework.boot spring-boot-starter-test test org.junit.vintage junit-vintage-engine org.springframework.boot.experimental spring-boot-test-autoconfigure-r2dbc test org.springframework.boot.experimental spring-boot-bom-r2dbc 0.1.0.BUILD-SNAPSHOT pom import

R2DBC Configuration File

@Configuration @EnableTransactionManagement public class R2DBCConfig extends AbstractR2dbcConfiguration{

@Value("${spring.data.mssql.host}") 
private String host;
@Value("${spring.data.mssql.port}") 
private int port;
@Value("${spring.data.mssql.database}") 
private String database;
@Value("${spring.data.mssql.username}") 
private String username;
@Value("${spring.data.mssql.password}") 
private String password;

@Value("${spring.application.name}") 
private String serviceName;

@Bean
@Override
public ConnectionFactory connectionFactory() {
    return new MssqlConnectionFactory(MssqlConnectionConfiguration.builder()
            .host(host)
            .port(port)
            .username(username)
            .password(password)
            .database(database)
            .build()); 
}

@Bean
  ReactiveTransactionManager transactionManager(ConnectionFactory connectionFactory) { 
    return new R2dbcTransactionManager(connectionFactory());
  }

@Bean
public DatabaseClient databaseClient() {
    return DatabaseClient.create(connectionFactory());
}

}

Entity class:-

@Table("[dbo].[CODE_DETAILS]") @Data @EqualsAndHashCode @ToString @AllArgsConstructor @NoArgsConstructor public class CodeDetails implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@Column("ID")
private Long ID;

@Column("Description")
private String description;

@Column("Json_Codes")
private String jsonCodes; -> Saving RAW json here and for which getting issues

@Column("Created_Date")
private LocalDateTime createdDate;

@Column("Created_By")
private String createdBy;

@Column("Updated_Date")
private LocalDateTime updatedDate;

@Column("Updated_By")
private String updatedBy;

}

Repository:-

public interface CodeDetailsRepository extends ReactiveCrudRepository<CodeDetails, Long>{

@Query("select * from [dbo].[CODE_DETAILS] where Description like 1$ ") Flux findbyDescription(String desc);

}

ServiceImpl Class :{ @Autowired private DatabaseClient client;

@autowired
private CodeDetailsRepository repository;

private Flux<CodeDetails> findbyDescriptionWithRepo(String desc){
    return repository.findbyDescription(desc);

}

private Flux<CodeDetails> findbyDescriptionWithClient(String desc){

return client.execute("select * from [dbo].[CODE_DETAILS] where Description=:desc ")
            .bind("desc",desc).as(CodeDetails.class).fetch().all();
}

}

[prakul323]

any update?

[SantoshDeepak1]

Hi Team, Is there any update. We are also facing the same issue in production. We have a simple schema in database table like below.

     ID      Email_Account
     594      emilia.halpert@tekcly.com,olivia.goldman@tekcly.com

When i am inserting new entity , email account column is inserted with correct data. But while i am selecting record through R2dbc mSSQl driver, Email_Account column value changing automatically like below

emilia.halpert@tekcly.com,olivia.goldman�䀀渀攀眀猀甀渀搀攀爀猀琀愀渀搀椀渀最⸀挀漀洀�

I am using Spring boot 2.3.1.RELEASE.

<dependency>
        <groupId>io.r2dbc</groupId>
        <artifactId>r2dbc-mssql</artifactId>
        <version>0.8.4.RELEASE</version><!--$NO-MVN-MAN-VER$ -->
        <exclusions>
            <exclusion>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-api</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

           <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

[SantoshDeepak1]

Hi Team,

I have observed issue is only occurring if the column datatype is nvarchar(max). If i am changing to nvarchar(4000) then issue is not reproducible.

[prakul323]

Hi

same I am also facing but nvarchar(max) should work with it please provide me solution to use it with same

[mp911de]

That's a duplicate of #169 that is fixed with #171.