Running Queries via Open Dental API

If you need to pull specific data from your practice database that isn't available in standard reports, the open dental API query endpoint is the tool for the job. This feature allows you to send custom SQL commands to your database and receive the results in a structured format, enabling you to build custom dashboards, automated reporting tools, or specialized data exports without manually running reports in the software.

Prerequisites

Before you can run queries, you must ensure your environment is configured correctly:

1. API Access Tier: You must have an active API subscription that includes the "Queries" permission. This is typically part of the $15/month tier (or higher).
2. Developer Portal Access: You need a developer account. If you do not have one, contact vendor.relations@opendental.com to request access.
3. eConnector: The office must have the eConnector service installed and running. This service acts as the bridge between the Open Dental headquarters API server and your local database.
4. API Setup: In Open Dental, go to Setup > Advanced Setup > API. Ensure the "Enabled" checkbox is checked and that you have added the Customer API Key generated from your developer portal.

Implementation

The query endpoint is strictly read-only. It will not allow commands that modify, delete, or insert data into your database.

To run a query, you will send a PUT request to the /queries/ShortQuery endpoint. You must include your Developer Key and Customer Key in the Authorization header.

Example using JavaScript (Node.js)

const axios = require('axios');

const developerKey = 'YOUR_DEVELOPER_KEY';
const customerKey = 'YOUR_CUSTOMER_KEY';
const baseUrl = 'https://api.opendental.com/api/v1';

async function runCustomQuery() {
  const sql = "SELECT PatNum, LName, FName FROM patient LIMIT 10;";
  
  try {
    const response = await axios.put(`${baseUrl}/queries/ShortQuery`, 
      { SqlCommand: sql },
      {
        headers: {
          'Authorization': `ODFHIR ${developerKey}/${customerKey}`,
          'Content-Type': 'application/json'
        }
      }
    );
    console.log(response.data);
  } catch (error) {
    console.error('Error executing query:', error.response ? error.response.data : error.message);
  }
}

runCustomQuery();

Common Errors

• 401 Unauthorized: This usually means your Authorization header is formatted incorrectly or your keys are invalid. Double-check that you are using the ODFHIR {DeveloperKey}/{CustomerKey} format.
• 403 Forbidden: The API key you are using does not have the "Queries" permission enabled. Check your permissions in the Developer Portal.
• 400 Bad Request: Your SQL command may be invalid, or you are attempting to run a command that modifies the database (e.g., UPDATE, DELETE, INSERT). Remember, only SELECT statements are permitted.
• 504 Gateway Time-out: Your query is taking longer than 60 seconds to execute. Try optimizing your SQL or narrowing your search criteria.

Limitations

• Read-Only: You cannot use this endpoint to change any data in Open Dental.
• Throttling: Depending on your API tier, requests are throttled. For the $15/month tier, requests are limited to one per second.
• Result Size: The ShortQuery endpoint is designed for smaller result sets. If you need to export massive amounts of data, consider using the Queries POST method, which writes results to an SFTP site in CSV format.
• Security: Queries are audited. Any SQL command you run is logged in the Open Dental Audit Trail.

Don't want to build your own dashboard? DentalCanvas already connects to your Open Dental data and shows you everything in a visual interface.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.