Recently I worked in a project where special characters needed to be stored in a database, to be specific we needed to handle Chinese characters, to achieve this, some configurations need to be done in the ColdFusion Administrator regarding the Connection String setting and the database has to be created with certain parameters.
Creating the Database to use Unicode
As you can see the character set is utf8mb4, and the collate is utf8mb4_unicode_ci. You may ask Why utf8mb4 instead of utf8?. Well the reason is that in MySQL at least, the set of characters handled by utf8 is shorter than utf8mb4. If you already have your database in utf8 and need to switch to utf8mb4 or to read more about this check this article.
Note. All the tables you create after will keep the Unicode character set and collate that you specified when the database was created.
Now that the database is created the next step is:
Configure Data Source Name (DSN) to use Unicode.
Here I will explain how to configure an already added database, this steps can help you add a new one.
- Open ColdFusion Administrator
- Locate DATA & SERVICES in the left menu
- Select Datasources to list the ones that are already added
- Click either on the name of the DSN in the "Data Source Name" column or in the Edit button in the "Actions" column
- Click on Show Advanced Settings
- In the "Connection String" box type
- Click on Submit
That's it, now you can use full Unicode in your application.