The driver can model RESTlets as views. This section shows how to use the included RESTlet script to query saved searches, including how to fine-tune the table schemas to represent your saved searches more closely.

Using RESTlets vs. the SavedSearches View

Using RESTlets is the recommended way to query saved searches. Using RESTlets is different than the method described in Saved Searches. This older method relies on the SuiteTalk API, which is more limited in what saved searches may return.

Using RESTlets to Query Saved Searches

Follow the steps below to deploy the included script and execute the RESTlet by executing a SELECT query:

Enable SuiteScript

Go to Setup -> Company -> Enable Features. Under the SuiteCloud tab, ensure the options for Client SuiteScript, Server SuiteScript, and SuiteScript Server Pages are all enabled.

Upload the SuiteScript File

Go to Documents -> Files -> SuiteScripts. Click the Add File button. Upload the search_script.js script that is included with the CData JDBC Driver for NetSuite. You can find it in the installation directory, under the db folder.

Create a Script Record

Go to Customization -> Scripting -> Scripts -> New. From the menu, find search_script.js and select it. Then click Create Script Record.

Deploy the Script Record

Give the script a name and under the Deployments tab add a title. Then click Save. This will create a deployment.

Note: While the status of the Script Deployment is Testing, only the user that created the deployment will return results from any saved searches queried. To make the RESTlet available for other users, update the Status of the Script Deployment to Released and add supported roles in the Audience tab.

Get the Deployment / Script Id

You will find a link to the deployment in the Script Record. Follow the link to the deployment. Or, you can also go to Customization -> Scripting -> Script Deployments and use the filters to find the deployment you made. In the deployment record, you will see a URL and External URL. Copy the script number and deployment number from this URL.

Get a Saved Search Id

To execute search_script.js, you need to pass it the Id of a saved search: Go to Lists -> Search -> Saved Searches and copy in the Id of a saved search. We recommend you use a search that is ordered by a column that will always give the same ordering, such as the Date Created. This is because NetSuite does not cache results that can cause duplicates or missing data if data is changed while you are paging through the results.

Configure Authentication

RESTlets support the following authentication mechanisms:

• User/Password: You must set the UseSessions connection property to true in addition to User and Password.

• OAuth: In addition to the OAuth connection properties, the AccountId is required for RESTlet OAuth authentication. To authenticate, set AccountId, OAuthClientId, OAuthClientSecret, OAuthAccessToken, and OAuthAccessTokenSecret.

  See Establishing a Connection for a connection guide.

Create a RESTlet Schema

Call the CreateRestletSchema stored procedure to create a view schema for a RESTlet. The stored procedure outputs the schema; schemas are defined in .rsd files. You can customize the column names with a text editor -- see the following section.

Below is an example of the stored procedure inputs. You must also set the Location connection property to point to a folder location where you would like the schema saved.

Note: Given that this driver supports multiple schemas, the file path should end with the root folder that contains all of the separate schema folders.

EXEC CreateRESTletSchema @TableName='MyRESTletSchema', @ScriptId='548', @DeploymentNum='1', @FlattenSelects='true', @UseLabels='true', @SearchId='customsearch795'

Note that you must provide at a minimum the TableName, ScriptId (obtained from the deployment URL), DeploymentNum (obtained from the deployment URL), and SearchId. FlattenSelects and UseLabels are optional.

• FlattenSelects: Set this parameter to false if your saved search includes multiselect columns. This parameter controls how the driver models the data types of selects (such as an entity / customer reference). When FlattenSelects is set to false, multiselect data is returned as a JSON aggregate.

  By default the driver exposes two columns for selects -- one for the name and one for the value (often an Id). This will not work correctly if your saved search includes multiselect columns. There is nothing in the RESTlet metadata that distinguishes multiselect from single-select columns.

• UseLabels: This determines if the driver should try to match column names to what would show up in the Saved Search results or just use the API name returned by the saved search itself. Note that even when UseLabels is set to true, names will be converted to be alphanumeric with underscores. Also, be aware that sometimes labels will come back with identical names for saved searches. In these cases, a number will be appended to the duplicate names to keep the names unique.

Execute the RESTlet

After connecting with Location set, you can retrieve data from the table with a simple SELECT query. For example:

SELECT * FROM MyRESTletSchema

Note: Paging is handled automatically by the CData JDBC Driver for NetSuite.

Fine-Tuning Schemas

Schemas are defined in .rsd files, which enable you to change the columns reported with a text editor. The .rsd files are written to the path specified by the Location connection property. To change column names, modify the <attr name=""> section.

Limitations

• Currently, the driver only supports RESTlets that conform to a response in the format of the included search_script.js script.
• The driver does not support server side filtering on the results. To enable queries to execute faster, define all filters within the saved search itself. Otherwise, client-side filters may be applied after the data is retrieved via SupportEnhancedSQL.