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
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.

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