Insert emojis in mysql 5.5

Question

Insert emojis in mysql 5.5

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I want to insert texts that have emojis in a MySQL table. I saw that it takes 4 bytes for each emoji because they come in 2 hexadecimals. The issue that I have seen there are different "solutions" and none of them worked for me.

Part of the error goes:

at java.lang.Thread.run (Unknown Source) Caused by: com.genexus.GXRuntimeException: java.sql.SQLException: Incorrect string value: '\ xF0 \ x9F \ x92 \ x98 \ xF0 \ x9F ...' for column 'TweetText' at row 1 at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1072) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3563) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3495) at com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2113) at com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2693) at com.mysql.jdbc.PreparedStatement.executeInternal (PreparedStatement.java:2102) at com.mysql.jdbc.PreparedStatement.executeUpdate (PreparedStatement.java:2395) at com.mysql.jdbc.PreparedStatement.executeUpdate (PreparedStatement.java:2313) at com.mysql.jdbc.PreparedStatement.executeUpdate (PreparedStatement.java:2298) at com.genexus.db.driver.GXPreparedStatement.executeUpdate (Unknown Source) at com.genexus.db.UpdateCursor.postExecute (Unknown Source) at com.genexus.db.DataStoreProvider.execute (Unknown Source) at com.genexus.db.DataStoreProvider.execute (Unknown Source)

Does anyone know how to set the mysql , or client.cfg or some workarround to achieve insert in the BD emojis?

The simplified code would be something like that, with &Tweet being a variable of type Business Component in GeneXus and &itemresult the result tweet of calling an API of Twitter:

&Tweet.TweetId=&itemresult.id_str
&Tweet.TweetText=&itemresult.text
&Tweet.Save()
commit

Thank you very much in advance

mysql java genexus

asked by bcamargo75 14.03.2016 в 17:04

source

2 answers

Real-time data binning with Angular and PHP How to copy a file instead of moving?

score 2 · Answer 1

Apparently the "character set" by default in MySQL is utf8 that supports UNICODE characters long between 1 and 3 bytes, so the emoji are not supported with this configuration.

As of MySQL 5.5.3, a new "character set" with name utf8mb4 was added that does support emoji.

Quoting the MySQL documentation :

The character set named utf8 uses a maximum of three bytes per character and contains only BMP characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character [...]

Translation:

The character set utf8 uses a maximum of three bytes per character and contains only BMP characters. As of MySQL 5.5.3, the character set utf8mb4 uses a maximum of four bytes per character [...]

As far as I know GeneXus does not support this configuration when creating the database, but you can try a ALTER DATABASE to change this configuration after it is created ( see MySQL documentation, in English ).

score 1 · Answer 2

I solved the issue, I went on to tell you how:

I updated the CHARACTER SET and the COLLATE of the database of utf8 to utf8mb4 . It happens that MySQL uses 3 bytes to store the utf8 characters and 4 bytes for the utf8mb4 catachors, so that the latter does support all the bytes of the emojis. Also, I updated the CHARACTER SET and COLLATE of the relevant tables.

The explanation of this step was found in link

Then I saw that I was using the library mysql-connector-java-5.1.11-bin.jar that brings Genexus Evo3 and Leap by default and copies it to the WEB-INF / lib of the tomcat. I downloaded a more current version: mysql-connector-java-5.1.38-bin.jar and it was fixed.