Writing utf8 to database

scw2hi · Post by **scw2hi** » Thu 01 Mar 2007 08:21

Hello,

I use ODAC version 5.80.0.40 to connect to our database (which is Oracle 10g R10.2.0.2.0). The development tool is Borland 2006 for Windows, language is C++.

I have created a small project where I started using the TntWare visual components and where I set the NLS_LANG to GERMAN_GERMANY.ATL32UTF8 .

If I use the following options for the session object I can read and write utf8 from a CLOB field but not from an varchar2 field.

CharLength = 0
Charset = AL32UTF8
UseUnicode = false

If I use the following options for the session object I can read and write utf8 from a varchar2 field but not from an clob field.

CharLength = 0
Charset = AL32UTF8
UseUnicode = true

So where is my problem? I´d like to use both the varchar2 and the clob field with one session.

Plash · Post by **Plash** » Thu 01 Mar 2007 11:33

Please describe more detailed the problem when you read/write data from/to a VARCHAR2 field with the UseUnicode option set to False.

When the UseUnicode option is True, the session uses UTF-16 character set. So you can not read and write UTF-8 data in that mode.

scw2hi · Post by **scw2hi** » Thu 01 Mar 2007 11:43

In case the UseUnicode mode is set to false. I insert an character like
U+0F60 འ e0 bd a0 TIBETAN LETTER -A
and everything looks fine.
When I post the record the inserted letter turns into an ? in the form as well as in the database.

ONLY for the field that is linked to the varchar2 field if I do the same in the field linked to clob field everthing works fine.

Plash · Post by **Plash** » Fri 02 Mar 2007 10:55

If you set the UseUnicode option to True, the Unicode CLOB values are read and written incorrectly because TNT controls cannot work with Unicode CLOBs. There are two ways to solve this problem:

1. You can use the TTntMemo component for CLOB fields instead of TTntDBMemo. In this case you will have to read and write values for such fields manually by using the GetLob method or the AsWideString property of TOraLob. For example:

Code: Select all

  TntMemo1.Text := OraTable1.GetLob('TEXT').AsWideString;  // reading from the field
 
  OraTable1.GetLob('TEXT').AsWideString := TntMemo1.Text;  // writing to the field

2. You can modify source code of TNT controls to access TMemoField using the GetLob method. Below is the example. Note, this code is posted "As is". We do not guarantee that this code is fully working. Find corresponding procedures in the TntDB unit and replace them with the following ones:

Code: Select all

function GetAsWideString(Field: TField): WideString; 
var 
  WideField: IWideStringField; 
  Blob: TBlob; 
begin 
  if (Field is TMemoField) and (Field.DataSet is TCustomDADataSet) and not Field.IsNull then begin 
    Blob := TCustomDADataSet(Field.DataSet).GetBlob(Field.FieldName); 
    if Blob.IsUnicode then 
      Result := Blob.AsWideString 
    else 
      Result := Blob.AsString; 
    Exit; 
  end;
  if Field.GetInterface(IWideStringField, WideField) then 
    Result := WideField.AsWideString 
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then begin 
    if Field.IsNull then 
      // This fixes a bug in TWideStringField.GetAsWideString which does not handle Null at all. 
      Result := '' 
    else 
      Result := TWideStringField{TNT-ALLOW TWideStringField}(Field).Value 
  end else 
    Result := Field.AsString{TNT-ALLOW AsString}; 
end;

Code: Select all

procedure SetAsWideString(Field: TField; const Value: WideString); 
var 
  WideField: IWideStringField; 
  Blob: TBlob; 
begin 
  if (Field is TMemoField) and (Field.DataSet is TCustomDADataSet) then begin 
    Blob := TCustomDADataSet(Field.DataSet).GetBlob(Field.FieldName); 
    if Blob.IsUnicode then 
      Blob.AsWideString := Value 
    else 
      Blob.AsString := Value; 
    TBlobField(Field).Modified := True; 
    Exit; 
  end; 

  if Field.GetInterface(IWideStringField, WideField) then 
    WideField.AsWideString := Value 
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then 
    TWideStringField{TNT-ALLOW TWideStringField}(Field).Value := Value 
  else 
    Field.AsString{TNT-ALLOW AsString} := Value; 
end;

scw2hi · Post by **scw2hi** » Fri 02 Mar 2007 13:19

Ok, I tried the second example.

First I changed the functions in TntDB.pas.
Then I switched UseUnicode to true;
Table column (clob) is defined as TTntMemoField.

I insert Unicode text into the two fields (varchar2, clob) and post the edits.
In the form everything looks fine, but in the database the string stored in the clob is not utf8. Only the string stored in the varchar2 field was converted to utf8 on post.

So this only seems to work for the form, but writing back to the database is still wrong for the clob.

It seems as if there is missing some kind of conversion between utf8(db) and unicode(form) which should be done by the Tnt components?!?

Plash · Post by **Plash** » Sat 03 Mar 2007 08:48

We could not reproduce the problem. Please specify whether the character set of your database is AL32UTF8. If it is possible, send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create server objects.

scw2hi · Post by **scw2hi** » Mon 05 Mar 2007 12:52

Yes characterset in db is AL32UTF8.
I have send a small demo project to [email protected] as you requested.

Plash · Post by **Plash** » Wed 07 Mar 2007 08:55

Make sure that code you have added to TntDB.pas is used when COMPILER_10_UP is defined. For the last version of the TNT controls you can use the code below.
If you are using fields of the TTntMemoField type and the UseUnicode option of TOraSession is set to True, you should set the TTntMemoField.EncodingMode property to emNone.

Code: Select all

function GetAsWideString(Field: TField): WideString;
var
  Blob: TBlob;
{$IFNDEF COMPILER_10_UP}
  WideField: IWideStringField;
{$ENDIF}
begin
  if (Field is TMemoField) and (Field.DataSet is TCustomDADataSet) and not Field.IsNull then begin
    Blob := TCustomDADataSet(Field.DataSet).GetBlob(Field.FieldName);
    if Blob.IsUnicode then
      Result := Blob.AsWideString
    else
      Result := Blob.AsString;
    Exit;
  end;
{$IFDEF COMPILER_10_UP}
  if (Field.ClassType = TMemoField{TNT-ALLOW TMemoField}) then
    Result := VarToWideStr(Field.AsVariant) { works for NexusDB BLOB Wide }
  else
    Result := Field.AsWideString
{$ELSE}
  if Field.GetInterface(IWideStringField, WideField) then
    Result := WideField.AsWideString
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then
  begin
    if Field.IsNull then
      // This fixes a bug in TWideStringField.GetAsWideString which does not handle Null at all.
      Result := ''
    else
      Result := TWideStringField{TNT-ALLOW TWideStringField}(Field).Value
  end else if (Field is TMemoField{TNT-ALLOW TMemoField}) then
    Result := VarToWideStr(Field.AsVariant) { works for NexusDB BLOB Wide }
  else
    Result := Field.AsString{TNT-ALLOW AsString};
{$ENDIF}
end;

Code: Select all

procedure SetAsWideString(Field: TField; const Value: WideString);
var
  Blob: TBlob;
{$IFNDEF COMPILER_10_UP}
  WideField: IWideStringField;
{$ENDIF}
begin
  if (Field is TMemoField) and (Field.DataSet is TCustomDADataSet) then begin
    Blob := TCustomDADataSet(Field.DataSet).GetBlob(Field.FieldName);
    if Blob.IsUnicode then
      Blob.AsWideString := Value
    else
      Blob.AsString := Value;
    TBlobField(Field).Modified := True;
    Exit;
  end;
{$IFDEF COMPILER_10_UP}
  if (Field.ClassType = TMemoField{TNT-ALLOW TMemoField}) then
    Field.AsVariant := Value { works for NexusDB BLOB Wide }
  else
    Field.AsWideString := Value;
{$ELSE}
  if Field.GetInterface(IWideStringField, WideField) then
    WideField.AsWideString := Value
  else if (Field is TWideStringField{TNT-ALLOW TWideStringField}) then
    TWideStringField{TNT-ALLOW TWideStringField}(Field).Value := Value
  else if (Field is TMemoField{TNT-ALLOW TMemoField}) then
    Field.AsVariant := Value { works for NexusDB BLOB Wide }
  else
    Field.AsString{TNT-ALLOW AsString} := Value;
{$ENDIF}
end;

scw2hi · Post by **scw2hi** » Wed 07 Mar 2007 09:46

When I try to compile the TntDB.pas with the edits you sent I get several Pascal Errors.

[Pascal Fehler] TntDB.pas(288): E2003 Undefinierter Bezeichner: 'TBlob'
[Pascal Fehler] TntDB.pas(293): E2003 Undefinierter Bezeichner: 'TCustomDADataSet'
[Pascal Fehler] TntDB.pas(293): E2015 Operator ist auf diesen Operandentyp nicht anwendbar
[Pascal Fehler] TntDB.pas(293): E2015 Operator ist auf diesen Operandentyp nicht anwendbar
[Pascal Fehler] TntDB.pas(294): E2066 Operator oder Semikolon fehlt
[Pascal Fehler] TntDB.pas(295): E2029 'THEN' erwartet, aber Bezeichner 'IsUnicode' gefunden
[Pascal Fehler] TntDB.pas(323): E2029 Anweisung erwartet, aber 'PROCEDURE' gefunden
[Pascal Fehler] TntDB.pas(325): E2003 Undefinierter Bezeichner: 'TBlob'
[Pascal Fehler] TntDB.pas(330): E2003 Undefinierter Bezeichner: 'TCustomDADataSet'
[Pascal Fehler] TntDB.pas(330): E2015 Operator ist auf diesen Operandentyp nicht anwendbar
[Pascal Fehler] TntDB.pas(331): E2066 Operator oder Semikolon fehlt
[Pascal Fehler] TntDB.pas(332): E2029 'THEN' erwartet, aber Bezeichner 'IsUnicode' gefunden
[Pascal Fehler] TntDB.pas(356): E2029 Anweisung erwartet, aber 'FUNCTION' gefunden
[Pascal Fataler Fehler] TntUnicodeVcl.dpk(69): F2063 Verwendete Unit '..\..\Source\TntDB.pas' kann nicht compiliert werden

So I think there is still something missing?! Where do you get the TBlob and the 'TCustomDADataSet'?

Plash · Post by **Plash** » Wed 07 Mar 2007 10:06

You should add the MemData and DBAccess units to the uses clause of the TntDB unit.

scw2hi · Post by **scw2hi** » Wed 07 Mar 2007 13:18

Now the TntDB.pas can be successfully compiled.

When I set the TTntMemoField.EncodingMode property to emNone it is not getting better.

Now I am able to write utf8 to the database but the TntDBMemo field on the form shows '?' instead of the most foreign characters after posting a record.

Plash · Post by **Plash** » Mon 12 Mar 2007 09:39

In your sample project you write Unicode data to the TEST_COL field incorrectly. The way you use works when the UseUnicode option of the TOraSession component is False and the Charset property is set to AL32UTF8.
But when the UseUnicode option is set to True, you should convert data to UTF-16 (WideString) and write it through TOraLob object. Use the AsWideString property or the Write method of TOraLob.

Code: Select all

otb_test->GetLob("TEST_COL")->AsWideString = TntMemo1->Text;

scw2hi · Post by **scw2hi** » Tue 13 Mar 2007 08:40

That was the right hint.

Now everything works as I expected it.

Thank you very much