NetSuite RESTlet にビューとしてアクセスする方法

CData Drivers (JDBC, ODBC, API Server, ADO.NET, SSIS, BizTalk, and Excel) は、多くのデータソースにSQL ベースのインターフェースを提供し、サードパーティツールからデータソースへのデータ連携を実現しています。この記事では、NetSuite のRESTlet をドライバーからSQL でクエリする方法を説明します。

SQL Access to NetSuite Saved Searches

NetSuite RESTlets are server-side scripts that interact with NetSuite data following RESTful principles. With the added support for RESTlets in the CData Drivers, users get SQL access to their NetSuite saved searches, exactly as they appear within the NetSuite UI. Users can quickly and easily see the results of saved searches, regardless of any complexities in the report, whether those are JOINs with other tables, formulas on fields, summaries, and more. The screenshot below showcases a saved search in the NetSuite UI (left) and the same results in Visual Studio Server Explorer using the CData ADO.NET Provider (right).

To view the results of a saved search, users can leverage the CreateRESTletSchema stored procedure to create views (read-only tables) of RESTlets. To use the stored procedure, deploy a RESTlet in NetSuite, note the Deployment & Script IDs and the ID of the Saved Search you want to process, and call the stored procedure.

Deploying the RESTlet in NetSuite

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 comes installed with the CData ADO.NET Provider for NetSuite 2018. 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.

Get the Deployment ID & 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. Note the script ID and deployment ID from this URL. For example, given the following URL /app/site/hosting/restlet.nl?script=999&deploy=1 the script ID is 999 and the deployment ID is 1.

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 note 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 the CData Driver

After deployment, you are ready to work with the RESTlet using the CData Drivers. Start by establishing the connection to NetSuite.

Configure Authentication

RESTlets support the following authentication mechanisms:

• User/Password: To use user/password authentication, you must set the UseSessions connection property to true.
• OAuth: In addition to the OAuth connection properties, the AccountId is required for RESTlet OAuth authentication.

Configure the Location Property

Refer to the help documentation for a step-by-step guide to connect.

Create the Schema and Execute the RESTlet

With the RESTlet deployed and the connection configured, simply call the CreateRESTletSchema stored procedure to create the table schema for the RESTlet. The stored procedure outputs the schema, which will also be defined in an .rsd configuration file. You can customize the schema with a text editor (refer to the Help documentation).

Calling the Stored Procedure

Below is an example of calling the stored procedure with sample inputs. Note that TableName, ScriptId, DeploymentNumber, and SearchId are required inputs while FlattenSelects and UseLabels are optional (see more information below).

EXEC CreateRESTletSchema @TableName='RESTlet999', @ScriptId='999', @DeploymentNumber='1', @FlattenSelects='true', @SearchID='customsearch795'

• FlattenSelects: Set this parameter to false if your saved search includes multi-select columns. This parameter controls how the provider models the data types of selects (such as an entity / customer reference).
  
  When FlattenSelects is set to false, multi-select data is returned as a JSON aggregate.
  
  By default, the provider 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 multi-select columns. There is nothing in the RESTlet metadata that distinguishes multi-select from single-select columns.
• UseLabels: This determines if the provider 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, the duplicate names will get a number appended to them to keep them unique.

Execute the RESTlet

With the Schema defined, you can retrieve data from the RESTlet with a simple SELECT query. For example:

SELECT * FROM RESTlet999

Note: Paging is handled automatically by the CData drivers.

Fine-Tuning Schemas

You can use a text editor to customize column behavior and other aspects of the schema: open the .rsd file that was written to the specified Location. Modify column behavior in the rsb:info section. For example, to change column names, modify the <attr name=""> section.

Considerations

• Currently, the drivers only support RESTlets that conform to a reponse in the format of the search_script.js script included in the installation.
• The drivers do 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.

More Information & Free Trials

Thanks to the standards-based driver for NetSuite by CData, users can connect BI, reporting, ETL, and custom applications to live NetSuite data, including the results of RESTlets written to process the results of Saved Searches. Read more about the drivers on the NetSuite page and download a free, 30-day trial to get started today.