Unicode in TUniQuery

[dafe]

Hi,

I'm wondering how to write an SQL statement into TUniQuery that contains unicode characters.

E.g.:

Code: Select all

SELECT * FROM ALL_TABLES WHERE TABLE_NAME='some unicode'

Since the SQL property is of type TStrings it doesn't supports wide strings. Any idea?

Thanks for any help
David

[Plash]

You can write a SQL statement with Unicode characters if you are using UniDAC for Delphi 2009. This version of Delphi supports Unicode in the string and TStrings types.

[dafe]

I'm using Delphi 7, does it mean UniDAC doesn't support Unicode for Delphi 7?

One of the key features of UniDAC you mention on your web page is:

Unicode and national charsets support

There is no note about that this feature is not supported in Delphi 7!!!

Why don't you use instead of TStringList for example TNTStringList or any other wide string list. At least I would expect that it would be possible to hand over the unicode text as UTF8 into a TStringList.

[Plash]

Unicode is not supported in the SQL property for Delphi 7. But you can use UTF8 for the SQL property. You need to set the character set in NLS_LANG to AL32UTF8. You can do it in several ways:

1. Change the NLS_LANG value in the registry. It is located in the key
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\

2. Set NLS_LANG environment variable. You can set it using Control Panel | System, or in your application with the following code:

SetEnvironmentVariable('NLS_LANG', 'AMERICAN_AMERICA.AL32UTF8');

[dafe]

Hi Plash,

trying to convert an SQL statement from WideString to UTF8 and use it in the SQL property, but unfortunately without any success. Here is piece of code which should work:

Code: Select all

procedure RetrieveTableTest;
var
  TableName: WideString;
  SQL: WideString;

begin
  QuAllTables.SQL.text := 'SELECT * FROM ALL_TABLES WHERE OWNER=''SCOTT''';
  QuAllTables.Execute;

  while not QuAllTables.Eof do
  begin
    TableName := QuAllTables.Fields.FieldByName( 'TABLE_NAME' ).AsVariant;
    SQL       := WideString( 'SELECT * FROM ALL_TABLES WHERE TABLE_NAME=''' ) +TableName+ WideString( '''' );

    QuGetSpecificTable.SQL.Text := ConvertWideStringToUtf8( SQL );
    QuGetSpecificTable.Execute;

Assert( not QuGetSpecificTable.Eof, 'Table ' +TableName+ ' not found' );

    QuAllTables.Next;
  end;
end;

function ConvertWideStringToUtf8(Value: WideString): UTF8String;
var
  Temp  : UTF8String;
  Copied: Integer;
begin
   Result := '';
   SetLength( Temp, Length( Value ) * 3 );
   Copied := UnicodeToUtf8(PChar(Temp), Length(Temp)+1, PWideChar( Value ), Length( Value ));
   if Copied > 0 then
   begin
     SetLength( Temp, Copied-1 );
     Result := Temp;
   end;
end;

Any idea why it doesn't work?

Thanks
David

NLS_LANG is set to AL32UTF8

[dafe]

Hi Plash,

After setting also the environment variable NLS_LANG to AL32UTF8 the above mentioned code works. Converting wide string SQL statement to UTF8 SQL statement seems to be a workarround that solve my problem.

Just not understand why the NLS_LANG in the registry is ignored, but this topic belongs not here.

Thank you for your help.
David

[Plash]

You should check that you have set NLS_LANG for correct Oracle home in the registry, and that the NLS_LANG environment variable is not defined.