How to set the character-set to utf8 at session level in jhipster

Question

When deploying my jhipster based application to cloud foundry (in my case Pivotal with ClearDB service) I don't have option to change the DB character set and not to update the JDBC parameters as it shared DB. the charset of the DB is latin1 and I need it to be utf-8 to be able support languages like Arabic and Hebrew.

So the only option I think about to support those languages is to init the DB session/connection when it's created, like running below sqls:

SET session character_set_client = charset_name;
SET session character_set_results = charset_name;
SET session character_set_connection = charset_name;

How this can be done in jhipster I don't see place where we can set DB connection/session init sqls and if you have any other recommendation?

Currently what happen is that Arabic/Hebrew input data coming from client saved in the DB as ???? BTW if I will update the DB entries using MYSQL Workbench the Arabic/Hebrew values are save correctly and also displayed correctly.

Thanks, Rabiaa

have you tried to add these parameters to the datasource URL in application*.yml ? — Gaël Marziou, Aug 04 '17 at 22:48
In the application-cloudfoundry.yml I don't see the datasource section is defined. how I see those values are updated by the cloud foundry based on the selected service. — Rabiaa Saffoury, Aug 05 '17 at 06:20
Thanks for your response, yes I have opened support ticket but they are not able to help how i can do this update/change. — Rabiaa Saffoury, Aug 05 '17 at 09:53
Have you tried this? https://spring.io/blog/2015/04/27/binding-to-data-services-with-spring-boot-in-cloud-foundry — Gaël Marziou, Aug 05 '17 at 10:07
Hi Thanks a lot I was able to solve the issue for now by below workaround, I will go over the link you sent and read it. what I did 1- ubind the clearDB service (but keep it) 2- create a new user provided service which it will use the clearDB but with my custom uri — Rabiaa Saffoury, Aug 05 '17 at 21:19
cf create-user-provided-service mysql-db -p '{"uri":"mysql://?useUnicode=true&characterEncoding=utf8&reconnect=true"}' cf bind-service digitalauthapp mysql-db cf restart digitalauthapp — Rabiaa Saffoury, Aug 05 '17 at 21:21
Great but it's not very readble in comments,could you create an answer so that it helps others too? — Gaël Marziou, Aug 06 '17 at 07:40

score 0 · Answer 1 · answered Aug 14 '17 at 12:26

The data was destroyed during INSERTion.

The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
The column in the database is CHARACTER SET utf8 (or utf8mb4). Fix this.
Also, check that the connection during reading is UTF-8.

See "question marks" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

Is that one question mark per character? Or two (in the case of Arabic / Hebrew)?

score 0 · Answer 2 · edited Feb 21 '19 at 21:41

Workaround to solve the issue, by creating proxy service:

1 Unbind the clearDB service (but keep it, just unbind)

2 Create new user provided service which will call the clearDB service with custom uri.

See the following commands:

cf create-user-provided-service mysql-db -p '{"uri":"mysql://<uri of the clearDB service>?useUnicode=true&characterEncoding=utf8&reconnect=true"}' 

cf bind-service <app name> mysql-db

cf restart <app name>

How to set the character-set to utf8 at session level in jhipster

2 Answers2