B4J Question [solved] mariadb (mysql) and replace

[Gianni M]

i have a jar app (B4J) on my pc win10 and mariadb
query is:

select
field1,
field2,
REPLACE(REPLACE(REPLACE(FORMAT(field3, 2), ".",  "p"), ",",  "."), "p",  ",") ,
field4
from

on my pc, it is ok (no error)

but on another pc (win10, same version java and version mariadb), app crash :
follow bit log error:

java.sql.SQLTransientConnectionException: (conn=54) Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE) for operation 'replace'
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:79)
        at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
.....
.....
....
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE) for operation 'replace'
        at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
        at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
        at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
        ... 9 more
Caused by: java.sql.SQLException: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE) for operation 'replace'

any suggest ?

 

[Lello1964]

I've found this solution, but never tested :

-- Input: Red, Blue, Green
-- Output: RGB
SELECT
s.Colors
FROM
(SELECT 'Red, Green, Blue' AS Colors) c
CROSS APPLY (SELECT REPLACE(c.Colors,'Red','R') AS Colors) r
CROSS APPLY (SELECT REPLACE(r.Colors,'Green','G') AS Colors) g
CROSS APPLY (SELECT REPLACE(g.Colors,'Blue','B') AS Colors) b
CROSS APPLY (SELECT REPLACE(b.Colors,', ','') AS Colors) s

How to Eliminate Ugly Nested REPLACE() Functions

Watch this week's video on YouTube How many times have you had to transform some column value and ended up stacking several nested SQL REPLACE()...

bertwagner.com

 

[aeric]

Try:
JdbcUrl=jdbc:mariadb://localhost/databasename?characterEncoding=utf8

 

[Gianni M]

Try:
JdbcUrl=jdbc:mariadb://localhost/databasename?characterEncoding=utf8

i try it; i will post result

 

[Gianni M]

I've found this solution, but never tested :

-- Input: Red, Blue, Green
-- Output: RGB
SELECT
s.Colors
FROM
(SELECT 'Red, Green, Blue' AS Colors) c
CROSS APPLY (SELECT REPLACE(c.Colors,'Red','R') AS Colors) r
CROSS APPLY (SELECT REPLACE(r.Colors,'Green','G') AS Colors) g
CROSS APPLY (SELECT REPLACE(g.Colors,'Blue','B') AS Colors) b
CROSS APPLY (SELECT REPLACE(b.Colors,', ','') AS Colors) s

How to Eliminate Ugly Nested REPLACE() Functions

Watch this week's video on YouTube How many times have you had to transform some column value and ended up stacking several nested SQL REPLACE()...

bertwagner.com

even a single "REPLACE" , not work
perhaps with: JdbcUrl=jdbc:mariadb://localhost/databasename?characterEncoding=utf8
in the afternoon

 

[Gianni M]

solved with solution suggest by @sirjo66

 SET CHARACTER SET utf8