Managing SQL Queries Using Service Layer API

To grant permission to a normal user to create, update, remove, and read Service Layer SQLQueries, carry out the following steps:

1. Log in to the SAP Business One, Desktop client as a super user.
2. From the menu, select Administration → System Initialization → Authorizations → General Authorizations.
3. Grant Full Authorization to the user on the sections Modify SQL Queries in Service Layer.

To grant permission to a normal user to execute (List) the query, carry out the following steps:

1. Log in to the SAP Business One, Desktop client as a super user.
2. From the menu, select Administration → System Initialization → Authorizations → General Authorizations.
3. Find the specific SQL Query from Service Layer SQL Query section.
4. Change No Authorization to Full Authorization.

In this video you’ll learn how to use SQLQuery entity and execute queries.

In this exercise, you’ll practice create, update, delete, and execute queries using SQLQuery entity in the Service Layer.

Before starting this exercise, make sure that you:

• Install a REST API client tool, such as Postman.
• Download the Demo UK International database from SAP Business One Localized Demo Databases
• Complete a login request so that you have a session ready to use.

1. To create a query definition, amend the following HTTP sample request and send:

Sample request

Code Snippet

Copy codeSwitch to dark mode

1234567
POST https://localhost:50000/b1s/v2/SQLQueries
{
    "SqlCode": "Demo_Q1",
    "SqlName": "Demo - list of open Salesorder",
    "SqlText": "SELECT T0.[DocNum],T0.[DocDate], T0.[DocDueDate], T0.[CreateDate],T2.[Name] 'Contact Parson' FROM ORDR T0 INNER JOIN OCPR T2 ON T0.[CntctCode] = T2.[CntctCode] WHERE T0.[DocStatus] = 'o'and T0.[DocDueDate] > '2020-01-01'order by T0.[Createdate] desc"
    }
 

2. Check the response:

   1. Check the response code - it should be 201 Created if the HTTP request is successful.

   2. Check the response content - it should contain information on the query definition you created.

3. To execute the query which you created in step1, amend the following HTTP sample request and send:

Sample request

Code Snippet

Copy codeSwitch to dark mode

1
POST https://localhost:50000/b1s/v2/SQLQueries('Demo_Q1')/List

4. Check the response:

   1. Check the response code - it should be 200 OK if the HTTP request is successful.

   2. Check the response content - it should contain the query result as defined in the query definition.

   3. At the end of the response content, there is the property odata.nextLink, which provides the link for the next collection chunk.

5. To update the query (which we created in the step with the parameters), amend the following HTTP sample request and send:

Sample request

Code Snippet

Copy codeSwitch to dark mode

1234567
PATCH https://localhost:50000/b1s/v2/SQLQueries('Demo_Q1')
{
    "SqlCode": "Demo_Q1",
    "SqlName": "Demo - list of open Salesorder",
    "SqlText": "SELECT T0.[DocNum],T0.[DocDate], T0.[DocDueDate], T0.[CreateDate],T2.[Name] 'Contact Parson' FROM ORDR T0 INNER JOIN OCPR T2 ON T0.[CntctCode] = T2.[CntctCode] WHERE T0.[DocStatus] = :DocStatus and T0.[DocDueDate] > :DocDueDate order by T0.[Createdate] desc"
}
 

6. Check the response:

   1. Check the response code - it should be 204 No Content if the HTTP request is successful.

   2. Check the response content - it should contain empty response.

7. To execute the query with parameters, amend the following HTTP sample request and send:

Sample request

Code Snippet

Copy codeSwitch to dark mode

12345
POST https://localhost:50000/b1s/v2/SQLQueries('Demo_Q1')/List
{
    "ParamList":"DocStatus='O'&DocDueDate='2020-01-01'"
}
 

8. Check the response:

   1. Check the response code - it should be 200 OK if the HTTP request is successful.

   2. Check the response content - it should contain the results for the query definition.

9. To delete the query definition created in step 1, amend the following HTTP sample request and send:

Sample request

Code Snippet

Copy codeSwitch to dark mode

1
DELETE  https://localhost:50000/b1s/v2/SQLQueries('Demo_Q1')

10. Check the response:

    1. Check the response code - it should be 204 - No Content, if the HTTP request is successful.

    2. Check the response content - it should contain an empty response.