About the Easysoft ODBC-QuickBooks Desktop Driver

The Easysoft ODBC-QuickBooks Desktop Driver provides real-time access to QuickBooks Desktop data from any application that supports ODBC.

In this section:

ODBC API and scalar functions
Data type mapping
SQL support
Inserting and updating QuickBooks Desktop data

ODBC API and scalar functions

API functions

Use this table to find out what ODBC API functions the Easysoft ODBC-QuickBooks Desktop Driver supports:

Function Status

Function	Status
`SQLAllocConnect`	Supported
`SQLAllocEnv`	Supported
`SQLAllocHandle`	Supported
`SQLAllocStmt`	Supported
`SQLBindCol`	Supported
`SQLBindParameter`	Supported
`SQLBrowseConnect`	Not supported
`SQLBulkOperations`	Not supported
`SQLCancel`	Supported
`SQLCloseCursor`	Supported
`SQLColAttribute`	Supported
`SQLColAttributes`	Supported
`SQLColumnPrivileges`	Supported
`SQLColumns`	Supported
`SQLConnect`	Supported
`SQLCopyDesc`	Supported
`SQLDisconnect`	Supported
`SQLDriverConnect`	Supported
`SQLDrivers`	Supported
`SQLEndTran`	Supported
`SQLError`	Supported
`SQLExecDirect`	Supported
`SQLExecute`	Supported
`SQLExtendedFetch`	Supported
`SQLFetch`	Supported
`SQLFetchScroll`	Supported
`SQLForeignKeys`	Supported
`SQLFreeConnect`	Supported
`SQLFreeEnv`	Supported
`SQLFreeHandle`	Supported
`SQLFreeStmt`	Supported
`SQLGetConnectAtt`	Supported
`SQLGetConnectOption`	Supported
`SQLGetCursorName`	Supported
`SQLGetData`	Supported
`SQLGetDescField`	Supported
`SQLGetDescRec`	Supported
`SQLGetDiagField`	Supported
`SQLGetDiagRec`	Supported
`SQLGetEnvAttr`	Supported
`SQLGetFunctions`	Supported
`SQLGetInfo`	Supported
`SQLGetStmtAttr`	Supported
`SQLGetStmtOption`	Supported
`SQLGetTypeInfo`	Supported
`SQLMoreResults`	Supported
`SQLNativeSql`	Supported
`SQLNumParams`	Supported
`SQLNumResultCols`	Supported
`SQLParamData`	Supported
`SQLParamOptions`	Supported
`SQLPrepare`	Supported
`SQLPrimaryKeys`	Supported
`SQLProcedureColumns`	Supported
`SQLProcedures`	Supported
`SQLPutData`	Supported
`SQLRowCount`	Supported
`SQLSetConnectAttr`	Supported
`SQLSetConnectOption`	Supported
`SQLSetCursorName`	Supported
`SQLSetDescField`	Supported
`SQLSetDescRec`	Supported
`SQLSetEnvAttr`	Supported
`SQLSetParam`	Supported
`SQLSetPos`	Supported
`SQLSetScrollOptions`	Supported
`SQLSetStmtOption`	Supported
`SQLSetStmtAttr`	Supported
`SQLStatistics`	Supported
`SQLTablePrivileges`	Supported
`SQLTables`	Supported
`SQLTransact`	Supported

Supported

Supported

Supported

Supported

Supported

Supported

Not supported

Not supported

Supported

Supported

Supported

Supported

SQLColumnPrivileges

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

SQLGetConnectOption

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Supported

SQLProcedureColumns

Supported

Supported

Supported

Supported

Supported

SQLSetConnectOption

Supported

Supported

Supported

Supported

Supported

Supported

Supported

SQLSetScrollOptions

Supported

Supported

Supported

Supported

Supported

Supported

Supported

Scalar functions

The Easysoft ODBC-QuickBooks Desktop Driver supports a number of scalar functions:

String functions
Numeric functions
Time, date, and interval functions
System functions
Conversion functions

Use either the SQL-92 or ODBC syntax with scalar functions. For example:

SELECT
  Invoice_Id,
  Customer_Name,
  EXTRACT(YEAR FROM Due_Date) as "Year"
FROM
  Invoice

SELECT
  Invoice_Id,
  Customer_Name,
  {fn EXTRACT(YEAR FROM Due_Date)} as "Year"
FROM
  Invoice

String functions

The Easysoft ODBC-QuickBooks Desktop Driver supports these string functions:

ASCII(string_exp)
BIT_LENGTH(string_exp)
CHAR(code)
CHAR_LENGTH(string_exp)
CHARACTER_LENGTH(string_exp)`
CONCAT(string_exp1, string_exp2)
DIFFERENCE(string_exp1, string_exp2)
INSERT(string_exp1, start, length, string_exp2)
LCASE(string_exp)
LEFT(string_exp, count)
LENGTH(string_exp)
LOCATE(string_exp1, string_exp2[,start])
LTRIM(string_exp)
OCTET_LENGTH(string_exp)
POSITION(char_exp IN char_exp)
REPEAT(string_exp, count)
REPLACE(string_exp1, string_exp2, string_exp3)
RIGHT(string_exp, count)
RTRIM(string_exp)
SOUNDEX(string_exp)
SPACE(count)
UCASE(string_exp)

Numeric functions

The Easysoft ODBC-QuickBooks Desktop Driver supports these numeric functions:

ABS(numeric_exp)
ACOS(float_exp)
ASIN(float_exp)
ATAN(float_exp)
CEILING(numeric_exp)
COS(float_exp)
COT(float_exp)
DEGREES(numeric_exp)
EXP(float_exp)
FLOOR(numeric_exp)
LOG(float_exp)
LOG10(float_exp)
MOD(integer_exp1, integer_exp2)
PI()
POWER(numeric_exp, integer_exp)
RADIANS(numeric_exp)
RAND([integer_exp])
ROUND(numeric_exp, integer_exp)
SIGN(numeric_exp)
SIN(float_exp)
SQRT(float_exp)
TAN(float_exp)
TRUNCATE(numeric_exp, integer_exp)

Time, date, and interval functions

The Easysoft ODBC-QuickBooks Desktop Driver supports these time, date, and interval functions:

CURRENT_DATE()
CURRENT_TIME[(time-precision)]
CURRENT_TIMESTAMP[(timestamp-precision)]
DAYNAME(date_exp)
DAYOFMONTH(date_exp)
DAYOFWEEK(date_exp)
DAYOFYEAR(date_exp)
EXTRACT(extract-field FROM extract-source)
HOUR(time_exp)
MINUTE(time_exp)
MONTH(date_exp)
MONTHNAME(date_exp)
NOW()
QUARTER(date_exp)
SECOND(time_exp)
TIMESTAMPADD(interval, integer_exp, timestamp_exp)
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)
WEEK(date_exp)
YEAR(date_exp)

System functions

The Easysoft ODBC-QuickBooks Desktop Driver supports these system functions:

DATABASE()
USER()

Conversion functions

The Easysoft ODBC-QuickBooks Desktop Driver supports supports both the SQL-92 CAST function and the ODBC CONVERT function for conversion between compatible data types.

Data type mapping

The Easysoft ODBC-QuickBooks Desktop Driver maps QuickBooks Desktop data types to ODBC data types in this way:

QuickBooks Desktop data type ODBC data type

QuickBooks Desktop data type	ODBC data type
`LONG NVARCHAR`	`SQL_WLONGVARCHAR`
`NATIONAL CHARACTER VARYING`	`SQL_WVARCHAR`
`NATIONAL CHARACTER`	`SQL_WCHAR`
`BIT`	`SQL_BIT`
`TINYINT`	`SQL_TINYINT`
`BIGINT`	`SQL_BIGINT`
`LONG VARBINARY`	`SQL_LONGVARBINARY`
`VARBINARY`	`SQL_VARBINARY`
`BINARY`	`SQL_BINARY`
`LONG VARCHAR`	`SQL_LONGVARCHAR`
`CHARACTER`	`SQL_CHAR`
`NUMERIC`	`SQL_NUMERIC`
`DECIMAL`	`SQL_DECIMAL`
`INTEGER`	`SQL_INTEGER`
`INTEGER AUTONUMBER`	`SQL_INTEGER`
`SMALLINT`	`SQL_SMALLINT`
`FLOAT`	`SQL_FLOAT`
`REAL`	`SQL_REAL`
`DOUBLE PRECISION`	`SQL_DOUBLE`
`CHARACTER VARYING`	`SQL_VARCHAR`
`DATE`	`SQL_TYPE_DATE`
`TIME`	`SQL_TIME`
`TIMESTAMP`	`SQL_TYPE_TIMESTAMP`
`INTERVAL YEAR`	`SQL_INTERVAL_YEAR`
`INTERVAL MONTH`	`SQL_INTERVAL_MONTH`
`INTERVAL DAY`	`SQL_INTERVAL_DAY`
`INTERVAL HOUR`	`SQL_INTERVAL_HOUR`
`INTERVAL MINUTE`	`SQL_INTERVAL_MINUTE`
`INTERVAL SECOND`	`SQL_INTERVAL_SECOND`
`INTERVAL DAY TO HOUR`	`SQL_INTERVAL_DAY_TO_HOUR`
`INTERVAL DAY TO MINUTE`	`SQL_INTERVAL_DAY_TO_MINUTE`
`INTERVAL DAY TO SECOND`	`SQL_INTERVAL_DAY_TO_SECOND`
`INTERVAL HOUR TO MINUTE`	`SQL_INTERVAL_HOUR_TO_MINUTE`
`INTERVAL HOUR TO SECOND`	`SQL_INTERVAL_HOUR_TO_SECOND`
`INTERVAL MINUTE TO SECOND`	`SQL_INTERVAL_MINUTE_TO_SECOND`

LONG NVARCHAR

SQL_WLONGVARCHAR

NATIONAL CHARACTER VARYING

SQL_WVARCHAR

NATIONAL CHARACTER

SQL_WCHAR

BIT

SQL_BIT

TINYINT

SQL_TINYINT

BIGINT

SQL_BIGINT

LONG VARBINARY

SQL_LONGVARBINARY

VARBINARY

SQL_VARBINARY

BINARY

SQL_BINARY

LONG VARCHAR

SQL_LONGVARCHAR

CHARACTER

SQL_CHAR

NUMERIC

SQL_NUMERIC

DECIMAL

SQL_DECIMAL

INTEGER

SQL_INTEGER

INTEGER AUTONUMBER

SQL_INTEGER

SMALLINT

SQL_SMALLINT

FLOAT

SQL_FLOAT

REAL

SQL_REAL

DOUBLE PRECISION

SQL_DOUBLE

CHARACTER VARYING

SQL_VARCHAR

DATE

SQL_TYPE_DATE

TIME

SQL_TIME

TIMESTAMP

SQL_TYPE_TIMESTAMP

INTERVAL YEAR

SQL_INTERVAL_YEAR

INTERVAL MONTH

SQL_INTERVAL_MONTH

INTERVAL DAY

SQL_INTERVAL_DAY

INTERVAL HOUR

SQL_INTERVAL_HOUR

INTERVAL MINUTE

SQL_INTERVAL_MINUTE

INTERVAL SECOND

SQL_INTERVAL_SECOND

INTERVAL DAY TO HOUR

SQL_INTERVAL_DAY_TO_HOUR

INTERVAL DAY TO MINUTE

SQL_INTERVAL_DAY_TO_MINUTE

INTERVAL DAY TO SECOND

SQL_INTERVAL_DAY_TO_SECOND

INTERVAL HOUR TO MINUTE

SQL_INTERVAL_HOUR_TO_MINUTE

INTERVAL HOUR TO SECOND

SQL_INTERVAL_HOUR_TO_SECOND

INTERVAL MINUTE TO SECOND

SQL_INTERVAL_MINUTE_TO_SECOND

Finding out more about data types on Windows

If you need more information about a data types, for example, the precision and scale, use Microsoft’s ODBC Test to do this.

Download the version of ODBC Test that matches your application’s architecture from:

https://www.easysoft.com/ftp/pub/utils/windows/odbc-test/
Copy both files to a folder on the machine where Easysoft ODBC-QuickBooks Desktop Driver is installed.
Double-click odbcte32.exe.
Select Con > Full Connect.
Choose your Easysoft ODBC-QuickBooks Desktop Driver data source from the list.
Choose Catalog > SQLGetTypeInfo.
Either choose SQL_ALL_TYPES=0 (1.0) or a specific data type from the DataType list.
Choose Results > Get Data All.

SQL support

The Easysoft ODBC-QuickBooks Desktop Driver supports these SQL statements, clauses, and operators:

SELECT
SELECT DISTINCT
WHERE
ORDER BY
AND
OR
NOT
INSERT INTO
NULL
UPDATE
DELETE
TOP
MIN
MAX
COUNT
SUM
AVG
LIKE
WILDCARDS
IN
BETWEEN
ALIASES
JOINS
UNION
GROUP BY
HAVING
EXISTS
CASE

Inserting and updating QuickBooks Desktop data

If the Easysoft ODBC-QuickBooks Desktop Driver implements a RQUseCachedID column for a table, a cache that enables requests to be batched together is available for that table.

The cache is committed or rolled back when an end transaction is sent. If your application allows you to turn autocommit off, you can execute several INSERT or UPDATE statements in one batch. For example:

-- AUTOCOMMIT OFF

-- BEGIN TRANSACTION
INSERT INTO BillItemLine ("VendorRef_listID", "RefNumber", "ItemGroupLine_ItemGroupRef_ListID" ) VALUES ('8000007A-1356973501', 666, '80000089-1349127814' )
INSERT INTO BillItemLine ("ItemGroupLine_ItemGroupRef_ListID", "RQUSeCachedID" ) values ('80000088-1349127627', 1 )
INSERT INTO BillItemLine ("ItemLine_ItemRef_ListID", "RQUSeCachedID" ) VALUES ('8000001A-1356973474', 1 )
-- END TRANSACTION

Each time a new record is inserted or a record is updated, the relevant ID (ListID or TxnID) is added to the cache along with the latest edit sequence.

When autocommit is on, RQUseCachedID or TxnID or ListID can be used to insert or update records, however each INSERT or UPDATE is a separate transaction.