Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Writing utf8 to database

https://forums.devart.com/viewtopic.php?f=5&t=9197

Page 1 of 1

Writing utf8 to database

Posted: Thu 01 Mar 2007 08:21
by scw2hi
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.

Posted: Thu 01 Mar 2007 11:33
by Plash
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.

Posted: Thu 01 Mar 2007 11:43
by scw2hi
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.

Posted: Fri 02 Mar 2007 10:55
by Plash
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;

Posted: Fri 02 Mar 2007 13:19
by scw2hi
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?!?

Posted: Sat 03 Mar 2007 08:48
by Plash
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.

Posted: Mon 05 Mar 2007 12:52
by scw2hi
Yes characterset in db is AL32UTF8.
I have send a small demo project to [email protected] as you requested.

Posted: Wed 07 Mar 2007 08:55
by Plash
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;

Posted: Wed 07 Mar 2007 09:46
by scw2hi
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'?

Posted: Wed 07 Mar 2007 10:06
by Plash
You should add the MemData and DBAccess units to the uses clause of the TntDB unit.

Posted: Wed 07 Mar 2007 13:18
by scw2hi
Now the TntDB.pas can be successfully compiled. :wink:

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.

Posted: Mon 12 Mar 2007 09:39
by Plash
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;

Posted: Tue 13 Mar 2007 08:40
by scw2hi
That was the right hint. :D

Now everything works as I expected it.

Thank you very much
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/