Solved

How to add Unicode strings to database?

Posted on 2002-03-06
8
256 Views
Last Modified: 2008-02-01
[Env: Win2k AS, SQL Server 7.0]

Hi,

My database has field of 'ntext' type. I want to somehow programmatically add to it data continaing
Unicode chars, for example Japanese. How can I do this? Using ODBC? May be someone can give a sample?

Thanks in advance!
0
Comment
Question by:long8
  • 4
  • 4
8 Comments
 
LVL 49

Expert Comment

by:DanRollins
ID: 6849912
How do you intend to connect to the database?  Will you be using ADO, OLEDB?  Is your program written using MFC?

-- Dan
0
 

Author Comment

by:long8
ID: 6849926
ADO. Actually it does not matter. If you can give me working ODBC and OLEDB code I will accept your answer.
I'm now trying parameterized quiries in ADO...

Also I'm using sql server 2000, not 7.0, I mistaken.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6850074
THe thing is, you really don't need to do anything if you are using ADO.  As a COM object, it sjould work in UNICODE as its native tounge,\.  One needs to take pains to program in ANSI.  

When you assign a value to an ntext field, just make sure that you assign a UNICODE string.  What happens when you do code like this:

#define RsITEM(rs,x) rs->Fields->Item[_variant_t(x)]->Value

spRS->Open(... )
spRS->RsITEM("LastName")= _bstr_t(L"Jones");

-- Dan
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:long8
ID: 6850687
//I have UTF-8 Japanese string. I'm converting it to UNICODE:
int nTextLen=MultiByteToWideChar(CP_UTF8,0,"タテスカンナニチトシト",-1,NULL,0);
wchar_t *wszText=new wchar_t[nTextLen+1];
MultiByteToWideChar(CP_UTF8,0,"タテスカンナニチトシト",-1,wszText,nTextLen);

//Then I convert string to BCB's analogue of BSTR:
//C++Builder implements the WideString type as a class.
//WideString represents a string of wide characters
//(wchar_t). As such, it is useful when working with COM.

//WideString uses BSTRs in its underlying implementation. In
//addition to the usual string operators, the class includes
//methods for converting to or from BSTR values as well as
//for sharing the value of an existing BSTR.
WideString ws = WideString(wszText);

// Here is the code performing adding new row to database
ADOConnection1->Open("sa", "");
ADOQuery1->Connection = ADOConnection1;
ADOQuery1->SQL->Text = "INSERT INTO [MRATestProduct].[dbo].[PrTrack1] VALUES (4, :Description";
ADOQuery1->Parameters->ParamByName("Description")->Value = ws;

ADOQuery1->ExecSQL();
   
ADOQuery1->Connection = NULL;    
ADOConnection1->Close();    

Resulting row in database contains quotation marks ('?') instead of Japanese chars.

When I'm setting parameter value as bstr, like this:
ADOQuery1->Parameters->ParamByName("Description")->Value = ws->c_bstr();

I see the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:Incorrect syntax near '@P1'.
0
 

Author Comment

by:long8
ID: 6850688
//I have UTF-8 Japanese string. I'm converting it to UNICODE:
int nTextLen=MultiByteToWideChar(CP_UTF8,0,"タテスカンナニチトシト",-1,NULL,0);
wchar_t *wszText=new wchar_t[nTextLen+1];
MultiByteToWideChar(CP_UTF8,0,"タテスカンナニチトシト",-1,wszText,nTextLen);

//Then I convert string to BCB's analogue of BSTR:
//C++Builder implements the WideString type as a class.
//WideString represents a string of wide characters
//(wchar_t). As such, it is useful when working with COM.

//WideString uses BSTRs in its underlying implementation. In
//addition to the usual string operators, the class includes
//methods for converting to or from BSTR values as well as
//for sharing the value of an existing BSTR.
WideString ws = WideString(wszText);

// Here is the code performing adding new row to database
ADOConnection1->Open("sa", "");
ADOQuery1->Connection = ADOConnection1;
ADOQuery1->SQL->Text = "INSERT INTO [MRATestProduct].[dbo].[PrTrack1] VALUES (4, :Description";
ADOQuery1->Parameters->ParamByName("Description")->Value = ws;

ADOQuery1->ExecSQL();
   
ADOQuery1->Connection = NULL;    
ADOConnection1->Close();    

Resulting row in database contains quotation marks ('?') instead of Japanese chars.

When I'm setting parameter value as bstr, like this:
ADOQuery1->Parameters->ParamByName("Description")->Value = ws->c_bstr();

I see the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:Incorrect syntax near '@P1'.
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 300 total points
ID: 6851356
I think you will need to use the CreateParameter function, specifying a type of adWChar.

There is an example of use of CreateParameter here:

http://search.microsoft.com/gomsuri.asp?n=1&c=rp_Results&target=http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthappendxvc.asp

(the example does not user wide characters, but it does show the correct sequence for a parameterized command.

-- Dan
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6871887
Hi long8,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 

Author Comment

by:long8
ID: 6875880
Well, I've got it working using parameterized queries and your example! Great thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
Many modern programming languages support the concept of a property -- a class member that combines characteristics of both a data member and a method.  These are sometimes called "smart fields" because you can add logic that is applied automaticall…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question