[QODBC-Online] QODBC Online Setup Options
Posted by Jack - QODBC Support on 10 November 2016 02:27 PM
QODBC Online Setup Options
QODBC Online General Setup
The first step in configuring the driver is to click on the QODBC Online Setup Screen, and the following general setup screen will become available.
Server: Indicates where the QODBC's Connector/SocketReceiver should be running. The default is localhost (i.e., on the same machine)
Port: Indicates which port QODBC and the QODBC's Connector/SocketReceiver should be running. Default is 8383
Data Source Name: This is a display name for your QuickBooks data, such as QuickBooks Data, QBPRO, My QB Data, etc. (Default Setting is QuickBooks Online Data).
Import/Export Connection Key: If you want to connect the same company file from another, you can export the connection key from one machine & import it to another.
Sandbox Company (for testing only): used If you want to work with QuickBooks Online sandbox company.
Connect to QuickBooks: used to authorize QODBC Online at QuickBooks Online to access QuickBooks Online company data through QODBC Online.
Test Connection to QuickBooks Online will determine if you have a proper installation and settings with QuickBooks Online.
QODBC Online Messages Setup
Messages allow you to review message logs from QODBC Online regarding errors and issues communicating with QuickBooks Online, clear these messages, and review messages produced by the QuickBooks Online API interface.
Detail Tracing (Slow) creates a trace log of exactly what QODBC Online is doing in the background with the application and is used for debugging purposes. Ensure the log is turned off when not needed, as these can use up large amounts of disk space.
Display Optimizer Status Panel will display the progress of optimization on the data. This is especially helpful on large data files being reloaded in their entirety.
When selected, Display Driver Status Panel shows precisely what QODBC Online is doing when accessing the QuickBooks Online data and provides a good assessment of speed using optimized and non-optimized data.
Verbose Optimizer Status Panel causes the Optimizer panel to come up whenever any optimizer happens. The normal checkbox only pops up the optimizer when doing the full update or reloading all.
QODBC Online Optimizer Setup
Recommended settings for using QODBC Online are shown here; read below for a summary explanation of all available options.
The optimizer brings some data to a local cache to increase query retrieval performance. Check to activate the optimizer.
Optimizer Database Folder specifies where the local data store will be saved on disk. The default location %AppData%\QODBC Driver for QuickBooks Online\Optimizer is under your Windows login account name under your Documents and Settings folder. The data will be stored in a file in this folder with a name similar to the company file name with a .opt extension. If you store that data somewhere other than the default, click the browse button and select the destination folder. With very large company files, we suggest running synchronization after hours.
Keep my optimized data up to date as of when you choose the optimizer to synchronize itself with your Company data. Depending on how static your data need is and the size of the file, you can opt for various time frames of synchronization. The farther down the list you go, the less synchronization will occur, and your queries will run much faster but will be using less current data than the company files.
The start of every query is the default setting using the most current data. Use this default setting if your question requires the latest up-to-the-minute data and speed is not the primary focus. Please remember that the QODBC Optimizer is local to your computer only, so when other users change things in QuickBooks, how does QODBC know? The optimizer will default update new and revised entries in a table from QuickBooks and then execute the query against the locally optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.
The start of every new connection (with "Load Updated Data" first) is used if you don't need to update the optimized data while you are, for example, running queries or importing or exporting data. The optimized information is updated upon a new connection and left updated until the next new connection occurs again.
The start of every new connection (with "Reload All Data" first) is used if you need to rebuild optimized data from scratch before running important queries (like Sales Commission) or importing or exporting data. The optimized data is reconstructed upon a new connection and updated until the next new connection occurs again.
The end of yesterday was used to balance speed and data updating better and would be the best option for most users. It only has to synchronize each table once daily, and the data will be current at the end of the previous day.
The end-of-last-month option is a significant speed boost for running last month's or last year's queries. It does not need to synchronize again to get the data.
The last time I pressed one of the load data button options allowing you to control the synchronized timing. Synchronizing only occurs when you manually run it by pressing the Load Updated Data in the QODBC setup screen.
For balances and stock quantities: the option Nulls - will return NULLs for all fields that cannot be kept in sync, Dirty Reads - means return the most current value that was loaded into the optimizer cache, and Real-Time uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real Time is the previous method used).
Optimizing data after an Insert or Update causes QODBC to do Load Updated Data after an insert or update is executed on a table. Useful for programmers who want to test that their data has successfully been written to QuickBooks.
The multi-Table Sync option is unchecked by default. If checked, the QODBC Online optimizer will use other tables to re-sync tables. However, this will slow down performance. When off, all Multi-Table Sync fields become Real-Time fields.
Load Updated Data: This button is used to Synchronize your optimized data with your company file. This will only load the data that has changed since the last automatic or manual synchronization.
Reload All Data: This button is used to rebuild the optimized data from scratch completely. It is useful when starting a new company if you do not want to synchronize the tables as you first encounter them and ensure that your data is 100% current.
QODBC Online Advanced Setup
The selected defaults in the Advanced settings should apply to most users and usually don't need to be changed.
Maximum Field Name Length: is set to the QuickBooks Online limitation, but specific applications like Delphi cannot handle this limit and must be set accordingly.
Use Case Sensitive RefNumber Searches, when checked, means a RefNumber of 'ABC' and 'abc' will be different but means QuickBooks can use an index internally for a search instead of a complete table scan. (Note: this feature applies to versions of QuickBooks 2005 and higher)
Include Raw Files, if checked, will display both filtered and raw data formats. QODBC formats data into SQL field types more easily used in programming languages for math and comparison operations. The Raw tables return the data as it is returned to QODBC without modification.
ODBC Compatibility "Default" is 2.1. ODBC compatibility is for specific applications that require ODBC 2.1 Compatibility to use QODBC. Anything that uses the Java ODBC library, like ColdFusion, requires this option turned on, or recordsets will be returned with no row sets or only 1-row sets.
If you use Microsoft Excel Power Query or Microsoft Power BI, change the ODBC Compatibility to 3.8.
SQL Server Support will turn transaction support on so that SQL Server will allow updates when using QODBC as a linked server. QODBC Online still does not support transactions but simulates the turning on and off of transactions.
The Advanced tab in QODBC Setup has new defaults for Use Iterators and Iterator or Chunk Size fields. This allows you to turn off iterators. This may be needed on systems like MS-SQL Server and Web Servers that use multiple threads. Iterator or Chunk Size determines the size of the row sets that are retrieved from QuickBooks. Set this value high if you are exporting records from QuickBooks. The maximum Iterator or Chunk size that can be set is 100000 (more elevated than that, and it resets itself back to the default of 500).
Iterators and Iterator or Chunk Size fields allow you to turn off iterators. This is needed on MS SQL and Web Servers that use multiple threads.
Text Field Handling has been added to QODBC v7.00.00.204 and later. Convert Fields > 255 to LONG VARCHAR and Limit length to 4096 characters (default): These fields help some development environments get data from extended areas that would not be able to otherwise. Connection string options include ConvertToLongVARCHAR=Yes|No, and MaxLONGVARCHAR=4096.
Convert All Numerics to VARCHARS is helpful for loading tables in Access when Access chooses the wrong size numeric field sizes. Access will give an error "data too large for field DECIMAL." When you get this error, switching this option on will allow all the data to be loaded into Access as strings. The data can be converted as needed by your programs.
Activate is the online option for entering the CDKey issued to you upon purchasing the product to fully activate QODBC Online from the evaluation version to Standard Edition that has been purchased. Suppose you do not have internet access to activate the product. In that case, you can use the phone option to supply your reseller or distributor with the information to activate the product.
Deactivate is the online method of deactivating a currently registered version of QODBC Online to transfer the product to another machine. Suppose internet access is not available from the registered machine. In that case, you can use the phone option to supply your reseller or distributor with the information to deactivate the product CDKey for you. This process will take 24 to 48 hours before your CDKey account is credited with the deactivated license, and you can reactivate the license on a different machine.