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?
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.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
Marked as answer by Kevin Yu - MSFT on May 21, 2007 08:24 AM
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?
>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
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
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
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.
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.
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.
Click "Mark as Answer" on the post that helped you.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
skysailor
Member
1 Points
13 Posts
utf-8 in database varchar
May 18, 2007 04:29 AM|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?
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: utf-8 in database varchar
May 19, 2007 10:22 PM|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.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
skysailor
Member
1 Points
13 Posts
Re: utf-8 in database varchar
May 21, 2007 11:31 PM|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?
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: utf-8 in database varchar
May 22, 2007 06:47 AM|LINK
>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
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239
skysailor
Member
1 Points
13 Posts
Re: utf-8 in database varchar
May 23, 2007 04:21 AM|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
(
@S nvarchar(100)
)
RETURNS varchar(100)
AS
BEGIN
DECLARE @RETURN varchar(100)
SET @RETURN = cast(@S as varchar(100))
RETURN @RETURN
END
skysailor
Member
1 Points
13 Posts
Re: utf-8 in database varchar
May 23, 2007 04:26 AM|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.
skysailor
Member
1 Points
13 Posts
Re: utf-8 in database varchar
May 23, 2007 04:31 AM|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.
TATWORTH
All-Star
72415 Points
14017 Posts
MVP
Re: utf-8 in database varchar
May 23, 2007 06:37 AM|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.
This earns you a point and marks your thread as Resolved so we will all know you have been helped.
FAQ on the correct forum http://forums.asp.net/p/1337412/2699239.aspx#2699239