REST API which accepts SQL and returns JSON

Question

Question

Evgeny Shvarov · Dec 8, 2020

#JSON #SQL #InterSystems IRIS

Hi folks!

Do we have an embedded REST API in IRIS which accepts arbitrary SQL and returns data in JSON?

Or if anyone did something like this?

The idea is to have something like MDX2JSON but SQL2JSON.

Thanks!

Discussion (11)3

Log in or sign up to continue

Anton Umnikov · Dec 8, 2020

s sc=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONFromSQL("json","SELECT Name FROM Sample.Person")

6 0

score 0 · Answer 1 · 2020-12-08T13:33:27-05:00

Nicholai Mitchko · Dec 8, 2020

+1 didn't know this existed, incredibly helpful!

0 0

score 0 · Answer 2 · 2020-12-09T09:14:31-05:00

Andy Coutinho · Dec 9, 2020

Very nice! thanks for sharing

0 0

score 0 · Answer 3 · 2020-12-13T06:37:31-05:00

Thanks, Anton!

I packed your answer in sql-res-api module.

if you zpm it, you have /sql rest which accepts SQL with POST request and returns JSON.

USER>zpm "install sql-rest-api"

score 2 · Answer 4 · 2020-12-08T14:00:34-05:00

Dmitry Maslennikov · Dec 8, 2020

Query request in Atelier API

2 0

score 1 · Answer 5 · 2020-12-08T17:12:53-05:00

Eduard Lebedyuk · Dec 8, 2020

Obvious security ramifications disclaimer.

Also RESTForms UI provides a locked down implementation.

1 0

score 0 · Answer 6 · 2020-12-18T11:52:02-05:00

Hi

I was going to offer to write such a function but it appears that other developers have already got solutions for your request. Pity, its a nice challenge

Nigel

score 0 · Answer 7 · 2020-12-18T12:00:11-05:00

Evgeny Shvarov · Dec 18, 2020

Hi Nigel! Thanks! Actually, I already crafted the module following @Anton Umnikov's advice above.

0 0

score 0 · Answer 8 · 2020-12-18T13:10:59-05:00

Lol, I was so busy reading all the other posts otherwise I would have sat down and written it.

Would you like to share your code with me (*** please ***)

Nigel

score 0 · Answer 9 · 2020-12-18T16:44:51-05:00

Evgeny Shvarov · Dec 18, 2020

Here it is!

0 0

score 0 · Answer 10 · 2020-12-19T01:16:45-05:00

Nigel Salm · Dec 19, 2020

Thank you Evgeny

0 0