Knowledge Base

Hint Ref: 021105230014
Hint Date: 23/05/2011

Hint Details:

ISSUE

A client required the ability to send SMS messages from Space Manager, using a French online SMS service. However, whilst it was clear that the original messages contained characters with accents, the recipients of the SMS messages found that where these accented characters should have been, two characters were missing and had been replaced by a single question mark.

Whilst the character set had been set correctly as UTF-8 within the XML job, nothing we originally did would result in the accented characters being displayed correctly in the received message.

SOLUTION

It was discovered that in order for the accented characters to be displayed correctly in the final message, they had to be sent to the SMS service (Via an HTTPRequest) as UTF-8 HEX.

The character to UF8 Hex conversion tables can be found here: http://www.utf8-chartable.de/

To use the UTF-8 Hex, add a '%' before each code segment (no spaces).

So 'à  ' would require a Hex code of 'C3 A0' which would be used as '%C3%A0'

However, below is a database function that will do the substitution, for French characters, for you. The UNICODE function returns the Unicode value for the original accented character and a CASE statement then inserts the modified UTF-8 HEX Code, when needed:

To use it, if your original message is in a variable called 'Text', add this line to your XML job;

 <SetVariable name="text" sql="select smsaccents(:text)"/>

Create FUNCTION "spaceman"."smsaccents"(in dummy long varchar)
returns long varchar
begin
 declare endsms long varchar;
 declare y integer;
 declare x char(1);
 declare a integer;
 set endsms='';
 set a=0;
 set y=1;
 while y <= length(dummy) loop
   set x=substr(dummy,y,1);
   set a=unicode(x);
   set endsms=endsms+
      case a
           when 224 then '%C3%A0'
           when 226 then '%C3%A2'
           when 231 then '%C3%A7'
           when 232 then '%C3%A8'
           when 233 then '%C3%A9'
           when 234 then '%C3%AA'
           when 235 then '%C3%AB'
           when 239 then '%C3%AF'
           when 244 then '%C3%B4'
           when 251 then '%C3%BB'
      else x
      end;
   set y=y+1
 end loop;
 return endsms
end;

(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)