Active Query Builder support area

Is it possible to format date constants in criteria fields automatically?

Avatar
  • updated
  • Completed

One of the common issues I and my users are having is our brains and data entry on the dates fields. We’re used to typing in 2/27/2012 and the criteria field would know it is a date field and reformat it to ‘02/27/2012’. Is there a way to add this to the product or a work around so the users don’t have to type the apostrophes?

Avatar
DAVID GRIFFIN

I had this issue and wrote a routine to quote fields.

 

In the GridCellChanging method, I put the following code;

 

<code>

procedure TfmAQBuilder.QueryBuilderGridCellChanging(
  Sender: TacQueryBuilder; AUnionSubQuery: TacUnionSubQuery;
  ASelectList: TacSelectList; ASelectItem: TacSelectItem;
  AGrid: TacQueryBuilderSelectListControl; ACol, ARow: Integer;
  AOldValue: WideString; var ANewValue: WideString; var AAllow: Boolean);

const QuotableFieldTypes = [ftString, ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftFixedChar,
                            ftWideString, ftOraClob, ftDate, ftDateTime, ftTime, ftUnknown];
      // Numeric Aggregates are set for SQL Server
      NumericAggregates : Array [0..3] of string = ('Count', 'Checksum_Agg', 'StDev', 'Var');

var
  FieldName : string;
  FieldNo   : Integer;
  I         : Integer;
begin
  if (chkParseCriteria.Checked) and
     ((ACol >= AGrid.FirstConditionColumnNum) and (ACol <= AGrid.LastConditionColumnNum)) and
     (AOldValue <> ANewValue)  and (ANewValue <> '') then
    begin
      // use case insensitive match - truncate aggregate to allow "Count", "Count_Big", etc
      if PosInStringArray(UpperCase(ASelectItem.AggregateString), NumericAggregates, [pisaTruncate]) > -1 then
        begin
          // Aggregate funtion returning a number not the original fieldtype
          ANewValue := ParseAQBSQLText(ANewValue, False);
        end
      else
        begin
          // Get the name of the underlying field
          if Pos('.', ASelectItem.ExpressionString) > 0 then
            begin
              FieldName := CopyRightS(ASelectItem.ExpressionString, Pos('.', ASelectItem.ExpressionString) + 1);
            end
          else
            begin
              FieldName := ASelectItem.ExpressionString;
            end;
          // find the field number in the used objects
          FieldNo := -1;
          for I := 0 to Sender.QueryStatistics.UsedDatabaseObjectColumns.Count - 1 do
            begin
              if UpperCase(FieldName) = UpperCase(Sender.QueryStatistics.UsedDatabaseObjectColumns[I].MetadataField.NameStr) then
                begin
                  FieldNo := I;
                  Break;
                end;
            end;    // for
          if FieldNo < 0 then
            begin
              ANewValue := ParseAQBSQLText(ANewValue, False);
            end
          else
            begin
              ANewValue := ParseAQBSQLText(ANewValue, (Sender.QueryStatistics.UsedDatabaseObjectColumns[FieldNo].FieldType in QuotableFieldTypes));
            end;
        end;
    end;
end;
<end code>

 

Then the library routine I call is:

 

<code>

function ParseAQBSQLText(Const InputString: string; UseQuotes: boolean): string;
// Parses SQL Text into AQB compatible format, quoting if required
var
  BadPos        : Cardinal;
  CutPoint      : Integer;
  I             : Integer;
  OutputList    : TStringList;
  OutputString  : string;
  Reps          : Cardinal;
  WorkingList   : TStringList;
  WorkingString : string;
begin
  // Check for non-parsing conditions
  if (Trim(InputString) = '') or                         // Empty string
     (Pos('''', InputString) > 0) or                     // Already quoted
     (UpperCase(Trim(InputString)) = 'NULL') or          // Contains NULL
     (UpperCase(Trim(InputString)) = 'IS NULL') or       // Contains IS NULL
     (UpperCase(Trim(InputString)) = 'NOT NULL') or      // Contains NOT NULL
     (UpperCase(Trim(InputString)) = 'IS NOT NULL') then // Contains Is NOT NULL
    begin
      result := InputString; // Don't Change it
    end
  else if ((Pos(' AND ', UpperCase(InputString)) = 0) and
           (Pos(' OR ', UpperCase(InputString)) = 0)) and
           (Pos(Copy(Trim(InputString), 1, 1), '>=<(+-') = 0) then
    begin
      if UseQuotes then
        begin
          result := QuotedStr(InputString); // Just quote it
        end
      else
        begin
          result := InputString; // Don't Change it
        end;

    end
  else
    begin
      // Transform a free text string into an AQB compatible condition string
      OutputList := TStringList.Create;
      try
        WorkingList := TStringList.Create;
        try
          OutputString := '';
          WorkingString := InputString;
          // Strip and retain spaces
          while Copy(WorkingString, 1, 1) = ' ' do
            begin
              OutputString := OutputString + ' ';
              WorkingString := CopyRightS(WorkingString, 2);
            end;    // while
          OutputList.Add(OutputString);

          // Make sure brackets are isolated
          Reps := 1;
          WorkingString := ReplaceStringAllS(WorkingString, '(', ' ( ', Reps);
          Reps := 1;
          WorkingString := ReplaceStringAllS(WorkingString, ')', ' ) ', Reps);
          WorkingString := Trim(WorkingString);

          // Break up the rest
          ExtractTokensS(WorkingString, ' ', #0, False, WorkingList);
          for I := 0 to WorkingList.Count - 1 do    // Iterate
            begin
              OutputList.Add(WorkingList[I]);
            end;    // for
        finally
          // free resources
          WorkingList.Free;
        end;  // try/finally

        if OutputList.Count = 2 then
          begin
            // Special Case - single term
            if (Pos('%', OutputList[1]) > 0) or
               (Pos('_', OutputList[1]) > 0) then
              begin
                OutputList[1] := 'Like ' + QuotedStr(OutputList[1]);
              end
            else if UpperCase(OutputList[1]) = 'NULL' then
              begin
                OutputList[1] := 'Is ' + OutputList[1];
              end
            else
              begin
                if UseQuotes then
                  begin
                    OutputList[1] := '= ' + QuotedStr(OutputList[1]);
                  end;
              end;
          end
        else if OutputList.Count = 3 then
          begin
            // Comparison operator + term
            if UseQuotes then
              begin
                OutputList[2] := QuotedStr(OutputList[2]);
              end;
          end
        else
          begin
            // Now process each item in turn
            for I := 1 to OutputList.Count - 1 do    // Iterate
              begin
                if ContainsOnlyS(OutputList[I], '()', BadPos) then
                   begin
                     // Ignore brackets
                   end
                else if ContainsOnlyS(OutputList[I], '=<>', BadPos) or
                   (UpperCase(OutputList[I]) = 'NOT') then
                  begin
                    // Operator
                    if NOT((OutputList[I - 1] = '(') and
                           (OutputList[I + 2] = ')')) then
                      begin
                        // Add Brackets around expression
                        OutputList[I] := '( ' + OutputList[I];
                        OutputList[I + 1] := OutputList[I + 1] + ' )';
                      end;
                  end
                else if (UpperCase(OutputList[I]) = 'OR') then
                  begin
                    // Handle "Or"
                    OutputList[I] := UpperCase(OutputList[I]);
                  end
                else if (UpperCase(OutputList[I]) = 'AND') then
                  begin
                    // Handle "And"
                    OutputList[I] := UpperCase(OutputList[I]);
                  end
                else
                  begin
                    // Just a term
                    if CopyRightAbsS(OutputList[I], 1) = ')' then
                      begin
                        // Already bracketted; Quote the first part only
                        CutPoint := Pos(' ', OutputList[I]);
                        OutputList[I] := QuotedStr(CopyLeftS(OutputList[I], CutPoint - 1)) + CopyRightS(OutputList[I], CutPoint);
                      end
                    else
                      begin
                        // Handle existing external brackets
                        if ContainsOnlyS(OutputList[I - 1], '=<>', BadPos) then
                          begin
                            // Previous term was operator
                            if UseQuotes then
                              begin
                                OutputList[I] := QuotedStr(OutputList[I]);
                              end;
                          end
                        else
                          begin
                            // Not an operator - simple term
                            if NOT((OutputList[I - 1] = '(') and
                                   ((OutputList.Count > (I + 1)) and (OutputList[I + 1] = ')'))) then
                              begin
                                if (Pos('%', OutputList[I]) > 0) or
                                   (Pos('_', OutputList[I]) > 0) then
                                  begin
                                    OutputList[I] := '( Like ' + QuotedStr(OutputList[I]) + ' )';
                                  end
                                else if UpperCase(OutputList[I]) = 'NULL' then
                                  begin
                                    OutputList[I] := '( Is ' + OutputList[I] + ' )'
                                  end
                                else
                                  begin
                                    if UseQuotes then
                                      begin
                                        OutputList[I] := '( = ' + QuotedStr(OutputList[I]) + ' )';
                                      end
                                    else
                                      begin
                                        OutputList[I] := '( = ' + OutputList[I] + ' )'
                                      end;
                                  end;
                              end
                            else
                              begin
                                if (Pos('%', OutputList[I]) > 0) or
                                   (Pos('_', OutputList[I]) > 0) then
                                  begin
                                    OutputList[I] := 'Like ' + QuotedStr(OutputList[I]);
                                  end
                                else if UpperCase(OutputList[I]) = 'NULL' then
                                  begin
                                    OutputList[I] := 'Is ' + OutputList[I];
                                  end
                                else
                                  begin
                                    if UseQuotes then
                                      begin
                                        OutputList[I] := '= ' + QuotedStr(OutputList[I]);
                                      end
                                    else
                                      begin
                                        OutputList[I] := '= ' + OutputList[I];
                                      end;
                                  end;
                              end;
                          end;
                      end;
                  end;
              end;    // for
          end;

        OutputString := '';
        for I := 0 to OutputList.Count - 1 do    // Iterate
          begin
            if I > 1 then // Don't pad leading spaces
              begin
                OutputString := OutputString + ' ';
              end;
            OutputString := OutputString + OutputList[I];
          end;    // for
      finally
        // free resources
        OutputList.Free;
      end;  // try/finally

      result := OutputString;
    end; // Parse text
end;
<end code>

 

Hope that may be of some use.

 ... Joe

Member of the UK Developers Group

Avatar
Anonymous

It is not possible in the Query Columns Grid, but we are in the process of creating a tree-like criteria editor in which the user will input date and time values using an appropriate editing controls.