utf-8 in database varchar

We have existing SQL Server databases with all text fields defined as varchar (not nvarchar) and the text in them is actually UTF-8. Our old system (written in Delphi) will read and write those strings correctly and display the international characters on a web page etc. We are rewriting our system in ASP.NET and using Datasets to access the data. Reading/writing the data does not treat the data as utf-8. How can I get it to treat it as utf-8?

Re: utf-8 in database varchar

Why not upgrade the VARCHAR to NVARCHAR? If you used stored procedures for your existing Delphi programe, leave the existimg sp with VARCHAR datatype and create a new set with NVARCHAR arguments.

Re: utf-8 in database varchar

So can the stored procedure return a field as a different datatype to the one stored? How is this conversion handled? Can you ensure that the varchar is encoded as utf-8 and not using a codepage?

Re: utf-8 in database varchar

>So can the stored procedure return a field as a different datatype to the one stored?
Yes!

>How is this conversion handled?
Explicitly using using CONVERT (although CAST can also be used)

>Can you ensure that the varchar is encoded as utf-8 and not using a codepage?
You can also specify an explicit collating sequence using COLLATE

The existing table will need to be changed from VARCHAR to NVARCHAR

Re: utf-8 in database varchar

I don't use stored procedures in the existing application. And there are two many tables to create stored procedures for them all. The code would have to change too much. So for now I am stuck with varchar fields. Is it not possible to cast the nvarchar to a varchar when saving the data to the database? I tried to write a stored function and it gives and exception. My function was :-

CREATE FUNCTION dbo.fnStringWrite
(
@S nvarchar(100)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @RETURN varchar(100)
SET @RETURN = cast(@S as varchar(100))
RETURN @RETURN
END

Re: utf-8 in database varchar

Is it possible for a function to return a varchar with different lengths?

It seems as though I need to decalre the return variable as say type varchar(100). I need to be able to handle various string lengths. Can I pass in the length of the string and use that when defining the varchar inoput and output lengths? Otherwise I need a function for every possible string field length.

Re: utf-8 in database varchar

How do you use COLLATE? I thought the collate only affected sort order or comparisons?

Does it also affect how the data is stored? If it does I assume it only affects which codepage is used to convert characters. This is not the same as UTF-8 encoding. I don't think there is a collation to force storage in utf-8 format.

Re: utf-8 in database varchar

COLLATE can be used in a WHERE clause to compare data in different collationg sequences as in

WHERE TABLE1.A = TABLEB.A COLLATE Latin1_General_CI_AS

Make a copy of the database and convert all the database VARCHAR to NVARCHAR. The casting should be in the old set of stored procedures. The simplest way forward however would be to update the delphi program to use NVARCHAR. That way the conversion issue will no longer arise.