Falcon Space. Creating an API service (incoming and outgoing API requests)

Introduction to the API

The system implements 2 mechanisms for working with the API

• Universal mechanism for incoming API requests-allows you to run some commands over HTTPS GET/POST from the outside and get some response in JSON/XML/Plain text format.
• Outgoing requests to external APIs - the system prepares a request to the external system, sends it via HTTPS GET/POST, and processes the response. 

Preparing outgoing requests, processing responses from outgoing requests, and processing incoming requests - all processing takes place through stored procedures. 

The API is Managed on the page/asapi

Universal mechanism for incoming API requests

How does the main incoming request process work?: 

1. External access via HTTPS GET to the auth method to get access token (the system checks the username and password and issues a token that will be checked in future requests). 
2. Sending an action request to perform an action (Get orders, create a new order). The following occurs within the request:
   1. The permissions to perform the operation are checked
   2. The stored procedure for processing the action is Started, and the response is prepared for the caller in the form of JSON, XML, and Plain text.
   3. A response is issued to the calling party

The API uses regular HTTPS requests with a response in JSON format. Basic methods

• auth-creating a session for using the API (parameters username, password)

• action-executing some API method (parameters can be any)

Using the API

1. Calling the get or Post authorization method /api/auth?username=&password={password}&output=json

• the API user's Name and password are set in the as_api_users table (this is not the username/password of a normal system user). 

• output-optional parameter, sets the output format (json, xml,text).

If the data is correct, we will get the token in the server response. {"errorCode":0,"token":"7285440B-BD32-405F-813D-C26DFED23DF5","result": true,"msg":""}
if there are errors, result = false and errorCode contains the error code. 

2. Calling the API method

/api/action/getOrders?token=7285440B-BD32-405F-813D-C26DFED23DF5&catID=1

Passing token, action (method code), and custom parameters. 

If everything is good, we get result: true and data contains output data from the method execution result. 
error Codes and descriptions: 

Note: 

1. in  ExtendedDictionaryParameter @parameters   we use Key, Value2, not Key, Value!
2. @parameters also passes the contents of the Request.InputStream request itself (in Key=InputStream)
3. @ parameters also passes the remoteIP - IP key of the calling party (for example, you can use it to check the legitimacy of an API request)

4. if you use the POST method for sending, be sure to specify 'content-type': 'application/x-www-form-urlencoded'. You can check such requests using the postman program.

Creating a new API method

The API is created as follows: 

1. Creates an action in the table as_api_actions (on the/asapi page)

• entityCode specifies the code of the entity we work with, for example order
• code-sets the action code. 
  

    2. The stored procedure api_{entityCode}_{code} is Created, which returns 2 select.

CREATE procedure [dbo].[api_order_getOrders]
@ExtendedDictionaryParameter READONLY parameters, -- parameters which are passed to the method
@username nvarchar(256) --API user (this is not the user's login in the system)
as
begin
	declare @catID int
	select @catID = cast(Value2 as int) from @parameters where [Key] = 'catID'

	/* select 1 - this is information about the operation. In errorCode, you can specify
          specific error codes for operations */
	select '' Msg, 1 Result, 0 errorCode

	/* select 2 - this is the data to be passed to the API request source
        (in the output JSON passed in the parameter data) */
	select * from ord_orders


       /* SELECT 3 A call to an external action (for example Request API)*/

end

 3.Calling the method as/api/action/actioncode1? token=token1&...{additional parameters}....

Calling the API without authorization

In this case, there is no need to use tokens.

• for action, specify the parameter withoutToken=true.

• Call the method without a token:/api/action/getOrders?catID=1

Setting the output format for the action. 
To do this,specify json, text, or xml in the outputType parameter for the action (as_api_actions). 

Idempotency property for API methods being created

To enable idempotence, a specific parameter or header must be passed to the API request(e.g. X-Request-ID),
containing a unique identifier: guid, a combination of the order number, date, and amount.
Each new request to be processed must include a new x-Request-ID value.
This way you can avoid problems with repeated requests (when the operation is performed twice on the server for 1 request).

Outgoing requests to external APIs

You can access external APIs by using External actions (apirequest code, usage is described in the Form documentation). 
To create a query, follow these steps:

• create a record of a new request (tables Outgoing API requests to /asapi) 

• implement the requestprocedure(it outputs the address and parameters for executing the request)

  • input @parameters ExtendedDictionaryParameter (collection of input parameters in Key nvarchar(32), Value2 nvarchar(max))  and @username (current user)

  • Returns SELECT 1 (Msg, Result and URL, ContentType)

    • URL to be called over HTTPS
    • ContentType-you can set your own ContentType for POST requests (for the form).
      • By default, it is substituted for forms multipart/form-data; boundary= - - - - - -
      • и для json тела application/json

  •  and SELECT 2 (parameters that will be passed externally - name, value, type). 

    • type-variant of form, header, json. 

      • If you need to send a post request, set the parameters in form. 

      • If json is passed (used for POST), its content will be the body of the entire POST request. All other parameters are ignored in this case. 

      • if you need regular get parameters, pass them through the URL

• implement the response processing procedure - response

  • input response from an external source as a string @response, @parameters ExtendedDictionaryParameter (a collection of input parameters in Key, value2 that came in the request procedure)

  • Response 

    • SELECT 1 Msg, Result, and Response (there may be additional processing and output of the response externally). 
      

    • SELECT 2 Calling external actions (External API requests, etc.)

• call the request via external actions (when saving the form, sending notifications, or other places). 

  for forms - Формы. How to output and process data on a form from an external source via the API 

When calling the API, for example, from a form, the Response is passed to theResponse field from SELECT 1.

You can pass the entire input variable there @response without treatment, or the results of parsing variable's @response in an understandable view.

You can use the method to test the API /Api/Req/{code} - it will return a response in JSON format. 

How do I send an outgoing POST request with a JSON body?

To do this, specify the request type POST and passing only 1 parameter with type=json in the format of a string with JSON.


Note: 
Consider the length of codes and keys (no more than 32 characters).

Work with JSON on SQL Server 
https://docs.microsoft.com/ru-ru/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15
https://stackoverflow.com/questions/2867501/parse-json-in-tsql
https://habr.com/ru/post/343062/
https://habr.com/ru/post/317166/

Work with XML on SQL Server 
https://www.sql.ru/forum/841296/razbor-xml-v-tablicu
https://stackoverflow.com/questions/15680259/parse-xml-in-sql-server/15681388
https://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server

Online editors for XML and JSON

Tools allow you to copy large text and view it through the element tree.

https://xmlgrid.net/ - view XML

https://jsoneditoronline.org/ - editor JSON