Articles in this section

See more

Connecting QuerySurge to JSON Documents with the CData JSON JDBC Driver

JSON, or JavaScript Object Notation, is a lightweight data interchange format commonly used in web applications, document data stores, APIs and numerous other data exchange processes. Because it has become so popular, JSON documents are an obvious target in data testing. JSON can be incorporated in QuerySurge data tests against flat files, XMLs, databases, big data stores and other JSON documents through a JSON-based JDBC driver.  In this article, we show the connection setup for the CData JSON JDBC driver.

mceclip1.png 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.

 

Adding a JSON Data Source Connection using the CData JDBC Driver

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

  1. Download the JSON JDBC driver from CData.
     

  2. Deploy the JSON 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.json.jar and b)  cdata.jdbc.json.lic.

     

  3. Login to QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Select Connections in the leftnav 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.json.JSONDriver
    Connection URL template: jdbc:json:DataModel=<model_type>URI=<file_location>


    We suggest using a DataModel of 'Relational', but see the CData driver documentation for more options. An actual URL (using a JSON file at path C:\colors.json) looks like (see below for file details):

    jdbc:json:DataModel=Relational;URI=C:\colors.json
  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 derived from the top-level JSON property name (see below).
     
  7. Verify the connection details, and optionally test the connection before saving.

You're all set! You can write and execute QueryPairs against the JSON data source of choice. See this article for how to Write SQL using the CData JSON JDBC Driver.

The CData documentation discusses different driver configuration options, advanced settings, and supported SQL syntax and functions.

 

Resources

Related articles