Tables. How to output data from a remote source (via the API) in the Table component

There Is an external system that has an API. You need to output data from this API to a table.

1. Specify the codes of the outgoing API request in the API Source field in the table settings (you can separate them with commas without spaces). In the database, this is the table field as_crud_tablesrequestGetItems nvarchar(512).

2. Implementing an outgoing call/asapi

Sample request:  

CREATE PROCEDURE [dbo].[api_falconUpdates_request]
	@parameters ExtendedDictionaryParameter READONLY,  -- incoming parameters for internal processing
	(use Key, Value2)
	@username nvarchar(32)  -- curent user.
AS
BEGIN
	-- SELECT 1  Msg, Result, Url (адрес, куда будет идти запрос)
	select '' Msg, 1 Result, 'https://falcon.web-automation.ru/api/action/updates?typeID=2' Url

	-- SELECT 2 PARAMETERS - parameters that will be passed to an external source
	select 1
    --select 'id' name, '5' value, '' [type] -- form (in the form passed), header (in http headers), 
	send get requests directly to the URL
END

All parameters that were passed to the GetItems table are passed in the @parameters parameter of this procedure

Response procedure unchanged: 

CREATE PROCEDURE [dbo].[api_falconUpdates_response]
	@response nvarchar(max),
	@parameters ExtendedDictionaryParameter READONLY,  -- incoming parameters for internal processing 
	(use Key, Value2 - the same as on request)

	@username nvarchar(32)
AS
BEGIN
	-- SELECT 1
	select '' Msg, 1 Result, @response Response
	-- SELECT 2 External action
END

We check that the request works well through the link for this element (/api/req/{code}).

3. Making GetItems for a table: 

CREATE PROCEDURE [dbo].[crud_tst-remotetable_getItems]
	@filters ExtendedDictionaryParameter READONLY,   -- IMPORTANT it must be the type 
	ExtendedDictionaryParameter (Key, Value2)
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare @result TABLE(
		id nvarchar(max),
		name nvarchar(max),
		[desc] nvarchar(max),
		typeID nvarchar(max),
		created nvarchar(max),
		isMain nvarchar(max),
		link nvarchar(max),
		techChange nvarchar(max)
	)
	-- retrieving the api request response with the falconUpdates code
	declare @falconUpdatesJSON nvarchar(max)
	select @falconUpdatesJSON = Value2 from @filters where [Key] = 'falconUpdates'
	
--The example of the answer: set @falconUpdatesJSON = '
--{"errorCode":0,"data":[{"id":1375,"name":"Updating the photo cropping mechanism (for the 
thumb version of the photo)", "desc":"","type":"error Correction","created":"2020-05-17T09:
22:32.977", "isMain":false,"link":"","techChange": null}, {"id":1362,"name": "Tree. Fixed 
the issue with the redirect and the drop-down menu item","desc":"","type": "error Correction",
"created": "2020-05-06T19:14:55.46","isMain":false,"link":"","techChange": null}, {"id":
1360,"name":"fixed a moment with paging in lists directories","desc":"","type":"error 
Correction","created":"2020-05-06T16:55:47.803","isMain":false,"link":"","techChange": null}, 
{"id":1359,"name": "fixed the moment of interaction between the modal window and the popover 
window (in quick addition for tables)","desc":"","type": "Fix errors","created":"2020-05-
06T16:00:49.217","isMain":false,"link":"","techChange": null}, {"id":1347,"name": "fixed an 
error with changing statuses in Kanban Board","desc":"","type": "error Correction","created": 
"2020-04-22T11:44:51.287","isMain":false,"link":"","techChange": null}, {"id":1345,"name":
"fixed an error in import parameters + added the itemID parameter for import/export","desc":
"","type":"error Correction","created":"2020-04-21T12:49:48.353","isMain":false,"link":"",
"techChange": null}, {"id":1341,"name": "fixed a critical error with table filters", "desc":
"If there were installed on page 2 tables with different filters, the filters of one table 
affected the other (JS error). ","type": "error Correction","created":"2020-04-19T11:11:11.
263","isMain":false,"link":"","techChange": null}, {"id":1338,"name":"logic Refinement for 
a Timer-type form field", "desc": "ability to restart the field, correction of code and 
itemID accounting in as-timer","type": "error Correction","created":"2020-04-17T21:41:04.297",
"isMain":false,"link":"","techChange": null}, {"id":1329,"name": "Fixed saving a single field 
for the checkbox Set form field","desc":"","type": "error Correction","created": "2020-04-
10T14:04:02.38","isMain":false,"link":"","techChange": null}, {"id":1327,"name":"edit error 
in Metrics (boards)","desc":"","type": "Fix error","created":"2020-04-02T20:47:23.627",
"isMain":false,"link":"","techChange": null}, {"id":1314,"name": "Uploading a table to Excel 
- deleting html tags and correcting the table border", "desc":"","type": "error Correction",
"created":"2020-03-30T15:46:03.927","isMain":false,"link":"","techChange": null}, {"id":1313,
"name":"fixed inaccuracy when printing the table (added page reloading)","desc":"","type": 
"error Correction","created":"2020-03-30T15:44:43.24","isMain":false,"link":"","techChange": 
null}, {"id":1312,"name": "fixed a script error after exporting a table to Excel","desc":"",
"type": "Fix errors","created":"2020-03-30T15:43:56.543","isMain":false,"link":"","techChange": 
null}, {"id":1308,"name":"fixed an error with saving HTML via inline edit in a table","desc":"
","type": "error Correction","created":"2020-03-25T16:04:09.103","isMain":false,"link":"","tech
Change": null}, {"id":1297,"name": "fixed nested table pagination error","desc":"","type": "error 
Correction","created": "2020-03-13T10:52:02.697","isMain":false,"link":"","techChange": null}, 
{"id":1295,"name": "fixed a moment on overlapping context forms and modal Windows","desc":"","type": 
"Fix errors","created":"2020-03-11T13:14:25.17","isMain":false,"link":"","techChange": null}, 
{"id":1274,"name":"fixed a critical error in the file Manager when applauding","desc":"","type": 
"error Correction","created": "2020-02-19T22:43:04.627","isMain": true,"link":"", "techChange":
"in the control panel, the file Manager code (uploadsadmin) - lowercase letters"}, {"id":1268,"name": 
"newtonsoft.JSON Liba Updated","desc": "System change, you must make changes to web. config when 
updating the kernel","type": "error Correction","created":"2020-02-16T12:27:33.45","isMain":false,
"link":"","techChange":"when updating the kernel, replace the JSON Libu in WebConfig \\n \\n \\n\\t
\\t\\t\\t\\t"}, {"id":1263,"name":"fixed the problem of Russian letters in the URL (when reduced to 
lowercase)","desc":"","type":"error Correction","created":"2020-02-13T10:37:41.677","ismain":false,
"link":"","techChange": null}, {"id":1257,"name": "fixed a bug on sharing a modal form and a popup 
forms", "desc":"","type": "error Correction","created":"2020-02-06T11:54:58.187","isMain":false,
"link":"","techChange":null}],"outputType":"json","result":true,"msg":""}
--'
	insert into @result
    SELECT *
    FROM OPENJSON(@falconUpdatesJSON, '$.data')
    WITH (
            id int '$.id',
            name nvarchar(512) '$.name',
            [desc] nvarchar(max) '$.desc',
            [type] nvarchar(512) '$.type',
            created nvarchar(512) '$.created',
            isMain nvarchar(512) '$.isMain',
            link nvarchar(512) '$.link',
            techChange nvarchar(512) '$.techChange'
        )
	-- 1 SELECT - data themselves
	select * from @result
	order by  id desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

	-- 2 SELECT - number in the table
	select count(*) from @result

END

 Note: 

  • We must use ExtendedDictionaryParameter.
  • The response from each API request is stored in @filters with the request code. 
  • In the example, we process the response in JSON. For this to work correctly, the SQL compatibility level must be at least 130.
--How do I find out the compatibility level of SQL Server?
SELECT compatibility_level, *
FROM sys.databases --WHERE name = 'dbName';

--How to set the compatibility level
ALTER DATABASE dbName
SET COMPATIBILITY_LEVEL = 130;

More information - https://docs.microsoft.com/ru-ru/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

Good article about parsing JSON via SQL - https://habr.com/ru/post/343062/

As a result, the component will first collect data from external sources and pass it to GetItems in the @filters collection (each response is found by the API request code).  

Example of a similar table  https://falcon.web-automation.ru/tst-remotetable

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started

SQL-инструмент для создания личных кабинетов на сайте

Суть подхода и история создания Falcon Space

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

 
Note