Migration from QODBC

This guide helps existing QODBC users migrate to Syntra ODBC. While both products provide SQL access to QuickBooks Desktop data, Syntra ODBC uses a fundamentally different architecture that offers significant performance advantages.

Architecture Differences

Feature	QODBC	Syntra ODBC
Protocol	ODBC only	Built-in SQL server (port 5433)
Query engine	Direct QBFC translation	Local cache + QBFC fallback
Read performance	200 ms – 30 s per query	< 1 ms (cached), 200 ms (live)
JOINs	Limited / client-side	Full server-side SQL
Aggregation	Limited	Full GROUP BY, HAVING, window functions
Subqueries / CTEs	Not supported	Full support
Concurrent queries	Serialized through QBFC	Concurrent (local cache)
Client compatibility	ODBC applications only	Any SQL client

Step 1: Install Syntra ODBC

Follow the Installation guide to install Syntra ODBC alongside your existing QODBC setup. Both can run simultaneously during migration.

Step 2: Update Connection Strings

QODBC Connection String (Before)

Driver={QODBC Driver for QuickBooks};DFQ=C:\path\to\company.QBW;

Syntra ODBC Connection String (After)

Driver={PostgreSQL Unicode};Server=localhost;Port=5433;Database=quickbooks;Uid=syntra;Pwd=yourpassword;

For non-ODBC clients (Power BI, Python, Node.js), use the native connection format. See Connection Strings.

Step 3: Update SQL Queries

Most standard SQL queries work without modification. Key differences:

Table Names

QODBC uses QuickBooks entity names directly. Syntra uses lowercase table names:

QODBC	Syntra ODBC
Customer	customers
Invoice	invoices
InvoiceLine	invoice_lines
Item	items
Vendor	vendors
Bill	bills
SalesReceipt	sales_receipts
Employee	employees

Column Names

Column names are similar but may differ in casing:

-- QODBC
SELECT CustomerRef_FullName FROM Invoice

-- Syntra ODBC
SELECT customer_ref_full_name FROM invoices

Run SHOW COLUMNS FROM tablename; to see exact column names.

JOINs

QODBC has limited JOIN support and often requires multiple separate queries. Syntra supports full JOINs:

-- QODBC: Two separate queries, joined in the application
-- Query 1: SELECT * FROM Invoice WHERE ...
-- Query 2: SELECT * FROM Customer WHERE ListID IN (...)

-- Syntra ODBC: Single query with JOIN
SELECT i.ref_number, c.name, i.balance_remaining
FROM invoices i
JOIN customers c ON i.customer_ref_list_id = c.list_id
WHERE i.txn_date >= '2025-01-01';

Aggregations

QODBC has limited aggregation support. Syntra supports full SQL aggregation:

-- Not possible with QODBC, works with Syntra
SELECT
    customer_ref_full_name,
    COUNT(*) AS InvoiceCount,
    SUM(balance_remaining) AS TotalOutstanding,
    AVG(balance_remaining) AS AvgInvoice
FROM invoices
WHERE is_paid = false
GROUP BY customer_ref_full_name
HAVING SUM(balance_remaining) > 1000
ORDER BY TotalOutstanding DESC;

Step 4: Update Refresh Logic

QODBC Approach

QODBC queries QuickBooks live on every request. Applications often implement their own caching or throttling.

Syntra Approach

Syntra caches data automatically with configurable staleness. You can remove any application-level caching logic:

-- Normal query: reads from cache (fast)
SELECT * FROM customers;

-- Force live data when needed
SET QB_MAX_STALENESS = 0;
SELECT * FROM customers;

Step 5: Handle Custom Fields

QODBC accesses custom fields through the DataExtRet mechanism. Syntra auto-discovers custom fields and exposes them as custom_* columns:

-- QODBC (complex DataExt query)
-- SP_REPORT ... DataExtName = 'Region' ...

-- Syntra ODBC
SELECT name, custom_region FROM customers;

See Custom Fields for details.

Step 6: Decommission QODBC

Once you have verified all your queries and applications work with Syntra ODBC:

1. Update all connection strings to use Syntra.
2. Remove any application-level caching logic that was needed for QODBC performance.
3. Uninstall the QODBC driver from ODBC Data Source Administrator.
4. Remove the QODBC software from Programs and Features.

Common Migration Issues

• Query returns different column names: Run SHOW COLUMNS FROM tablename; to see Syntra’s column names and update your queries.
• Missing table: Run SHOW TABLES; to see all available tables. Some QODBC pseudo-tables (reports, etc.) may not have direct equivalents.
• Performance difference on first query: The first query after Syntra starts may take longer while the initial cache sync completes. Subsequent queries are fast.