Last post May 23, 2007 02:37 AM by TATWORTH
May 18, 2007 12:29 AM|skysailor|LINK
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?
May 19, 2007 06:22 PM|TATWORTH|LINK
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.
May 21, 2007 07:31 PM|skysailor|LINK
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?
May 22, 2007 02:47 AM|TATWORTH|LINK
>So can the stored procedure return a field as a different datatype to the one stored?
>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
May 23, 2007 12:21 AM|skysailor|LINK
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
DECLARE @RETURN varchar(100)
SET @RETURN = cast(@S as varchar(100))
May 23, 2007 12:26 AM|skysailor|LINK
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.
May 23, 2007 12:31 AM|skysailor|LINK
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.
May 23, 2007 02:37 AM|TATWORTH|LINK
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.