Matillion ETL Data Model for Netsuite
Aggregate columns are the columns that will appear on base tables which aggregate all of the data contained within child collections. Because these columns include all the data of a child collection, they can become very large. In some situations, such as writing the data to an offline database, it may be advisable to set AggregateColumnMode to either Ignore or List. The data in child tables can still be retrieved by setting IncludeChildTables to true. Setting AggregateColumnMode to List will still cause aggregate columns to be listed for use with inserts and updates.
|Ignore||All aggregate will be ignored and will not show up as available colums in the table definition.|
|List||Aggregate columns will be listed in all tables, but on base tables such as SalesOrders, they will not retrieve data from NetSuite.|
|ListAndRetrieve||Aggregate columns will be listed and requested on all tables.|
This field is used to authenticate to NetSuite. These IDs are different from application IDs created in versions 2015.1 and older. You can find your applications in the NetSuite UI under Setup > Integration > Manage Integrations. An application ID is set by default, but can optionally be specified.
When AutoCache is set, the driver maintains a cache in the background and executes queries against the local data by default. The driver incrementally updates the cache, retrieving only changesets instead of first dropping the cached tables and retrieving the entire dataset again. The driver can update the cache on an interval (the default) or when you execute a SELECT query to the remote NetSuite data.
This functionality gives you the option to query a persistent, up-to-date cache or execute real-time queries with a failover solution for connectivity loss.
By default, the driver replicates on an interval. You can use the CacheTolerance property to control how often the driver retrieves changesets from the NetSuite API.
the CacheTolerance functionality makes AutoCache a single setting to implement robust caching. CacheTolerance is also useful for caching if your application generates many queries in succession -- CacheTolerance prevents the driver from running a replication on every query, as is the case when CacheQueryResult is set.
CacheQueryResult is a way to query NetSuite in real time while maintaining a cache for offline use. Set CacheQueryResult to update the cache whenever you execute a SELECT statement. When you execute a SELECT statement with AutoCache and CacheQueryResult set, the driver executes the query to the remote data and persists the results; rows that already exist are overwritten. That is, SELECT statements are used to create and refresh the cache, not to query it. Data manipulation commands are executed to the remote data as well.
To query the cached data, set the Offline property. If you need to query the cached data in an online connection, you can append #CACHE to the table name. For example:
SELECT * FROM [Account#CACHE]
When AutoCache is set, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:
When this property is set, the driver builds a temporary, in-memory dataset that caches the results of the queries you execute. Result sets for subsequent queries are extracted from this dataset, if possible.
This property is useful in BI, analytics, and other tools that generate queries for you. In these tools, explicit cache queries with the #CACHE syntax may not be an option.
The cache database is determined based on the CacheDriver and CacheConnection properties. You will need at least the following information to connect to one of the caching databases below. To load the caching database's driver, refer to CacheDriver.
The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. The documentation for the underlying JDBC driver will have all of the available properties.
In most caching scenarios, you need to set only the following:
For other Java DB configurations, you will need to use CacheDriver and CacheConnection.
Below is the Derby JDBC URL syntax:
jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]For example, to cache to an in-memory database, use the following:
To cache to SQLite, you can use the SQLite JDBC driver. Below is the syntax of the JDBC URL:
The installation includes the CData JDBC Driver for MySQL. Below is an example JDBC URL:
jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cacheBelow are typical connection properties:
The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:
jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]For example:
jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=trueBelow are typical SQL Server connection properties:
To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.
Below is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:
jdbc:oracle:thin:scott/tiger@myhost:1521:orclBelow are typical connection properties:
Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).
Below is the JDBC URL syntax for the official PostgreSQL JDBC driver:
jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]For example, the following connection string connects to a database on the default host (localhost) and port (5432):
jdbc:postgresql:postgresBelow are typical connection properties:
You can cache to any database that you have a JDBC driver for, including CData JDBC drivers.
The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you would like to use to cache data.
Note that you must also add the CacheDriver JAR to the classpath.
The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.
The driver simplifies Java DB configuration. Java DB is the Oracle distribution of Derby. The JAR is shipped in the JDK. You can find the JAR, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath.
jdbc:netsuite:CacheLocation='c:/Temp/cachedir';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;To customize the Java DB JDBC URL, use CacheProvider and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:
jdbc:netsuite:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
Below is a JDBC URL for the SQLite JDBC driver:
jdbc:netsuite:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
Below is a JDBC URL for the included CData JDBC Driver for MySQL:
jdbc:netsuite:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;The CData JDBC Driver for MySQL is located in the lib subfolder of the CData JDBC Driver for NetSuite 2016 installation directory.
The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:
jdbc:netsuite:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
Below is a JDBC URL for the Oracle Thin Client:
jdbc:netsuite:Cache Driver=oracle.jdbc.driver.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
The following JDBC URL uses the official PostgreSQL JDBC driver:
jdbc:netsuite:CacheDriver=org.postgresql.Driver;CacheConnection='jdbc:postgresql://localhost:5433/postgres?user=postgres&password=admin';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR to the classpath. The JAR, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.
CacheLocation defaults to the directory specified by the Location setting.
As you execute queries with this property set, the relevant system tables in the NetSuite catalog are cached to the file store specified by CacheLocation.
Setting CacheMetadata and CacheLocation will retrieve metadata only once over multiple connections. For example, metadata will be retrieved only when a connection pool is initialized.
The driver automatically caches metadata in memory when you open a connection, so within the same connection schema discovery is only expensive on the first query. QueryCache only becomes useful when retrieving the system catalog on every connection open becomes expensive. For example, when you are working with large amounts of metadata or when you have many simultaneous, short-lived connections.
When CacheQueryResult and AutoCache is set, the entire result of every SELECT query is cached in the cache database. Note that the performance can be significantly impacted when using AutoCache when the NetSuite API does not support the REPLICATE Statements statement.
The tolerance for stale data in the cache specified in seconds. The driver will check with the data source for newer records after the tolerance interval has expired. Otherwise it will return the data directly from the cache.
When set to 'true', a connection will be made to NetSuite when the connection is opened. This property enables the 'Test Connection' feature available in various database tools.
This feature acts as a NOOP command as it is used to verify a connection can be made to NetSuite and nothing from this initial connection is maintained.
Setting this property to 'false' may provide performance improvements (depending upon the number of times a connection is opened).
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.
Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.
This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.
|TUNNEL||80||When this is set, the driver opens a connection to NetSuite and traffic flows back and forth through the proxy.|
|SOCKS4||1080||When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.|
|SOCKS5||1080||When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.|
If this is set to true, tables will be displayed for all child lists of a given entity. For instance, the CashRefund table in the NetSuite development environment has a child list called ItemList. Therefore, a new table called CashRefund_ItemList will be displayed if this property is set to true. This can be useful for listing each item in the list in its own row.
Setting this to true will cause custom fields to be displayed directly on tables as their own rows. However, it will cause lower performance when retrieving the table metadata information for the first time on an open connection. Table metadata is stored on the connection and cleared when the connection is closed.
Setting this to true will cause custom lists types to be included as their own tables. However, it will cause lower performance when retrieving the table metadata information for the first time on an open connection. Table metadata is stored on the connection and cleared when the connection is closed.
Setting this to true will cause custom record types to be included as their own tables. However, it will cause lower performance when retrieving the table metadata information for the first time on an open connection. Table metadata is stored on the connection and cleared when the connection is closed.
Many fields in NetSuite are references to other types of records. For instance, an Invoice might reference an Account and a Customer record. There are several pieces of data that can be returned by NetSuite when retrieving data from a record reference field. The available values are:
|InternalId||The NetSuite foreign key for the record reference.|
|ExternalId||An optional foreign key stored in a data source other than NetSuite associated with the record. ExternalIds are used for synchronizing NetSuite data with other data sources.|
|Name||A readable name for the record referenced.|
|Type||The type of record referenced. This is not always given a value as the given field may only have one type.|
The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The Location property is only needed if you would like to customize definitions (e.g., change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.
The schema files used in your application must be deployed with other assemblies. You must also ensure that Location points to the folder that contains the schema files. The folder location can be a relative path from the location of the executable.
For more control over what is written to the log file, take a look at Verbosity.
Most users in NetSuite will get one session they can use to connect to NetSuite Web Services at a time. If one attempts to establish a second session when an existing session is already established, NetSuite will throw an exception. While the CData JDBC Driver for NetSuite 2016 attempts to share sessions across threads for the same user, it is still possible to have an established session that prevents the driver from establishing a new one. For instance, the driver may have been forcibly terminated, resulting in a session that was never properly closed.
Setting this property to true will cause the driver to check for existing sessions when it attempts to create a new one. If an existing session is unrecognized by the driver, it will close it in order to establish its own session. It is recommended to only set this property to true if the driver is the only tool being used to connect to your user account via the web services.
Except under special circumstances, most NetSuite user accounts will be limited to one session at a time. This presents potential issues if executing requests across multiple threads as NetSuite will throw an exception if it detects that another session for the user is attempting to be created without closing the previous session. In most cases the existing session will be shared across multiple threads for the same user.
If the user has a SuiteCloud Plus license from NetSuite, then they do have access to concurrent Web services sessions. You can set the maximum number of concurrent sessions they have access to here. That number should be obtained from NetSuite.
A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. There is no limit by default. Values lower than 100kB will use 100kB as the value instead.
In order to avoid needless overhead, the driver downloads a number of metadata files from the NetSuite API. If NetsuiteMetadataFolder is not specified then the Location will be used. Always set a value for NetsuiteMetadata for best performance.
The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.
The access token is used in place of your username and password. The access token protects your credentials by keeping them on the server.
The OAuthAccessTokenSecret property is used to connect and authenticate using OAuth. The OAuthAccessTokenSecret is retrieved from the OAuth server as part of the authentication process. It is used with the OAuthAccessToken and can be used for multiple requests until it times out.
OAuth requires you to register your application. As part of the registration, you will receive a client Id, sometimes also called a consumer key, and a client secret. You must specify both the OAuthClientId and OAuthClientSecret to connect to an OAuth server.
OAuth requires you to register your application. As part of the registration you will receive a client Id and a client secret, sometimes also called a consumer secret. You must specify both the OAuthClientId and OAuthClientSecret to connect to an OAuth server.
When Offline is set to TRUE, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.
The Other property is a semicolon-separated list of name-value pairs used in connection parameters specific to a data source.
|CachePartial=True||Caches only a subset of columns, which you can specify in your query.|
|QueryPassthrough=True||Passes the specified query to the cache database instead of using the SQL parser of the driver.|
|SupportAccessLinkedMode||In Access' linked mode, it is generally a good idea to always use a cache as most data sources do not support multiple Id queries. However if you want to use the driver in Access but not in linked mode, this property must be set to False to avoid using a cache of a SELECT * query for the given table.|
|ConvertDateTimeToGMT||Whether to convert date-time values to GMT, instead of the local time of the machine.|
|RecordToFile=filename||Records the underlying socket data transfer to the specified file.|
|ClientCulture||This property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'.|
|Culture||This setting can be used to specify culture settings that determine how the driver interprets certain data types that are passed into the driver. For example, setting Culture='de-DE' will output German formats even on an American machine.|
The pagesize can control the number of results requested from NetSuite on a given query. Setting a higher pagesize will cause more data to come back in a given request, but may take longer to execute. Setting a smaller pagesize is generally recommended to ensure timeout exceptions do not occur.
Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.
The authentication type can be one of the following:
By default, the driver uses the system HTTP proxy. Set this to false if you want to connect to another proxy.
To connect to an HTTP proxy, see ProxyServer.
For other proxies, such as SOCKS or tunneling, see FirewallType.
This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.
If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.
If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.
For SOCKS 5 authentication or tunneling, see FirewallType.
By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.
The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.
If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.
By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.
This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:
|AUTO||Default setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.|
|ALWAYS||The connection is always SSL enabled.|
|NEVER||The connection is not SSL enabled.|
|TUNNEL||The connection is through a tunneling proxy: The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.|
You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the username of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a username in one of the following formats:
This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; i.e., "*=*".
The QueryCache property allows you to cache the results of a query in memory and use them until the cache expires. The cache expires after the seconds specified in this property. The in-memory query cache is shared across connections, so it can help with performance even if more than one connection is being used.
The cache manager for QueryCache will not only use the cache for exactly the same query, but also use the cache for queries that represent a subset of data in the cached query. For example, in the following queries, the cache created while executing Query A will be used to obtain the results for both Query B and Query C.
SELECT * FROM Account; // Query A SELECT * FROM Account WHERE Name LIKE '%John'; // Query B SELECT InternalId, Name FROM Account LIMIT 10; // Query C
Setting the QueryCache property can improve performance in the following scenarios:
When you need to execute the same or similar queries from multiple connections: With this property set, all connections share the same temporary cache instead of each executing a query to NetSuite. This can be useful in connection pooling.
When you need to query cached data with generated queries: Data access wizards and dashboard designers generate an underlying SQL query, which you may have a limited ability to modify. Typically, to pass through SQL to query a cache, you need to set Offline. Offline queries may be unacceptable if your BI and analytics need to reflect changes to the data in real time. But, slow networks or servers can result in reports with unacceptable lag.
QueryCache enables you to execute standard SQL against a local cache while remaining online to periodically refresh the cache.
Note that Offline and QueryCache do not provide an ad-hoc way to select a cached table or a remote table after opening a connection; the properties allow access to only one or the other. The #CACHE SQL syntax enables you to explicitly select a cached table, without going offline. Refer to Caching Explicitly for more information.
If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.
Memorized transactions in NetSuite are transactions that have been memorized for potentially being used again at a later time. They can be set up to recur on a regular basis or as a reminder to the user.
Together with User, Password, and AccountId this field is used to authenticate to NetSuite. If no RoleId is specified, the user's default role will be used. Otherwise, a RoleId may be obtained by logging into NetSuite, and navigating to the Setup -> Integration -> Web Services Preferences page. Here you can select a different Web Services Default Role as well as obtain the Id, displayed next to the specified role.
The RTK property may be used to license a build. Please see the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine will be rejected.
This property can take the forms:
|A full PEM Certificate (example shortened for brevity)||-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----|
|A path to a local file containing the certificate||C:\cert.cer|
|The public key (example shortened for brevity)||-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----|
|The MD5 Thumbprint (hex values can also be either space or colon separated)||ecadbdda5a1529c58a1e9e09828d70e4|
|The SHA1 Thumbprint (hex values can also be either space or colon separated)||34a929226ae0819f2ec14b4a3d904f801cbb150d|
If not specified, any certificate trusted by the machine will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).
Use QueryCache to cache memory for a given time in seconds or to disable in-memory caching.
Use QueryCache to cache in memory for a given time in seconds or to disable in-memory caching.
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.
If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition.
If Timeout expires and the operation is not yet complete, the driver throws an exception.
If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition. If Timeout expires and the operation is not yet complete, the driver throws an exception.
In NetSuite, operations can take a very long time to return if retrieving data from child tables or retrieving data from from a given table with AggregateColumnMode set to ListAndRetrieve. For instance, it is not unheard of for it to take in excess of 10 minutes to retrieve 1000 SalesOrders in a single request from NetSuite when AggregateColumnMode is set to ListAndRetrieve. Unfortunately, this is a limitation of the NetSuite Web Services with no known solution. If you need to work with aggregate columns or child tables, set Timeout to 0, set a small Pagesize, and select specific columns instead of everything.
NetSuite responses can be fairly slow, especially when inserting, updating, or deleting many records at a time. In these situations it is best to use asynchronous services to submit the data. Asynchronous services allow NetSuite to process the data while your application continues executing. The downside of using asynchronous services is that you will need to check the Job in NetSuite to see if NetSuite has finished processing the request and see if there were any issues.
NetSuite returns dates offset based on your user timezone preferences. This applies only to Dates, and not to Datetimes, which always return the same way no matter your preferences. We attempt a best effort to automatically correct for this and return dates as they would appear in the NetSuite UI. However, this is not 100% perfect due to how a few time zones are offset by 24 hours or greater depending on daylight savings time. If your user preferences are set to +13:00, +12:00, or -12:00, this value must be specified to get dates to show up correctly. For other offsets it is not required but recommended for best accuracy.
Set UseSandbox to true to use your sandbox instead of your live data.
If UseSessions is set to true, then user credentials will not be submitted on each request to NetSuite. Instead, a single Login will be attempted to establish the session, and from that point on, the session will be used on each subsequent request. This is a more secure method of communication.
In general, most NetSuite users can only establish a single session with a company file. The CData JDBC Driver for NetSuite 2016 is aware of this limitation and attempts to share the session for a given user across connections if multiple threads are active. However, if the same user is being used to connect from multiple machines or multiple different products, then there are likely to be connection problems. If your use case involves using the same user in these situations, it is better to set UseSessions to false and instead submit credentials on each request to avoid leaving sessions open beyond a single request at a time.
Upserts can be used to potentially update an existing record when inserting. NetSuite handles this by using the ExternalId on a given record. If you perform an insert when the ExternalId you specify exists in NetSuite, it will instead update the corresponding record. Otherwise an insert will take place. Set this value to false to always insert new records regardless.
The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:
|1||Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.|
|2||Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.|
|3||Setting Verbosity to 3 will additionally log the body of the HTTP requests.|
|4||Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.|
|5||Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.|
In most cases the version should not need to be changed. However, if you modify the version, please make sure beforehand that the version specified exists as part of the NetSuite API. If an incorrect version is specified, the driver will be unable to retrieve or update tables in NetSuite.
Listing the Views from some databases can be expensive. Providing a list of Views in the connection string improves the performance of the driver.
This is an override for the web services host if for some reason the host cannot be dynamically resolved. It intended as a last resort and should not normally need to be specified.
In most cases the web service host will be dynamically determined by using the NetSuite web service request that retrieves the correct web service host for your Account as well as the UseSandbox connection property. This will result in one extra request each time you create and establish a new connection. If you would like to avoid this extra request, you can instead set the WebServiceHost to override it. Note that specifying WebServiceHost will cause UseSandbox to be ignored.
The currently accepted web service hosts for NetSuite are the following: webservices.netsuite.com webservices.na1.netsuite.com webservices.sandbox.netsuite.com
Gets the consolidated exchange rate, by default for each month of the actual year . You can also filter the result by PeriodName (ex. WHERE PeriodName = 'Q2 2008') or by Period_InternalId (ex. WHERE Period_InternalId = '166') .
Consolidated exchange rates for every period:
SELECT * FROM ConsolidatedExchangeRate
Consolidated exchange rates for months appearing during and after the year 2010:
SELECT * FROM ConsolidatedExchangeRate WHERE StartYear = '2010'
Consolidated exchange rate for each month of a specific period, filtering by the period name:
SELECT * FROM ConsolidatedExchangeRate WHERE PeriodName = 'Q2 2008'
Consolidated exchange rate for each month of a specific period, filtering by parent internal_id:
SELECT * FROM ConsolidatedExchangeRate WHERE Parent_InternalId = '138'
Consolidated exchange rate for a specific month, filtering by period name:
SELECT * FROM ConsolidatedExchangeRate WHERE PeriodName = 'Mar 2007'
Consolidated exchange rate for a specific month, filtering by period internal_id:
SELECT * FROM ConsolidatedExchangeRate WHERE Period_InternalId = '166'
|CurrentRate||Double||Current actual exchange rate|
|AverageRate||Double||Average actual exchange rate|
|HistoricalRate||Double||Historical actual exchange rate|
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
Returns information about the custom fields in NetSuite. CustomFields may be retrieved by CustomFieldType, or by a combination of CustomFieldType and InternalId. If no CustomFieldType is specified, all custom fields will be returned.
|InternalId||String||The InternalId of the custom field.|
|ScriptId||String||The ScriptId of the custom field.|
|Label||String||The name of the custom field.|
|CustomFieldType||String||The type of custom field being requested.|
|AccessLevel||String||The level of access granted to interact with this custom field.|
|AvailableExternally||Boolean||A boolean indicating if this custom field is available externally.|
|CheckSpelling||Boolean||A boolean indicating if spell check is enabled for this custom field.|
|DefaultChecked||Boolean||A boolean indicating if this field is checked by default.|
|DefaultSelection_InternalId||String||The internalid of the default record for this custom field.|
|DefaultSelection_Name||String||The name of the default record for this custom field.|
|DefaultValue||String||The default value for this custom field.|
|Description||String||A description for the custom field.|
|DynamicDefault||String||The dynamic default value determined when an entity is created.|
|DisplayType||String||The way the column is displayed to a user on NetSuite.|
|FieldType||String||A boolean describing what type of data is stored in the custom field.|
|Help||String||Help information that comes up when you mouse over the custom field in NetSuite.|
|IsFormula||Boolean||A boolean indicating if the custom field is a formula.|
|IsMandatory||Boolean||A boolean indicating if the custom field is mandatory.|
|IsParent||Boolean||A boolean indicating if the custom field is a parent.|
|ItemSubType||String||What type of items this custom field is available for (purchase, sale, or both).|
|MaxLength||Long||The maximum length for the custom field if it is an integer or long.|
|MaxValue||Double||The maximum value for the custom field if it is a double.|
|MinValue||Double||The minimum value for the custom field if it is a double.|
|Owner_Name||String||The name of the owner of the custom field.|
|Owner_InternalId||String||The internalid of the owner of the custom field.|
|SelectRecordType_InternalId||String||The internalId of the type of record that may be selected for this custom field.|
|SelectRecordType_Name||String||The name of the type of record that may be selected for this custom field.|
|ShowInList||Boolean||A boolean indicating if this field should be shown in a listing.|
|ShowIssuesChanges||Boolean||A boolean indicating if NetSuite shows changes to this field on the Issue record.|
|StoreValue||Boolean||A boolean indicating if this field stores a value.|
|Subtab_Name||String||The name of the subtab this custom field may be found on.|
|Subtab_InternalId||String||The internal id of the subtab this custom field may be found on.|
|RecType_Name||String||The name of the record type this custom field is applicable for. Only valid for OtherCustomFields.|
|RecType_InternalId||String||The internalid of the record type this custom field is applicable for. Only valid for OtherCustomFields.|
|AppliesToAllItems||Boolean||A boolean indicating if this custom field applies to all items.|
|AppliesToCase||Boolean||A boolean indicating if this custom field applies to the support case record.|
|AppliesToCampaign||Boolean||A boolean indicating if this custom field applies to the campaign record.|
|AppliesToContact||Boolean||A boolean indicating if this custom field applies to the contact record.|
|AppliesToCustomer||Boolean||A boolean indicating if this custom field applies to the customer record.|
|AppliesToEmployee||Boolean||A boolean indicating if this custom field applies to the employee record.|
|AppliesToEvent||Boolean||A boolean indicating if this custom field applies to the event record.|
|AppliesToGiftCerts||Boolean||A boolean indicating if this custom field applies to gift certificates.|
|AppliesToGroup||Boolean||A boolean indicating if this custom field applies to groups.|
|AppliesToInventory||Boolean||A boolean indicating if this custom field applies to the inventory items.|
|AppliesToIssue||Boolean||A boolean indicating if this custom field applies to the issue record.|
|AppliesToItemAssembly||Boolean||A boolean indicating if this custom field applies to the item assemblies.|
|AppliesPerKeyword||Boolean||A boolean indicating if this custom field applies per keyword.|
|AppliesToKit||Boolean||A boolean indicating if this custom field applies to kit items.|
|AppliesToLots||Boolean||A boolean indicating if this custom field applies to all items lots.|
|AppliesToMfgProjectTask||Boolean||A boolean indicating if this custom field applies to manufacturing operation tasks.|
|AppliesToNonInventory||Boolean||A boolean indicating if this custom field applies to non inventory items.|
|AppliesToProject||Boolean||A boolean indicating if this custom field applies to the job record.|
|AppliesToOtherCharge||Boolean||A boolean indicating if this custom field applies to other charges.|
|AppliesToOtherName||Boolean||A boolean indicating if this custom field applies to other names records.|
|AppliesToPartner||Boolean||A boolean indicating if this custom field applies to the partner record.|
|AppliesToPhoneCall||Boolean||A boolean indicating if this custom field applies to the phone call record.|
|AppliesToProjectTask||Boolean||A boolean indicating if this custom field applies to the project task record.|
|AppliesToPriceList||Boolean||A boolean indicating if this custom field applies to the price list.|
|AppliesToSerialized||Boolean||A boolean indicating if this custom field applies to all serialized items|
|AppliesToService||Boolean||A boolean indicating if this custom field applies to service items.|
|AppliesToSolution||Boolean||A boolean indicating if this custom field applies to the solution record.|
|AppliesToStatement||Boolean||A boolean indicating if this custom field applies to the statement record.|
|AppliesToTask||Boolean||A boolean indicating if this custom field applies to the task record.|
|AppliesToWebSite||Boolean||A boolean indicating if this custom field applies to the website record.|
|AppliesToVendor||Boolean||A boolean indicating if this custom field applies to the vendor record.|
|BodyAssemblyBuild||Boolean||A boolean indicating if this custom field appears on the body of assembly build transactions.|
|BodyBom||Boolean||A boolean indicating if this custom field should be printed on on the body of a BOM.|
|BodyCustomerPayment||Boolean||A boolean indicating if this custom field appears on the body of customer payment transactions.|
|BodyDeposit||Boolean||A boolean indicating if this custom field appears on the body of deposit transactions.|
|BodyExpenseReport||Boolean||A boolean indicating if this custom field appears on the body of expense report transactions.|
|BodyInventoryAdjustment||Boolean||A boolean indicating if this custom field appears on the body of inventory adjustment transactions.|
|BodyItemFulfillment||Boolean||A boolean indicating if this custom field appears on the body of item fulfillment transactions.|
|BodyItemFulfillmentOrder||Boolean||A boolean indicating if this custom field appears on the body of item fulfillment order transactions.|
|BodyItemReceipt||Boolean||A boolean indicating if this custom field appears on the body of item receipt transactions.|
|BodyItemReceiptOrder||Boolean||A boolean indicating if this custom field appears on the body of item receipt order transactions.|
|BodyJournal||Boolean||A boolean indicating if this custom field appears on the body of journal transactions.|
|BodyOpportunity||Boolean||A boolean indicating if this custom field appears on the body of opportunity transactions.|
|BodyPickingTicket||Boolean||A boolean indicating if this custom field should be printed on the body of picking tickets.|
|BodyPrintFlag||Boolean||A boolean indicating if this custom field should be printed on the body of standard forms.|
|BodyPrintPackingSlip||Boolean||A boolean indicating if this custom field should be printed on the body of packing slips.|
|BodyPrintStatement||Boolean||A boolean indicating if this custom field should be printed on the body of statements.|
|BodyPurchase||Boolean||A boolean indicating if this custom field appears on the body of purchase transactions.|
|BodySale||Boolean||A boolean indicating if this custom field appears on the body of sale transactions.|
|BodyStore||Boolean||A boolean indicating if this custom field appears on the body of store transactions.|
|BodyTransferOrder||Boolean||A boolean indicating if this custom field appears on the body of transfer order transactions.|
|BodyVendorPayment||Boolean||A boolean indicating if this custom field appears on the body of vendor payment transactions.|
|ColAllItems||Boolean||A boolean indicating if this custom field appears in the item list for all items.|
|ColBuild||Boolean||A boolean indicating if this custom field appears in the item list for work order transactions.|
|ColExpense||Boolean||A boolean indicating if this custom field appears in the item list for expense transactions.|
|ColExpenseReport||Boolean||A boolean indicating if this custom field appears in the item list for expense report transactions.|
|ColGroupOnInvoices||Boolean||A boolean indicating if this custom field is grouped on invoices.|
|ColItemFulfillment||Boolean||A boolean indicating if this custom field appears in the item list for item fulfillment transactions.|
|ColItemFulfillmentOrder||Boolean||A boolean indicating if this custom field appears in the item list for item fulfillment order transactions.|
|ColItemReceipt||Boolean||A boolean indicating if this custom field appears in the item list for item receipt transactions.|
|ColItemReceiptOrder||Boolean||A boolean indicating if this custom field appears in the item list for item receipt order transactions.|
|ColJournal||Boolean||A boolean indicating if this custom field appears in the item list for journal transactions.|
|ColKitItem||Boolean||A boolean indicating if this custom field appears in the item list for kit / assembly components.|
|ColOpportunity||Boolean||A boolean indicating if this custom field appears in the item list for opportunity transactions.|
|ColPackingSlip||Boolean||A boolean indicating if this custom field should be printed on the item list for packing slips.|
|ColPickingTicket||Boolean||A boolean indicating if this custom field should be printed on the item list for picking tickets.|
|ColPrintFlag||Boolean||A boolean indicating if this custom field should be printed on the item list for standard forms.|
|ColPurchase||Boolean||A boolean indicating if this custom field appears in the item list for purchase transactions.|
|ColReturnForm||Boolean||A boolean indicating if this custom field should be printed on the item list for return forms.|
|ColSale||Boolean||A boolean indicating if this custom field appears in the item list for sale transactions.|
|ColStore||Boolean||A boolean indicating if this custom field applies to your web store items .|
|ColStoreHidden||Boolean||A boolean indicating if this custom field should be hidden on your web store transactions.|
|ColStoreWithGroups||Boolean||A boolean indicating if this custom field should apply to item groups.|
|ColTime||Boolean||A boolean indicating if this custom field appears in the item list for time transactions.|
|ColTimeGroup||Boolean||A boolean indicating if this custom field appears apply to time groups.|
|ColTransferOrder||Boolean||A boolean indicating if this custom field appears in the item list for transfer order transactions.|
|ItemsListAggregate||String||An aggregate of individual items this custom field applies to.|
|DeptAccessListAggregate||String||An aggregate of the departments that have access to the custom field.|
|RoleAccessListAggregate||String||An aggregate of roles that have access this custom field.|
|SubAccessListAggregate||String||An aggregate of the subsidiaries that have access to this custom field.|
|TranslationsListAggregate||String||An aggregage of translations for the custom field label and help.|
Returns information about the custom records in NetSuite.
|InternalId||String||The InternalId of the custom record.|
|ScriptId||String||The ScriptId of the custom record.|
|Name||String||The name of the custom record.|
|AccessType||String||The access type for the custom record.|
|AllowAttachments||Boolean||A boolean indicating if the custom record allows attachments.|
|AllowNumberingOverride||Boolean||A boolean for the allow override property fo the custom record.|
|AllowQuickSearch||Boolean||A boolean indiating if quick searches are enabled for the custom record.|
|Description||String||A description of the custom record.|
|Disclaimer||String||A disclaimer for the custom record.|
|EnableInlineEditing||Boolean||A boolean indicating if inline editing is enabled.|
|EnableMailMerge||Boolean||A boolean indicating if mail merge is enabled.|
|EnableNameTranslation||Boolean||A boolean indicating if name translations are enabled.|
|EnableNumbering||Boolean||A boolean indicating if numbering is enabled.|
|IncludeName||Boolean||A boolean idicating if the name field is included.|
|IsAvailableOffline||Boolean||A boolean indicating if the custom record is available offline.|
|IsInactive||Boolean||A boolean indicating if the record is inactive.|
|IsNumberingUpdateable||Boolean||A boolean indicating if the numbering number is updatable.|
|IsOrdered||Boolean||A boolean indicating if records are ordered.|
|NumberingCurrentNumber||Long||The current numbering number.|
|NumberingInit||Long||The initial numbering number.|
|NumberingMinDigits||Long||A minimum number of digits for the numbering number.|
|NumberingPrefix||String||A prefix for the numbering number.|
|NumberingSuffix||String||A suffix added to the numbering number.|
|Owner_internalid||String||The internalid of the owner of the custom record.|
|Owner_name||String||The name of the owner of the custom record.|
|ShowCreationDate||Boolean||A boolean indicating if the custom record should show the creation date.|
|ShowCreationDateOnList||Boolean||A boolean indicating if the custom record should show the creation date when listed.|
|ShowId||Boolean||A boolean indicating if the custom record should show the id.|
|ShowLastModified||Boolean||A boolean indicating if the custom record should show the last modified date.|
|ShowLastModifiedOnList||Boolean||A boolean indicating if the custom record should show the creation date when listed.|
|ShowNotes||Boolean||A boolean indicating if the custom record should show notes.|
|ShowOwner||Boolean||A boolean indicating if the custom record should show the owner.|
|ShowOwnerAllowChange||Boolean||A boolean indicating if the custom record should show if the owner allows changes.|
|ChildrenListAggregate||String||An aggregate of the children collection for the custom record.|
|FormsListAggregate||String||An aggregate of the forms collection for the custom record.|
|LinksListAggregate||String||An aggregate of the links collection for the custom record.|
|ManagersListAggregate||String||An aggregate of the managers collection for the custom record.|
|OnlineFormsListAggregate||String||An aggregate of the online forms collection for the custom record.|
|ParentsListAggregate||String||An aggregate of the parents collection for the custom record.|
|PermissionsListAggregate||String||An aggregate of the permissions collection for the custom record.|
|SublistsListAggregate||String||An aggregate of the sublists collection for the custom record.|
|TabsListAggregate||String||An aggregate of the tabs collection for the custom record.|
|TranslationsListAggregate||String||An aggregate of the translations collection for the custom record.|
Retrieves enumeration values for a given table. Will only return values if the TableName is specified. Only available for native tables within NetSuite and not Custom tables.
|ColumnName||String||The InternalId of the role.|
|TableName||String||The name of the table to retrieve enumeration value information from.|
|Values||String||The available values for the enumeration column.|
Returns the roles available for the authenticated user. This may include information for additional accounts the user is authorized to access.
|AccountId||String||The account the role is associated with.|
|RoleId||String||The InternalId of the role.|
|AccountName||String||The name of the account.|
|RoleName||String||The name of the role.|
Retrieves a posting transaction summary from NetSuite. This is the reporting mechanism available from NetSuite. All record reference columns may be used in filter using either either the = or IN operator. Other operator types are not available. For NetSuite OneWorld customers, ConvertToSubsidiary may also be specified as the subsidiary all response values should be converted to.
|OriginalAmount||Double||The original amount as returned by NetSuite in the currency of the subsidiary returned.|
|OriginalCurrency||String||The name of the original currency. Will only have a value if ConvertToSubsidiary is specified.|
|ConvertedAmount||Double||The amount converted to the currency of the subsidiary specified by ConvertToSubsidiary.|
|ConvertedCurrency||String||The name of the currency that has been converted to. Will only have a value if ConvertToSubsidiary is specified.|
|ConvertToSubsidiary||String||The subsidiary to convert to.|
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
|rows@next||String||This is a system property for controling paging.|
Returns the saved searches in NetSuite. A SearchType must be specified in order to retrieve saved searches.
|InternalId||String||The InternalId of the saved search.|
|Name||String||The name of the saved search.|
|SearchType||String||The type of search. This value must be specified when retrieving data.|
Returns the raw XML from a saved search.
|SavedSearchId||String||The InternalId of the saved search.|
|SearchType||String||The type of search being used.|
|ResponseXML||String||The ScriptId of the custom field.|
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
|Rows@Next||String||This is a system input used for paging. Please do not manually set the value.|
A view for transactions on NetSuite.
|InternalId [KEY]||String||The internalid of the transaction.|
|Type||String||The transaction type.|
|Account_InternalId||String||Internal id of the account.|
|Account_Name||String||Name of the account.|
|Amount||Double||Amount on the transaction.|
|BillingAddress||String||The billing address.|
|BillingAccount_InternalId||String||The billing account internal id.|
|BillingAccount_Name||String||The billing account name.|
|CloseDate||Datetime||The date closed.|
|CreatedDate||Datetime||The date created.|
|CreatedFrom_InternalId||String||Created from id.|
|Currency_InternalId||String||The currency id.|
|Currency_Name||String||The currency name.|
|CustomForm_InternalId||String||The custom form id.|
|CustomForm_Name||String||The custom form name.|
|DueDate||Datetime||The due date.|
|String||The email address.|
|EndDate||Datetime||The end date.|
|Entity_InternalId||String||The entity id.|
|Entity_Name||String||The entity name.|
|Entity_Type||String||The entity type.|
|ExchangeRate||Double||The exchange rate.|
|ExpectedCloseDate||Datetime||The expected close date.|
|ExternalId||String||The transaction external id.|
|Fob||String||The email address.|
|IntercoTransaction_InternalId||String||The intercompany transaction id.|
|Job_InternalId||Job||The job id.|
|Job_Name||Job||The job name.|
|LastModifiedDate||Datetime||The last modified date.|
|LeadSource_InternalId||String||The lead source id.|
|LeadSource_Name||String||The lead source name.|
|LeadSource_Type||String||The lead source type.|
|Location_InternalId||String||The location id.|
|Location_Name||String||The location name.|
|Partner_InternalId||String||The partner id.|
|Partner_Name||String||The partner name.|
|PnRefNum||String||The P/N Ref.|
|Probability||Double||The probability of a deal.|
|ProjectedAmount||Double||The projected amount.|
|PromoCode_InternalId||String||The promo code id.|
|PromoCode_Name||String||The promo code name.|
|SalesEffectiveDate||Datetime||The sales effective date.|
|SalesRep_InternalId||String||The sales rep id.|
|SalesRep_Name||String||The sales rep name.|
|StartDate||Datetime||The start date.|
|Terms_InternalId||String||The terms id.|
|Terms_Name||String||The terms name.|
|TranDate||Datetime||The transaction date.|
|TranId||String||The document number/id.|
|TransactionNumber||String||The transaction number.|
|SavedSearchId||String||Specifies the InternalId of the saved search. This is an alternative to supplying the criteria directly in the query. The search can instead be saved on NetSuite's end to perform more complicated operations than those available in the Web API.|
|rows@next||String||This is a system property that controls paging.|