Thursday, June 3, 2010

ADS 10 Tip #4 – Unicode Support

Unicode support has been added to Advantage. This includes three new field types nChar, nVarChar and nMemo which can be used to store Unicode characters. Unicode characters are stored in UTF-16 encoding which uses two bytes per character. Over 200 Unicode collations are available allowing creation of indexes on the Unicode field types.


The sp_GetCollations system procedure returns a list of available collations. You can also use Unicode string literals in your SQL statements. This allows you to filter the results as well as insert Unicode data into the field. Several example SQL statements are below.

// Get a list of Collations

// Get a list of Arabic collations 
EXECUTE PROCEDURE sp_GetCollations( 'ar%' );

// Support for Unicode string literals 
SELECT * FROM PhraseBook WHERE JP = 'どうぞ';

// Insert Unicode values using SQL 
INSERT INTO PhraseBook (ENG, JP, Phonetic)
  VALUES ('Happy Birthday', 'お誕生日おめでとう', 'o tanjoubi omedeto'); 

// Create an index using a Unicode collation
EXECUTE PROCEDURE sp_CreateIndex90( 'StringTbl', 'StringTbl.adi', 'Arabic', 
				    'ARA', '', 0, 0, ':ar_IQ');

When using Unicode field types you must distribute some additional files to your clients. The Unicode functions are stored in aciu32.dll (aciu64.dll for x64) and Unicode collations are stored in icudt40l.dat. Any application that attempts to open a table containing Unicode field types must have these files available.

There is a Unicode Demonstration available on the DevZone.

No comments: