Automatic query providers

Available since version 2.384.99 through the XLSX workbook facade on Windows; automatic provider selection occurs only during an explicit query refresh

Refresh an existing query

Workbook.QueryProviders.BaseDirectory := 'C:\Data';
Workbook.QueryProviders.MaxInputBytes := 64 * 1024 * 1024;
Workbook.QueryProviders.MaxResultCells := 2000000;
Workbook.QueryProviders.TimeoutSeconds := 30;
Status := Sheet.RefreshQueryTable('ImportedData', 1000000);

TXLSXWorkbook.QueryProviders: TXLSQueryProviderDispatcher exposes a workbook-owned dispatcher from lxQueryProviders; do not free the dispatcher

TXLSXWorksheet.RefreshQueryTable(const AName: WideString; AMaxRows: Integer = 1000000; AOnProgress: TXLSQueryRefreshProgress = nil): Integer and the overload taking a zero-based AIndex select an existing query and use this dispatcher

Existing overloads accepting an explicit TXLSQueryTableProvider continue to use that provider directly, including their existing rejection of a missing provider; they do not fall back to automatic dispatch

Refresh returns 1 on success, 0 on cancellation or -1 on failure, with diagnostic codes 1401 and 1400 under xlsOperationRefresh; the existing transactional result application, schema validation, literal strings and formatting behavior remain in effect

These diagnostics are named xlsDiagnosticQueryRefreshCancelled and xlsDiagnosticQueryRefreshFailed; acquiring the workbook write guard occurs before status conversion, so a frozen read view or other write-guard rejection raises an exception

Opening and saving a workbook never fetch connection data, evaluate queries or act on retained RefreshOnLoad metadata; explicitly refresh each required query before saving its result

Supported built-in providers

• xlckText reads local delimited or fixed-width text with strict UTF-8, UTF-16LE, UTF-16BE, Windows-1252 or Latin-1 decoding, configured starting rows and supported field types including six date orders and skipped fields; delimited input also supports quoted multiline records
• xlckAdo, xlckOleDb and xlckOdbc use installed Windows ADO and database drivers with read-only connections and recordsets; CommandType = 2 selects SQL text and CommandType = 3 selects a table command
• xlckWeb uses Windows WinHTTP for an anonymous HTTP or HTTPS GET of one nonempty rectangular HTML table selected by its positive index or name; supported response character sets are UTF-8, UTF-16, Windows-1252 and Latin-1

BaseDirectory supplies the base for relative local text paths only; database connection strings and Web URLs remain explicit connection metadata

An unspecified text encoding accepts ASCII-only input unless a supported byte order mark identifies the encoding; non-ASCII input requires an explicit supported encoding or byte order mark

For a delimited Text connection, set TextPrompt = False, TextDelimited = True and exactly one delimiter, without collapsing consecutive delimiters; newly created connections default to prompting and a tab delimiter, so clear TextTab when choosing a different delimiter

Fixed-width text

Set TextPrompt = False and TextDelimited = False, then add TextFields in strictly increasing zero-based Position order, starting at zero; each field ends at the next position or the physical record terminator, and xltiftSkip excludes its field from the result

Positions count decoded UTF-16 code units rather than input bytes; a boundary splitting a surrogate pair rejects, and CRLF, LF and CR terminate records independently of delimiter and qualifier settings

Field padding is trimmed before conversion, including explicitly typed text, matching the verified native fixed-width import behavior; quotes, tabs and delimiter characters inside a field remain literal input, and short records supply empty trailing fields without changing the declared schema

Without TextFields, each record is one general field; TextFirstRow selects the first schema record, Query.Headers skips that record's values, and explicit blank records remain rows

Existing row, input-byte, result-cell and 32767-code-unit field limits apply; invalid metadata, conversion failures or excessive input clear staged data before the transactional worksheet update, and cancellation preserves the previous result cells

Connection.TextPrompt := False;
Connection.TextDelimited := False;
Connection.TextFields.Add(xltiftGeneral, 0);
Connection.TextFields.Add(xltiftText, 8);
Status := Sheet.RefreshQueryTable('Imported');

For a Web connection, set WebHtmlTables = True and WebHtmlFormat = 'none'; supported requests reject embedded credentials, fragments, authentication and redirects

The built-in providers reject unsupported metadata in their respective connection kinds; database refresh rejects OLAP or server commands, connection-file indirection, stored passwords and credential prompts, Text refresh rejects file prompts, and Web refresh requires anonymous table metadata

Typed database parameters

SQL text commands support positional ? markers bound through an ADO Command, in Connection.Parameters order; parameter values never replace SQL text, and names label bindings without changing positional order

Preflight counts markers outside single-quoted strings, double-quoted or backtick identifiers, bracketed identifiers, line comments and nested block comments, including doubled quote or bracket escapes; unmatched quotes or comments and marker-count mismatches reject before opening a connection

Use ParameterType = 'value' with an explicit ValueKind of xlcpvInteger, xlcpvDouble, xlcpvBoolean or xlcpvString and its corresponding value property; zero, False and empty strings are values, and xlcpvNone does not infer a value during execution

Connection.CommandType := 2;
Connection.CommandText := 'SELECT Amount FROM Sales WHERE Amount > ?';
with Connection.Parameters.Add do
begin
  Name := 'MinimumAmount';
  ParameterType := 'value';
  ValueKind := xlcpvInteger;
  IntegerValue := 0;
  SqlType := 4;
end;
Status := Sheet.RefreshQueryTable('SalesQuery');

Use ParameterType = 'cell', ValueKind = xlcpvCell and CellReference for a fully qualified local worksheet reference such as Inputs!$A$1 or 'Sales Input'!B2; quoted sheet names use doubled apostrophes, and ranges, external workbooks, names and unqualified cell references reject

Worksheet refresh snapshots stored scalar values and available formula caches without recalculation or packed-cell materialization; missing formula caches and error cells reject, while a missing or blank cell supplies SQL null only with an explicit supported SQL type

The cell snapshot retains its Variant type, allowing signed Int64, Currency, typed date and null inputs without reducing them to the persisted 32-bit integer or Double literal fields; directly stored date, null and Int64 literals are outside the native parameter metadata model, so use typed cell bindings for those values

SqlType uses ODBC SQL type codes, which are mapped explicitly to ADO types; it is not an ADO DataTypeEnum value

Supported explicit types also accept SQL null; arrays, reference Variants, errors, nonfinite numbers, unsupported SQL types and conversions that would lose integer precision reject, and NUMERIC or DECIMAL require precision and scale metadata that this binding API does not provide

ADO receives the typed value and declared size, with at least one code unit allocated for empty text; native drivers remain responsible for SQL dialect, supported parameter types and result conversions, so an installed provider can still reject a valid binding or have narrower numeric or date capabilities

Jet and ACE use typed OLE DATE bindings for validated DATE, TIME and TIMESTAMP values to preserve date and time independently of locale-dependent timestamp text projections; the DATE and TIME validation rules still apply, and null projection capabilities remain provider-specific

Parameters require a SQL text command and are limited to 1024 per fetch, 255 code units per name and 32767 code units per text value; command text, parameter references, names and payloads also consume the input-byte budget

Prompts and unknown parameter extension attributes reject; RefreshOnChange remains retained metadata and does not trigger background refresh, and opening or saving does not resolve cells or execute commands

Direct Fetch supports literals; FetchWithCellResolver(Connection, Query, MaxRows, out Data, var Abort, AResolver) accepts a TXLSQueryParameterCellResolver callback for cell values, whose Boolean result indicates whether a stored value is available

The callback is scoped to the call and is not retained; automatic worksheet refresh supplies its local resolver, and registered custom providers still take precedence and own their parameter semantics

Web refresh supports plain text/html tables without spanning cells, nested selected tables or script-driven content; unsupported layouts and entities reject rather than producing partial data

Register a custom provider

Workbook.QueryProviders.RegisterProvider(xlckWeb, CustomProvider);
try
  Status := Sheet.RefreshQueryTable('RemoteData');
finally
  Workbook.QueryProviders.RegisterProvider(xlckWeb, nil);
end;

TXLSQueryProviderDispatcher.Create constructs an independently owned dispatcher for direct use; the workbook creates and owns its own instance

RegisterProvider(AKind: TXLSConnectionKind; AProvider: TXLSQueryTableProvider) installs a borrowed handler for a declared connection kind, and nil unregisters it; the handler must outlive registration

A registered handler takes precedence over the built-in provider and can support application-specific authentication or connection kinds; registration changes, configuration changes and recursive dispatch reject while fetching, and invalid enum values reject before array access

Fetch(Connection, Query, MaxRows, out Data, var Abort) receives detached metadata during worksheet refresh and returns a rectangular TXLSQueryResultData; cancellation or exceptions clear staged results before propagation

Resource limits

MaxInputBytes defaults to 67108864 bytes and bounds text or Web input and supported database result payloads; MaxResultCells defaults to 2000000 cells and bounds the rectangular result

TimeoutSeconds defaults to 30 and configures supported native database or HTTP phases; it is not a guaranteed deadline for the complete operation or a custom provider

All three settings and MaxRows must be positive, and the timeout must fit a native millisecond integer; the dispatcher rejects excess rows, columns or cells without truncation, while byte and input limits are enforced by the built-in providers and remain the registered handler's responsibility for custom fetches

Worksheet refresh validates all result values and restores cells and query or table metadata after cancellation or application errors; custom providers remain responsible for honoring their own external-operation limits

Native XLSX result bindings

Table-backed database queries use the table-to-query-table relationship, coherent field IDs, column identities and a hidden local destination name; standalone supported Text and Web destinations retain their result range

TXLSXTable.ColumnUniqueNames[Index]: WideString exposes zero-based native column identities, preserving them through assignment, copying and reopening together with ColumnQueryTableFieldIds

A legacy Text connection bound directly as an external table is not a supported native export shape and saving rejects it before output; explicitly refreshed text values can populate an ordinary table, or an installed ADO/ODBC text driver can provide a native table-backed database query

Unrelated or unsupported imported relationships and extension XML remain preserved; this feature does not convert opaque external graphs into supported refreshable queries

See connection and transactional query APIs for destination validation, progress callbacks and the surrounding metadata model