Hi,
I'm still porting from DBISAM to EDB, it's not as smooth as I had expected
Why does Locate('Name', 'foo ', []) return true even though there's
just a record with Name = 'foo' (no trailing space)?

Regards

--
Arno

Arno

>Why does Locate('Name', 'foo ', []) return true even though there's
>just a record with Name = 'foo' (no trailing space)?

As a guess Name is defined as a VarChar not a Char. VarChars will store the trailing space but will not take it into account when searching. At least in SQL can't remember about navigational stuff but since you have the "problem" I assume it ignores it there as well.

The behaviour was (I sort of remember) a compromise after people (mainly me) complained about storing trailing spaces. There is another unfortunate effect table.FieldByName('Name').AsString = 'foo ' will return false.

If my guess is right and you want trailing spaces  try changing the column type to Char. Then you'll need to match the contents exactly with all the right padding (or at least I assume so - I don't use Char).

Alternatively if you want the same behaviour as DBISAM check out the extensions ng for the table & query components I wrote to handle it.

Roy Lambert [Team Elevate]

Roy,

Thank you for your reply.

> As a guess Name is defined as a VarChar not a Char.

Correct, however doesn't matter, same behaviour with both Char and VarChar fields.

> VarChars will store the trailing space but will not take it into account when searching.

What a mess is this string handling, my small brain doesn't get it? IMO a space is a character as any other character as well. So when I search for "foo " (with trailing space) in a VarChar field that should not be the same as a search for "foo" (without trailing space), correct?

> At least in SQL can't remember about navigational stuff but since you have the "problem"
> I assume it ignores it  here as well.

It's a pity As it is that there's no option that truncates string-values automatically to field's size in order to avoid Exceptions when you assign a string to a string-field longer than field's size.

> There is another unfortunate effect table.FieldByName('Name').AsString = 'foo ' will return false.

Indeed, what a mess:
 if Dataset.Locate('Name', 'foo ', []) then
 begin
   if not (Dataset.FieldByName('Name').AsString = 'foo ') then
     raise Exception.Create('What a punk');
 end;

> Alternatively if you want the same behaviour as DBISAM

That's all I want, with one additional feature, namely Unicode support.

> check out the extensions ng
> for the table & query components I wrote to handle it.

Can you provide a link to that post?

--
Arno

Arno


>What a mess is this string handling, my small brain doesn't get it? IMO a space is a character as any other character as well. So when I search for "foo " (with trailing space) in a VarChar field that should not be the same as a search for "foo" (without trailing space), correct?

I actually disagree totally with trailing spaces being stored. It is madness to do that, unless you also intend to have all onscreen displays or printouts  to indicate there is a trailing space


>It's a pity As it is that there's no option that truncates string-values automatically to field's size in order to avoid Exceptions when you assign a string to a string-field longer than field's size.

Nope, but my table and wuery extensions do

>> check out the extensions ng
>> for the table & query components I wrote to handle it.
>
>Can you provide a link to that post?

Not without looking, so I'll let you do that <vbg>

Roy Lambert [Team Elevate]

Arno Garrels wrote:

> Hi,
> I'm still porting from DBISAM to EDB, it's not as smooth as I had
> expected Why does Locate('Name', 'foo ', []) return true even
> though there's just a record with Name = 'foo' (no trailing space)?
>
> Regards

Arno,

I got similar results within the ElevateDB Manager. I migrated Delphi's
DBDemos database into ElevateDB Manager. I ran a few queries against
the customer table and got some interesting results.


SELECT Company FROM customer
WHERE Company LIKE '%club'

Returned no results

-----------------

I changed the coallation of the "Company" column to be case insensitive
and reran the query.

ALTER TABLE "customer"
ALTER COLUMN "Company" AS VARCHAR(30) COLLATE "UNI_CI"

SELECT Company FROM customer
WHERE Company LIKE '%club'

VIP Divers Club
Marmot Divers Club
Makai SCUBA Club
Action Club
Blue Sports Club
Catamaran Dive Club

This is what I expected to see.
-----------------

SELECT Company FROM customer
WHERE Company LIKE '%club '

VIP Divers Club
Marmot Divers Club
Makai SCUBA Club
Action Club
Blue Sports Club
Catamaran Dive Club

This is not what I expected to see. I expected to get zero results
because of the trailing space used in the LIKE clause.
-----------------

SELECT Company FROM customer
WHERE Company LIKE '%club_'

Marmot Divers Club
Makai SCUBA Club

I replaced the trailing [Space] with the wildcard character and got the
above results. Now, I'm thinking that there is some sort of trailing
character at the end of these two records. So I modified the query one
more time.
-----------------

SELECT '|' + Company + '|' From customer
WHERE Company LIKE '%club_'

|Marmot Divers Club|
|Makai SCUBA Club|

When I changed to output to bracket the Company field with '|'
characters there was no visible trailing character on either of these
results. Interesting...  perhaps Tim can shed some light on this one.


--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Roy,

> I actually disagree totally with trailing spaces being stored. It is madness to do that, unless you also intend to have all onscreen displays or printouts  to indicate there is a trailing space.

IMO that was possible in DBISAM and that never made a problem.
Anyway my question is not about store trailing spaces but unexpected search results


>It's a pity As it is that there's no option that truncates string-values automatically to field's size in order to avoid Exceptions when you assign a string to a string-field longer than field's size.

> Nope, but my table and wuery extensions do

I only found several versions of a nlhTable.pas that cannot prevent the truncation exception.
So I gave it myself a trial today though I do not have the source of EDB at home.

--
Regards

{code}
{**************************************************************************************************}
{                                                                                                  }
{ The contents of this file are subject to the Mozilla Public License Version 1.1 (the "License"); }
{ you may not use this file except in compliance with the License. You may obtain a copy of the    }
{ License at http://www.mozilla.org/MPL/                                                           }
{                                                                                                  }
{ Software distributed under the License is distributed on an "AS IS" basis, WITHOUT WARRANTY OF   }
{ ANY KIND, either express or implied. See the License for the specific language governing rights  }
{ and limitations under the License.                                                               }
{                                                                                                  }
{ The helper functions for patching are Copyright (C) 2008-2009 Andreas Hausladen.                 }
{ All Rights Reserved.                                                                             }
{                                                                                                  }
{ Portions created by Arno Garrels are Copyright (C) 2013 Arno Garrels.                            }
{ All Rights Reserved.                                                                             }
{                                                                                                  }
{**************************************************************************************************}

{$A8,B-,C+,D+,E-,F-,G+,H+,I+,J-,K-,L+,M-,N-,O+,P+,Q-,R-,S-,T-,U-,V+,W-,X+,Y+,Z1}

unit PatchPack;

interface

const
 PatchPackVersion = '1.0';
 PatchPackDate = '2013/18/08';  // yyyy/dd/mm

{
Usage
=====
  Add the unit to the .dpr file's uses-list.
  C++Builder user can add the file to the project (Menu Project/Add to project)

Example
=======
  uses
    FastMM4, // optional memory manager
    PatchPack,
    Forms,
    Unit1 in 'Unit1.pas';

---------------------------------------------------------------------------- }

{$Define NoEDBTruncationErrors}

implementation

uses
 Windows, SysUtils, DB;

{ ---------------------------------------------------------------------------- }
{ Helper functions, shared }

type
 THackWideStringField = class(TWidestringField);
 THackStringField     = class(TStringField);

 TJumpOfs = Integer;
 PPointer = ^Pointer;

type
 PXRedirCode = ^TXRedirCode;
 TXRedirCode = packed record
   Jump: Byte;
   Offset: TJumpOfs;
 end;

 PWin9xDebugThunk = ^TWin9xDebugThunk;
 TWin9xDebugThunk = packed record
   PUSH: Byte;
   Addr: Pointer;
   JMP: TXRedirCode;
 end;

 PAbsoluteIndirectJmp = ^TAbsoluteIndirectJmp;
 TAbsoluteIndirectJmp = packed record
   OpCode: Word;   //$FF25(Jmp, FF /4)
   Addr: PPointer;
 end;

{ Hooking }

function GetActualAddr(Proc: Pointer): Pointer;

 function IsWin9xDebugThunk(AAddr: Pointer): Boolean;
 begin
   Result := (AAddr <> nil) and
             (PWin9xDebugThunk(AAddr).PUSH = $68) and
             (PWin9xDebugThunk(AAddr).JMP.Jump = $E9);
 end;

begin
 if Proc <> nil then
 begin
   if (Win32Platform <> VER_PLATFORM_WIN32_NT) and IsWin9xDebugThunk(Proc) then
     Proc := PWin9xDebugThunk(Proc).Addr;
   if (PAbsoluteIndirectJmp(Proc).OpCode = $25FF) then
     Result := PAbsoluteIndirectJmp(Proc).Addr^
   else
     Result := Proc;
 end
 else
   Result := nil;
end;

procedure HookProc(Proc, Dest: Pointer; var BackupCode: TXRedirCode);
var
 n: NativeUInt;
 Code: TXRedirCode;
begin
 Proc := GetActualAddr(Proc);
 Assert(Proc <> nil);
 if ReadProcessMemory(GetCurrentProcess, Proc, @BackupCode, SizeOf(BackupCode), n) then
 begin
   Code.Jump := $E9;
   Code.Offset := PAnsiChar(Dest) - PAnsiChar(Proc) - SizeOf(Code);
   WriteProcessMemory(GetCurrentProcess, Proc, @Code, SizeOf(Code), n);
 end;
end;

procedure UnhookProc(Proc: Pointer; var BackupCode: TXRedirCode);
var
 n: NativeUInt;
begin
 if (BackupCode.Jump <> 0) and (Proc <> nil) then
 begin
   Proc := GetActualAddr(Proc);
   Assert(Proc <> nil);
   WriteProcessMemory(GetCurrentProcess, Proc, @BackupCode, SizeOf(BackupCode), n);
   BackupCode.Jump := 0;
 end;
end;

procedure ReplaceVmtField(AClass: TClass; OldProc, NewProc: Pointer);
type
 PVmt = ^TVmt;
 TVmt = array[0..MaxInt div SizeOf(Pointer) - 1] of Pointer;
var
 I: Integer;
 Vmt: PVmt;
 n: NativeUInt;
 P: Pointer;
begin
 OldProc := GetActualAddr(OldProc);
 NewProc := GetActualAddr(NewProc);

 I := vmtSelfPtr div SizeOf(Pointer);
 Vmt := Pointer(AClass);
 while (I < 0) or (Vmt[I] <> nil) do
 begin
   P := Vmt[I];
   if (P <> OldProc) and (Integer(P) > $10000) and not IsBadReadPtr(P, 6) then
     P := GetActualAddr(P);
   if P = OldProc then
   begin
     WriteProcessMemory(GetCurrentProcess, @Vmt[I], @NewProc, SizeOf(NewProc), n);
     Exit;
   end;
   Inc(I);
 end;
end;

function GetDynamicMethod(AClass: TClass; Index: Integer): Pointer;
asm
 call System.@FindDynaClass
end;

procedure DebugLog(const S: string);
begin
 OutputDebugString(PChar('PatchPack installed: ' + S));
end;
{ End of helper functions }

{ **************************************************************************** }

{$IFDEF NoEDBTruncationErrors}
var
 BackupSetAsString, BackupSetAsWideString, BackupSetAsAnsiString: TXRedirCode;

type
 TWideStringFieldFix = class(TWideStringField)
 protected
   procedure SetAsString(const Value: string); override;
   procedure SetAsWideString(const Value: UnicodeString); override;
 end;

(* Not implemented yet *
 TStringFieldFix = class(TStringField)
 protected
   procedure SetAsAnsiString(const Value: AnsiString); override;
 end;

procedure TStringFieldFix.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 := Self.Size;
 end;
 StrLCopy(PBuf, PAnsiChar(Value), Len);
 if Transliterate then
   DataSet.Translate(PBuf, PBuf, True);
 SetData(PBuf);
end;
*)

procedure TWideStringFieldFix.SetAsString(const Value: string);
var
 Val: string;
begin
 if Length(Value) > Self.Size then
   Val := Copy(Value, 1, Size)
 else
   Val := Value;
 SetData(PChar(Val), False);
end;

procedure TWideStringFieldFix.SetAsWideString(const Value: UnicodeString);
var
 Val: string;
begin
 if Length(Value) > Self.Size then
   Val := Copy(Value, 1, Size)
 else
   Val := Value;
 SetData(PChar(Val), False);
end;

procedure InitEDBTruncationErrorFix;
begin
 DebugLog('NoEDBTruncationErrors');
 HookProc(@THackWideStringField.SetAsString, @TWideStringFieldFix.SetAsString, BackupSetAsString);
 HookProc(@THackWideStringField.SetAsWideString, @TWideStringFieldFix.SetAsWideString, BackupSetAsWideString);
 // HookProc(@THackStringField.SetAsAnsiString, @TStringFieldFix.SetAsAnsiString, BackupSetAsAnsiString);
end;

procedure FiniEDBTruncationErrorFix;
begin
 UnhookProc(@THackWideStringField.SetAsString, BackupSetAsString);
 UnhookProc(@THackWideStringField.SetAsWideString, BackupSetAsWideString);
 // UnhookProc(@THackStringField.SetAsAnsiString, BackupSetAsAnsiString);
end;
{$ENDIF NoEDBTruncationErrors}

{ **************************************************************************** }

initialization
{$IFDEF NoEDBTruncationErrors}
 InitEDBTruncationErrorFix;
{$ENDIF NoEDBTruncationErrors}

finalization
 // In revers order
{$IFDEF NoEDBTruncationErrors}
 FiniEDBTruncationErrorFix;
{$ENDIF NoEDBTruncationErrors}

end.
{code}  

Arno

Thanks for letting me know that.


I'll have to check the version I'm using at present with what's on the ng. If its different I'll upload the latest version for those who, like myself, don't have the source to ElevateDB.

Roy Lambert

Arno


My table component still nicely truncates, but the query component doesn't. Its a while since I wrote it, and I think I would have tested it out but obviously I haven't encountered a field overflow for a while so didn't realise it no longer worked.

My guess is Tim has changed something along the way so the events I was hooking into no longer apply.I'll have to work on it

Roy Lambert

Arno


I've talked to Tim about it and my modified query component works if you're editing a sensitive result set but not for UPDATE statements. They're carried out in the bowels of the engine. Either I didn't test properly or I forgot.

Roy Lambert

Michael,

<< When I changed to output to bracket the Company field with '|' characters
there was no visible trailing character on either of these results.
Interesting...  perhaps Tim can shed some light on this one. >>

It's a bug with LIKE.  I'll have a fix in the next build.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com