Query SharePoint Data in DataGrip

DataGrip is a database IDE that allows SQL developers to query, create, and manage databases. When paired with the CData JDBC Driver for SharePoint, DataGrip can work with live SharePoint data. This article shows how to establish a connection to SharePoint data in DataGrip and use the table editor to load SharePoint data.

Getting Started

---

Create a New Driver Definition for SharePoint

The steps below describe how to create a new Data Source in DataGrip for SharePoint.

1. In DataGrip, click File -> New > Project and name the project 👁 Creating a new DataGrip project.
   
2. In the Database Explorer, click the plus icon () and select Driver. 👁 Adding a new Driver.
   
3. In the Driver tab:
   • Set Name to a user-friendly name (e.g. "CData SharePoint Driver")
   • Set Driver Files to the appropriate JAR file. To add the file, click the plus (), select "Add Files," navigate to the "lib" folder in the driver's installation directory and select the JAR file (e.g. cdata.jdbc.sharepoint.jar).
   • Set Class to cdata.jdbc.sharepoint.SharePoint.jar
4. Click "Apply" then "OK" to save the Connection 👁 A configured Driver (Salesforce is shown).
   

Configure a Connection to SharePoint

1. Once the connection is saved, click the plus (), then "Data Source" then "CData SharePoint Driver" to create a new SharePoint Data Source.
2. In the new window, configure the connection to SharePoint with a JDBC URL.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the SharePoint JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

    java -jar cdata.jdbc.sharepoint.jar
    

   Fill in the connection properties and copy the connection string to the clipboard.

   Set the URL property to the base SharePoint site or to a sub-site. This allows you to query any lists and other SharePoint entities defined for the site or sub-site.

   The User and Password properties, under the Authentication section, must be set to valid SharePoint user credentials when using SharePoint On-Premise.

   If you are connecting to SharePoint Online, set the SharePointEdition to SHAREPOINTONLINE along with the User and Password connection string properties. For more details on connecting to SharePoint Online, see the "Getting Started" chapter of the help documentation

   👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)
   
3. Set URL to the connection string, e.g.,

   jdbc:sharepoint:User=myuseraccount;Password=mypassword;Auth Scheme=NTLM;URL=http://sharepointserver/mysite;SharePointEdition=SharePointOnPremise;

4. Click "Apply" and "OK" to save the connection string 👁 A configured Data Source (Salesforce is shown).
   

At this point, you will see the data source in the Data Explorer.

Execute SQL Queries Against SharePoint

To browse through the SharePoint entities (available as tables) accessible through the JDBC Driver, expand the Data Source.

To execute queries, right click on any table and select "New" -> "Query Console."

In the Console, write the SQL query you wish to execute. For example:

SELECT Name, Revenue FROM MyCustomList

Download a free, 30-day trial of the CData JDBC Driver for SharePoint and start working with your live SharePoint data in DataGrip. Reach out to our Support Team if you have any questions.