Special Commands

Syntra ODBC extends standard SQL with several special commands for controlling cache behavior, voiding QuickBooks transactions, and managing the sync engine.

SET QB_MAX_STALENESS

Controls how fresh the data must be when reading from the cache. The value is in seconds.

-- Force a live query, bypassing the cache entirely
SET QB_MAX_STALENESS = 0;
SELECT * FROM customers;

-- Accept data up to 5 minutes old
SET QB_MAX_STALENESS = 300;
SELECT * FROM invoices;

How It Works

When you issue a SELECT query, Syntra checks the age of the cached data for that table:

• If the cached data is fresher than QB_MAX_STALENESS, the query reads from the local cache.
• If the cached data is older than QB_MAX_STALENESS, Syntra fetches live data from QuickBooks before returning results.

The default staleness is determined by the table’s polling tier:

Tier	Default Staleness
Hot	60 seconds
Warm	180 seconds
Cold	900 seconds
Frozen	3600 seconds

Setting QB_MAX_STALENESS = 0 guarantees a live fetch from QuickBooks for every query. This is useful for debugging or verifying recent writes, but will be slower than cached reads.

The setting applies to the current session (connection) only.

CALL qb_void()

Voids a transaction in QuickBooks. Unlike DELETE, which removes the transaction entirely, voiding preserves the transaction record with a zero amount, matching QuickBooks Desktop’s built-in void behavior.

CALL qb_void('Invoice', '12345-1234567890');
CALL qb_void('SalesReceipt', '67890-1234567890');
CALL qb_void('Bill', '11111-1234567890');
CALL qb_void('CreditMemo', '22222-1234567890');
CALL qb_void('Check', '33333-1234567890');

Parameters

Parameter	Description
TxnType	The QuickBooks transaction type (e.g., Invoice, Bill, SalesReceipt, Check, CreditMemo, JournalEntry)
TxnID	The transaction’s unique ID in QuickBooks

Finding the TxnID

SELECT txn_id, ref_number, customer_ref_full_name, txn_date
FROM invoices
WHERE ref_number = 'INV-1001';

Then use the returned txn_id in the void call.

CALL qb_rebuild_all()

Forces a complete rebuild of the cache-backed tables by re-fetching their data from QuickBooks Desktop.

CALL qb_rebuild_all();

When to Use

• After making significant changes directly in QuickBooks Desktop
• After adding new custom fields in QuickBooks
• If the cache appears to be out of sync with QuickBooks
• After upgrading Syntra ODBC to a new version

Important Notes

• The rebuild covers the tables Syntra maintains in its local cache. On QuickBooks Enterprise with live reads enabled, tables served live are always current and are not part of the cache, so they are skipped (there is nothing to rebuild for them).
• The rebuild runs in the background and returns immediately. The server remains available, and queries continue to serve data from the existing cache until each table’s pull completes.
• This operation can take several minutes depending on the size of your QuickBooks company file.
• Do not run this command frequently. The incremental sync mechanism keeps the cache current for normal operations.

CALL qb_sync_table()

Forces an immediate sync of a specific cache-backed table:

CALL qb_sync_table('accounts');
CALL qb_sync_table('items');

This is more targeted than qb_rebuild_all() and completes much faster when you only need to refresh one table. It returns a one-row result describing the outcome (for example, the number of rows synced).

On QuickBooks Enterprise with live reads enabled, calling this on a table that is served live returns an informational message rather than syncing: those tables are always current and are not maintained in the cache. See Live Data Reads.

SHOW TABLES

Lists all available QuickBooks tables:

SHOW TABLES;

SHOW COLUMNS

Lists all columns for a specific table:

SHOW COLUMNS FROM customers;
SHOW COLUMNS FROM invoices;