0
I have a business need to take the results of an API, in realtime / when queried (as opposed to using a scheduled action to do this periodically) and make them accessible via a SQL view.
An application that I need to integrate with another application can perform some functionality via SQL integrations with SQL views that it just can't do via results of a web service call.
So my question to the public here is whether they are aware of a product or solution that can be customized to take the results of a JSON / XML collection of a web service query - that the product or solution must execute on it's own when queried via SQL - and format / return them via a sort-of emulated SQL view, assuming said product can emulate a SQL server.
Is this possible, or am I day-dreaming?
I've attempted to do some research on this on my own, but have only found articles showing how to do the opposite - host an API that returns SQL results via REST/SOAP.
schizoid04
Posted 2017-02-13T19:58:45.423
Reputation: 119
As a background - The application I need to integrate with is a cloud application, and I will never have direct access to its SQL server; yet something I may need to do requires access to a table / view in SQL, hence me asking whether there's a way to emulate a SQL view using an application that displays the results of a REST/SOAP API query. – schizoid04 – 2017-02-13T20:00:22.760
https://boundlessgeo.com/2012/04/http-for-postgresql/ https://github.com/pramsey/pgsql-http also oracle surely can map a xml file as a read-only table (and in plsql you can do htp requests through utl_http) but i do not know about some easy way to make it as a view... how about constructing a "database connector" for the app which actually calls a webservice and not a database? could that be an option? – Fiisch – 2017-02-13T20:05:51.160
also, are the data needed in realtime? wouldn't some script refreshing data in the table (say, every few minutes) sufficient? it is way easier than anything I've written above :) – Fiisch – 2017-02-13T20:13:21.190
It needs to be realtime; if the data is more than a few seconds out of date it's probably not acceptable to the client we're implementing the application for. Additionally, the platform I am using would only be able to refresh data via a scheduled action up to once per hour (limitation of the platform, specifics probably not needed). As mentioned above, I can call a web service and can perform some actions based on the results, but the functionality in the app that I'm working with that I need can only be performed with SQL tables/views, in an MSSSQL server. – schizoid04 – 2017-02-13T23:31:50.233
1
I think somebody borked the design and you should escalate this problem to PM. Anyway... a thing you could use is some ESB or similar product - they do this kind of stuff. However I thought of one workaround with mssql... create stored procedure which loads data from webservice into some table ( http://stackoverflow.com/questions/17407338/how-can-i-make-http-request-from-sql-server ). Then before reading the data, you have to call it to refresh them. If your app cannot call stored procs, you can always pair them with some trigger. It is ugly, slow, nonsensical but it will probably work.
– Fiisch – 2017-02-14T07:35:31.327Thanks for the information. It sounds like what I'm asking for isn't very feasible – schizoid04 – 2017-02-14T15:10:20.577