The TacQueryBuilder component can work with a single SELECT statement only. To work with SQL scripts containing multiple statements (not only SELECT ones), use the TacSQLScript component.

TacSQLScript allows you to:

• Split the text into separate SQL statements and present it as a collection of statements (add, insert, remove, etc.),
• Edit each statement separately,
• Detect the type of SQL statement and find out if it's possible to edit it visually using the TacQueryBuilder component,
• Compile the statements back into the script, saving the formatting and intermediate comments between statements.

How to work with the TacSQLScript?

• To initialize an instance of TacSQLScript, assign the right syntax provider to the SyntaxProvider property.
• Assign the script to the Text property.
• Loop through the collection using the Statements[] and Count properties.
• Each item of this collection has the TacSQLStatement type which has the following properties to work with statements:
  • StatementType: get the statement type string ("SELECT", "CREATE TABLE", etc.)
  • Text: gets and sets the text of the statement.
  • IsSelect: returns True if the statement can be edited visually using the TacQueryBuilder component.
  • Start, TextLength: indicate the position of the statement in the script.
• Save and load the script using the Text property, LoadFromFile, LoadFromStream, SaveToFile, and SaveToStream methods.

Code sample:

procedure TfMain.ExecuteScript(const AFileName: string);
var
  script: TacSqlScript;
  i: Integer;
  statement: TacSqlStatement;
  dataset: TDataset;
begin
  script := TacSqlScript.Create;
  try
    script.LoadFromFile(AFileName);

    // for each statement in the script
    for i := 0 to script.Count - 1 do
    begin
      statement := script[i];

      if statement.IsSelect then
      begin
        // it is a SELECT statement - prepare and open the dataset
        dataset :=  queryBuilder.MetadataProvider.CreateSQLDataset(false, false);
        queryBuilder.MetadataProvider.SetSQLDatasetCommand(dataset, statement.Text);
        dataset.Open;

        // ... process dataset, for example attach to grid ...
      end
      else
        // if it is not a SELECT statement - execute it as a command
        queryBuilder.MetadataProvider.ExecSQL(statement.Text)
    end;
  finally
    script.Free;
  end;
end;

How to skip SET, DECLARE and other statements preceding the SELECT statement in the script?

A frequent case: When you need to edit the SELECT statement prenex SET, DECLARE or some other expressions using the TacQueryBuilder component, attach the TacSQLScriptBuilderPlainText to it instead of TacSQLBuilderPlainText component.

Just assign the script to the TacSQLScriptBuilderPlainText.SQL property so that the fist SELECT statement will be loaded into the visual query builder. The TacQueryBuilder and other TacSQLBuilderPlainText components will work with a single SELECT statement as usual. You'll get the script with the modified SELECT statement back from the TacSQLScriptBuilderPlanText.SQL property.