NetworkedPlanet Blog Insights into developing with NetworkedPlanet products

Known Issue: TMCore07 SP4 – Display of non-ascii characters

Description
TMCore07 SP4 included a fix for the tm_displayName function in the TMCore database that provides the default display name for a topic. Prior to SP4, this function returned a varchar result which meant that non-ASCII characters would display as a ?. From SP4 the function was updated to return an nvarchar result, allowing any Unicode characters to be correctly displayed. Unfortunately the early release of the SP4 package did not include this updated function in the database upgrade script.
Systems Affected
This problem will show up only on databases that have been upgraded from a previous version of TMCore - clean database installations are not affected by this problem. In addition, the TMCore07 SP4 installer has been fixed so that downloads of the installer taken on or after the 21st August 2009 do not exhibit this problem either for new databases or for upgraded databases.
Fix
If you downloaded TMCore07 SP4 prior to 21st August 2009, you can apply the update for the tm_displayName function by running the following SQL script against the TMCore database:

-------------------------------------------------------------------
-- tm_displayName
-- Returns a display string for a topic selected from the topics variant names and topic names.
-- Since: 1.0
-- Last Updated: 2.4
if exists (select * from sysobjects
where id = object_id(N'[tm_displayName]') and
xtype in(N'FN',N'IF',N'TF'))
DROP FUNCTION [tm_displayName]
go
CREATE FUNCTION dbo.tm_displayName (@topic int)
RETURNS nvarchar(512)  AS
BEGIN
DECLARE @disp int
DECLARE @ret nvarchar(512)
SELECT @disp = (SELECT TOP 1 topic_id from tm_si
WHERE
tm_si.topicmap = (select topicmapref from topic where id = @topic) and
subj_id='http://www.topicmaps.org/xtm/1.0/core.xtm#display')
SET @ret = (SELECT TOP 1 variant_value FROM tm_nameVariantValue JOIN tm_variantScope ON tm_nameVariantValue.variant_id = tm_variantScope.variant_id
WHERE
tm_nameVariantValue.topic_id = @topic AND tm_variantScope.scoping_topic_id = @disp
ORDER BY tm_nameVariantValue.variant_id ASC)
IF (@ret IS NULL)
SET @ret = (SELECT TOP 1 name_value FROM tm_nameValue JOIN tm_nameScope ON tm_nameValue.name_id = tm_nameScope.name_id
WHERE
tm_nameValue.topic_id = @topic AND tm_nameScope.scoping_topic_id = @disp
ORDER BY tm_nameValue.name_id ASC)
IF (@ret IS NULL)
SET @ret = (SELECT TOP 1 name_value FROM tm_nameValue JOIN tm_nameScope ON tm_nameValue.name_id = tm_nameScope.name_id
WHERE
tm_nameValue.topic_id = @topic and ((tm_nameScope.scoping_topic_id IS NULL)  or (tm_nameScope.scoping_topic_id = -1))
ORDER BY tm_nameVALUE.name_id ASC)
IF (@ret IS NULL)
SET @ret = (SELECT TOP 1 name_value FROM tm_nameValue WHERE tm_nameValue.topic_id = @topic ORDER BY tm_nameValue.name_id)
RETURN @ret
END
GO
grant exec on tm_displayName to tm_reader, tm_writer
go