Query SAS xpt Data as a SQL Server Database in Node.js

You can use CData Connect AI to query SAS xpt data through a SQL Server interface. Follow the procedure below to create a virtual database for SAS xpt in Connect AI and start querying using Node.js.

CData Connect AI provides a pure MySQL, cloud-to-cloud interface for SAS xpt, allowing you to easily query live SAS xpt data in Node.js — without replicating the data to a natively supported database. As you query data in Node.js, CData Connect AI pushes all supported SQL operations (filters, JOINs, etc) directly to SAS xpt, leveraging server-side processing to quickly return SAS xpt data.

Configure SAS xpt Connectivity for NodeJS

Connectivity to SAS xpt from NodeJS is made possible through CData Connect AI. To work with SAS xpt data from NodeJS, we start by creating and configuring a SAS xpt connection.

1. Log into Connect AI, click Sources, and then click Add Connection

2. Select "SAS xpt" from the Add Connection panel

3. Enter the necessary authentication properties to connect to SAS xpt.

   Connecting to Local SASXpt Files

   You can connect to local SASXpt file by setting the URI to a folder containing SASXpt files.

   Connecting to S3 data source

   You can connect to Amazon S3 source to read SASXpt files. Set the following properties to connect:

   • URI: Set this to the folder within your bucket that you would like to connect to.
   • AWSAccessKey: Set this to your AWS account access key.
   • AWSSecretKey: Set this to your AWS account secret key.
   • TemporaryLocalFolder: Set this to the path, or URI, to the folder that is used to temporarily download SASXpt file(s).

   Connecting to Azure Data Lake Storage Gen2

   You can connect to ADLS Gen2 to read SASXpt files. Set the following properties to connect:

   • URI: Set this to the name of the file system and the name of the folder which contacts your SASXpt files.
   • AzureAccount: Set this to the name of the Azure Data Lake storage account.
   • AzureAccessKey: Set this to our Azure DataLakeStore Gen 2 storage account access key.
   • TemporaryLocalFolder: Set this to the path, or URI, to the folder that is used to temporarily download SASXpt file(s).
   
4. Click Save & Test
5. Navigate to the Permissions tab in the Add SAS xpt Connection page and update the User-based permissions.

Add a Personal Access Token

When connecting to Connect AI through the REST API, the OData API, or the Virtual SQL Server, a Personal Access Token (PAT) is used to authenticate the connection to Connect AI. It is best practice to create a separate PAT for each service to maintain granularity of access.

1. Click on the Gear icon () at the top right of the Connect AI app to open the settings page.
2. On the Settings page, go to the Access Tokens section and click Create PAT.
3. Give the PAT a name and click Create.
4. The personal access token is only visible at creation, so be sure to copy it and store it securely for future use.

With the connection configured and a PAT generated, you are ready to connect to SAS xpt data from Node.js.

Query SAS xpt from Node.js

The following example shows how to define a connection and execute queries to SAS xpt with the SQL Server module. You will need the following information:

• server: tds.cdata.com
• port: 14333
• user: a Connect AI user (e.g. [email protected])
• password: the PAT for the above user
• database: The connection you configured for SAS xpt (SASXpt1)

Connect to SAS xpt data and start executing queries with the code below:

var sql = require('mssql')
var config = {
	server: 'tds.cdata.com',
	port: 14333, 
	user: '[email protected]', //update me
	password: 'CONNECT_USER_PAT', //update me	
	options: {
		encrypt: true,
		database: 'SASXpt1'
	}
}

sql.connect(config, err => { 
    if(err){
        throw err ;
    }
    new sql.Request().query('SELECT * FROM SampleTable_1', (err, result) => {
        console.dir(result)
    })
        
});

sql.on('error', err => {
    console.log("SQL Error: " ,err);
})