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:

TABLE_CAT

TABLE_SHEM


TABLE_NAME


COLUMN_NAME


DATA_TYPE


TYPE_NAME

COLUMN_SIZE

BUFFER_LENGTH

DECIMAL_DIGITS


NUM_PREC_RADIX


NULLABLE


REMARKS


COLUMN_DEF


SQL_DATA_TYPE


SQL_DATETIME_SUB


CHAR_OCTET_LENGTH


ORDINAL_POSITION


IS_NULLABLE

QUERYABLE UPDATEABLE INSERTABLE REQUIRED_ON_INSERT

FORMAT

RELATES_TO

JUMPIN_TYPE

CUSTOM_FIELD_NAME

QODBC

Customer

ListID

12

VARCHAR

36

36

0

0

NULL

12

36

0

NO 1 1 0 0

%s

ListID

QODBC

Customer

TimeCreated

11

TIMESTAMP

23

16

0

1

NULL

9

3

1

YES 1 1 0 0 CCCC-MM-DDThh:mm:ssZ

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. See https://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:

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 Invoice where TxnID = ‘3FE5-1071508388’