InstantCommand

Unit: InstantCommandCategory: Core

Overview

The InstantCommand unit implements IQL (InstantObjects Query Language), a SQL-like language for querying persistent objects. IQL provides database-independent object queries that are automatically translated to native SQL by the broker's translator.

Key Concepts:

• IQL - Object-oriented query language (similar to SQL but for objects)
• IQL Objects - Parse tree representing IQL statements
• Command - Parsed IQL statement ready for execution
• Translator - Converts IQL to native SQL
• Parameters - Named parameters in queries (:ParamName)
• Paths - Object navigation (Contact.Category.Name)

IQL Architecture:

IQL Text → Parser → IQL Objects → Translator → Native SQL → Database
"SELECT * FROM TContact WHERE Category.Name = 'Customer'"
         ↓
   IQLCommand (parse tree)
         ↓
   Translator (broker-specific)
         ↓
"SELECT T0.* FROM Contacts T0 LEFT JOIN Categories T1..."

IQL Language Reference

Basic Syntax

SELECT [DISTINCT] <specifier> FROM <class> [WHERE <condition>] [ORDER BY <order>]

Components

• Specifier: *, attribute name, or expression
• Class: Business class name (e.g., TContact, TCategory)
• Condition: WHERE clause with comparisons and logic
• Order: ORDER BY clause with ASC/DESC

Simple Query Examples

-- All contacts
SELECT * FROM TContact

-- Specific attributes
SELECT Name, Email FROM TContact

-- With condition
SELECT * FROM TContact WHERE Name = 'John Doe'

-- With parameter
SELECT * FROM TContact WHERE Name = :ContactName

-- With order
SELECT * FROM TContact ORDER BY Name ASC

-- Distinct values
SELECT DISTINCT Category FROM TContact

Path Navigation

IQL supports object navigation using dot notation:

-- Navigate through reference
SELECT * FROM TContact WHERE Category.Name = 'Customer'

-- Multiple levels
SELECT * FROM TPhone WHERE Contact.Category.Name = 'VIP'

-- In ORDER BY
SELECT * FROM TContact ORDER BY Category.Name, Name

Operators

Comparison Operators

Operator	Description	Example
=	Equal	Name = 'John'
<> or !=	Not equal	Status <> 'Deleted'
<	Less than	Age < 30
>	Greater than	Age > 18
<=	Less than or equal	Price <= 100
>=	Greater than or equal	Rating >= 4.5
LIKE	Pattern match	Email LIKE '%@example.com'
IS	NULL comparison	Email IS NULL
IS NOT	NOT NULL comparison	Email IS NOT NULL

Logical Operators

Operator	Description	Example
AND	Logical AND	Age > 18 AND Status = 'Active'
OR	Logical OR	City = 'NY' OR City = 'LA'
NOT	Logical NOT	NOT Deleted

Arithmetic Operators

Operator	Description	Example
+	Addition	Price + Tax
-	Subtraction	Quantity - Reserved
*	Multiplication	Price * Quantity
/	Division	Total / Count
MOD	Modulo	Value MOD 10

Functions

Aggregate Functions

-- COUNT
SELECT COUNT(*) FROM TContact

-- SUM
SELECT SUM(Amount) FROM TOrder

-- AVG
SELECT AVG(Rating) FROM TProduct

-- MIN/MAX
SELECT MIN(Price), MAX(Price) FROM TProduct

String Functions

-- UPPER/LOWER
SELECT * FROM TContact WHERE UPPER(Name) = 'JOHN DOE'

-- SUBSTRING
SELECT SUBSTRING(Name, 1, 10) FROM TContact

-- LENGTH
SELECT * FROM TContact WHERE LENGTH(Name) > 20

Date Functions

-- YEAR, MONTH, DAY
SELECT * FROM TOrder WHERE YEAR(OrderDate) = 2024

-- CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
SELECT * FROM TOrder WHERE OrderDate >= CURRENT_DATE

Subqueries

-- IN subquery
SELECT * FROM TContact WHERE Category IN (
  SELECT Id FROM TCategory WHERE Active = TRUE
)

-- EXISTS subquery
SELECT * FROM TCategory WHERE EXISTS (
  SELECT * FROM TContact WHERE Contact.Category = SELF
)

-- SELF keyword refers to the outer query's current object

Complex Query Examples

-- Multiple conditions with AND/OR
SELECT * FROM TContact
WHERE (City = 'New York' OR City = 'Boston')
  AND Status = 'Active'
  AND Category.Name = 'Customer'
ORDER BY Name

-- Pattern matching with LIKE
SELECT * FROM TContact
WHERE Email LIKE '%@company.com'
  AND Name NOT LIKE 'Test%'

-- Date range query
SELECT * FROM TOrder
WHERE OrderDate >= '2024-01-01'
  AND OrderDate < '2024-12-31'
  AND Status IN ('Shipped', 'Delivered')
ORDER BY OrderDate DESC

-- NULL handling
SELECT * FROM TContact
WHERE Email IS NOT NULL
  AND Category IS NOT NULL

-- Arithmetic expressions
SELECT * FROM TOrder
WHERE (Price * Quantity) > 1000
  AND (Price * Quantity * 0.9) < Budget

Class Hierarchy

TInstantIQLObject (abstract base)
├── TInstantIQLCommand (SELECT statement)
├── TInstantIQLOperator (operators)
│   ├── TInstantIQLRelOp (=, <, >, LIKE, etc.)
│   ├── TInstantIQLAddOp (+, -, OR)
│   ├── TInstantIQLMulOp (*, /, MOD, AND)
│   └── TInstantIQLSignOp (+, - unary)
├── TInstantIQLFactor (operands and conditions)
│   ├── TInstantIQLOperand
│   │   ├── TInstantIQLPath (attribute navigation)
│   │   ├── TInstantIQLConstant (literals)
│   │   ├── TInstantIQLParam (parameters)
│   │   └── TInstantIQLBaseFunction
│   │       ├── TInstantIQLFunction (COUNT, SUM, etc.)
│   │       └── TInstantIQLSubqueryFunction (EXISTS)
│   ├── TInstantIQLClauseFactor (parenthesized clause)
│   └── TInstantIQLNotFactor (NOT operator)
├── TInstantIQLCondition
│   ├── TInstantIQLTerm (multiplication/division)
│   ├── TInstantIQLExpression (addition/subtraction)
│   └── TInstantIQLClause (comparison)
├── TInstantIQLOrder (ORDER BY)
├── TInstantIQLOrderSpec (order field with direction)
├── TInstantIQLSpecifier (SELECT clause)
├── TInstantIQLClassRef (FROM class)
├── TInstantIQLSubquery (subquery)
└── TInstantIQLParameters (function parameters)

TInstantIQLReader (IQL parser)
TInstantIQLWriter (IQL generator)

TInstantIQLTranslator (IQL to SQL translator)
TInstantIQLCommandTranslator (command translator base)

TInstantIQLObject

Abstract base class for all IQL syntax tree nodes.

Inheritance: TPersistent → TInstantIQLObject

Key Properties

Property	Type	Description
Owner	TInstantIQLObject	Owner node in tree
Root	TInstantIQLObject	Root node of tree
ObjectCount	Integer	Number of child nodes
Objects[Index]	TInstantIQLObject	Child node by index
Text	string	IQL text representation

Key Methods

constructor Create(AOwner: TInstantIQLObject); virtual;
destructor Destroy;

// Parse from text
procedure Read(Reader: TInstantIQLReader);

// Generate text
procedure Write(Writer: TInstantIQLWriter);

// Clear node
procedure Clear;

// Check if at this node type
class function AtInstance(Reader: TInstantIQLReader): Boolean;

Example: Parsing IQL

var
  Command: TInstantIQLCommand;
  Reader: TInstantIQLReader;
  Stream: TStringStream;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    Stream := TStringStream.Create('SELECT * FROM TContact WHERE Name = ''John''');
    try
      Reader := TInstantIQLReader.Create(Stream);
      try
        Command.Read(Reader);
        ShowMessage('Parsed successfully');
        ShowMessage('Class: ' + Command.ObjectClassName);
      finally
        Reader.Free;
      end;
    finally
      Stream.Free;
    end;
  finally
    Command.Free;
  end;
end;

Example: Generating IQL

var
  Command: TInstantIQLCommand;
  Writer: TInstantIQLWriter;
  Stream: TStringStream;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    // Build command programmatically...
    Command.ClassRef.ObjectClassName := 'TContact';
    // ...

    Stream := TStringStream.Create('');
    try
      Writer := TInstantIQLWriter.Create(Stream);
      try
        Command.Write(Writer);
        ShowMessage(Stream.DataString);  // Generated IQL
      finally
        Writer.Free;
      end;
    finally
      Stream.Free;
    end;
  finally
    Command.Free;
  end;
end;

TInstantIQLCommand

Represents a complete IQL SELECT statement.

Inheritance: TInstantIQLObject → TInstantIQLCommand

Key Properties

Property	Type	Description
Specifier	TInstantIQLSpecifier	SELECT clause (*, attributes)
ClassRef	TInstantIQLClassRef	FROM class
Clause	TInstantIQLClause	WHERE condition
Order	TInstantIQLOrder	ORDER BY specification
Distinct	Boolean	DISTINCT keyword present
Any	Boolean	True if specifier is *
ObjectClassName	string	Class name from ClassRef
ResultClassName	string	Result class (for joins, etc.)

Example: Query Structure

var
  Command: TInstantIQLCommand;
  Clause: TInstantIQLClause;
  Path: TInstantIQLPath;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    Command.Text := 'SELECT * FROM TContact WHERE Category.Name = ''Customer''';

    // Access parsed structure
    ShowMessage('Class: ' + Command.ObjectClassName);  // TContact
    ShowMessage('Distinct: ' + BoolToStr(Command.Distinct));  // False
    ShowMessage('Any: ' + BoolToStr(Command.Any));  // True (*)

    // Navigate WHERE clause
    if Assigned(Command.Clause) then
    begin
      Clause := Command.Clause;
      if Assigned(Clause.Expression) and
         Assigned(Clause.Expression.Term) and
         Assigned(Clause.Expression.Term.Factor) and
         (Clause.Expression.Term.Factor is TInstantIQLPath) then
      begin
        Path := TInstantIQLPath(Clause.Expression.Term.Factor);
        ShowMessage('Path: ' + Path.Text);  // Category.Name
      end;
    end;
  finally
    Command.Free;
  end;
end;

TInstantIQLPath

Represents object attribute navigation (e.g., Category.Name, Contact.Category.Name).

Inheritance: TInstantIQLOperand → TInstantIQLPath

Key Properties

Property	Type	Description
AttributeCount	Integer	Number of path segments
Attributes[Index]	string	Attribute name by index
SubPath[Index]	string	Partial path up to index
HasAttributes	Boolean	True if path has segments

Example Usage

var
  Path: TInstantIQLPath;
begin
  Path := TInstantIQLPath.Create(nil);
  try
    Path.Text := 'Contact.Category.Name';

    ShowMessage(Format('Attributes: %d', [Path.AttributeCount]));  // 3

    for I := 0 to Path.AttributeCount - 1 do
      ShowMessage(Format('%d: %s → %s',
        [I, Path.Attributes[I], Path.SubPath[I]]));

    // Output:
    // 0: Contact → Contact
    // 1: Category → Contact.Category
    // 2: Name → Contact.Category.Name
  finally
    Path.Free;
  end;
end;

TInstantIQLConstant

Represents a literal value in IQL (string, number, date, etc.).

Inheritance: TInstantIQLOperand → TInstantIQLConstant

Key Properties

Property	Type	Description
Value	string	Constant value
IsSelf	Boolean	True if value is SELF keyword

Constant Formats

// String (single quotes)
'John Doe'
'O''Connor'  // Escaped quote

// Number
42
3.14
-100

// Date/Time (ISO format in quotes)
'2024-01-15'
'14:30:00'
'2024-01-15 14:30:00'

// Boolean
TRUE
FALSE

// NULL
NULL

// SELF (refers to current object in subquery)
SELF

Example

var
  Const: TInstantIQLConstant;
begin
  Const := TInstantIQLConstant.Create(nil);
  try
    Const.Text := '''John Doe''';
    ShowMessage('Value: ' + Const.Value);  // 'John Doe' (with quotes)

    Const.Text := '42';
    ShowMessage('Value: ' + Const.Value);  // 42

    Const.Text := 'SELF';
    ShowMessage('Is SELF: ' + BoolToStr(Const.IsSelf));  // True
  finally
    Const.Free;
  end;
end;

TInstantIQLParam

Represents a named parameter (:ParamName).

Inheritance: TInstantIQLOperand → TInstantIQLParam

Key Properties

Property	Type	Description
ParamName	string	Parameter name (without colon)

Example

var
  Param: TInstantIQLParam;
  Command: TInstantIQLCommand;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    Command.Text := 'SELECT * FROM TContact WHERE Name = :ContactName';

    // Find parameter in parse tree
    // (Navigate through Clause → Expression → Term → Factor)
    if Assigned(Command.Clause) and
       Assigned(Command.Clause.NextClause) and
       Assigned(Command.Clause.NextClause.Expression) and
       Assigned(Command.Clause.NextClause.Expression.Term) and
       (Command.Clause.NextClause.Expression.Term.Factor is TInstantIQLParam) then
    begin
      Param := TInstantIQLParam(Command.Clause.NextClause.Expression.Term.Factor);
      ShowMessage('Parameter: ' + Param.ParamName);  // ContactName
    end;
  finally
    Command.Free;
  end;
end;

TInstantIQLFunction

Represents a function call (COUNT, SUM, UPPER, etc.).

Inheritance: TInstantIQLBaseFunction → TInstantIQLFunction

Key Properties

Property	Type	Description
FunctionName	string	Function name (e.g., 'COUNT', 'SUM')
Parameters	TInstantIQLParameters	Function parameters (linked list)

Supported Functions

Aggregate Functions

• COUNT(*) or COUNT(attribute)
• SUM(attribute)
• AVG(attribute)
• MIN(attribute)
• MAX(attribute)

String Functions

• UPPER(string)
• LOWER(string)
• SUBSTRING(string, start, length)
• LENGTH(string)
• TRIM(string)

Date Functions

• YEAR(date)
• MONTH(date)
• DAY(date)
• HOUR(time)
• MINUTE(time)
• SECOND(time)
• CURRENT_DATE
• CURRENT_TIME
• CURRENT_TIMESTAMP

Math Functions

• ABS(number)
• ROUND(number, decimals)
• FLOOR(number)
• CEILING(number)

Example

var
  Command: TInstantIQLCommand;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    // Aggregate function
    Command.Text := 'SELECT COUNT(*) FROM TContact';

    // String function
    Command.Text := 'SELECT * FROM TContact WHERE UPPER(Name) = ''JOHN DOE''';

    // Date function
    Command.Text := 'SELECT * FROM TOrder WHERE YEAR(OrderDate) = 2024';

    // Parse and use...
  finally
    Command.Free;
  end;
end;

TInstantIQLSubqueryFunction

Represents EXISTS function with subquery.

Inheritance: TInstantIQLBaseFunction → TInstantIQLSubqueryFunction

Key Properties

Property	Type	Description
FunctionName	string	Always 'EXISTS'
Subquery	TInstantIQLSubquery	Nested SELECT statement

Example

var
  Command: TInstantIQLCommand;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    Command.Text :=
      'SELECT * FROM TCategory ' +
      'WHERE EXISTS (SELECT * FROM TContact WHERE Contact.Category = SELF)';

    // This selects all categories that have at least one contact
    // SELF refers to the outer query's current Category object
  finally
    Command.Free;
  end;
end;

TInstantIQLOperator Classes

Represent operators in expressions.

TInstantIQLRelOp - Relational Operators

type
  TInstantIQLOperatorType = (
    otEQ,    // =
    otGT,    // >
    otLT,    // <
    otNE,    // <> or !=
    otEN,    // (internal)
    otEG,    // >=
    otGE,    // >=
    otLE,    // <=
    otEL,    // <=
    otLike,  // LIKE
    otIs,    // IS
    otIn,    // IN
    otNotIn, // NOT IN
    ...
  );

TInstantIQLAddOp - Addition Operators

• + (otAdd)
• - (otSub)
• OR (otOr)
• XOR (otXor)

TInstantIQLMulOp - Multiplication Operators

• * (otMul)
• / (otDiv)
• DIV (otFDiv)
• MOD (otMod)
• AND (otAnd)

TInstantIQLSignOp - Unary Sign Operators

• + (unary plus)
• - (unary minus)

TInstantIQLOrder and TInstantIQLOrderSpec

Represent ORDER BY clause.

Inheritance:

• TInstantIQLObject → TInstantIQLOrder
• TInstantIQLObject → TInstantIQLOrderSpec

TInstantIQLOrderSpec Properties

Property	Type	Description
Expression	TInstantIQLExpression	What to order by
OrderDirection	TInstantIQLOrderDirection	odAsc or odDesc

Example

var
  Command: TInstantIQLCommand;
  Order: TInstantIQLOrder;
  Spec: TInstantIQLOrderSpec;
begin
  Command := TInstantIQLCommand.Create(nil);
  try
    Command.Text := 'SELECT * FROM TContact ORDER BY Category.Name ASC, Name DESC';

    // Navigate ORDER BY
    Order := Command.Order;
    if Assigned(Order) then
    begin
      Spec := Order.OrderSpec;
      ShowMessage('First order: ' + Spec.Expression.Text);  // Category.Name
      ShowMessage('Direction: ' + GetEnumName(TypeInfo(TInstantIQLOrderDirection),
        Ord(Spec.OrderDirection)));  // odAsc

      if Assigned(Order.NextOrder) then
      begin
        Spec := Order.NextOrder.OrderSpec;
        ShowMessage('Second order: ' + Spec.Expression.Text);  // Name
        ShowMessage('Direction: ' + GetEnumName(TypeInfo(TInstantIQLOrderDirection),
          Ord(Spec.OrderDirection)));  // odDesc
      end;
    end;
  finally
    Command.Free;
  end;
end;

TInstantIQLTranslator

Abstract base class for translating IQL to native SQL.

Inheritance: TPersistent → TInstantIQLTranslator

Note: Actual translators are in InstantBrokers.pas (TInstantRelationalTranslator)

Key Properties

Property	Type	Description
Command	TInstantIQLCommand	Parsed IQL command
CommandText	string	Original IQL text
RequestedLoadMode	TInstantLoadMode	Requested query mode
ActualLoadMode	TInstantLoadMode	Actual query mode
ResultClassName	string	Result class name

Translation Process

1. Parse IQL text → TInstantIQLCommand
2. Analyze metadata (classes, attributes, tables)
3. Build translation context (table aliases, joins)
4. Translate IQL objects → SQL fragments
5. Assemble final SQL statement

Example: Using Translator (via Query)

uses
  InstantPersistence;

var
  Query: TInstantQuery;
begin
  Query := TInstantQuery.Create(nil);
  try
    Query.Connector := FireDACConnector;

    // Set IQL command
    Query.Command := 'SELECT * FROM TContact WHERE Category.Name = :CategoryName';

    // Set parameter
    Query.Params.ParamByName('CategoryName').AsString := 'Customer';

    // Open executes translation and SQL
    Query.Open;

    // Access translated SQL (broker-specific)
    if Query is TInstantCustomRelationalQuery then
      ShowMessage(TInstantCustomRelationalQuery(Query).Statement);
    // Shows: SELECT T0.Class, T0.Id, ... FROM Contacts T0 LEFT JOIN ...

    // Use objects
    for I := 0 to Query.ObjectCount - 1 do
      Memo1.Lines.Add(TContact(Query.Objects[I]).Name);
  finally
    Query.Free;
  end;
end;

Common Usage Patterns

Simple Query with Parameters

uses
  InstantPersistence;

var
  Query: TInstantQuery;
  Contact: TContact;
begin
  Query := TInstantQuery.Create(nil);
  try
    Query.Connector := MyConnector;
    Query.Command := 'SELECT * FROM TContact WHERE Email = :Email';
    Query.Params.ParamByName('Email').AsString := 'john@example.com';
    Query.Open;

    if Query.ObjectCount > 0 then
    begin
      Contact := TContact(Query.Objects[0]);
      ShowMessage(Contact.Name);
    end;
  finally
    Query.Free;
  end;
end;

Query with Object Navigation

var
  Query: TInstantQuery;
begin
  Query := TInstantQuery.Create(nil);
  try
    Query.Connector := MyConnector;

    // Navigate through reference
    Query.Command :=
      'SELECT * FROM TContact ' +
      'WHERE Category.Name = :CategoryName ' +
      'ORDER BY Name';

    Query.Params.ParamByName('CategoryName').AsString := 'Customer';
    Query.Open;

    for I := 0 to Query.ObjectCount - 1 do
      Memo1.Lines.Add(TContact(Query.Objects[I]).Name);
  finally
    Query.Free;
  end;
end;

Dynamic Query Building

function BuildQuery(const AClassName: string; const AConditions: array of string): string;
var
  SQL: TStringBuilder;
  I: Integer;
begin
  SQL := TStringBuilder.Create;
  try
    SQL.Append('SELECT * FROM ');
    SQL.Append(AClassName);

    if Length(AConditions) > 0 then
    begin
      SQL.Append(' WHERE ');
      for I := Low(AConditions) to High(AConditions) do
      begin
        if I > Low(AConditions) then
          SQL.Append(' AND ');
        SQL.Append(AConditions[I]);
      end;
    end;

    Result := SQL.ToString;
  finally
    SQL.Free;
  end;
end;

// Usage
var
  QueryText: string;
begin
  QueryText := BuildQuery('TContact', [
    'Status = ''Active''',
    'Category.Name = :CategoryName',
    'Email IS NOT NULL'
  ]);

  // SELECT * FROM TContact WHERE Status = 'Active' AND Category.Name = :CategoryName AND Email IS NOT NULL
end;

Aggregate Queries

var
  Query: TInstantQuery;
  Count: Integer;
begin
  Query := TInstantQuery.Create(nil);
  try
    Query.Connector := MyConnector;

    // Count query
    Query.Command := 'SELECT COUNT(*) FROM TContact WHERE Status = ''Active''';
    Query.Open;

    // Note: Aggregate queries return result differently
    // Check broker documentation for aggregate result handling
  finally
    Query.Free;
  end;
end;

Parsing IQL Manually

function ParseIQL(const AIQL: string): TInstantIQLCommand;
var
  Reader: TInstantIQLReader;
  Stream: TStringStream;
begin
  Result := TInstantIQLCommand.Create(nil);
  Stream := TStringStream.Create(AIQL);
  try
    Reader := TInstantIQLReader.Create(Stream);
    try
      Result.Read(Reader);
    finally
      Reader.Free;
    end;
  finally
    Stream.Free;
  end;
end;

// Usage
var
  Command: TInstantIQLCommand;
begin
  Command := ParseIQL('SELECT * FROM TContact WHERE Name = ''John''');
  try
    ShowMessage('Class: ' + Command.ObjectClassName);
    ShowMessage('Has WHERE: ' + BoolToStr(Assigned(Command.Clause)));
  finally
    Command.Free;
  end;
end;

Validating IQL Syntax

function ValidateIQL(const AIQL: string; out ErrorMsg: string): Boolean;
var
  Command: TInstantIQLCommand;
  Reader: TInstantIQLReader;
  Stream: TStringStream;
begin
  Result := False;
  ErrorMsg := '';
  Command := TInstantIQLCommand.Create(nil);
  try
    Stream := TStringStream.Create(AIQL);
    try
      Reader := TInstantIQLReader.Create(Stream);
      try
        try
          Command.Read(Reader);
          Result := True;
        except
          on E: Exception do
            ErrorMsg := E.Message;
        end;
      finally
        Reader.Free;
      end;
    finally
      Stream.Free;
    end;
  finally
    Command.Free;
  end;
end;

// Usage
var
  ErrorMsg: string;
begin
  if not ValidateIQL('SELECT * FROM TContact WHERE Name =', ErrorMsg) then
    ShowMessage('Invalid IQL: ' + ErrorMsg);
end;

Best Practices

1. Use Parameters

   • Always use :ParamName for values
   • Never concatenate values into IQL strings
   • Prevents SQL injection
   • Allows statement caching

2. Path Navigation

   • Use object navigation instead of JOINs
   • Category.Name instead of manual joins
   • InstantObjects generates optimal SQL

3. Query Scope

   • Keep queries focused
   • Use specific attributes instead of * when possible
   • Consider MaxCount for large result sets

4. Ordering

   • Always specify ORDER BY for predictable results
   • Use DESC explicitly when needed
   • Can order by navigated attributes

5. NULL Handling

   • Use IS NULL and IS NOT NULL
   • Not = NULL (incorrect)

6. String Matching

   • Use LIKE with wildcards: '%pattern%'
   • Use UPPER() or LOWER() for case-insensitive
   • Remember to escape quotes: 'O''Connor'

7. Performance

   • Add indexes on frequently queried attributes
   • Use DISTINCT only when necessary
   • Consider COUNT(*) vs. retrieving all objects

IQL Limitations

Not Supported

❌ INSERT, UPDATE, DELETE - Use object methods (Store, Dispose) ❌ GROUP BY - Use aggregate functions differently ❌ HAVING - Filter in application code ❌ Complex JOINs - Use object navigation instead ❌ UNION - Execute multiple queries ❌ CREATE TABLE, ALTER TABLE - Use DBBuilder/DBEvolution ❌ Stored Procedures - Execute via broker directly

Workarounds

Multiple result sets:

// Execute two queries and combine
var
  Query1, Query2: TInstantQuery;
  Results: TObjectList;
begin
  Results := TObjectList.Create(False);
  try
    Query1 := TInstantQuery.Create(nil);
    try
      Query1.Connector := MyConnector;
      Query1.Command := 'SELECT * FROM TContact WHERE City = ''NY''';
      Query1.Open;
      Results.AddRange(Query1.Objects);
    finally
      Query1.Free;
    end;

    Query2 := TInstantQuery.Create(nil);
    try
      Query2.Connector := MyConnector;
      Query2.Command := 'SELECT * FROM TContact WHERE City = ''LA''';
      Query2.Open;
      Results.AddRange(Query2.Objects);
    finally
      Query2.Free;
    end;

    // Use combined Results
  finally
    Results.Free;
  end;
end;

Troubleshooting

Problem: "Expected token ... found ..."

Cause: Syntax error in IQL Solution: Check IQL syntax, quotes, keywords

Problem: "Unknown attribute ..."

Cause: Attribute doesn't exist in metadata Solution: Verify attribute name, check case sensitivity, ensure model is loaded

Problem: "Parameter ... not found"

Cause: Parameter used in IQL but not set Solution: Set parameter value before opening query

Problem: "Cannot navigate path ..."

Cause: Invalid object navigation Solution: Ensure all path segments are valid Reference or Part attributes

Problem: Query returns no objects but should

Cause: Parameter type mismatch or incorrect value Solution: Check parameter types (AsString vs. AsInteger), verify values

Problem: "Aggregate function not supported"

Cause: Broker doesn't support specific function Solution: Check broker documentation, use alternative approach

See Also

• InstantPersistence - TInstantQuery component
• InstantBrokers - SQL translation
• InstantMetadata - Metadata system
• Using an InstantQuery - Query usage guide
• Filtering - Filtering with TInstantSelector
• Sorting - Sorting results

Version History

• Version 3.0 - Initial IQL implementation
• Version 3.5 - Added subquery support (EXISTS)
• Version 4.0 - Enhanced function support
• Version 4.2 - 64-bit compatibility
• Version 4.3 - Performance improvements in translation