Search              building menu display...

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'