I would like to run an external service e.g. SQL database query triggered from a cloud task. This is no problem, except when the response takes more than 5 minutes to complete, because cc tasks timeout after 5 minutes.
How can I still get the db query result after the 5 minutes have lapsed?
The 5 minute CC runtime is a hard limit. So if your process is going to take more than 5minutes I would recommend making it asynchronous.
The following workflow could work.
- CloudCode tells SQL to do something but does not wait for a response
- SQL does something until it is done (however long it takes)
- SQL informs JourneyApps that it is done
3.1 Option 1: Have SQL post the entire response to a CC Webtask (Custom API)
3.2 Option 2: Have SQL write a single object to the JA Backend which then triggers a webhook which then triggers a CC task to go and fetch the completed response from SQL (assuming the response is stored somewhere. If not, go with option 1)
I think for “remote” requests that will take this long to complete it makes sense to have them complete asynchronously and not sit and wait. The alternative to the above workflow is to have the CC task trigger the SQL process and then separately from that have a way of “continuously” asking SQL if the process is done and rescheduling itself to keep asking until it is done, at which point you get the response back from SQL and then you can parse it. But I prefer the first.
I hope this helps.
Our SQL server resides on an Amazon RDS and we connect directly to it using the node js mysql library in cloudcode. How do I get SQL to post the entire response to a cc task without using any middleware or private server?
Is this perhaps a feature of MYSQL that you can include a call back function in the SQL query which does an HTTP POST request back to cc, only once the query has completed?
Unfortunately I am not intimately familiar with that implementation of SQL server, but I would be surprised if there exists an out-of-the-box way to provide a callback function to a SQL process.
What I had in mind would require you to change the way you are implementing the integration and make it completely asynchronous instead. So you would basically be telling SQL to do something and then SQL would do it and probably write the result somewhere temporarily and then trigger a stored procedure that reads it and then posts the result to a CloudCode webtask.
Calling a REST API call from a SQL stored procedure should be fairly straightforward, but unfortunately I am not a SQL expert.
The bottom line is that you “should” not have a synchronous integration that takes > 5 mins to complete. How you go about solving it will depend on the specific mechanism at your disposal, of which I can only really comment on the JourneyApps side of things.
I hope this helps
Thanks for the info, I think your recommendation will work. The trick lies in using a stored procedure. Will start building the implementation and let you know how it works out.