How to add Unicode strings to database?

[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!
long8Asked:
Who is Participating?
 
DanRollinsConnect With a Mentor Commented:
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
 
DanRollinsCommented:
How do you intend to connect to the database?  Will you be using ADO, OLEDB?  Is your program written using MFC?

-- Dan
0
 
long8Author Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
DanRollinsCommented:
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
 
long8Author Commented:
//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
 
long8Author Commented:
//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
 
DanRollinsCommented:
Hi long8,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 
long8Author Commented:
Well, I've got it working using parameterized queries and your example! Great thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.