Active Query Builder support area

Is it possible to keep the user's SQL formatting in the text editor while the query hasn't been modified in the visual query builder?

Last modified:


It's possible, but a little bit tricky.

Below is a piece of the code of a form that contains a Query Builder and a Text Editor controls on it. The Query Builder gets updated only if the syntax of SQL query typed in the Text Editor is correct, and it has been really changed (changing the SQL text formatting hasn't taken into account).

If a parsing error occurred, the user could be guided to the erroneous token in the query text to correct the error (actGoToErrorPosExecute), or get back to the SQL that was parsed fine (actReturnGoodParseExecute). If the user is not satisfied with getting back to the last parsed query, he can get back to the erroneous query (actReturnToPreviousStateExecute).

 

unit uQueryBuilder;

interface

  TfQueryBuilder = class(TForm)
    // ...

  private
    fSQLChanged: boolean; 
    lastParsedSQL: string;
    previousParsedSQL: string; 
    previousStateSQL: string;
    fLastErrorPos: TacPos;

    acQueryBuilder: TacQueryBuilder;

    function ParseQuery(const aSQL: string): Boolean;
    procedure ShowParsingError(ASQLError: EacSQLError);

    // ...
  end;

implementation

 // ...

procedure TfQueryBuilder.seSQLChange(Sender: TObject);
begin
    fChangedSQL := true;
end;

procedure TfQueryBuilder.seSQLExit(Sender: TObject);

  function SqlTextHasBeenChanged: Boolean;
  begin
    Result := acQueryBuilder.SQL <> lastParsedSQL;
  end;

  function QueryBuilderHasBeenChanged: Boolean;
  begin
    Result := acQueryBuilder.SQL <> previousParsedSQL;
  end;

begin
  Assert(Sender is TacSqlTextEditor);

  if not fSQLChanged or (csDestroying in ComponentState) then
    Exit;

  // Save the Text Editor state here

  if not ParseQuery(TacSqlTextEditor(Sender).Text) then
    Abort;

  if SqlHasBeenChanged then
  begin
    acQueryBuilder.SQL := lastParsedSQL;

    if not QueryBuilderHasBeenChanged then
    begin
      fTextEditor.Text := previousStateSQL;

      // Restore the Text Editor state here
    end;
  end;
end;


function TfQueryBuilder.ParseQuery(const aSQL: string): Boolean;

  function ParseAndGenerateSQL(const ASQL: string): string;
  var
    q: TSQLSubQueryExpression;
    lCTE, lFromObj: TObjectList;
  begin
      q := acQueryBuilder.SQLContext.ParseSelect(ASQL)
      try

        // that's magic ;)
        lCTE := TObjectList.Create(false);
        try
          lFromObj := TObjectList.Create(false);
          try
            q.PrepareAndFixupRecursive(lCTE, lFromObj);
          finally
            lFromObj.Free;
          end;
        finally
          lCTE.Free;
        end;

        Result := q.SimpleSQL(acSQLBuilderPlainText.SQLBuilder);

      finally
        q.Free;
      end;
  end;

begin
  previousStateSQL := aSQL;
  
  if Trim(aSQL) = '' then
  begin
    Result := True;
    Exit;
  end;

  previousParsedSQL := acQueryBuilder.SQL;
  try
    lastParsedSQL := ParseAndGenerateSQL(aSQL);
    result := True;

  except
    on E: EacSQLError do
    begin
      result := false;
      fLastErrorPos := ASQLError.ErrorPos;
      ShowParsingErrorHint(E);
    end
    else
      Raise;
  end;
end;

procedure TfQueryBuilder.ShowParsingError(ASQLError: EacSQLError);
begin
  ShowMessage(ASQLError.Message); // display the error the way you like
end;

procedure TfQueryBuilder.actReturnToPreviousStateExecute(Sender: TObject);
begin
  seSQL.Text := previousStateSQL;
end;

procedure TfQueryBuilder.actGoToErrorPosExecute(Sender: TObject);
begin
  seSQL.ScrollToPosition(fLastErrorPos.line - 1, fLastErrorPos.col);
  seSQL.Caret.TextPosition := TextLocation(fLastErrorPos.line - 1, fLastErrorPos.col);
  seSQL.SetFocus;
end;

procedure TfQueryBuilder.actReturnGoodParseExecute(Sender: TObject);
begin
  seSQL.Text := lastGoodSQL;
end;

 


Is this article helpful for you?