SuiteQL
The following feature was introduced on NetSuite in 2020.1. Your Version must be set to 2020.1 or higher to use it. In addition, you must enable the feature for it in NetSuite under Company -> Enable Features -> SuiteCloud -> REST Web Services.
The driver supports this connection by the Schema connection property. Simply set Schema to SuiteQL to access the service.
Differences between SuiteTalk and SuiteQL
SuiteQL offers an Oracle syntax to query data from NetSuite. The default service supported in SuiteTalk is instead designed using a number of SOAP requests to retrieve data in a format that is more closely associated with the saved searching in NetSuite. This means that SuiteQL can be used to handle much more complicated queries that SuiteTalk. For instance, joins, aggregations, group by, order by, and formulas can all be handled server side using SuiteQL. In contrast, SuiteTalk only supports some joins and predicates. In general, SuiteQL is a more powerful option for executing queries faster against NetSuite due to its more rich support for handling SQL.
Metadata Handling
Metadata in SuiteQL is handled in two ways: Custom Fields / Columns, and Native tables / column. Custom fields and columns contain a metadata service that can be used to retrieve data from. So when IncludeCustomFieldColumns and IncludeCustomRecordTables are set to true, this metadata may be retrieved automatically and accurately.
NetSuite does not contain an API method of returing metadata about native tables and columns. Therefore, native tables such as the account table must have their metadata hardcoded within the driver. Oddly, NetSuite will throw exceptions from the API indicating there was a problem with the submitted SuiteQL when your account or role do not have permissions to view a given table or column, even if it is defined within SuiteQL. To try and avoid these exceptions, we have added a RowScanDepth connection property. This defaults to 1000, which is one page worth of data. Leaving this enabled will ensure the listed columns for tables are available for you on your connection. However, SuiteQL does not return fields when they are null. So it is possible some fields will not show up that you require access to. You may disable this option by setting RowScanDepth to 0, but will need to account for potentially listing columns that SuiteQL will throw exceptions when you try to retrieve data from them.
Limitations of SuiteQL
As a releatively new feature in NetSuite and the CData JDBC Driver for NetSuite in general, SuiteQL contains a number of limitations. These include:
- No support for INSERT / Update / Delete. Only SELECT queries are supported.
- Only OAuth / Token Based Authentication is supported. The SuiteQL REST API does not support user/password authentication.
- Not all Oracle formulas are supported in SuiteQL.
Supported Formulas
The following lists the available formulas in SuiteQL as of version 2020.1.
- ABS
- ACOS
- ADD_MONTHS
- APPROX_COUNT_DISTINCT
- ASCII
- ASCIISTR
- ASIN
- ATAN
- ATAN2
- AVG
- BFILENAME
- BITAND
- CEIL
- CHARTOROWID
- CHR
- COALESCE
- COMPOSE
- CONCAT
- CORR
- CORR_K
- CORR_S
- COS
- COSH
- COUNT
- COVAR_POP
- COVAR_SAMP
- CURRENT_DATE
- CURRENT_TIMESTAMP
- DECODE
- DECOMPOSE
- DENSE_RANK
- EMPTY_BLOB
- EMPTY_CLOB
- EXP
- EXPR
- FLOOR
- FROM_TZ
- GREATEST
- INITCAP
- INSTR
- LAST_DAY
- LEAST
- LENGTH
- LENGTH2
- LENGTH4
- LENGTHB
- LENGTHC
- LN
- LOCALTIMESTAMP
- LOG
- LOWER
- LPAD
- LTRIM
- MAX
- MEDIAN
- MIN
- MOD
- MONTHS_BETWEEN
- NANVL
- NEW_TIME
- NEXT_DAY
- NLSSORT
- NLS_INITCAP
- NLS_LOWER
- NLS_UPPER
- NULLIF
- NVL
- NVL2
- ORA_HASH
- POWER
- RANK
- REGEXP_INSTR
- REGEXP_REPLACE
- REGEXP_SUBSTR
- REMAINDER
- REPLACE
- ROUND
- ROW_NUMBER
- RPAD
- RTRIM
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- SUBSTR
- SUM
- SYS_EXTRACT_UTC
- TAN
- TANH
- TO_BINARY_DOUBLE
- TO_BINARY_FLOAT
- TO_CHAR
- TO_CLOB
- TO_DATE
- TO_MULTI_BYTE
- TO_NCHAR
- TO_NCLOB
- TO_NUMBER
- TO_SINGLE_BYTE
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TRANSLATE
- TRUNC
- TZ_OFFSET
- UNISTR
- UPPER
- VSIZE
- WIDTH_BUCKET