QODBC.com Tools for QuickBooks

Search

             building menu display...

QODBC Quick Links
What is QODBC
Download
Buy Now
How to Install QODBC
Connecting to QuickBooks
Using QuickBooks Remotely
64 Bit Connections
QODBC Version Differences
QODBC Technical Reference
Tutorials
Support/FAQ
Reseller Program
OEM Program
        Go Back

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 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:

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'

Shared bottom Border

Did you find the answer you were looking for on this page? If not, please Contact Us. We are happy to help you find the solution you are looking for. Thanks!

Copyright © 2017 FLEXquarters North America Sales All rights reserved
QuickBooks and the Gold and Silver Developer Logos are trademarks and/or registered trademarks of Intuit Inc., displayed with permission. RSS What is RSS?