POST Query Timeout Issue

[mandmdiet]

Hi all,

We have a need to export a large amount of claim data from Open Dental. To accomplish this, we're using the Queries POST API endpoint. One problem we're having, is that, although the query executes under the timeout of 60 seconds, the API holds on to the request until the file upload to the SFTP server is also completed. This is causing us an issue because, we can't know or anticipate how much time the server might need to package the query results into a file and upload that file to us. Even in the most ideal situation for some of our clients this could go beyond 60 seconds (some clients have a slower internet bandwidth), but if there is ever an unusual delay, even rarely (such as unusually slow network speeds) the call will surely prove to be unreliable at best.

We're already dividing the query into chunks, but we worry that picking a chunk size that both never encounters a timeout like this and also doesn't divide the requested data into too small of a size isn't possible. Is there an API setting that will allow these requests to be released as soon as the client server successfully receives it and then do the packaging and uploading of the file in its own time? There's no reason for our requests to wait for that entire process to finish since we're not getting the response from the query directly anyway. If that's not an option, is there something else we can do while still using the public OD API?

Thank you

[SLeon]

I want to make sure I understand your concern correctly. You are using the Queries POST Sftp endpoint and the request (query + document transfer) time is exceeding the server timeout. And your specific question is, "If my request times out, how do I know if the document was successfully created?" and "What strategies can I employ to have successful API requests (201 Created) and successful document transfers (valid file on the Sftp site)?

[mandmdiet]

Sure, that sounds like a good place to start. Also wondering if, since the Queries POST endpoint isn't returning anything to me directly, can it be used in a fire and forget style where we're not waiting on the query to run and the file to be uploaded thus making the timeout irrelevant?

[SLeon]

Good morning,

... can it be used in a fire and forget style where we're not waiting on the query to run and the file to be uploaded thus making the timeout irrelevant?

With some research this appears to be something we can do. We would add a parameter that you could choose to pass in for this behavior, as to not break developers' existing implementations.

Ideally, your API request would return immediately in the case of SQL error and Sftp site connection initiation. However, after the actual file transfer starts ("fire and forget") you would not be informed of downstream Sftp issues (connection/network interruption, etc.) and your application would need to handle that case.

I have added this to our development list. We will update this thread with more information on this Feature Request as it enters development.

[mandmdiet]

Wow, that's great news. Thank you SLeon!

[DerekR]

We have completed this enhancement.

IsAsync has been added to Queries POST and is available starting in version 23.3.26. When IsAsync is set to "true", the file transfer will continue asynchronously.

See https://www.opendental.com/site/apiqueries.html for more information.

Thanks!

[mandmdiet]

This is great, we really appreciate this DerekR!