Automatic query providers

Supplied from version 2.384.99 onwards via the XLSX workbook facade on Windows; automatic provider selection happens only whilst an explicit query refresh is running

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 gives access to a workbook-owned dispatcher held in lxQueryProviders; never free the dispatcher yourself

TXLSXWorksheet.RefreshQueryTable(const AName: WideString; AMaxRows: Integer = 1000000; AOnProgress: TXLSQueryRefreshProgress = nil): Integer together with the overload that takes a zero-based AIndex pick an existing query and route the work through this dispatcher

The existing overloads that accept an explicit TXLSQueryTableProvider keep using that provider directly, still rejecting a missing provider as before; they never fall back to automatic dispatch

Refresh yields 1 on success, 0 on cancellation and -1 on failure, with diagnostic codes 1401 and 1400 recorded under xlsOperationRefresh; the established transactional result application, schema validation, literal strings and formatting behaviour all stay in force

These diagnostics are called xlsDiagnosticQueryRefreshCancelled and xlsDiagnosticQueryRefreshFailed; the workbook write guard is taken before status conversion, so a frozen read view or any other write-guard rejection raises an exception

Opening and saving a workbook never fetches connection data, evaluates queries or acts upon retained RefreshOnLoad metadata; refresh every required query explicitly 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 the supported field types, which cover six date orders and skipped fields; delimited input also accepts quoted multiline records
• xlckAdo, xlckOleDb and xlckOdbc rely on installed Windows ADO and database drivers with read-only connections and recordsets; CommandType = 2 chooses SQL text and CommandType = 3 chooses a table command
• xlckWeb uses Windows WinHTTP to perform an anonymous HTTP or HTTPS GET of a single nonempty rectangular HTML table picked by its positive index or name; the supported response character sets are UTF-8, UTF-16, Windows-1252 and Latin-1

BaseDirectory provides the base for relative local text paths alone; database connection strings and Web URLs stay explicit connection metadata

Where the text encoding is unspecified, ASCII-only input is accepted unless a supported byte order mark identifies the encoding; non-ASCII input needs an explicit supported encoding or a byte order mark

For a delimited Text connection, set TextPrompt = False, TextDelimited = True and precisely one delimiter, with consecutive delimiters left uncollapsed; freshly created connections default to prompting with a tab delimiter, so clear TextTab when you pick another delimiter

Fixed-width text

Set TextPrompt = False and TextDelimited = False, then add TextFields in strictly increasing zero-based Position order beginning at zero; each field runs to the next position or to the physical record terminator, and xltiftSkip leaves its field out of the result

Positions are counted in decoded UTF-16 code units rather than input bytes; a boundary that splits a surrogate pair is rejected, and CRLF, LF and CR each terminate records independently of the delimiter and qualifier settings

Field padding is trimmed before conversion, explicitly typed text included, matching the verified native fixed-width import behaviour; quotes, tabs and delimiter characters within a field stay literal input, and short records supply empty trailing fields without altering the declared schema

With no TextFields, every record is a single general field; TextFirstRow picks the first schema record, Query.Headers skips that record's values, and explicit blank records still count as rows

The existing row, input-byte, result-cell and 32767-code-unit field limits all apply; invalid metadata, conversion failures or excessive input clear the 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 together with WebHtmlFormat = 'none'; supported requests refuse embedded credentials, fragments, authentication and redirects

The built-in providers refuse unsupported metadata within 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 insists on anonymous table metadata

Typed database parameters

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

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

Use ParameterType = 'value' with an explicit ValueKind of xlcpvInteger, xlcpvDouble, xlcpvBoolean or xlcpvString plus the matching value property; zero, False and empty strings all count as values, and xlcpvNone infers no 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' with ValueKind = xlcpvCell and CellReference for a fully qualified local worksheet reference such as Inputs!$A$1 or 'Sales Input'!B2; quoted sheet names double their apostrophes, whilst ranges, external workbooks, names and unqualified cell references are rejected

Worksheet refresh takes a snapshot of stored scalar values and available formula caches without recalculation or packed-cell materialisation; missing formula caches and error cells are rejected, whilst a missing or blank cell yields SQL null only when an explicit supported SQL type is given

The cell snapshot keeps its Variant type, so signed Int64, Currency, typed date and null inputs pass through without being squeezed into the persisted 32-bit integer or Double literal fields; directly stored date, null and Int64 literals sit outside the native parameter metadata model, so prefer typed cell bindings for those values

SqlType takes ODBC SQL type codes, mapped explicitly onto ADO types; it is not an ADO DataTypeEnum value

The supported explicit types also take SQL null; arrays, reference Variants, errors, nonfinite numbers, unsupported SQL types and conversions that would sacrifice integer precision are rejected, and NUMERIC or DECIMAL call for precision and scale metadata this binding API does not offer

ADO is handed the typed value and declared size, with at least one code unit set aside for empty text; native drivers stay responsible for SQL dialect, supported parameter types and result conversions, so an installed provider may still turn down a valid binding or offer narrower numeric or date capabilities

Jet and ACE use typed OLE DATE bindings for validated DATE, TIME and TIMESTAMP values, keeping date and time separate from locale-dependent timestamp text projections; the DATE and TIME validation rules still hold, and null projection remains provider-specific

Parameters need a SQL text command and are capped at 1024 per fetch, 255 code units per name and 32767 code units per text value; command text, parameter references, names and payloads also draw on the input-byte budget

Prompts and unknown parameter extension attributes are rejected; RefreshOnChange stays retained metadata and triggers no background refresh, and opening or saving neither resolves cells nor executes commands

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

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

Web refresh handles plain text/html tables with no spanning cells, nested selected tables or script-driven content; unsupported layouts and entities are refused instead of yielding 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 builds an independently owned dispatcher for direct use; the workbook creates and owns its own instance separately

RegisterProvider(AKind: TXLSConnectionKind; AProvider: TXLSQueryTableProvider) fits a borrowed handler to a declared connection kind, and nil removes it again; the handler must outlive the registration

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

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

Resource limits

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

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

All three settings and MaxRows have to be positive, and the timeout must fit within a native millisecond integer; the dispatcher refuses surplus rows, columns or cells without truncation, whilst byte and input limits are enforced by the built-in providers and stay the registered handler's responsibility for custom fetches

Worksheet refresh validates every result value and restores cells and query or table metadata after cancellation or application errors; custom providers stay responsible for honouring their own external-operation limits

Native XLSX result bindings

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

TXLSXTable.ColumnUniqueNames[Index]: WideString reveals zero-based native column identities, kept through assignment, copying and reopening alongside ColumnQueryTableFieldIds

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

Unrelated or unsupported imported relationships and extension XML stay preserved; this feature will not turn opaque external graphs into supported refreshable queries

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