BroadSQL BroadSQL Downloads
Documentation / Commands / PULL

PULL

Core command

PULL is kept so existing scripts keep running; DUMP is the export command to use and document. PULL runs exactly like DUMP (same sources, optional TO, formats, defaults and files, see HELP DUMP): PULL / exports the displayed result and never runs the query again. AS H2 reuses or creates the named H2 connection; MODE OVERWRITE (the default) recreates the table, MODE APPEND KEY(<column>) [FORCE] inserts only rows whose key is greater than the target's current maximum, MODE APPEND (no KEY) adds every row when the table has the same columns, names and order as the result, and every run is recorded in BROADSQL_PULL_AUDIT. AS XLSX and AS ODS replace one worksheet (or append to it with MODE APPEND) and keep a QUERIES worksheet; they accept a trailing OPEN to open the file afterwards. The Connections Definition File ($CDF) cannot be a destination.

Arguments

<source> [TO <destination>] [AS <format>] [MODE OVERWRITE | MODE APPEND | MODE APPEND KEY(<column>) [FORCE]] [OPEN], with the sources, destinations and formats of DUMP

Examples

PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2;
PULL CUSTOMER TO WORKCOPY.CUSTOMER AS H2 MODE APPEND KEY(ID);
PULL (SELECT * FROM NEW_ORDERS) TO WORKCOPY.ORDERS AS H2 MODE APPEND;
PULL (SELECT ID, NAME FROM CUSTOMER WHERE COUNTRY = 'FR') TO REPORT.FRENCH_CUSTOMERS AS XLSX OPEN;
PULL CUSTOMER TO REPORT_CUSTOMERS AS CSV;
PULL API RESULT TO API_USERS AS JSON;
PULL (SELECT * FROM ORDERS WHERE CUSTOMER_ID = ${id}) TO WORKCOPY.ORDERS AS H2 MODE APPEND KEY(ID);

Notes

See the Export guide for workflows, output formats, OPEN behavior and provenance.

PULL is a compatibility alias of DUMP, the export command: it runs exactly like DUMP (same sources, optional TO, formats and defaults; PULL / exports the result that was displayed, never running the query again), and every earlier PULL command line keeps working. Only its success message keeps the wording "pulled into". As with DUMP, the query in parentheses may use ${name} variables, bound as typed values (with MODE APPEND, the key filter's value is bound after them), and a script source takes named arguments and is exported only when its final status is SUCCESS.

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 the shared ExportPipeline. 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).

GitHub #152 - both AS <format> and the destination's .<table/tab> part are optional (grammar simplification only, every existing explicit form keeps working unchanged - see PullCommandParser for the full grammar and rationale): PULL (...) TO toto; means PULL (...) TO toto.DATA AS <configured DefaultFileFormat>; - an omitted .<table/tab> always defaults to DATA (fixed, not configurable), and an omitted AS <format> uses the DefaultFileFormat INI setting, like DUMP (getDefaultFileFormat()). Unlike DUMP <table> alone, a <i>present but unrecognized</i> DefaultFileFormat value refuses the implicit- format form outright with a configuration error naming the setting, rather than silently substituting ODS - an absent key is unaffected and still silently resolves to ODS, so this never blocks a PULL that gives AS <format> explicitly.

AS H2 has two modes: MODE OVERWRITE (the default), which drops and recreates the table from scratch every run, and MODE APPEND KEY(<column>) [FORCE], 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; the source query's shape is checked by PullSourceShapeValidator (a JOIN is allowed, a comma-joined/derived FROM or any aggregation is not), and the key column's NULL-safety is checked at runtime by checkKeyNullability - see docs/EXPORT_TO_H2.md, "APPEND KEY(...) opened up to joined queries", for the full reasoning, including the empirically confirmed limitation that this check cannot be trusted for an outer join on every JDBC driver.

MODE APPEND without KEY, for AS H2, AS XLSX and AS ODS and every source, adds every row of the result to the existing table or worksheet when it has the same number of columns, with the same names, in the same order as the result; types are not compared and the query is not examined. A missing destination is created; a different column structure is refused before anything is written. See DUMP.

The Connections Definition File ($CDF), or any connection that opens the same database file, is refused as an AS H2 destination.

All four source forms are supported: a bare table/view name (a shortcut for SELECT * FROM <name>), / for the last result displayed (never re-run), a parenthesized query - parentheses are mandatory for this form, e.g. PULL (SELECT ...) TO ... - and, since the XT02 overnight batch's sub-sprint 7 ("API result export and local snapshot"), the literal two-token phrase API RESULT for the last RUN result held in memory (see LastApiExecutionResultHolder) - the direct sibling of / for API results rather than SQL results. MODE APPEND is rejected for this fourth source (no source-side SQL shape to validate a delta filter against); AS JSON for this source exports the <i>flattened tabular</i> shape (its columns), not the original raw response body - see ExportPipeline.

<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 ExportPipeline#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. Registered as a standalone connection - no Database Group (INSTANCE_ID left null) - in the built-in LOCAL Environment (see resolveLocalEnvironmentId): a local extract copied from a source is not itself an environment of that source's logical database, so it must never be attributed to the source's (or any other) Database Group. See ExportPipeline#createH2Connection and docs/CONNECTION_MODEL.md.

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.4.5.

← All commands