Enterprise Resource Planning Blogs by SAP
Get insights and updates about cloud ERP and RISE with SAP, SAP S/4HANA and SAP S/4HANA Cloud, and more enterprise management capabilities with SAP blog posts.
cancel
Showing results for 
Search instead for 
Did you mean: 
Trinidad
Product and Topic Expert
Product and Topic Expert
41,285
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!
42 Comments
0 Kudos
Good news in 2021
reynear
Explorer
0 Kudos
Hi, I am testing and see message: "Unrecognized resource path" Which version of SAP B1 support this feature?
former_member721896
Discoverer
0 Kudos
This works on SAP Business One for SAP HANA?
Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Reynerio,

As of SAP Business One 10.0 FP 2011, the Service Layer on Microsoft SQL Server and SAP HANA supports a highly flexible SQL Query.

Please check the official documentation for more details and create a post in the SAP Community sharing what you have tested and the error message you get so other experts can help solving your issue.

https://help.sap.com/doc/6ab840ef2e8140f3af9eeb7d8fef9f06/10.0/en-US/Working_with_SAP_Business_One_S... => Section 4. SQL Query

Regards,
Trinidad.

 

Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi Edgar,
As of SAP Business One 10.0 FP 2011, the Service Layer on Microsoft SQL Server and SAP HANA supports a highly flexible SQL Query.

Please check the official documentation for more details
https://help.sap.com/doc/6ab840ef2e8140f3af9eeb7d8fef9f06/10.0/en-US/Working_with_SAP_Business_One_S... => Section 4. SQL Query

Hope this helps
Trinidad.
txemapar
Participant
Hi mariatrinidad.martinezgea 
What a nice one!

I am testing the new functionality and user data tables are not supported yet.

I have tried to add a user data table in the list of allowed tables file, but the Service Layer reponses with "user defined table @XXXX Not Supportted ".

 

Is expected that, in the future, SQLQuery can be performed on user data tables?.
Thanks

former_member733135
Discoverer
How could I do a search by text using like '% searching%' with parameters
Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi,

Interesting requirement indeed.
Please post your request for UDTs queries into the Customer Influence system (S-user required).
Regards,

Trinidad.

 
Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi,

Could the "like" keyword help?

select CardCode from ordr where CardCode like 'c%'

Please check the document Working with SAP Business One Service Layer “4.6 SQL Keywords” sectionto get the full list of SQL keywords supported.

Hope it helps
Trinidad.
chladek
Participant
0 Kudos

Hi,
right now I am trying it, but in my case when I call the query it returns:

502 Proxy Error
The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request.
Reason: Error reading from remote server502 Proxy Error

Probably I will need increase ProxyTimeout :_(
Regards Zdenek

txemapar
Participant

Hi.

The feature "User Define table on SQLQuery for Service layer" is part of SAP Business One 10.0 Feature Package 2102

https://influence.sap.com/sap/ino/#/idea/263917

Thanks.

txemapar
Participant
0 Kudos
Hi chladek
We had been trying to make a sql query as you said, but we get with an error. Afeter many tries we make the conclusion that the problem is with user fields.



Every time we try to select an user defined fiel we get an error (502), but if we do not place any user defined field in the query the query works as normal.


mariatrinidad.martinezgea  Any suggestion or idea?




andy_bai
Advisor
Advisor
0 Kudos
an example:
GET /b1s/v1/SQLQueries('s1')/List




{
"SqlText": "select [U_F1] from [@MYTBL]",
"value": [
{
"U_F1": "test data"
},
{
"U_F1": "test data1"
},
{
"U_F1": "i001"
},
{
"U_F1": "i002"
}
]
}
Trinidad
Product and Topic Expert
Product and Topic Expert
0 Kudos
Hi Txema,

Have you tried to restart SL after adding the UDF?

Can you please try with a simple query like my colleague Andy did? It works fine for him.

If you cannot make it work after restart please create a support incident so it can be investigated further by our support team.

Regards,

Trinidad.
csimpson
Explorer
Hello,

I am trying to make a post call but I keep getting this error. Is there something I am missing or is the below not allowed?
{
"SqlCode": "WebItems",
"SqlName": "GetWebItems",
"SqlText": "SELECT (OnHand - IsCommited + OnOrder) as available FROM OITM WHERE T0.U_WebItem = 'Y'"
}

Error:
{
"error" : {
"code" : 701,
"message" : {
"lang" : "en-us",
"value" : "Invalid SQL syntax:, line 1, character position 14, mismatched input '-' expecting FROM"
}
}
}
txemapar
Participant
0 Kudos
hi mariatrinidad.martinezgea , andy.bai

I think that your Service Layer is working on a SQLServer enviroment. It´s Correct?.

We are testing SQLQuery funcionality in SAP HANA enviroment.

 

GET---> SQLQueries('sqlPPPP')


{
"odata.metadata": "https://SERVERNAME/b1s/v1/$metadata#SQLQueries/@Element",
"odata.etag": "W/\"356A192B7913B04C54574D18C28D46E6395428AB\"",
"SqlCode": "sqlPPPP",
"SqlName": "PruebaSQLParams",
"SqlText": "select \"U_USE\" from \"@NVT_FT_USO\"",
"ParamList": null,
"CreateDate": "2021-05-10",
"UpdateDate": "2021-05-10"
}



 

 

GET-->SQLQueries('sqlPPPP')/List

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html>

<head>
<title>502 Proxy Error</title>
</head>

<body>
<h1>Proxy Error</h1>
<p>The proxy server received an invalid
response from an upstream server.<br />
The proxy server could not handle the request<p>Reason: <strong>Error reading from remote server</strong></p>
</p>
</body>

</html>


 

 

Thanks!!
tkammer
Explorer
You use T0 in the WHERE clause but don't seem to have defined T0. It might work when you simply remove the T0 and just use U_WebItem on its own, because you only have one table in the FROM clause anyway.
tkammer
Explorer
This is a really great feature. But two things are missing in my opinion:

Firstly: Someone who is allowed to create queries should also be allowed to execute queries ad-hoc. Think of it like creating a query, executing it, then deleting it again.

Secondly, and this is crucial: It must be possible to allow normal users to create queries (and/or execute ad-hoc queries) because otherwise this is almost useless for loosely-coupled third-party applications that are targeted for cloud deployments of SAP Business One.

Currently only a superuser is allowed to create queries, and cloud operators don't give customers a superuser login. This means every time an updated third-party application needs a new query or a modification to an existing query, the cloud operators have to make a manual change, if they support doing that at all!

If this issue was addressed, this feature would bring cloud-based deployments of SAP Business One a bit closer to the flexibility of on-premises deployments in terms of supporting third-party extensions.
csimpson
Explorer
0 Kudos
Right that isn't the issue though the T0 is just a typo but isn't causing the error. The issue is :
(OnHand - IsCommited + OnOrder)
tkammer
Explorer
0 Kudos
Interesting. Have you tried without the parentheses?
csimpson
Explorer
0 Kudos
Yes and with and without the alias. According to the documentation this should work.
tkammer
Explorer
I just tried it and you're right, this seems to be a bug. I would open a support ticket if you haven't already.
0 Kudos
Hi!

How can I pass more than one param to ParamList?

I take error:
{
"error": {
"code": 704,
"message": {
"lang": "en-us",
"value": "Parameter error."
}
}
}

Trying this JSON:
{
"SqlCode" : "sql01purinst",
"SqlName": "queryOnPCH6",
"SqlText": "SELECT T1.DocEntry, T0.InstlmntID AS InstallmentId, T0.DueDate, T0.DunDate AS LastDunningDate, T0.InsTotal AS Total, T0.PaidToDate, T0.InstPrcnt AS Percentage, T0.DunnLevel AS DunningLevel, T0.InsTotalFC AS TotalFC, T0.Ordered AS PaymentOrdered FROM PCH6 T0 INNER JOIN OPCH T1 ON T1.DocEntry = T0.DocEntry WHERE T1.CardCode = :cdcode AND (T1.BPLId = :bp AND T0.InsTotal <> T0.PaidToDate)",
"ParamList": "cdcode,bp"
}
0 Kudos
I cannot get sql queries to work. Everything else I have tried works fine, so the service layer IS running.

I try this
POST https://bitp-des-005:50000/b1s/v2/SQLQueries

with this body:
{
"SqlCode":"01",
"SqlName":"List",
"SqlText":"SELECT PriceList, Price, Currency, Factor, PriceType FROM ITM1 WHERE ItemCode=:itemCode",
"ParamList":"itemCode"
}

 

And receive this response:
{
"error": {
"code": "299",
"message": "Internal server error."
}
}

I cannot figure out what I am doing wrong. Do I need to do something to enable SQL queries?

I am using SAP B1 10.0 FP2108.

 

Sincerly,

 

Thomas
qscvisag
Explorer
0 Kudos
Hi Thomas

Just in case it has not been resolved for you, I found this post (https://answers.sap.com/questions/13575285/sql-queries-with-service-layer-returns-error-code.html)

that describes the following solution:

  • uninstall Servicelayer

  • delete Folder "Program Files\SAP\SAP Business One ServerTools\ServiceLayer"

  • install Servicelayer


Kind regards

Quentin
I much appreciate your comment, but that is actually my own question as well 🙂

But, it has been resolved, thank you.
tkammer
Explorer
0 Kudos
I think you should leave out the ParamList from the JSON you're sending. It will be generated automatically.
divtech
Explorer
0 Kudos
Hi Taylan/Charles,

Did you guys end up getting this to work? We are having some trouble with this exact problem on version 10 HANA FP 2111.

{
"SqlCode": "Sq01",
"SqlName": "ItemAvailable",
"SqlText": "SELECT T0.ItemCode,T0.U_Program_St_Dt,(T1.OnHand-T1.IsCommited) As Available FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode Where T1.WhsCode='01'"
}


With the following response from the service layer:
{
"error": {
"code": 701,
"message": {
"lang": "en-us",
"value": "Invalid SQL syntax:, line 1, character position 64, mismatched input '.' expecting FROM"
}
}
}

 

Any feedback is appreciated! Thanks!

Jaz
user4711
Explorer
0 Kudos
Hello,

that's a nice feature. It could become a perfect addition in combination with standard Service Layer routes.

Anyway, we struggle with the amount of accessible tables. For example, we cannot access Service Calls (OSCL). But it seems like there are many other missing tables.

Is there a roadmap for tables to be integrated? On the current state of this feature (published over a year ago) I cannot rely and base my application.

Best regards

Thomas
user4711
Explorer
Ah, it works like this. Documentation talks mostly about removing tables from the allowlist, but we were actually pretty stupid to not try adding columns to it. Works like a charm:

 

If you need access to more tables then you need to add the tables to the allowlist.

 

Please try the below steps.

1. Open /usr/sap/SAPBusinessOne/ServiceLayer/conf/b1s_sqltable.conf. (This file is in JSON format)

2. Add the required tables in TableList:

3. Save the file.

4. restart the service layer.

5. Now try to create SQLQueries.

 

Please refer to page 161, of the service layer guide.

https://help.sap.com/doc/6ab840ef2e8140f3af9eeb7d8fef9f06/10.0/en-US/Working_with_SAP_Business_One_S...

 
former_member816643
Discoverer
0 Kudos
I try to add a user-defined table in the list of allowed tables file (Table name "SYNC")
{
"TableList": [
"ADM1",
"ORDR",
"CINF",
"SYNC"
],
"ColumnExcludeList": {},
"ColumnIncludeList": {}
}

with this body:
{
"SqlCode": "getSyncItems",
"SqlName": "GetItems",
"SqlText": "select ItemCode, ItemName from SYNC"
}

And receive this response:
{
"error": {
"code": 402,
"message": {
"lang": "en-us",
"value": "Bad configuration in file 'b1s_sqltable.conf'"
}
}
}
0 Kudos
Hi, I am testing and see message: "Unrecognized resource path"

I have SAP Business One 10.0 FP 2020

 

POST https://XXXXXXXX:50000/b1s/v2/SQLQueries

{

 "SqlCode": "MyNewSQLQuery",

 "SqlName": "GetItems",

 "SqlText": "select ItemCode, ItemName, ItmsGrpCod from OITM"

}



Res

---------------------------------------------------------------


{

    "error": {

        "code": 200,

        "message": {

            "lang": "en-us",

            "value": "Unrecognized resource path."

        }

    }

}



Why?
peternjesen
Member
0 Kudos
Hi

Has anyone found a solution to this error:

SELECT T0.ItemCode,(T0.OnHand-T0.IsCommitted) AS 'Available' FROM OITM T0 WHERE T0.ItemCode = 'A00001'

Request:

{

    "error": {

        "code": 701,

        "message": {

            "lang": "en-us",

            "value": "Invalid SQL syntax:, line 1, character position 32, mismatched input '.' expecting FROM"

        }

    }

}

azaleamaemae
Explorer
0 Kudos
Thomas,

We had a couple of SQLQueries working great for us, until a recent upgrade.

Now we are also getting the Code 299 / Internal Server Error response.

Our SAP technicians haven't been able to replicate the issue in response to our incident ticket.

How did you resolve this?
azaleamaemae
Explorer
0 Kudos
Hi Taylan -

I've been developing an integration with our CRM software (Salesforce) and needed a way to load a specific Business Partner address without having to load all 4,000 addresses that are associated with some of our BP's - and the SQLQuery gave us the perfect solution.

So, I understand why you're saying it's not a good solution for your loose-coupled cloud scenario, but, you might be overstating the case by calling it "almost useless."
S0010812346
Explorer
0 Kudos
Hi Walter,

Maybe the problem is because Service Layer requests are case sensitive. For example, for logon, make sure that you are using →/b1s/v1/Login instead of →/b1s/v1/login.

Best regards

Walter.
rajesh_khater
Active Participant
0 Kudos
To load a specific Business Partner, you could have also used the BusinessPartners endpoint with a specific CardCode, like this:

https://localhost:50000/b1s/v1/BusinessPartners('C00002')

 
rajesh_khater
Active Participant
0 Kudos
The Page No can change from version to version.

Refer to the section 4.5 Query Allowlist and under that 4.5.1 Table Allowlist

 
rajesh_khater
Active Participant
0 Kudos

It will work if you prefix the UDT name with @ i.e. @SYNC

I just tried with a UDT table. Basically you have to give the table name exactly as it is in the database.

rajesh_khater
Active Participant
0 Kudos

You try any SQL with the + (addition or concatenation) or - (subtraction) or * (for multiplication) operators, it is not working.

It seems to be a limitation of Service Layer. These operators are not documented in the Service Layer Guide under the section SQL Keywords and SQL Functions or anywhere else. So I am assuming that the Service Layer does not support these operators.

The Service Layer Guide mentions this:

"From the practical usage perspective, the Service Layer is designed to support the following subset of SQL keywords, which are most commonly used by partners"

It seems their research missed the fact that usage of + and - operators is very very common !

Even common functions like CAST or CONVERT are not supported.

ealves
Member
0 Kudos
Olá, preciso de ajuda!

 

Estou tentando criar uma consulto que utiliza DAYS_BETWEEN, porém estou recebendo o erro abaixo:

 

Consulta: 

SELECT
T0.CardCode,
COUNT(T0.CardCode) AS QtdDebit,
SUM(T1.Debit) AS DebitTotal
FROM JDT1 T1
INNER JOIN OINV T0 ON T0.DocNum = T1.BaseRef
WHERE T1.TransType = 13
AND T1.ShortName = T0.CardCode
AND T1.BalDueDeb = T1.Debit
AND T1.MthDate IS NULL
AND (DAYS_BETWEEN(CURRENT_DATE, T1.DueDate)) < 0
GROUP BY T0.CardCode

 

Error:

{

    "error": {

        "code": 701,

        "message": {

            "lang": "en-us",

            "value": "Invalid SQL syntax:SELECT\r\n\t T0.CardCode,\r\n\t COUNT(T0.CardCode) AS QtdDebit,\r\n\t SUM(T1.Debit) AS DebitTotal \r\nFROM JDT1 T1 \r\nINNER JOIN OINV T0 ON T0.DocNum = T1.BaseRef \r\nWHERE T1.TransType = 13 \r\nAND T1.ShortName = T0.CardCode \r\nAND T1.BalDueDeb = T1.Debit \r\nAND T1.MthDate IS NULL \r\nAND (DAYS_BETWEEN(CURRENT_DATE, T1.DueDate)) < 0 \r\nGROUP BY T0.CardCode, line 11, character position 5, Cannot support this function or expression: 'DAYS_BETWEEN'"

        }

    }

}

GurayMollov
Explorer
0 Kudos
Units of Measure tables must be added to "Allowed Tables" list.