BroadSQL BroadSQL
Documentation / Commands / PULL

PULL

Core command

Copies a query's current results into a table of an H2 database (AS H2), a tab of an Excel/ODS file (AS XLSX/AS ODS), or a whole flat file (AS CSV/AS TXT/AS JSON/AS MD/AS HTML). AS H2 reuses an existing connection by name, or creates and registers one automatically; MODE OVERWRITE (the default) drops and recreates the table each run, MODE APPEND KEY(<column>) only inserts rows newer than the target's current maximum key value (MODE MERGE not implemented yet). AS XLSX/AS ODS always erase and replace the named tab, leaving every other tab in the file untouched, and keep a 'QUERIES' info tab listing each tab's query, date, source connection, and row count. Every flat-file format (CSV/TXT/JSON/MD/HTML) always fully overwrites the whole file - no MODE clause, no per-file metadata, since a flat file has no second tab to hold it; AS CSV uses CsvSeparator from the INI file (semicolon if absent), AS TXT always uses a tab (neither honors SET SEP); AS JSON writes a single array of objects (numbers written exactly, ISO-8601 dates); AS MD writes a GitHub-Flavored-Markdown table; AS HTML writes a standalone <table> fragment meant to be pasted into an email/wiki page. This whole command is the recommended, unified replacement for EXPORT/DUMP's file output for a one-shot query/table extraction (EXPORT/DUMP remain fully supported, unchanged).

Arguments

<source> TO <name>.<destination> AS H2 [MODE OVERWRITE | MODE APPEND KEY(<column>)] | AS XLSX | AS ODS | <source> TO <name> AS CSV | AS TXT | AS JSON | AS MD | AS HTML - source is a bare table/view name, '/' for the last query held in memory, or a parenthesized query, e.g. (SELECT ...) - parentheses are mandatory for a literal query. For AS H2, <name> is a plain identifier (no path, at most 15 characters): if it already names a registered H2 connection it is reused, otherwise a new H2 database is created in the default export folder and registered under that name; <destination> is the table to (re)create. MODE APPEND KEY(<column>) requires a single numeric or date/time column and a simple single-table source query (no JOIN/GROUP BY/UNION/ORDER BY/LIMIT). For AS XLSX/AS ODS, <name> is a plain file name (no path) resolved directly to <name>.xlsx/.ods in the default export folder - no MODE clause; <destination> is the tab to erase and replace (at most 31 characters, none of \ / : ? * [ ], and not 'QUERIES', reserved for the info tab). For AS CSV/AS TXT/AS JSON/AS MD/AS HTML, <name> is a plain file name with no dot at all (no <destination> part - a flat file has nothing to address after one), resolved directly to <name>.<csv|txt|json|md|html> in the default export folder - no MODE clause, always a full overwrite; BLOB/CLOB/binary/structural columns are rejected before the file is touched, for every format.

Examples

PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2;
PULL (SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR') TO WORKCOPY.FRENCH_CUSTOMERS AS H2;
PULL / TO WORKCOPY.LASTRESULT AS H2;
PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2 MODE APPEND KEY(ID);
PULL CUSTOMER TO REPORT.CUSTOMERS AS XLSX;
PULL (SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR') TO REPORT.FRENCH_CUSTOMERS AS ODS;
PULL CUSTOMER TO REPORT_CUSTOMERS AS CSV;
PULL (SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR') TO FRENCH_CUSTOMERS AS TXT;
PULL CUSTOMER TO REPORT_CUSTOMERS AS JSON;
PULL CUSTOMER TO REPORT_CUSTOMERS AS MD;
PULL CUSTOMER TO REPORT_CUSTOMERS AS HTML;

Notes

PULL <source> TO <name>[.<destination>] AS H2 | XLSX | ODS | CSV | TXT | JSON | MD | HTML - copies a query's current results into a table of an H2 database, a tab of an .xlsx/ .ods file, or a whole flat file (.csv/.txt/.json/.md/ .html). The eight destination kinds share only this grammar and the command name - see docs/PULL_TO_SPREADSHEET.md, "Relationship to PULL ... AS H2" - each is executed independently by executeToH2/executeToSpreadsheet/executeToFlatFile. The rest of this Javadoc covers AS H2 only; see docs/PULL_TO_SPREADSHEET.md for AS XLSX/AS ODS (destination file resolution, tab erase-and-replace semantics, the shared "QUERIES" info tab) and docs/PULL_TO_TEXT.md for the flat-file family (destination grammar, the CSV separator, JSON's number/ date conventions, Markdown's escaping, HTML's fragment shape, why there is no per-file metadata for any of these five formats).

AS H2 has two modes: MODE OVERWRITE (the default), which drops and recreates the table from scratch every run, and MODE APPEND KEY(<column>), which never drops or updates anything - it creates the table on its first run, and on every later run inserts only the rows whose key column is greater than the current maximum in the target table (computed fresh each time, not tracked as separate state), so a plain re-run picks up exactly the rows added at the source since the last pull. See docs/EXPORT_TO_H2.md, "Modes", for the full design discussion, including why there is no keyless/blind-insert APPEND and why the APPEND KEY(...) source query is currently restricted to a simple single-table query (checked by PullSourceShapeValidator).

All three source forms are supported: a bare table/view name (a shortcut for SELECT * FROM <name>), / for the last query held in memory, or a parenthesized query - parentheses are mandatory for this form, e.g. PULL (SELECT ...) TO ....

<name> (the part of the destination before the dot) is looked up in the CDF connections vault. Exactly one of three things happens, see docs/EXPORT_TO_H2.md ("How AS H2 further evolved") for the full design discussion:

  • A connection named <name> exists and is not H2 - the PULL is aborted with an explicit error rather than touching it.
  • A connection named <name> exists and is H2 - it is reused directly, with whatever credentials the CDF already holds for it, same as any normal CONNECT.
  • No connection named <name> exists - BroadSQL creates <default export folder>/<name>.mv.db, registers it as a new H2 connection named <name> (same persistence call ADD CONNECTION uses - saveDatabaseDefinition, no extra master password prompt needed since the vault is already unlocked for the session), with fixed default credentials sa / clipper8AD - not real protection (identical on every install, recoverable from the shipped classes), only there because BroadSQL itself needs a user name and password to reopen the connection later - then proceeds exactly as the case above. Its URL also includes CASE_INSENSITIVE_IDENTIFIERS=TRUE (see createH2Connection), so a table or column can be referenced later with any casing, quoted or not - existing connections are never altered to add this, so they keep behaving exactly as they do today.

This retires the earlier, superseded two-keyword design (AS DB for an existing connection, a path-accepting AS H2 for an ephemeral file) - the choice between them depended on hidden state (did a connection already exist under that name?) rather than on anything about what the user was trying to do. <name> is a plain identifier now: no path, no further dot, none of the characters Windows forbids in a file name, not a reserved Windows device name, and at most 15 characters (the CDF's connection ID column width) - all validated by PullCommandParser before this command ever runs.

In both the reuse and create cases, the target table does not need to exist beforehand: its columns and types are derived entirely from the query's own result set - a column aliased with AS in the source query keeps that alias as its name in the target table.

MODE MERGE is recognized by the command's grammar but not implemented yet - using it fails with an explicit error rather than being silently ignored or misinterpreted.

Known limitation: unlike other long-running commands, CTRL+C does not currently cancel a PULL in progress.

Last modified in release 5.0.3.

← All commands