Reading and writing unicode strings to database

[Guest]

Hi,

I have some basic problems with write and read unicode strings to db. I know that it is lame, but if u could help me that would be great.

lets create a table is sql for example:
create table testtbl (a number, b nvarchar2(40))

;we use oracle server 9 and client 9. and Borland C++ Builder 6 by the way.

writing:
WideString ws;
... //initialize the ws with a eg. string containing Russian characters
// I set the OraSession1->Options->UseUnicode to true in design time
OraQuery1->SQL->Clear();
OraQuery1->SQL->Add("insert into testtbl values (:nr, :wstr)");
OraQuery1->ParamByName("nr")->AsInteger=7;
OraQuery1->ParamByName("wstr")->AsWideString=ws;
OraQuery1->Execute();


well it write some thing to the db, but I dont know if it is unicoded or not, because i see only ???? instead of the string in the db viewer.
But I cannot read it back anyway.

reading:
OraQuery1->SQL->Clear();
OraQuery1->SQL->Add("select a, b from testtbl");
OraQuery1->Open();
while(!OraQuery1->Eof)
{int i=OraQuery1->FieldsByName("A")->AsInteger; //ok i==7
WideString ws2=OraQuery1->FieldsByName("B")->AsWideString;
//error: TField dont have the AsWideString property.
//If I use the AsString then ws=="??????"
//I realized that BCB's TWideString dont have the AsWideString property as well
//I saw that the TOraDataSet class (which is what the TQuery uses) has a lot of GetFile, GetLob, GetInterval... methods but there is no GetWideString
//How read back the widestring, (and did i wrote it to the db correctly)
}


Waiting for your kindly reply
Gabor

[Alex]

There are two things that you need to do:
First - if you use National Oracle data types you should set TOraParam->National property to true
(please see "TOraParam.National" topic in ODAC help)
Second - you should cast your field OraQuery1->FieldsByName("B") to TWideStringField, and use it.
(please see "Working with Unicode character data" topic in ODAC help)

[Gabor]

Hi Alex!

Thank you for your answer, i have read the help topics you suggested, but it didn't worked for me.

(OraSession1->Options->UseUnicode was set to true in design time)

OraQuery1->SQL->Clear();
OraQuery1->SQL->Add("insert into testtbl values (:nr, :wstr)");
OraQuery1->Params->Items[0]->AsInteger=7;
OraQuery1->Params->Items[1]->National=true; /*runtime error: 'Wrong national type' but the help said it is boolean */
OraQuery1->Params->Items[1]->AsWideString=ws; //ws is 4 russian character
OraQuery1->Execute();

If I dont set the national property then it do something, but if i try to read it back like this:

OraQuery1->SQL->Clear();
OraQuery1->SQL->Add("select a, b from testtbl");
OraQuery1->Open();
while(!OraQuery1->Eof)
{int i=OraQuery1->FieldsByName("A")->AsInteger; //ok i==7
WideString ws2=((TWideStringField*)(OraQuery1->FieldsByName("B")))->Value;

//ws2=="????" (I get back only 4 ? characters.)

Does this type of reading is good?
How can I set the TOraParam->National propery?
(I got ODAC 5.55.0.20)

[Paul]

We cannot reproduce your problem with ODAC 5.55.0.20, Oracle server 9.2.0.1 ('NLS_NCHAR_CHARACTERSET'=UTF8 or 'NLS_NCHAR_CHARACTERSET'=AL16UTF16), Oracle Client 9.2.0.1, 9.2.0.4. To avoid 'Wrong national type' message please set TOraParam.DataType=ftWideString before assigning TOraParam.National. Please check charset for national characters in your database

select * from nls_database_parameters where parameter = 'NLS_NCHAR_CHARACTERSET'

[Guest]

Thanks Paul!

I tryed to set the
Param.DataType=ftWideString;
before the
Param.National=true;
and it is working.
I can finally write cyrillic unicode to the db and read it back.
(by the way: the NLS_NCHARCHARACTERSET=AL16UTF16 for us)

thanks you for your helps.

Gabor