Goto and Find functions?

[tatamata]

Lazarus+Zeos+PostgreSQL.
It seems that I can't use SetKey, GotoKey, GotoNearest; FindKey, FindNearest in for searching values in a table?
Are there alternative methods for performing searches in a TZTable?
Thanks,

Zlatko

[btrewern]

First thing first - Don't use TZTable for anything other than very small tables!!

Try using Locate or Lookup.

Regards,

Ben

[MarkusD]

Dind't have time to test these functions, but perhaps it helps.

Code: Select all

function TZTable.FindKey(var KeyValues: array of TVarRec): Boolean;
var
  V: Variant;
  i: integer;
  FieldRefs: TObjectDynArray;
  FieldDirs: TBooleanDynArray;
  OnlyDataFields: Boolean;
  KeyFields: String;
begin
  DefineSortedFields( Self, IndexFieldNames, FieldRefs, FieldDirs, OnlyDataFields);
  for i := 0 to High( FieldRefs) do
    KeyFields := KeyFields + TField( FieldRefs[ i]).FieldName + ';';
  KeyFields := copy( KeyFields, 1, Length( KeyFields) - 1);
  if Length( FieldRefs) = 1 then
    Result := Locate( KeyFields, ConvertToVariant( KeyValues[ 0]), [ loCaseInsensitive])
  else
  begin
    V := VarArrayCreate( [ 0, Length( FieldRefs) - 1], varVariant);
    for i := 0 to High( FieldRefs) do
      if i < Length( KeyValues) then
        V[ i] := ConvertToVariant( KeyValues[ i])
      else
        V[ i] := Null;
    Result := Locate( KeyFields, V, [ loCaseInsensitive])
  end;
end;

procedure TZTable.FindNearest(var KeyValues: array of TVarRec);
var
  I, RowNo, RowCount: Integer;
  FieldRefs: TObjectDynArray;
  FieldDirs: TBooleanDynArray;
  OnlyDataFields: Boolean;
  KValues: TZVariantDynArray;

  function CompareFieldsFromResultSet: Boolean;
  var
    I: Integer;
    ColumnIndex: Integer;
    CurrentType : TZSQLType;
  begin
    Result := True;
    for I := 0 to High(KValues) do
    begin
      ColumnIndex := TField( FieldRefs[I]).FieldNo;

      if KValues[I].VType = vtNull then
      begin
        Result := ResultSet.IsNull( ColumnIndex);
        if not Result then
          Break;
        Continue;
      end;

      CurrentType := ResultSet.GetMetadata.GetColumnType( ColumnIndex);
      case CurrentType of
        stBoolean:
          begin
            if FieldDirs[I] then
              Result := KValues[I].VBoolean <=
                ResultSet.GetBoolean(ColumnIndex)
            else
              Result := KValues[I].VBoolean >=
                ResultSet.GetBoolean(ColumnIndex)
          end;
        stByte,
        stShort,
        stInteger,
        stLong:
          begin
            if FieldDirs[I] then
              Result := KValues[I].VInteger <=
                ResultSet.GetLong(ColumnIndex)
            else
              Result := KValues[I].VInteger >=
                ResultSet.GetLong(ColumnIndex);
          end;
        stFloat,
        stDouble,
        stBigDecimal:
          begin
            if FieldDirs[I] then
              Result := ( KValues[I].VFloat <
                ResultSet.GetBigDecimal(ColumnIndex)) or
                ( Abs(KValues[I].VFloat - ResultSet.GetBigDecimal(ColumnIndex))
                  < FLOAT_COMPARE_PRECISION)
            else
              Result := ( KValues[I].VFloat >
                ResultSet.GetBigDecimal(ColumnIndex)) or
                ( Abs(KValues[I].VFloat - ResultSet.GetBigDecimal(ColumnIndex))
                  < FLOAT_COMPARE_PRECISION);
          end;
        stDate,
        stTime,
        stTimestamp:
          begin
            if FieldDirs[I] then
              Result := CompareDateTime( KValues[I].VDateTime,
                ResultSet.GetTimestamp(ColumnIndex)) <> GreaterThanValue
            else
              Result := ComparedateTime( KValues[I].VDateTime,
                ResultSet.GetTimestamp(ColumnIndex)) <> LessThanValue;
          end;
        stUnicodeString:
          begin
            if FieldDirs[I] then
              Result := WideCompareText( KValues[I].VUnicodeString,
                ResultSet.GetUnicodeString(ColumnIndex)) <= 0
            else
              Result := WideCompareText( KValues[I].VUnicodeString,
                ResultSet.GetUnicodeString(ColumnIndex)) >= 0;
          end;
        else
          if FieldDirs[I] then
            Result := CompareText( KValues[I].VString,
              ResultSet.GetString(ColumnIndex)) <= 0
          else
            Result := WideCompareText( KValues[I].VString,
              ResultSet.GetString(ColumnIndex)) >= 0;
      end;

      Result := Result or ResultSet.WasNull;
      if not Result then
        Break;
    end;
  end;

begin
  DefineSortedFields( Self, IndexFieldNames, FieldRefs, FieldDirs, OnlyDataFields);
  SetLength( FieldRefs, Length( KeyValues));
  SetLength( FieldDirs, Length( KeyValues));
  SetLength( KValues, Length( KeyValues));
  for I := 0 to High( KeyValues) do
    KValues[ I] := DecodeVariant( ConvertToVariant( KeyValues[ I]));
  PrepareValuesForComparison( FieldRefs, KValues,
    ResultSet, False, True);

  { Processes only data fields. }
  I := 0;
  RowCount := CurrentRows.Count;
  while True do
  begin
    while ( I >= RowCount) and FetchOneRow do
      RowCount := CurrentRows.Count;
    if I >= RowCount then
      Break;

    RowNo := Integer( CurrentRows[ I]);
    ResultSet.MoveAbsolute( RowNo);

    if not CompareFieldsFromResultSet then
    begin
      Break;
      DoBeforeScroll;
      MoveRecNo( I + 1);
      DoAfterScroll;
    end;

    Inc(I);
  end;
end;

procedure TZTable.GotoCurrent(ZTable: TZTable);
var
  V: Variant;
  i: integer;
  FieldRefs: TObjectDynArray;
  FieldDirs: TBooleanDynArray;
  OnlyDataFields: Boolean;
  KeyFields: String;
begin
  DefineSortedFields( Self, IndexFieldNames, FieldRefs, FieldDirs, OnlyDataFields);
  for i := 0 to High( FieldRefs) do
    KeyFields := KeyFields + TField( FieldRefs[ i]).FieldName + ';';
  KeyFields := copy( KeyFields, 1, Length( KeyFields) - 1);
  if Length( FieldRefs) = 1 then
    Locate( KeyFields, ZTable.FieldByName( TField( FieldRefs[ 0]).FieldName).Value, [ loCaseInsensitive])
  else
  begin
    V := VarArrayCreate( [ 0, Length( FieldRefs) - 1], varVariant);
    for i := 0 to High( FieldRefs) do
      V[ i] := ZTable.FieldByName( TField( FieldRefs[ 0]).FieldName).Value;
    Locate( KeyFields, V, [ loCaseInsensitive]);
  end;
end;

Best Regards,
Markus Dütting
cosymed AG

[tatamata]

Thanks.
Anyway, Locate is just good enough for most purposes, I think.

Regards,

Zlatko

[MarkusD]

If you look at the code you will see that GoToCurrent and FindKey both use Locate. Only FindNearest wouldn't work with it, so i copied the code from Locate and ( hopefully) made the appropriate changes.

Best Regards,

Markus Dütting
cosymed AG