NEW!!! SAP Business One Service Layer – SQL Query

As of SAP Business One 10.0 FP 2011 you can directly create SQL queries via Service Layer and run them directly without previously requiring the creation of a view. This is a great feature that I’m happy to share as I’m sure you will appreciate!!!

Let me explain with some samples how you can easily use this feature. Full details are available in the document Working with SAP Business One Service Layer “4 SQL Query” chapter.

Check also this great video to see it in action!

Of course this feature is only to query data and not to update or post new data as you know SAP Business One database cannot be modified via SQL queries and only via the provided APIs (be it DI-API or Service Layer).

How to create a new query

The first step to run a query via Service Layer is to create the query with a POST request:

POST https://server:50000/b1s/v1/SQLQueries 
{
 "SqlCode": "MyNewSQLQuery",
 "SqlName": "GetItems",
 "SqlText": "select ItemCode, ItemName, ItmsGrpCod from OITM"
}

How to execute your sql query

Once the SQLQuery created you can execute your query via the List function.
The List function can be invoked in the following way with verb GET or POST:

GET https://server:50000/b1s/v1/SQLQueries('MyNewSQLQuery')/List

POST https://server:50000/b1s/v1/SQLQueries('MyNewSQLQuery')/List

Upon success, the service returns a JSON payload, containing the exact columns requested in the SQLQuery select clause.

{
 "odata.metadata" : "https://server:50000/b1s/v1/$metadata#SAPB1.SQLQueryResult",
 "SqlText" : "select [ItemCode], [ItemName], [ItmsGrpCod] from [OITM]",
 "value" : [
  {
    "ItemCode" : "i001",
    "ItemName" : "i001",
    "ItmsGrpCod" : 100
  },
  {
    "ItemCode" : "i002",
    "ItemName" : "i002",
    "ItmsGrpCod" : 100
  },
...
]
}

As you may imagine many options are available for this feature, here you have a short list I want to share with you, please check the documentation to get all the details.

SQL Keywords

You can create much more complex queries by using keywords like orderby, groupby, right join, left join, inner join,…

SQL Functions

Currently, only the aggregation functions and a limited set of other functions like sum, avg, max, min, left, right, distinct, count, lower, upper, isnull, ifnull,… are supported. More functions would be considered for support in future according to customers’ requirements. Don’t hesitate to create a request in our SAP Business Customer Influence session!

Allowed tables and fields

The documentation provides the full list of allowed tables you can query, we find of course OITM, OCRD, ORDR, OINV, OCRP, ORTT,… among many others.

By default for each table all fields can be read.
You can limit the columns you want to expose by configuring the fields to be excluded or included with the “ColumnExcludeList” and “ColumnIncludeList”:

{
  "TableList": [
    "ADM1",
    "ORDR",
    "CINF"
  ],
  "ColumnExcludeList": {
    "ORDR": [
    "CreateDate",
    "UpdateDate"
    ],
    "CINF": [
      "Algo",
      "AliasUpd",
      "TrailDays"
    ]
  },
  "ColumnIncludeList": {
    "ADM1": [
    "CurrPeriod",
    "Street"
    ]
  }
}​

Check the documentation for details on how to configure the AllowList file.

Queries with parameters

You can create queries with parameters, in the following example :docTotal is a parameter:

POST https://server:50000/b1s/v1/SQLQueries HTTP/1.1
{
 "SqlCode": "sql01",
 "SqlName": "queryOnOrder",
 "SqlText": "select DocEntry, DocTotal, DocDate, comments from ordr where DocTotal
> :docTotal"
}

Then when we execute the query we provide the parameter value either in the body if using a POST request:

POST https://server:50000/b1s/v1/SQLQueries('sql01')/List 
{
 "ParamList": "docTotal=10.1"
}

or in the URL if using a GET request:

GET https://server:50000/b1s/v1/SQLQueries('sql07')/List?docTotal=10.1 

Pagination 

The paging mechanism on the server side is a MUST for the List function of entity SQLQueries, as it can protect the server resource from exhausting in case there are millions of records returned in one roundtrip, or in the case of a careless user joining multiple big tables without applying correct filtering conditions.

The Service Layer allows clients to change the default paging size by specifying the following request header, but please don’t abuse of this option and keep the value as lower as possible to improve performances:

Prefer: odata.maxpagesize=5

SQLQueries CRUD Operations

You can of course get the list of SQLQueries available on your Service Layer as well as create, modify and delete SQLQueries. Please check the documentation for more details.

SQL Normalization

As you know the Service Layer supports both Microsoft SQL Server and SAP HANA databases. In order to provide a unified development experience the Service Layer internally parses and normalizes the raw SQL identifiers based on the underlying database.

I don’t want to repeat myself but I really recommend you to check the document Working with SAP Business One Service Layer “4 SQL Query” chapter to get all the details. This blog is just a short overview of the available capabilities!

Hope you will enjoy this new feature.

Let us know!