Running Queries via Open Dental API

If you need to pull specific, custom data out of your practice management software that isn't available in standard reports, you need to use the open dental API query endpoint. This feature allows you to execute SQL queries against your database through the API, giving you the flexibility to create custom dashboards, automated exports, or specialized reporting tools that fit your practice's unique needs.

Prerequisites

Before you can start running queries, you must ensure your environment is configured correctly. The API is not enabled by default.

1. API Access Tier: You must have an active API subscription that includes the ApiQueries permission. This is typically part of the $15/month (or higher) tier. Check your current subscription details in the Open Dental Developer Portal.
2. Developer Portal Access: You must have a registered account on the Open Dental Developer Portal to generate your DeveloperKey.
3. eConnector: The eConnector service must be installed and running on your server. This service acts as the bridge between your external application and your local database.
4. Enable API: In your Open Dental software, go to Setup > Advanced Setup > API. Ensure the "Enabled" checkbox is checked. You will also need to add the CustomerKey generated in the Developer Portal here.
5. Permissions: Ensure the CustomerKey you are using has been granted the ApiQueries permission.

Implementation

The open dental API query endpoint accepts a POST request containing your SQL query. Because this is a powerful tool, you must be careful with the SQL you write.

The base URL for your requests will be https://{serverIP}:{port}/api/v1/.

Here is a basic example using JavaScript (Node.js) to execute a query:

const axios = require('axios');

// Replace with your actual credentials and endpoint
const baseUrl = 'https://your-server-ip:30223/api/v1/';
const developerKey = 'YOUR_DEVELOPER_KEY';
const customerKey = 'YOUR_CUSTOMER_KEY';

async function runQuery() {
  try {
    const response = await axios.post(`${baseUrl}Queries`, {
      "Query": "SELECT PatNum, LName, FName FROM patient LIMIT 10;"
    }, {
      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);
  }
}

runQuery();

Common Errors

When working with the open dental API query endpoint, you may encounter these common issues:

• 401 Unauthorized: This usually means your DeveloperKey or CustomerKey is incorrect, or the CustomerKey has not been enabled in the Setup > Advanced Setup > API window.
• 403 Forbidden: The CustomerKey you are using does not have the ApiQueries permission assigned to it.
• 400 Bad Request: This often indicates a syntax error in your SQL query or that the eConnector is not running. Ensure your SQL is valid for MySQL (the database engine Open Dental uses).
• 500 Internal Server Error: This may occur if your query is too complex, takes too long to execute, or attempts to access tables that are restricted.

Limitations

• Read-Only: The query endpoint is strictly for SELECT statements. You cannot use it to INSERT, UPDATE, or DELETE data.
• Rate Limiting: To protect database performance, requests are throttled. If you have the ApiQueries permission, you are generally limited to one request per second.
• Data Restrictions: You cannot access sensitive system tables or configuration data that could compromise the security or integrity of your practice management software.
• Pagination: If your query returns a large number of rows, you must use LIMIT and OFFSET in your SQL to paginate the results, as the API will not return thousands of records in a single call.

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.