Connecting QuerySurge to SASxpt with the CData JDBC Driver

Versions: 8.0+

SASxpt, or SAS Transport File Format, is an openly documented specification maintained by SAS, a commercial company with a variety of software products for statistics and business analytics. Because it has become so popular, SASxpt files are an obvious target in data testing. SASxpt files can be incorporated in QuerySurge data tests and tested against flat files, XMLs, databases, big data stores and other SASxpt files.  In this article, we show the connection setup for the CData SASxpt JDBC driver.

Note: RTTS, the vendor of QuerySurge, partners with CData to make a broad range of JDBC drivers available to QuerySurge users. For information about our partnership, click here. See all of CData's JDBC offerings here. For questions related to ordering, contact us here.

For a SASxpt CData Connection, you will need the following information:

• Directory path where the SASxpt files are located
• The file extension should be included on all SASxpt files. (.XPT)

 Note: The directory specified should only contain SASxpt files. The presence of other file types can cause unwanted metadata issues and behavior when using the Connection in the QueryWizard.

Adding a SASxpt Data Source Connection using the CData JDBC Driver

To connect to a SASxpt data source, you'll need to use the Connection Extensibility feature of the QuerySurge Connection Wizard.

1. Download the SASxpt JDBC driver from CData.
   
   
2. Deploy the SASxpt JDBC driver to your Agent(s). To install the CData driver, unzip the driver download and run their driver installer (setup.jar) to install the driver on your Agent box(es) (instructions for doing so can be found in the readme.txt file). We recommend that you install the driver in the default location. Once installed, copy the driver file(s) and the license file from the installation directory to your QuerySurge Agent jdbc directory (see the relevant Knowledge Base article for deploying drivers to Agents on Windows or Agents on Linux). You'll need to deploy a driver for each Agent box on which you intend to use the driver. 
   

   Note: For this example the deployed files are: a) cdata.jdbc.sasxpt.jar and b)  cdata.jdbc.sasxpt.lic.
   
   

3. Login to QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Select Connections in the left nav tree, and click the Add button (at the bottom left of the main panel). Leave the Advanced Mode checkbox unchecked. You'll need the following standard information for the next few steps.
   

   Driver Class: cdata.jdbc.sasxpt.SASXptDriver

   Connection URL template: jdbc:sasxpt:URI=<Folder Path>
   

   The SASxpt JDBC CData driver connects to a folder. Each agent you wish to use the SASxpt CData connection needs to be able to access the folder. Replace <Folder Path> with your folder path.
4. Enter a Connection Name, and select * All Other JDBC Connections (Connection Extensibility) from the Data Source dropdown menu.
   
   
   
5. Enter the Driver Class.
   
   
   
6. Enter the Connection URL. 
   
   You can enter an optional Test Query. A test query should only return one row and one column. The table name, in this case, is a CData driver system table that should return if the connection is configured correctly.
   
   
7. Verify the connection details, and optionally test the connection before saving.
   

You're all set! You can write and execute QueryPairs against SASxpt files. The CData documentation discusses different driver configuration options, advanced settings, and supported SQL syntax and functions.