Problem with SQL statement on a Chinese Windows XP

[Bartom]

Hi all,

I have problem to execute some SQL statement with non ASCII characters in comments.

Exemple:

Code: Select all

--This is a comment with non ACSII éàèöüä
Select * from MY_TABLE

This problem happens only on a machine with:
- Windows XP in Chinese installed
- The regional settings are set to Chinese RPC
- And Language for non-unicode programs is set to Chinese RPC too
- The Database is Unicode in AL32UTF8

If I set Language for non-unicode programs to English-US it works fine, but I cannot do that on all machine of customer.

I think that the problem happens because OCI engine that execute the SQL statement is not unicode. It uses the charset of the machine. In this case charset is Chinese RPC and if I have comment in French or German with 'éèàüöï' it does not work.

Can you resolve this problem? What about Globalization in ODAC?
Is there a solution to strip all comments from SQL statement before execution?

Thanks, regards,

Maurizio

[Plash]

You should not use non ACSII characters in SQL property, if your application works on computers with different regional settings.
The form is not loaded correctly if a string property contains non ACSII characters. Depending on Delphi version such characters can be treated as chinese characters or it can be replaced with '?'.

When executing SQL, OCI uses a character set in NLS_LANG parameter from environment variable or HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ registry key. Probably you have Chinese character set in NLS_LANG. It is required to convert Chinese data to Unicode.

[Bartom]

Hi,

Sorry but I could not fix the bug with your last solution, but I have more information for you.

I did more test and now I can reproduce the problem on my own computer.

Steps to reproduce the bug:

1. Create VCL Application program and copy the following code source to it

Code: Select all

unit WinTest;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Ora, System.ComponentModel, Borland.Vcl.StdCtrls;

type
  TForm2 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    oSession: TOraSession;
    oCommand: TOraQuery;
  public
    { Public declarations }
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
  end;

var
  Form2: TForm2;

implementation

{$R *.nfm}

{ TForm2 }

procedure TForm2.Button1Click(Sender: TObject);
begin
  try
    oCommand.Open;
    ShowMessage(
      oCommand.SQL.Text+sLineBreak+
      'RecordCount: '+oCommand.RecordCount.ToString);
    oCommand.Close;
    oSession.DisConnect;

  except
    on e: exception do
      ShowMessage(e.ToString);
  end;
end;

constructor TForm2.Create(AOwner: TComponent);
begin
  inherited;
  oSession := TOraSession.Create(nil);
  oCommand := TOraQuery.Create(nil);

  oSession.Server := 'MYBASE';
  oSession.Schema := 'MYSCHEMA';
  oSession.Username := test;
  oSession.Password := test;
  oSession.Connect;

  oCommand.Session := oSession;
  oCommand.SQL.Text :=
    '-- ceci est un ééé   éà àéàéà    commantaire   àéàéà'+sLinebreak+
    'Select * from MY_TABLE '+sLinebreak+
    '-- àéàéàé  ';
end;

destructor TForm2.Destroy;
begin
  inherited;
  FreeAndNil(oSession);
  FreeAndNil(oCommand);
end;

end.

2. Compile your application and save it

3. Open computer “Regional and Language Options” from control panel.
Change your language to Chinese (PRC) in the “regional Options” TabSheet .
Change the “Language for non-unicode program” to Chinese (PRC) to the “Advanced” TabSheet
Restart your computer.

4. Test your saved application.

Just for information, I tested the same application using OracleConnection and OracleCommand from the System.Data.OracleClient namespace of the .Net Framework and it works !

So I deduct that the problem comes from ODAC.

I hope that you can help me to fix this bug.

Thank you,

Maurizio.

[Plash]

System.Data.OracleClient.OracleCommand class uses Unicode for the text of SQL statements.

ODAC does not support Unicode in the text of SQL. So that you cannot use national characters in the SQL text if you run the program with different regional settings. You should remove national characters from the SQL.

[Bartom]

ok thanks.

I consider this thread close.

Maurizio