TatMetaSQL Component

TatMetaSQL is a component that encapsulates an SQL statement, keeping in its properties all info needed to build the SQL statement, like fields to be selected, tables involved, order fields, and so on. The primary use for TatMetaSQL component is to allow building of SQL statements in an easy way. Here are some advantages of using it:

There is no need to know SQL syntax – all that you need to do is to manipulate properties and objects inside TatMetaSQL;
There is no need to care about target database – TatMetaSQL will build SQL statement for you, with correct syntax for Oracle, Microsoft SQL Server, Interbase, and more;
It is very easy to have DB applications that works for multiple databases – thanks to feature described above;
TatMetaSQL contains a visual design-time editor for SQL, allowing visual building of SQL statement;
It is easy to allow end-user to change SQL – you can also use visual components that does that for you with few lines of code.

For complete information about TatMetaSQL methods and properties, see the Query Studio component reference at online help from IDE.

Working with TatMetaSQL – step by step

Basic example

This example ilustrates how to use TatMetaSQL. Let's say that you just want to query the customers database. Here is how you do in normal way:

MyQuery1.SQL.Text := 'Select C.CustNo from Customer C';

Here is how you do with TatMetaSQL:

with MetaSQL.SQLTables.Add do
begin
  TableName := 'Customer';
  TableAlias := 'C';
end;
with MetaSQL.SQLFields.Add do
begin
  FieldName := 'CustNo';
  TableAlias := 'C';
end;
MyQuery1.SQL.Text := MetaSQL.SQL;

Looks like it is more complicated, but suppose that now you want to change SQL in order to select both CustNo and Company fields. In the classic way, you would just change SQL text string. With MetaSQL, you do this:

with MetaSQL.SQLFields.Add do
begin
  FieldName:='Company';
  TableAlias:='C';
end;

Now imagine that you want to allow your end-user to do it. And not only add fields to select, but changing order, filtering, and so on. It will be very complicated to change SQL string, specially if the SQL is complex. TatMetaSQL makes it simpler.

Defining tables to query

From now, we are going to build an SQL step-by-step. First, set SQL syntax:

MetaSQL.SQLSyntax := ssBDELocal;

Now, first step to build an SQL statement is to define tables to query. This is done using TatMetaSQL.SQLTables property:

with MetaSQL.SQLTables.Add do
begin
  TableName:='Customer';
  TableAlias:='C';
end;

Which generated the SQL:

SELECT
  *
FROM
  Customer C

All references to a table in TatMetaSQL is done using the table alias (specified in TableAlias property). So, you must not add two tables with same TableAlias. Now if we add one more table:

with MetaSQL.SQLTables.Add do
begin
  TableName:='Orders';
  TableAlias:='O';
end;

TatMetaSQL will be aware that you will query two tables. But you must then indicate how these two tables are linked (see Defining table links).

Defining fields to be selected

The main property in TatMetaSQL component is the SQLFields property. Here you define not only fields to be selected, but also fields that can you can order by, group by or that you can use in conditions, inside where clauses or join clauses.

The code below add four fields in TatMetaSQL (CustNo and Company, from Customer table, and CustNo and OrderNo, from Orders table):

with MetaSQL.SQLFields.Add do
begin
  DataType:=ftFloat;
  FieldName:='CustNo';
  TableAlias:='C';
  FieldAlias:='Customer_CustNo';
end;
with MetaSQL.SQLFields.Add do
begin
  DataType:=ftString;
  FieldName:='Company';
  TableAlias:='C';
  FieldAlias:='Company';
end;
with MetaSQL.SQLFields.Add do
begin
  DataType:=ftFloat;
  FieldName:='CustNo';
  TableAlias:='O';
  FieldAlias:='Orders_CustNo';
end;
with MetaSQL.SQLFields.Add do
begin
  DataType:=ftFloat;
  FieldName:='OrderNo';
  TableAlias:='O';
  FieldAlias:='OrderNo';
end;

Here, TableAlias and FieldName property indicates the origin of field (from which table it comes, and the name of the field in database). And, FieldAlias property is the "ID" of the field in TatMetaSQL. Just like TableAlias for tables, FieldAlias is used by other parts of TatMetaSQL to make a reference to a specified field in SQLField property. Thus, you must not add to fields with same FieldAlias.

Defining table links

To define links (joins) between tables, you must use TatMetaSQL.TableJoins property:

with MetaSQL.TableJoins.Add do
begin
  PrimaryTableAlias:='C';
  ForeignTableAlias:='O';
  LinkType:=altInnerJoin;
  with JoinConditions.Add do
  begin
    ConditionType:=ctFieldCompare;
    FieldAlias:='Customer_CustNo';
    Operator:='=';
    Value:='Orders_CustNo';
  end;
end;

After this join, the resulting SQL is the following:

SELECT
  C.CustNo Customer_CustNo,
  C.Company Company,
  O.CustNo Orders_CustNo,
  O.OrderNo OrderNo
FROM
  Customer C,
  Orders O
WHERE
  ((
    C.CustNo = O.CustNo
  )
  )

To build the join, PrimaryTableAlias and ForeignTableAlias are used to indicate the table alias of tables that are to be linked. LinkType property is used to indicate if it will be inner join or outer join (left or right). After that, a condition is added to join, comparing the CustNo field of one table to another. Conditions will be described further.

Now let's show again the use of TatMetaSQL to easily change SQL. Suppose that we want to change the join for inner to outer (left join), in order to return all customers, even those that don't have a related record in Orders table:

MetaSQL.TableJoins.FindLink('C','O').LinkType := altLeftJoin;

This simple change will result in a different SQL:

SELECT
  C.CustNo Customer_CustNo,
  C.Company Company,
  O.CustNo Orders_CustNo,
  O.OrderNo OrderNo
FROM
  (Customer C LEFT JOIN Orders O
  ON (((
    C.CustNo = O.CustNo
  )
  )))

Defining order fields

In TatMetaSQL, you can define fields to order returned records. Use TatMetaSQL.OrderFields property to do that:

with MetaSQL.OrderFields.Add do
begin
  FieldAlias:='Customer_CustNo';
  SortType:=ortDescending;
end;

And result SQL is:

SELECT
  C.CustNo Customer_CustNo,
  C.Company Company,
  O.CustNo Orders_CustNo,
  O.OrderNo OrderNo
FROM
  (Customer C LEFT JOIN Orders O
  ON (((
    C.CustNo = O.CustNo
  )
  )))
ORDER BY
  C.CustNo DESC

Note that, one more time, field alias is used to tell meta sql which field the SQL should be ordered by. SortType property is used to indicate a descending order.

Defining conditions (filtering)

To finish our example SQL, we will use conditions to filter records. The code below will return only customers which number is less than 1250:

with MetaSQL.Conditions.Add do
begin
  ConditionType:=ctValueCompare;
  FieldAlias:='Customer_CustNo';
  Operator:='<';
  Value:=1250;
end;

Result SQL statement is:

SELECT
  C.CustNo Customer_CustNo,
  C.Company Company,
  O.CustNo Orders_CustNo,
  O.OrderNo OrderNo
FROM
  (Customer C LEFT JOIN Orders O
  ON (((
    C.CustNo = O.CustNo
  )
  )))
WHERE
  (
  C.CustNo < 1250
  )
ORDER BY
  C.CustNo DESC

A key property in a condition is ConditionType property. The value of this property will determine how condition expression will be built, based on properties FieldAlias, Operator, Value and Expression. Check the ConditionType property of Reference section of this document to know about the possible values of ConditionType.

Changing SQL syntax

After all tables and fields are defined, joins are built, order fields and conditions are set, what if we want to run this SQL on Oracle database? Just one line of code:

MetaSQL.SQLSyntax := ssOracle;

Now, the result SQL is:

SELECT
  C.CustNo Customer_CustNo,
  C.Company Company,
  O.CustNo Orders_CustNo,
  O.OrderNo OrderNo
FROM
  Customer C,
  Orders O
WHERE
  ((
    C.CustNo = O.CustNo(*)
  )
  )
AND
  (
  C.CustNo < 1250
  )
ORDER BY
  C.CustNo DESC

The MetaSQL visual editor

Previous chapter described main properties of SQL, and how to change it at runtime. But the most common way to build an SQL statement in TatMetaSQL is at design-time. All key properties like SQLFields, OrderFields, SQLTables, etc., can be manipulated using standard design-time Delphi object inspector. The figure below shows an example of editing/adding fields to be selected (SQLFields property).

design sql fields Design-time edit with standard Delphi object inspector

Another way to build SQL statement at design-time is using the TatMetaSQL editor. With visual editor, you can add/edit items like fields, tables and joins in an easy way. The following steps show how to use visual editor to build the same SQL built in previous chapter by code.

Visual editor overview

Visual editor has several tabs: Tables, Fields, Joins, etc., one for each important collection of items you can manipulate in MetaSQL. For each tab that you can add/edit/remove item, visual editor has a common part at the top: buttons New, Edit, Save and Cancel.

New button: add a new item in list.
Edit button: edit properties of selected item (you can also press Enter key when an item is selected).
Save button: save changed properties in item (you can also press Enter key when editing item properties).
Cancel button: cancel changes in item properties (you can also press Esc key when editing item properties).

To delete items, use Del key when item is selected.

Choosing a database to work on

At the top of visual editor there is a combo box where you can choose a database alias. You don't need to choose a DB alias to use visual editor, but if you do it will help you. There are several places where you will need to choose a table name, a field name, a field type and so on, and if you have defined a DB alias, visual editor will show you a list of options (table names, field names, etc.) to choose from.

Checking option Save last used database will keep the selected DB alias next time you open visual editor.

Adding tables

Just like when building SQL by code, adding tables is the first step when using visual editor. Just use buttons to add tables, and set properties like TableName and TableAlias. The figure below shows the tables defined in visual editor.

metasql tables Adding tables in visual editor

Adding fields

Next tab in visual editor is Fields, where you define fields to be selected and fields that will be used in conditions (filtering), ordering and grouping.

metasql fields Including fields in visual editor

Note that here you can check/uncheck fields. This is because fields have an Active property. This checking/unchecking task will set Active property to true or false. To more info about Active property and the other field properties you can set here in visual editor, see the TatMetaSQL reference later in this document.

The Fields tab has also two extra buttons: Append from dataset and Append from table.

Append from dataset button: Clicking this button will bring a list of TDataset descendant components (TTable, TQuery, etc.) that exists in the same form that own TatMetaSQL component. After choosing desired dataset component, the visual editor will create one new field in metasql for each persistent field in dataset, copying properties like FieldName, DisplayLabel and DataType.
Append from table button: Only works if there is a database alias selected. Brings a list of existing tables in database. After choosing table, visual editor will create one new field in metasql for each existing field in database.

Defining table joins (links)

The Joins tab allow you to define table joins, just like by code. Here in the example of the figure below, only one join is added, between tables Customer and Orders.

metasql joins Editing joins and join conditions in visual editor

In main windows of visual editor, you only add the join and set tables to join and link type. You must then add join conditions, use Edit subconditions button or double clicking the join. This will bring another window to edit join coniditions. The figure above shows both windows.

Each join must have a least one condition of type ctFieldCompare. In that condition you must define a comparison between fields of both tables being linked. Then you can add more conditions for the join, if you need to.

Defining conditions

In visual editor you can also define conditions, in the tab Conditions. To build the same SQL of previous chapter, we must define here a condition to filter SQL when customer number is less than 1250. The figure below shows the condition created.

Here the visual editor shows a different layout: there is a tree view at the left of condition list. This is because conditions are recursive: each condition has a property called SubConditions, which holds a collection of conditions. So, when a condition has subconditions, and its ConditionType property is set to ctSubConditions, than the condition is represented by its SubConditions, inside parentesis. This is useful to build nested conditions and group then with OR or AND logical operators.

In visual editor, the tree view at the left is used to see conditions structure. The root node Main conditions is not a condition itself, but represents the conditions that are directly create inside the TatMetaSQL.Conditions property. The "(AND)" after the name indicate how conditions inside that node will be grouped (in this case, with "and" operator).

metasql conditions Adding conditions in visual editor

When you select a node in treeview, the condition list at the right will display the conditions of that node. In the example of figure above, the Main conditions node is selected, so all its conditions is displayed at the right (in this case, only condition Condition0). To add a new condition, you must select node first, and then click New button. This will create a new subcondition of selected condition in tree view.

Conditions also have Active property, and because of that there is a check box at the left of each condition. You can the activate or deactivate condition by checking/unchecking it. Non active conditions will not be included in SQL.

Defining order fields

Defining order fields in visual editor is simple: just add items and define Field alias and Sort type. The figure below shows the example, using the same order field and sort type of previou chapter (customer number, in descending order).

metasql order Defining order fields

Order fields also have Active property. In visual editor, you use check box at the left of each order field to activate/deactivate orders. Only active order fields will be included in SQL.

Viewing SQL statement and data result

In visual editor, tab SQL displays the result SQL statement, as showed in the figure below. You cannot change SQL statement here, it is only for you to preview it and check if you have correctly built the Meta SQL.

metasql sql SQL statement preview

In addition to SQL statement preview, you can test the SQL in database, if you have defined a database alias. Tab Data result will execute the query in database and display a result dataset in a grid form. The figure below shows the data result for our SQL example.

metasql data Result of execution of SQL statement in database

TatMetaSQL vs. SQL statement – quick comparison

For a quick reference, the tables below show the relation between a property in a class and its influence on SQL statement. The part of SQL statement that is manipulated by property is displayed in bold.

TatMetaSQL class

Property	SQL Statement
SQLTables	Select * from Customer C, Order O, Parts P where C.CustNo=2
SQLFields	Select C.CustNo CustomerNumber, C.Company Company, O.OrderNo OrderNumber from Customer C, Orders O where C.CustNo=O.CustNo
TableJoins	Select * from Customer C Inner Join Orders O ON (C.CustNo=O.CustNo AND (C.CustNo=2 OR C.CustNo=3))
Conditions	Select * from Customer C where *C.CustNo=10 or (C.Company Like 'A' AND C.City IS NULL)**
GroupFields	Select O.CustNo, SUM(O.Price) from Orders O Group By O.CustNo
OrderFields	Select * from Customer C Order By C.Country, C.City
CustomFilter	Select * from Customer C where C.CustNo=2 AND *(C.City IN (Select P.City From City P where P.City like 'A'))**
ConditionsLogicalOper	Select * from Customer C where C.CustNo=10 OR (C.Company Like 'A*' AND C.City IS NULL) OR C.CustNo=3

TatSQLField class

Property	SQL Statement
FieldAlias	Select C.CustNo CustomerNumber from Customer C
FieldName	Select C.CustNo CustomerNumber from Customer C
TableAlias	Select C.CustNo CustomerNumber from Customer C
GroupFunction	Select Count(C.CustNo) from Customer C
FieldExpression	Select *O.Price O.Quantity** from Orders O

TatSQLOrderField class

Property	SQL Statement
FieldAlias	Select * from Orders O Order By O.Date Desc
SortType	Select * from Orders O Order By O.Date Desc

TatSQLGroupField class

Property	SQL Statement
FieldAlias	Select O.CustNo, SUM(O.Price) from Orders O Group By O.CustNo

TatSQLTable class

Property	SQL Statement
TableAlias	Select * from Customer C
TableName	Select * from Customer C

TatSQLCondition class

Property	SQL Statement
FieldAlias	Select * from Customer C where C.Company Like 'A*'
Operator	Select * from Customer C where C.Company Like 'A*'
Value	Select * from Customer C where C.Company Like *'A'**
Expression	Select * from Orders O where *O.Price O.Quantity > 2000**
SubConditionsLogicalOper	Select * from Customer C where C.CustNo=10 OR (C.Company Like 'A' AND* C.City IS NULL) OR C.CustNo=3
SubConditions	Select * from Customer C where C.CustNo=10 OR *(C.Company Like 'A' AND C.City IS NULL)** OR C.CustNo=3

TatSQLJoin class

Property	SQL Statement
JoinConditionsLogicalOper	Select * from Customer C Inner Join Orders O ON (C.CustNo=O.CustNo AND (C.CustNo=2 OR C.CustNo=3))
JoinConditions	Select * from Customer C Inner Join Orders O ON (C.CustNo=O.CustNo AND (C.CustNo=2 OR C.CustNo=3))
PrimaryTableAlias	Select * from Customer C Inner Join Orders O ON (C.CustNo=O.CustNo AND (C.CustNo=2 OR C.CustNo=3))
ForeignTableAlias	Select * from Customer C Inner Join Orders O ON (C.CustNo=O.CustNo AND (C.CustNo=2 OR C.CustNo=3))
LinkType	Select * from Customer C Inner Join Orders O ON (C.CustNo=O.CustNo AND (C.CustNo=2 OR C.CustNo=3))