Wrong field length when accessing Unicode DB in Ansi mode

[cis-wurzen]

We do right now deliver the released version of our application with Ansi DB access and we want to switch to Unicode DB access somewhen next year (we do drive already an Unicode DB branch in parallel). Since it is necessary to have a DB with an Unicode charset like AL32UTF8 in order to store unicode strings, upgrading the DB charset isn't easy, our (not comprehensive) tests with the Ansi DB application against the Unicode DB where promising and so we planned to install Oracle using an Unicode charset for new customers to avoid the DB charset upgrade later. Lately we tried that, but using the application fails due wrong field lengths and field types in several modules.

I've created a test case and the reason for the failure seems to be in OraClasses.TOCICommand.GetFieldDesc8 where in mode CharUsed FConnection.FCharLength = 1 needs to be taken into account. Ironically this is done in the LITE version and so this would pass the test case. In order to fix the issue I've removed {$IFDEF LITE} in the following snippet

Code: Select all

if CharUsed then
  if FConnection.FUseUnicode {$IFDEF LITE}or (FConnection.FCharLength = 1){$ENDIF} then begin
    Check(OCIAttrGet2(hParam, OCI_DTYPE_PARAM, ValueInt, nil, OCI_ATTR_CHAR_SIZE, hOCIError));
    Field.Length := sb2(ValueInt);
  end

Changing > into >= in the last CharUsed if statement from

Code: Select all

// in non Unicode mode and CharLength > 1 size of field = length in chars * size of char in bytes
else if FConnection.FCharLength > 1 then begin

to

Code: Select all

// in non Unicode mode and CharLength > 1 size of field = length in chars * size of char in bytes
else if FConnection.FCharLength >= 1 then begin

would also be an option.

The test case for this is the following:

SQL for the test table

Code: Select all

CREATE TABLE ODACTESTUNICODEDB (
  FIELD10   VARCHAR2(10),
  FIELD240  VARCHAR2(240)
);

ALTER TABLE ODACTESTUNICODEDB ADD CONSTRAINT ODACTESTUNICODEDBPK PRIMARY KEY (FIELD10);

Delphi console application

Code: Select all

program ODACAnsiAccessUnicodeDB;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Ora,
  OraError,
  OraSmart;

{
CREATE TABLE ODACTESTUNICODEDB (
  FIELD10   VARCHAR2(10),
  FIELD240  VARCHAR2(240)
);

ALTER TABLE ODACTESTUNICODEDB ADD CONSTRAINT ODACTESTUNICODEDBPK PRIMARY KEY (FIELD10);
}

const
  cServer = 'YourServer';
  cUsername = 'YourUser';
  cPassword = 'YourPassword';

function TestAnsiAccessUnicodeDBFieldLength: Boolean;
var
  se: TOraSession;
  qr: TSmartQuery;
  FS, Counter: Integer;
begin
  Counter := 0;
  se := TOraSession.Create(nil);
  qr := TSmartQuery.Create(nil);
  try
    se.Server := cServer;
    se.Username := cUserName;
    se.Password := cPassword;
    //se.Options.UseUnicode := True;//#1 makes it pass
    //se.Options.Charset := 'WE8MSWIN1252';//#2 does not help
    with qr do
    begin
      Session := se;
      Options.LongStrings := False;//#3 fails also with True (False demos wrong field type)
      SQL.Add('DELETE FROM ODACTESTUNICODEDB');
      ExecSQL;
      SQL.Clear;
      SQL.Add('SELECT * FROM ODACTESTUNICODEDB');
      Keyfields := 'FIELD10';
      Open;

      //check VARCHAR2(10) displaywidth
      FS := Fields[0].DisplayWidth;
      if FS = 10 then
        Inc(Counter)
      else
        WriteLn('FAIL #1 - ', FS);

      //check VARCHAR2(240) displaywidth
      FS := Fields[1].DisplayWidth;
      if FS = 240 then
        Inc(Counter)
      else
        WriteLn('FAIL #2 - ', FS);

      //with LongStrings = False VARCHAR2(240) should be TxStringField
      if Pos('StringField', Fields[1].ClassName) > 0 then
        Inc(Counter)
      else
        WriteLn('FAIL #3 - ', Fields[1].ClassName);

      //insert a record with max number of chars
      Insert;
      Fields[0].AsString := '0123456789';
      Post;

      //insert a record with number of chars > max number of chars
      //o leads to "ORA-12899: ... (actual: 11, maximum: 10)" in failure case
      //o should lead to "ORA-00001..." in pass case, because content gets truncated after 10th char
      try
        Insert;
        Fields[0].AsString := '0123456789+';
        Post;
      except
        on E: EOraError do
        begin
          if EOraError(E).ErrorCode = 1 then
            Inc(Counter)
          else
          if (EOraError(E).ErrorCode = 12899) and (Pos(': 10', E.Message) > 0) then
            WriteLn('FAIL #4 - ' + E.Message)
          else
            raise;
        end
        else
          raise;
      end;
      Close;
    end;
  finally
    se.Free;
  end;
  Result := Counter = 4;
end;

begin
  try
    if TestAnsiAccessUnicodeDBFieldLength then
      WriteLn('PASS')
    else
      WriteLn('FAIL');
  except
    on E: Exception do
    begin
      WriteLn('FAIL - Exception Error');
      WriteLn('  E.ClassName = ', E.ClassName);
      WriteLn('    E.Message = ', E.Message);
    end;
  end;
  ReadLn;
end.

Steps:
- execute the script
- create a new console application in Delphi and paste the code
- adjust the constants cServer, cUserName and cPassword
(important DB parameters are NLS_LENGTH_SEMANTICS = Char and NLS_CHARACTERSET = AL32UTF8)
- compile and run the example

expected: output is PASS
actual:
FAIL #1 - 40
FAIL #2 - 10
FAIL #3 - TMemoField
FAIL #4 - ORA-12899: ..."ODACTESTUNICODEDB"."FIELD10" (...: 11, ...: 10)

FAIL

[bork]

Thank you for the information, we will fix ODAC behavior with Options.CharLength = 1. Now the default value of Options.CharLength will be changed to 0, and ODAC will automatically detect CharLength for backward compatibility. But if you set Options.CharLength = 1, then you will get the expected behavior.

But there are two notes:

Note 1:
FS := Fields[0].DisplayWidth is incorrect, you should use FS := Fields[0].Size;

Note 2:
Fields[0].AsString := '0123456789+' will be posted to database without any exceptions, because Delphi uses the following code to assign strings:

Code: Select all

procedure TStringField.SetAsAnsiString(const Value: AnsiString);
var
  Len: Integer;
  PBuf: PAnsiChar;
  Temp: AnsiString;
  Buffer: array[0..dsMaxStringSize] of AnsiChar;
begin
  if DataSize > SizeOf(Buffer) then
  begin
    SetLength(Temp, strlen(PAnsiChar(Value))+1);
    PBuf := PAnsiChar(Temp);
    Len := StrLen(PAnsiChar(Value));
  end else
  begin
    PBuf := Buffer;
    Len := Size;
  end;
  StrLCopy(PBuf, PAnsiChar(Value), Len);   <--- Here your string will be cut to 10 characters length !!!
  if Transliterate then
    DataSet.Translate(PBuf, PBuf, True);
  SetData(PBuf);
end;

[cis-wurzen]

Thank you for the information, we will fix ODAC behavior with Options.CharLength = 1. Now the default value of Options.CharLength will be changed to 0, and ODAC will automatically detect CharLength for backward compatibility. But if you set Options.CharLength = 1, then you will get the expected behavior.

TIA.

But there are two notes:

Note 2:
Fields[0].AsString := '0123456789+' will be posted to database without any exceptions, because Delphi uses the following code to assign strings:

Code: Select all

procedure TStringField.SetAsAnsiString(const Value: AnsiString);
var
  ...
begin
  ...
  StrLCopy(PBuf, PAnsiChar(Value), Len);   <--- Here your string will be cut to 10 characters length !!!
  ...
end;

Test #4 does explicitly use that feature in connection with the size bug and to pass the test an exception has to occur!

This particular test is doing this:
1. insert record with PK '0123456789'
2. try to insert a record with PK '0123456789+'

When everything works as expected then the value in the second step is truncated after the 10th char and trying to post the record has to fail with a PK violation (ORA-00001). With the wrong size the value is not truncated and posting does thatswhy fail with ORA-12899. When performing the second step without the first and an empty table then there should be of course no exception.

[bork]

Hello

We released the new ODAC build (ver. 8.1.4) yesterday. I hope all your issues were resolved. Please try it and post feedback, if any issues has left.

[cis-wurzen]

Thanks. Works so far as expected when setting Options.CharLength to 1.