API Queries

See API Specification

Queries POST

Version Added: 21.1

Runs a custom query against the database. Queries are screened to be read-only. Temporary tables are allowed. Any command that would change the database will not be run. The Audit Trail for these requests includes the command that was run.

Rarely, an office will insist on higher security for queries. There is a OpenDentalWebConfig.xml file in the eConnector installation folder. This config file has two tags that are normally empty: <UserLow> and <PasswordLow>. You can set up a different MySQL that has more restrictive permissions than the MySQL user used for all the other API methods. Once this MySQL user is set up, enter their username and password into <UserLow> and <PasswordLow>, and that user will be used here for queries. If those tags are blank, then the regular MySQL user will be used. Don't remove those tags, or the eConnector won't start.

The results of the query are written to a file and saved to the SFTP site specified in the JSON. SftpPort is optional with port 22 used by default. The SftpAddress string must contain the full filePath. The user with the SFTP credentials must have write permission in this directory. Directory will be created if it does not exist, and files already exististing with the specified name will be overwritten. Query results are written in comma-delimited CSV format. If there are no results, then the file will only contain "OK".

Example Requests:
POST /queries

{
"SqlCommand": "SELECT PatPlanNum, PatNum, InsSubNum FROM patplan",
"SftpAddress": "MySftpSite/myUsername/Patient Plans/PatPlans-35.csv",
"SftpPort": 25,
"SftpUsername": "myUsername",
"SftpPassword": "myPassword"
}

{
"SqlCommand": " SELECT * FROM patient WHERE Birthdate LIKE '%-06-22' ",
"SftpAddress": "MySftpSite/myUsername/Birthdays/Jun22.csv",
"SftpUsername": "myUsername",
"SftpPassword": "myPassword"
}

{
"SqlCommand": "SELECT * FROM InsSub WHERE DateTerm < '2021-01-01'",
"SftpAddress": "MySftpSite/myUsername/Insurance Subscriptions/Expired2021.csv",
"SftpUsername": "myUsername",
"SftpPassword": "myPassword"
}

Example Response:
201 Created
400 BadRequest (SQL syntax and Sftp connection errors)
401 Unauthorized (Query is not read-only or is not executing on temporary tables)

Queries PUT ShortQuery

Version Added: 21.2

Runs a custom query against the database, returning at most 100 rows. Queries are screened to be read-only. Temporary tables are allowed. Any command that would change the database will not be run. See Queries Post, above, for an explanation of the completely optional restricted MySQL UserLow. The Audit Trail for these requests includes the command that was run.

The results of the query are returned as a datatable in the JSON. While pagination is supported for results that return over 100 rows, it is recommended to instead use Queries POST for longer results.

Example Requests:
PUT /queries/ShortQuery
PUT /queries/ShortQuery?Offset=200

{
"SqlCommand": "SELECT clinic.Description, COUNT(*) AS NumberOfPatients
FROM patient,clinic
WHERE patient.ClinicNum=clinic.ClinicNum
GROUP BY clinic.ClinicNum"
}

{
"SqlCommand": "SELECT * FROM payperiod"
}

Example Responses:
[
{
"PayPeriodNum": 200,
"DateStart": "2021-05-01T00:00:00",
"DateStop": "2021-05-31T00:00:00",
"DatePaycheck": "2021-06-04T00:00:00"
},
{
"PayPeriodNum": 201,
"DateStart": "2021-06-01T00:00:00",
"DateStop": "2021-06-30T00:00:00",
"DatePaycheck": "2021-07-05T00:00:00"
}
]
200 OK
400 BadRequest (SQL syntax errors)
401 Unauthorized (Query is not read-only or is not executing on temporary tables)