Solved

How to add Unicode strings to database?

Posted on 2002-03-06
8
245 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
Container Orchestration platforms empower organizations to scale their apps at an exceptional rate. This is the reason numerous innovation-driven companies are moving apps to an appropriated datacenter wide platform that empowers them to scale at a …
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now