Access NetSuite RESTlets as Views
View a tutorial that explains SQL access to NetSuite saved searches.
Join the DZone community and get the full member experience.Join For Free
The CData Drivers (JDBC, ODBC, API Server, ADO.NET, SSIS, BizTalk, and Excel) implement standards-based SQL access to various data sources, enabling third-party tools to interoperate. You can use CData Drivers to create reports on NetSuite data, and you can also access existing reports. This article shows how to query a RESTlet in NetSuite.
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:
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 and 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.
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
In addition to the required authentication connection properties, set the Location connection property, in the Schema section, to the folder where you want the schema to be written.
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 multiselect 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, multiselect 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 multiselect columns. There is nothing in the RESTlet metadata that distinguishes multiselect 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.
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.
- 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 and 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.
Published at DZone with permission of Jerod Johnson, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.