Knowledgebase
[QODBC-ALL] & [QXL- ALL] Stored Procedures Command List
Posted by Juliet (QODBC Support) on 28 July 2009 08:04 AM

[QODBC-ALL] & [QXL- ALL] Stored Procedures Command List

Applicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online] 

 

Stored Procedures

SP_COLUMNS table name

Instruction: Returns a recordset of the columns in the specified table.

Example: sp_columns Customer

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME COLUMNNAME TYPE TYPE NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS DEFAULT ...
QODBC   Customer ListID 12 VARCHAR 36 36 0   0   NULL ...
QODBC   Customer TimeCreated 11 TIMESTAMP 23 10 0   1   NULL ...
... ... ... ... ... ... ... ...

 

SP_FQSAVETOCACHEROLLBACK on|off

Instruction: Sets or clears whether the cache is backed up or reset if an error occurs when doing multiple inserts using the FQSaveToCache flag field. The off mode can help with performance if the rollback is not needed. The default is on.

Example: sp_fqsavetocacherollback off

Returns: This is an execute the command and does not return a recordset.

 

SP_LASTINSERTID

Instruction: Returns a recordset with one row (or multiple rows if done after a sp_batchupdate command) and two columns containing the last ListID or last TxnID from the last insert done and its error status on the current connection. The value is obtained from the return value of the previous insert (or inserts if done inside a sp_batchstart/sp_batchupdate block) performed on the same connection for that table.

Example: sp_lastinsertid Customer

Returns:

LastInsertId ErrorMessage
F3B0-1355557956  

 

SP_LASTINSERTIDRETURN  on|off

Instruction: Sets or clears whether the last insert ID is returned.

Example: sp_lastinsertidreturn off

Returns: This is an execute the command and does not return a recordset.

 

SP_OPTIMIZEFULLSYNC  tablename|ALL

Instruction: This command will reload the specified table from scratch. It helps ensure that the optimizer perfectly syncs with the QuickBooks company file. The ALL option will do all tables.

Example: sp_optimizefullsync Customer

Returns: This is an execute the command and does not return a recordset.

 

SP_OPTIMIZEUPDATESYNC tablename|ALL

Instruction: This command will synchronize the specified table with the QuickBooks data file using changed and deleted data. It helps ensure that the optimizer is up to date with the QuickBooks company file. The ALL option will do all tables.

Example: sp_optimizeupdatesync Customer

Returns: This is an execute the command and does not return a recordset.

 

SP_PRIMARYKEYStablename

Instruction: Returns a recordset of the primary key segments in the specified table.

Example: sp_primarykeys Customer

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME COLUMNNAME KEYSEQ PKNAME
QODBC   Customer ListID 1 Customer_PrimaryKey

 

SP_QBFILENAME

Instruction: This command returns the full path to the open QuickBooks company file. It returns one column, one-row record set.

Example: sp_qbfilename

Returns:

QBFileName
C:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Test Company Files\Medal Crest Homes, LLC.QBW

 

*Note: sp_report and other reports commands are unavailable for QODBC POS. QuickBooks POS SDK does not support reports, and thus it is not available in QODBC POS.

SP_REPORTtablename [show]

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

Returns:

Current_Title Amount_1_Title Amount_2_Title Amount_3_Title Amount_4_Title Amount_5_Title Label Current Amount_Count Amount_1 Amount_2 Amount_3 Amount_4 Amount_5
Current 1 – 30 31 – 60 61 – 90 >90 TOTAL 1456 Red Cloud Peak - Barbour 0.00 5 0.00 0.00 0.00 25671.35 25671.35
Current 1 – 30 31 – 60 61 – 90 >90 TOTAL 1463 Red Cloud Peak - MCH 0.00 5 0.00 0.00 0.00 29353.00 29353.00
... ... ... ... ... ... ... ... ... ... ... ... ... ...

 

SP_REPORTS

Instruction: Returns a recordset of the information of all reports.

Example: sp_reports  

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME TYPE NAME REMARKS
QODBC   APAgingDetail SP_REPORT Vendors & Payables->A/P Aging Detail
QODBC   APAgingSummary SP_REPORT Vendors & Payables->A/P Aging Summary
QODBC   ARAgingDetail SP_REPORT Customers & Receivables->A/R Aging Detail

 

SP_SPECIALCOLUMNS  tablename [ ROWVER ]|[ BEST_ROWID ]

Instruction: 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 COLUMNNAME TYPE TYPE NAME PRECISION LENGTH SCALE PSEUDO
2 ListID 12 VARCHAR 36 36 0 0

Example2: sp_specialcolumns Customer RowVer  

Returns:  

SCOPE COLUMNNAME TYPE TYPE NAME PRECISION LENGTH SCALE PSEUDO
2 EditSequence 12 VARCHAR 16 16 0 0

 

SP_TABLES  table name

Instruction: Returns a recordset with the list of tables available from the ODBC Driver.

Example: sp_tables

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME TYPE NAME REMARKS DELETEABLE VOIDABLE INSERT_ONLY
QODBC   Account TABLE Chart of 1 0 0
QODBC   AccountTaxLineInfo TABLE Account Tax 0 0 0
   

 

SP_CATEGORIES

Instruction: Returns a recordset with the list of the categories for all the tables.

Example: sp_categories

Returns:

CATEGORY NAME
Accounting & Taxes
Banking
Budgets
Company & Financial
Customers & Receivables
Employees & Payroll
Inventory
….

 

SP_CATEGORYTABLES

Instruction: Returns a recordset with the list of all the tables and the categories they belong to.

Example: sp_categorytables

Returns:

CATEGORY NAME TABLE NAME
Accounting & Taxes Account
Accounting & Taxes AccountTaxLineInfo
Accounting & Taxes Class
Accounting & Taxes journal entry
Accounting & Taxes JournalEntryCreditLine
Accounting & Taxes JournalEntryDebitLine
Accounting & Taxes JournalEntryLine
Accounting & Taxes SpecialAccount
Banking ReceivePaymentToDeposit
Banking SalesTaxPaymentCheck

 

SP_CATEGORYREPORTS

Instruction: Returns a recordset with the list of all the reports and the categories they belong to.

Example: sp_categoryreports

Returns:

CATEGORY NAME REPORT NAME
Accounting & Taxes AuditTrail
Accounting & Taxes GeneralLedger
Accounting & Taxes IncomeTaxDetail
Accounting & Taxes Journal
Accounting & Taxes TxnDetailByAccount
Accounting & Taxes TxnListByDate
Accounting & Taxes IncomeTaxSummary
Accounting & Taxes trial balance
Banking check details
Banking DepositDetail

 

SP_FOREIGNKEYS  table name table name

Instruction: Returns a recordset of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Returns:

PKQUALIFIERNAME PKOWNERNAME PKTABLENAME PKCOLUMNNAME FKQUALIFIERNAME FKOWNERNAME FKTABLENAME FKCOLUMNNAME KEYSEQ UPDATE RULE DELETE RULE FKNAME PKNAME SEVERABILITY
QODBC   customer ListID QODBC   Invoice CustomerRefListID 1 3 3 Invoice_Customer_Link Customer_PrimaryKey 7

 

SP_PARAMETERS

Instruction: Returns a recordset with the list of the detailed information of parameters of each report.

Example: sp_parameters

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME PARAMETER NAME TYPE TYPE NAME PRECISION LENGTH DEFAULT DATATYPE DATETIME_SUBTYPE VALUES VALUE_TYPE LOOKUP_VALUE LOOKUP_DISPAY_FILED DEFAULT_VALUE REMARKS ADVANCED
QODBC   APAgingDetail DateFrom 9 DATE 10 6   9 1 {d'yyyy-mm-dd'} Date       DateFrom 0
QODBC   APAgingDetail DateTo 9 DATE 10 6   9 1 {d'yyyy-mm-dd'} Date       DateTo 0
QODBC   APAgingDetail DateMacro 12 VARCHAR 4096 4096   12   |All|Today|ThisWeek|This…. FixedList       DateMacro 0
….

 

SP_STATISTICS  tablename

Instruction: Returns a recordset with the list of all the indexes and a list of statistical information of the specified table or indexed view.

Example: sp_statistics Customer

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME NONUNIQUE INDEXQUALIFIERNAME INDEXNAME TYPE SEQININDEX COLUMNNAME COLLATION CARDINALITY PAGES FILTER
QODBC   Customer 0   Customer_PrimaryKey 3 1 ListID A      
QODBC   Customer 1   Customer_TimeModified 3 1 TimeModified A      

 

Keywords: last insert id

(794 vote(s))
Helpful
Not helpful

Comments (3)
Brendy
20 September 2012 03:39 AM
At last, soemone comes up with the "right" answer!
Edward Dewsnap
19 January 2018 12:01 PM
Hello, I am new to this product and we just purchased the server license. My only objective with this software is to be able to run a simple query or execute a procedure that will return a list of items and their quantity on hand. I see it on Quickbooks under Item List but none of the inventory tables contain the value on hand value. Is this obtained by calculating data. Thanks
Jack
22 January 2018 10:57 AM
Hi Edward,

In case QuantityOnHand returning the null value, Please change the following value on the Optimizer tab and try again.

QODBC Setup screen --> Optimizer --> Under "For balances and stock quantities" select Either "Dirty Reads"/"Real Time"

Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2457/45/balancecurrentbalancequantityonhand-is-missing-value-or-showing-null-value

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).