TBISQL is a record type that provides static methods to convert between SQL syntax and TeeBI query objects (TDataSelect, TSummary, TBIQuery).
Overview
TBISQL acts as a bidirectional translator:
- SQL to Objects: Parse SQL strings into query provider objects
- Objects to SQL: Generate SQL syntax from query objects
This enables both programmatic query construction and SQL-based data manipulation.
Class Definition
TBISSQL = record
public
// Convert provider to SQL
class function From(const AProvider: TDataProvider): String; overload; static;
// Convert TDataSelect to SQL
class function From(const ASelect: TDataSelect): String; overload; static;
// Convert TSummary to SQL
class function From(const ASummary: TSummary): String; overload; static;
// Convert TDataItem to SQL
class function From(const AData: TDataItem): String; overload; static;
// Parse and execute SQL
class function From(const AData: TDataItem;
const SQL: String;
const GetData: TGetDataProc = nil;
const ErrorProc: TBIErrorProc = nil): TDataItem; overload; static;
// Parse SQL to provider
class function ProviderFrom(const AData: TDataItem;
const SQL: String;
const GetData: TGetDataProc = nil;
const ErrorProc: TBIErrorProc = nil): TDataProvider; static;
end;
Methods
From (Provider to SQL)
Converts a data provider object to its SQL representation.
The provider object to convert (TDataSelect or TSummary)
var
Summary: TSummary;
SQL: String;
begin
Summary := TSummary.Create(nil);
Summary.AddMeasure(Data['Sales'], TAggregate.Sum);
Summary.AddGroupBy(Data['Country']);
SQL := TBISQL.From(Summary);
// Result: "select Country, Sum(Sales) from Data group by Country"
end;
From (SQL to Data)
Parses SQL syntax, executes the query, and returns the result.
Source data item to query
Optional callback to resolve data names
Optional error handling callback
var
Result: TDataItem;
begin
Result := TBISQL.From(MyData, 'select Country, Sum(Sales) from Data group by Country');
// Result contains aggregated data
end;
ProviderFrom
Parses SQL syntax and returns the provider object without executing.
Source data item for context
SQL query string to parse
Optional callback to resolve data names
Optional error handling callback
var
Provider: TDataProvider;
begin
Provider := TBISQL.ProviderFrom(MyData, 'select * from Customers where Country="USA"');
// Provider is a TDataSelect object
end;
Supported SQL Syntax
TBISQL supports a subset of standard SQL:
SELECT Queries
select [distinct] <columns>
from <table>
[where <condition>]
[order by <columns> [asc|desc]]
[limit <number>]
[offset <number>]
GROUP BY Queries (Summaries)
select <dimensions>, <aggregates>
from <table>
[where <condition>]
group by <dimensions>
Aggregate Functions
Count(field)Sum(field)Average(field) or Avg(field)Minimum(field) or Min(field)Maximum(field) or Max(field)First(field)Last(field)
Date/Time Parts
select Year(OrderDate), Sum(Amount)
from Orders
group by Year(OrderDate)
Examples
Simple SELECT
var
Result: TDataItem;
begin
Result := TBISQL.From(Customers, 'select Name, City from Customers');
end;
SELECT with WHERE
Result := TBISQL.From(Orders,
'select OrderID, Amount from Orders where Amount > 1000');
SELECT with DISTINCT
Result := TBISQL.From(Products,
'select distinct Category from Products');
Aggregation
Result := TBISQL.From(Sales,
'select Country, Sum(Revenue) from Sales group by Country');
Multiple Grouping
Result := TBISQL.From(Sales,
'select Year(Date), Country, Sum(Revenue) ' +
'from Sales ' +
'group by Year(Date), Country');
Sorting
Result := TBISQL.From(Products,
'select Name, Price from Products order by Price desc');
Result := TBISQL.From(Customers,
'select * from Customers limit 10 offset 20');
Converting Objects to SQL
var
Select: TDataSelect;
SQL: String;
begin
Select := TDataSelect.Create(nil);
Select.Add(Data['Name']);
Select.Add(Data['Price']);
Select.Filter := TDataFilter.FromString(Data, 'Price > 100');
SQL := TBISQL.From(Select);
// Result: "select Name, Price from Data where Price > 100"
end;
Error Handling
Provide a custom error handler to gracefully handle parse errors:
function MyErrorHandler(const Sender: TObject; const Error: String): Boolean;
begin
ShowMessage('SQL Error: ' + Error);
Result := True; // Return True to suppress exception
end;
var
Result: TDataItem;
begin
Result := TBISQL.From(MyData, 'select invalid syntax', nil, MyErrorHandler);
end;
See Also
