QODBC Stored Procedures Reference

QODBC Stored Procedures Reference

This is a list of all of the SQL stored procedures supported by the QODBC Driver and their associated syntax..

QODBC Stored Procedures

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

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:

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:

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:

SP_REPORT – Similar to the SQL Keyword SELECT. Used to run built in QuickBooks reports. See http://www.qodbc.com/data, make your country selection then select the REPORTS link.

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:

Example:

sp_specialcolumns Customer RowVer

Returns:

SP_TABLES – Returns a recordset with the list of tables available from the ODBC Driver.

Example:

sp_tables

Returns:

 

SP_VOID – Used just like the SQL Keyword DELETE but voids the records requested.

Example:

sp_void Invoice where TxnID = ‘3FE5-1071508388’