Getting started

To execute a query use one of the following THtSQL class methods:

•FromFile

•FromFolder

•FromDataset

•FromDatamodule

•FromList

•FromArray

•FromSchema

•FromJSON.

Common parameters are:

•ASQL: SQL query to execute
AParams: Query parameters (:param)

•ADialect - SQL dialect class, by default TSQLDialectAll is used, it is a superset of Firebird, Oracle and MS SQL functions and statements.

function FromFile(const AFile, ASQL: string; const AParams: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Query data from file or ZIP archive. AFile should contain full file (zip) name including path and extension. When file do not have nested tables (and is not zip) it is registered as 'data' table.

Example of selecting from CSV file: table name is 'data' because CSV do not have nested tables.

THtSQL.FromFile('c:\c1000.csv', 'select first 10 * from data order by c', []);

Example of selecting from XLSX file: table name is Sheet1 because XLSX can contain several sheets

THtSQL.FromFile('c:\sample.xlsx', 'select a, b, c from Sheet1 where e =:1', ['Seattle']);

function FromFolder(const AFolder, ASQL: string; const AParams: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Query data from files in a folder. Complex files are referenced using file name without extension and table name. Example of selecting from Excel file sample.xlsx:

THtSQL.FromFolder('c:\demo', 'select * from sample.Sheet1 order by c', []);

function FromDataset(ADataset: TDataset; const ASQL: string; const AParams: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Select from singl dataset

function FromDatamodule(ADatamodule: TComponent; const ASQL: string; const AParams: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Execute query using datasets from datamodule or form.

function FromList(ASource: TObject; const ASQL: string; const Params: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Query data from object list or any object with IEnumerble / GetEnumerator support. All elements should be of the same type. Example:

THtSQL.FromList(CustList, 'select name, address from data order by name', []).ToDataset(FDMemTable1);

function FromArray(var ASource; TypeInfo: PTypeInfo; const ASQL: string; const Params: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Query data from array of objects or records. All array elements should be of the same type. Example:

type
TCustArray = array of TCustomer;
var A: TCustArray;
THtSQL.FromArray(A, TypeInfo(TCustArray), 'select name, address from data order by name', []).ToDataset(FDMemTable1);

function FromSchema(ASchema: TDMSchema; const ASQL: string; const AParams: array of variant; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Query data from virtual schema containing datasources of different types. See Virtual schema section.

function FromJSON(const AJSON, ASQL: string; const AParams: array of variant; const AXPath: string = ''; ADialect: TSQLDialectClass = nil): IHtSQLResult;

Query data from JSON string.