Convert database to UTF-8

What it does 

 Special characters causing errors 

 The default support for UTF8 is 3 byte. 

 Using 4 byte UTF content in text areas, can cause errors, that looks like 

 java.sql.SQLException: Incorrect string value: '\xC2\x96 Z. ...' for column 'NOTES' at row 1 

 How to invoke 

 Update the database to full UTF8 support using the following command 

 main?command=dk.p2e.blanket.codeunit.common.PageConvertDatabaseToUTF8 

 Note an administration profile is needed to complete the operation. 

 You might also have to update the tomcat server.xml file. You have to add  useBodyEncodingForURI="true" to the connectors. 

 Note that this codeunit has build in protection against being excuted multiple times at once, and should be executed every time a new field or entity has been added to the database. 

 Issue before version 11315 on MariaDB 

 Due to a difference in how MySQL and MariaDB stores "DEFALUT NULL", when executing this codeunit on a MariaDB server, all columns with the default value of null would be updated to have a default value of 'NULL' (the string NULL, not the empty value null). 

 To find the affected columns and generate all the required sql to fix the issue, run the following query. Remember to reaplce [LIVE DB] with the name of the live database of your instance! 

 SELECT a.TABLE_NAME, a.COLUMN_NAME, CONCAT('ALTER TABLE ', a.TABLE_NAME, ' ALTER ', a.COLUMN_NAME, ' SET DEFAULT NULL;') AS upd

FROM information_schema.columns AS a

INNER JOIN INFORMATION_SCHEMA.TABLES b ON 

	a.TABLE_CATALOG = b.TABLE_CATALOG AND 

	a.TABLE_SCHEMA = b.TABLE_SCHEMA AND 

	a.TABLE_NAME = b.TABLE_NAME AND

	LOWER(b.table_type) != 'view'

WHERE a.COLUMN_DEFAULT = '\'NULL\'' AND 

a.TABLE_SCHEMA = '[LIVE DB]' 

 This should create one row for every column in the database that has a default value of the string 'NULL'. 

 Take the output of the third column, copy all rows and execute them against the database. Should look something like this: 

 ALTER TABLE form ALTER SagNavnI18N SET DEFAULT NULL;

ALTER TABLE form ALTER EntityIcon SET DEFAULT NULL;

ALTER TABLE form ALTER Description SET DEFAULT NULL;

ALTER TABLE form ALTER DescriptionI18N SET DEFAULT NULL;

ALTER TABLE form ALTER MailDomains SET DEFAULT NULL;

ALTER TABLE form ALTER MailSecurity SET DEFAULT NULL;

ALTER TABLE form ALTER Codeunit SET DEFAULT NULL;

ALTER TABLE form ALTER Instructions SET DEFAULT NULL; 

 Go back and execute the first query, it now should return 0 rows. 

 Options 

 None. 

 Configuration 

 None. 

 Developer info 

 

 Type: CodeunitPagecontent 

 Security: Requires administrator 

 Classpath: dk.p2e.blanket.codeunit.common.PageConvertDatabaseToUTF8