<< Click to Display Table of Contents >> Navigation: »No topics above this level« Introduction |
HTML SQL Library is a set of classes for retrieving and transforming data between different sources using SQL. It doesn't depend on any external library or DB server and can work with almost any source including files, strings and Delphi classes.
SQL query can be written in any modern dialect - Oracle, Firebird, MS SQL Server, etc. No need to setup connection or create query instance, just call one of the class methods. Example:
THtSQL.FromFile('c:\sample.xlsx', 'select * from sheet1 where _row > 0').ToJSONFile('c:\sample.json');
What it can be used for:
•Convert to/from CSV, JSON, XML, HTML, Excel, SQL script.
•Upload files/objects to database
•Export data from database
•Check data consistency
•Display data/objects in DB-aware controls, HtPanel, StringGrid, VirtualTreeview, ControlList
•Extend database capabilities (f.e. use Pivot in Firebird).
•Serialize and deserialize objects
•Reduce calls to REST server by using already loaded data.
Supported data sources:
•CSV file
•JSON file
•JSON string
•XML file
•HTML file
•Text file with fixed size fields
•SQL script
•ZIP archive
•XLS/XLSX Excel sheet (requires HTML Office Library).
•Outlook PST/OST file (requires HTML Office Library).
•TList<T>
•TObjectList
•Any class with IEnumerable or GetEnumerator support
•TDataset
•array of T
•SQL Query
Custom sources can be added by implementing IDMDatasource interface.
Supported dialects:
•SQL 92
•Oracle
•MS SQL Server
•Firebird
•MySQL
•PostrgesSQL
•SQLite
•ElevateDB
Result can be exported to
•CSV
•XML
•JSON
•SQL script
•TDataset
•TStringList
•TObjectList
•SQL table
•HTML (plain table or using template)
•Array of variant
•Binary file
or any other format, using custom processing of result data.
For example, the following line will extract 'movies' table data from SQL script and save it to JSON file:
THtSQL.FromFile('c:\movies.sql', 'select * from movie', []).ToJSONFile('c:\movies.json');
Library supports almost all SQL features, including joins, order, group by, having, cube, rollup, pivot, select from select, subselects, etc.