QODBC Stored Procedures Reference
This is a list of most of the SQL stored procedures supported by the QODBC Driver and their associated syntax..
SP_BATCHCLEAR tablename - Clears any cached batch statements for the given table. This is an execute query and does not return a recordset.
Example: sp_batchclear Customer
SP_BATCHSTART tablename - Starts batch mode inserts/updates for a given table. All inserts or updates issued after this statement on this table will be cached and not transmitted to QuickBooks until the SP_BATCHUPDATE command is issued. This is an execute command and does not return a recordset.
Example: sp_batchstart Customer
SP_BATCHUPDATE tablename - Submits a batch of previously cached inserts/updates for a given table. All inserts or updates issued since the sp_batchstart statement will be transmitted to QuickBooks. Use sp_lastinsertID to retrieve the status of the batch update. This is an execute command and does not return a recordset.
Example: sp_batchupdate Customer
Complete example:
BatchStart
sp_batchstart InvoiceLine
BatchInsert1
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch1', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0)
BatchInsert2
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'Batch2', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0)
BatchUpdate
sp_batchupdate InvoiceLine
SP_CLEARLASTINSERTID tablename - Clears all data related to the last insert and sp_batch commands. This is an execute command and does not return a recordset.
Example: sp_clearlastinsertid Customer
SP_COLUMNS tablename - Returns a recordset of the columns in the specified table.
Example: sp_columns Customer
Returns:
|
TABLE_CAT
|
QODBC
|
QODBC
|
|
TABLE_SHEM
|
|
|
|
TABLE_NAME
|
Customer
|
Customer
|
|
COLUMN_NAME
|
ListID
|
TimeCreated
|
|
DATA_TYPE
|
12
|
11
|
|
TYPE_NAME
|
VARCHAR
|
TIMESTAMP
|
|
COLUMN_SIZE
|
36
|
23
|
|
BUFFER_LENGTH
|
36
|
16
|
|
DECIMAL_DIGITS
|
0
|
0
|
|
NUM_PREC_RADIX
|
|
|
|
NULLABLE
|
0
|
1
|
|
REMARKS
|
|
|
|
COLUMN_DEF
|
NULL
|
NULL
|
|
SQL_DATA_TYPE
|
12
|
9
|
|
SQL_DATETIME_SUB
|
|
3
|
|
CHAR_OCTET_LENGTH
|
36
|
|
|
ORDINAL_POSITION
|
0
|
1
|
|
IS_NULLABLE
|
NO |
YES |
| QUERYABLE |
1 |
1 |
| UPDATEABLE |
1 |
1 |
| INSERTABLE |
0 |
0 |
| REQUIRED_ON_INSERT |
0 |
0 |
|
FORMAT
|
%s
|
CCCC-MM-DDThh:mm:ssZ
|
|
RELATES_TO
|
|
|
|
JUMPIN_TYPE
|
ListID
|
|
|
CUSTOM_FIELD_NAME
|
|
|
SP_FQSAVETOCACHEROLLBACK on|off - Sets or clears whether the cache is backed up or reset if an error occurs when doing multiple inserts using FQSaveToCache flag field. The default is on. The off mode can help with performance if the rollback is not needed. This is an execute command and does not return a recordset.
Example: sp_fqsavetocacherollback off
SP_LASTINSERTID - Returns a recordset with one row (or multiple rows if done after an sp_batchupdate command) and two columns containing the last ListID or last TxnID from the last insert done and it error status on the current connection. The value is obtained from the return value of the last insert (or inserts if done inside an sp_batchstart/sp_batchupdate block) performed on the same connection for that table.
Example: sp_lastinsertid Customer
Returns:
| LastInsertID |
ErrorMessage
|
| 4A0000-1071523027 |
|
SP_OPTIMIZEFULLSYNC tablename|ALL - This command will reload the specified table from scratch. It is useful for making sure that the optimizer is in perfect sync with the QuickBooks company file. The ALL option will do all tables. This is an execute command and does not return a recordset.
Example: sp_optimizefullsync Customer
SP_OPTIMIZEUPDATESYNC tablename|ALL - This command will synchronize the specified table with the QuickBooks data file using changed and deleted data. It is useful for making sure that the optimizer is up to date with the QuickBooks company file. The ALL option will do all tables. This is an execute command and does not return a recordset.
Example: sp_optimizeupdatesync Customer
SP_PRIMARYKEYS tablename - Returns a recordset of the primary key segments in the specified table.
Example: sp_primarykeys Customer
Returns:
|
TABLE_CAT
|
TABLE_SCHEM
|
TABLE_NAME
|
COLUMN_NAME
|
KEY_SEQ
|
PK_NAME
|
|
QODBC
|
|
Customer
|
ListID
|
1
|
Customer_PrimaryKey
|
SP_QBFILENAME - This command returns the full path to the open QuickBooks company file. It returns a one column, one row recordset.
Example: sp_qbfilename
Returns:
| QBFileName |
| C:\Program Files (x86)\Intuit\QuickBooks Enterprise Solutions 6.0\sample_product-based business.qbw |
SP_REPORT - Similar to the SQL Keyword SELECT. Used to run built in QuickBooks reports.
Example: sp_report ARAgingSummary show Current_Title, Amount_Title, Text, Label, Current, Amount parameters DateMacro = 'Today', AgingAsOf = 'Today'
SP_SPECIALCOLUMNS tablename [ROWVER]|[BEST_ROWID] - Returns a recordset of the special columns. RowVer describes which column holds the row ID and Best_RowID describes the column that is the row identifier. Best_RowID is the default if neither is specified.
Example: sp_specialcolumns Customer Best_RowID
Returns:
|
SCOPE
|
COLUMN_NAME
|
DATA_TYPE
|
TYPE_NAME
|
COLUMN_SIZE
|
BUFFER_LENGTH
|
DECIMAL_DIGITS
|
PSEUDO_COLUMN
|
|
2
|
ListID
|
12
|
VARCHAR
|
36
|
36
|
0
|
0
|
Example: sp_specialcolumns Customer RowVer
Returns:
|
SCOPE
|
COLUMN_NAME
|
DATA_TYPE
|
TYPE_NAME
|
COLUMN_SIZE
|
BUFFER_LENGTH
|
DECIMAL_DIGITS
|
PSEUDO_COLUMN
|
|
2
|
EditSequence
|
12
|
VARCHAR
|
16
|
16
|
0
|
0
|
SP_TABLES - Returns a recordset with the list of tables available from the ODBC Driver.
Example: sp_tables
Returns:
|
TABLE_CAT
|
TABLE_SCHEM
|
TABLE_NAME
|
TABLE_TYPE
|
REMARKS
|
DELETEABLE
|
VOIDABLE
|
|
QODBC
|
|
Account
|
TABLE
|
Account
|
1
|
0
|
|
QODBC
|
|
Bill
|
TABLE
|
Bill
|
1
|
1
|
|
...
|
|
|
|
|
|
|
SP_VOID - Used just like the SQL Keyword DELETE but voids the records requested.
Example: sp_void from Invoice where TxnID = '3FE5-1071508388'
| 
