SYSPRO OData Service
Exploring
The SYSPRO OData feature lets you connect securely to SYSPRO data without direct SQL access and retrieve data for use by third-party applications. It is a future-proof technology that provides a RESTful API to the SYSPRO database and allows OData-compliant applications to query SYSPRO data in a standard and controlled manner.
Although it is available for both on-premise and cloud solutions, SYSPRO OData is the only mechanism available when applications need access to SYSPRO data in the SYSPRO Cloud ERP environment (the SYSPRO Cloud ERP platform doesn't allow direct database access by any application or personnel).
The SYSPRO OData Service is a standalone OData service based on OData 4.0 standards that enables third party applications and solutions to connect to the SYSPRO data tables (i.e. company and system databases):
-
Customers can use it to connect to applications and solutions (such as analysis tools or subscriber feeds)
-
Independent Software Companies (ISV’s) can use it to connect their applications to SYSPRO for read-only data access.
The service queries the system upon start-up to retrieve all the SYSPRO operators that have been configured for OData access. Therefore, when the SQL query is issued, it uses the credentials stored against the operator (in the Operator Maintenance program) for authentication and not the credentials that were provided to the third party user.
The advantage of this is that the third party user doesn't know the SQL login used for the query, no-one will know what the password is and the SYSPRO administrator can still have over-site on what the third party user is accessing.
This service is used by:
-
Data Connector - OData
SYSPRO Business Activity Queries are SQL views that are created in the relevant company databases and (because they are defined in SYSPRO) are recognized as data sources. They provide a view into the business data commonly used during the typical business management process. The queries are built and secured in the core ERP system and then surfaced to users via their enterprise reporting and connectivity solutions.
SYSPRO's Connected Services consist of multiple capabilities that provide integration touch points between SYSPRO and external parties.
Existing Connected Services include:
-
e.net Solutions
-
SYSPRO Ken the BOT (available from SYSPRO 8 2018 R2)
-
SYSPRO Supply Chain Portal (available from SYSPRO 8 2019 R2)
-
Tax Connector - Avalara (available from SYSPRO 8 2021 R1)
-
Tax Connector - Vertex (available from SYSPRO 8 2021 R1)
-
Data Connector - OData (available from SYSPRO 8 2021 R2)
OData (i.e Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. It's a REST-based protocol for querying and updating data. It is built on standardized technologies such as HTTP, Atom/XML, and JSON and is different from other REST-based web services in that it provides a uniform way to describe both the data and the data model.
OData helps you focus on your business logic while building RESTful APIs without having to worry about the various approaches to define request and response headers, status codes, HTTP methods, URL conventions, media types, payload formats, query options, etc.
It also provides guidance for tracking changes, defining functions/actions for reusable procedures and sending asynchronous/batch requests.
The OData metadata (a machine-readable description of the data model of the APIs) enables the creation of powerful generic client proxies and tools.
OData is considered to be a flexible technology for enabling interoperability between disparate data sources, applications, services and clients.
Visit https://www.odata.org for more information.
SYSPRO has commercial arrangements with several Independent Software Vendors (ISVs). The products that ISVs develop require integration into SYSPRO, often using e.net Solutions to call e.net business objects.
Run Time Product licenses are issued for this purpose as they allow third-party products to connect seamlessly to e.net Solutions at a SYSPRO site (if they've selected to license the Run Time Product) without any further e.net licensing implications.
While the Run Time Product license doesn't require any additional configuration or assignment of licenses at the site, the SYSPRO site must have licensed the underlying required module.
For example:
If the Run Time Product accesses the Accounts Receivable Query Functional Area, then the SYSPRO site must have also licensed the Accounts Receivable module (which would inevitably be the case otherwise there wouldn't be any Accounts Receivable data).
Run Time Products were typically allowed open access to any e.net functional area. However, from SYSPRO 8 2021 R1 onwards, it's possible to have two types of Run Time Product access:
-
Standard
This means that all functional areas are accessible to the Run Time product.
-
Managed
This means that a limited number of functional areas are accessible by the Run Time Products.
In this case, the Run Time Products definition file lists the e.net functional areas required and the license generated and applied at the SYSPRO site ensures that only these functional areas are licensed.
You can check the type of access for your Run Time Product, by viewing the Run Time Products pane of the Company Maintenance program (indicated in the Access column).
Starting
To use this feature, the following module(s) must be installed according to the terms of your software license agreement:
-
e.net System Manager
-
Data Connector - OData
The following technology prerequisites are applicable to using the SYSPRO OData Service:
-
Microsoft .NET Framework 4.6
-
Internet Information Services (IIS) - including:
-
.NET Core hosting bundle (version 3.1.12 or higher)
Minimum .NET Core frameworks required by the SYSPRO OData API:
-
Microsoft.AspNetCore.App (version 3.1.8 or higher)
-
Microsoft.NETCore.App (version 3.1.0 or higher)
-
NETStandardLibrary (version 2.1.0 or higher)
-
-
SSL Certificate for live production sites
The SYSPRO OData deployment must be secured by an IIS certificate.
An SSL certificate can be purchased online and deployed on the host server. It is essentially the remit of the solution platform provider to assist in deploying a valid SSL certificate into the IIS instance and to configure the solution for access on Port 443.
-
-
A connection to Microsoft SQL Server
-
A connection to the SYSPRO 8 e.net Communications Load Balancer via net.tcp
-
This service is installed using the SYSPRO Installer Application.
We strongly recommend exposing this service over HTTPS to ensure security.
The following setup options must be configured to use this service:
Setup Options > System Setup > Connectivity
OData
-
OData endpoint
This configuration is required to connect to the SYSPRO OData Service (i.e. the OData layer).
The authentication used by the SYSPRO OData Service is Basic Authentication with the appropriate SQL user credentials. Therefore, a separate SQL login must be created for this service, as using the standard sa user is not recommended.
You can use the Generate Credentials function within the OData section of the Operator Maintenance program to automatically create these SQL users and link them to an operator defined as an OData user.
The SYSPRO OData Service completely bypasses SYSPRO’s security framework because it doesn't use a standard SYSPRO operator to login to SYSPRO OData, therefore security controls (such as branch access, etc.) doesn't apply.
Solving
The following files are located in the folder where you installed the service:
-
appsettings.json
-
web.config
If the SYSPRO OData Service is unable to start, a file named ODataFailedToStart.txt is created in the default install location (i.e. inetpub > wwwroot > SYSPRO8OData) and includes details of the error(s) for the startup failure.
An OData Validation error message is returned if an error occurs during the process of a URL request, such as incorrect parameter information contained within the endpoint.
For example:
Ensure that you capture the correct base URL and subsequent entities (e.g. company ID, table name, query parameters, etc.).
The URL used to query data within SYSPRO comprises the base URL for the OData website, followed by separate entities that change depending on the information you are querying (e.g. company ID, table name, query parameters, etc.).
For example:
https://localhost/SYSPRO8Odata/SYSPROOData/edu1/ArCustomer
where:
-
https://localhost/SYSPRO8Odata/SYSPROOData indicates the static base URL.
-
/edu1/ArCustomer indicates the dynamic entities added to the endpoint.
-
edu1 indicates the company being queried.
-
ArCustomer indicates the table within that company.
-
The only difference between on-premise and SYSPRO Cloud ERP sites connecting to SYSPRO OData is the base URL for the OData Website.
For example:
-
On premise:
https://localhost/SYSPRO8Odata/SYSPROOData/edu1/ArCustomer
-
SYSPRO Cloud ERP:
https://pmotst01.sysprocloud.com/SYSPRO8OData/SYSPROOdata/edu1
The format of the call to query company data is:
https:// ServiceBaseURL /SYSPROOData/ SYSPRO Company / SYSPRO Table ? Query parameters
For example:
https://localhost/SYSPRO8Odata/SYSPROOData/edu1/ArCustomer?select=Customer,Name
The format of the call to query system data is:
https:// ServiceBaseURL /SYSPROOData/ system / SYSPRO Table ? Query parameters
For example:
https://localhost/SYSPRO8Odata/SYSPROOData/system/AdmOperator?$Select=Operator, Name, GroupCode&$Top=2
The SYSPRO OData solution provides a standard way of connecting and integrating with other applications and is therefore applicable to anyone who wants to retrieve data from the SYSPRO databases using one of the following methods:
-
Excel, PowerBI, Tableau and other external analytical solutions.
-
PHP where a REST-friendly address is required, as well as the ability to query data and insert select statements in a browser (e.g. HTTP requests).
-
Developers working in Visual Studio who need to query SYSPRO data.
ISV's can access SYSPRO OData (without the customer having to license it for their SYSPRO site) by passing their runtime product code when calling the SYSPRO OData Service.
To pass a runtime product code with a call to the SYSPRO OData Service, the words #RunTimeProductCode# and the actual product code must be included as part of the username of the basic authentication information for all API requests.
The structure of the username field should be as follows:
[username]#RunTimeProductCode#[runtime product code]
For example:
-
Runtime product code: OData123
-
Username: Blogs
Based on this example, the username field for the basic authentication must be specified as:
Blogs#RunTimeProductCode#OData123
If no runtime product code is provided and the site isn't licensed for the Data Connector - OData module, then the SYSPRO OData functionality will not be available for use.
You would use the GetVersion call.
For example:
https://localhost/SYSPRO8OData/GetVersion
The GetVersion call not only returns the version of the SYSPRO OData Service; it also:
-
Refreshes the company data structure to ensure that any database configuration changes made within SYSPRO are refreshed and used by the service.
-
Verifies that the SYSPRO OData Services are communicating correctly.
When the GetVersion call is unsuccessful, a log file is generated in the Inetpub>wwwroot>SYSPROOData>Log folder named ODataFailedToStart.txt.
SYSPRO OData supports paging as the $top and $skip clauses are built-in as standard.
All calls are GET type calls, as SYSPRO OData only supports reading data.
Currently, SYSPRO OData doesn't have rate limitations (e.g. rows per minute or per second limiting). However, to assist with faster calls and returns, developers and ISV's need to be mindful of how much data they are consuming, taking care to use query parameters to limit the amount of data being returned.
-
Open you browser.
-
Enter your URL and the $metadata clause.
For example:
https://localhost/SYSPRO8OData/SYSPROOData/edu1/$metadata
This returns an XML format listing of each table as an entity (i.e. table name in the database), properties (i.e. column names) and navigation properties.
To override the set limits of data returned, specify the normal OData URL conventions using OData structure and query language.
There are various ways to overcome the 5000 row limit:
-
You can limit the calls to weeks, periods, months or years and then combine multiple calls into one dataset within your BI tool.
-
You can use an ETL tool to refresh the data into a Data Lake or Repository and then run the BI tool on the dataset created from a feed within the SYSPRO OData solution.
-
The SYSPRO Business Activity Queries feature (available from SYSPRO 8 2021 R2) lets you create Views on the Database for OData consumption, thereby providing aggregation and summary custom datasets. These can then be used within Insight Tiles, reports, etc.
Custom form tables don't appear in the listing of tables as you would expect to see them in Microsoft SQL Server (i.e. SQL tables ending with a plus). Instead, they are now suffixed with _company.
For example:
Microsoft SQL Server: ApSupplier+
OData: ApSupplier_edu1
The SYSPRO OData solution can be deployed on-premise or within the SYSPRO Cloud offering:
-
On-Premise (or MCS Cloud):
The SYSPRO Region/Partner Tech Consultant or Customer
-
Cloud:
The SYSPRO Cloud support teams. Specifically, by change request to managed services or via the cloud portal.
For on-premise deployments, the deployment team should have the following skills:
-
Windows Service Deployment (to install and configure the solution)
-
SQL Instance Management (to configure users and manage permissions)
-
OData awareness (to output data)
-
SYSPRO OData best practice awareness (to optimize performance)
-
IIS SSL Certification Application (for security)
Requirements:
-
Your server certificate
-
Your intermediate certificates
-
Your private key
Installation instructions:
-
Launch the Internet Information Services (IIS) Manager.
-
Select your server name from the Connections menu (i.e. the host to which you want to install the certificate).
-
Navigate to the Security section by selecting the Server Certificates icon under Security.
-
Select the Complete Certificate Request function from the Actions menu.
-
From the Complete Certificate Request wizard, browse for your Server Certificate file (previously saved on your server’s desktop) and select OK.
Binding your certificate to your website:
The following describes how to assign or bind the SSL certificate to the appropriate website:
-
From the Connections menu in Internet Information Services (IIS) Manager, expand your server's name, followed by the Sites folder, and then select the site that you want to secure.
-
Select the Bindings function of the Actions menu.
-
From the Site Bindings window, select the Add function.
If you already have the appropriate site binding created, select the Edit function to change the SSL Certificate accordingly.
-
Enter the following within the Add Site Binding window:
-
Type: https
-
IP Address: All unassigned.
If you have multiple IP addresses, select the correct one that applies.
-
Port: 443
-
SSL Certificate: Select the friendly name of the SSL certificate you've installed.
You can also select the View function to confirm the validity of the certificate.
-
-
Select OK to finish binding the SSL certificate to your live website.
As SSL certificates have an expiry date, ensure that you renew your certificates timeously to prevent any unexpected down time.
The SYSPRO OData deployment must be secured by an IIS certificate.
An SSL certificate can be purchased online and deployed on the host server. It is essentially the remit of the solution platform provider to assist in deploying a valid SSL certificate into the IIS instance and to configure the solution for access on Port 443.
The core function of an SSL certificate is to protect server-client communication. It protects your information online by encrypting any data sent to you, and then decrypting it once you've received it (i.e. the data is locked and can only be unlocked by the intended recipient as no-one else can have the key to open it).
SSL certificates also provide website authentication - identity verification being one of the most important aspects of web security.
The e.net business object layer is still the preferred mechanism of applications accessing any business logic, including data queries, as well as add, update, delete and transactional access.
The Data Connector - OData module is designed for cases where the e.net business object layer is unable to provide the data access required for the business solution.
The SYSPRO OData feature is a significant improvement over applications that use direct database access to the SYSPRO data.
This is because that type of direct database access doesn't provide traceability or visibility about what access is being performed, regardless of whether the access has the correct permissions and how frequently access is being made.
This lack of visibility and traceability leads to a high-risk site.
As direct SQL access is restricted in the SYSPRO Cloud ERP environment, sites that upgrade from SYSPRO on-premise to a SYSPRO Cloud deployment are required to re-skill in OData and update any linked solutions using OData (rather than ODBC access to the SQL database).
Using
The following outlines the steps you need to follow after purchasing the Data Connector - OData module:
-
Ensure that the following technology prerequisites have been installed:
-
Microsoft .NET Framework 4.6
-
Internet Information Services (IIS) - including:
-
.NET Core hosting bundle (version 3.1.12 or higher)
Minimum .NET Core frameworks required by the SYSPRO OData API:
-
Microsoft.AspNetCore.App (version 3.1.8 or higher)
-
Microsoft.NETCore.App (version 3.1.0 or higher)
-
NETStandardLibrary (version 2.1.0 or higher)
-
-
SSL Certificate for live production sites
The SYSPRO OData deployment must be secured by an IIS certificate.
An SSL certificate can be purchased online and deployed on the host server. It is essentially the remit of the solution platform provider to assist in deploying a valid SSL certificate into the IIS instance and to configure the solution for access on Port 443.
-
-
A connection to Microsoft SQL Server
-
A connection to the SYSPRO 8 e.net Communications Load Balancer via net.tcp
-
-
Install the SYSPRO OData Service using the SYSPRO Installer Application.
Considerations:-
During the installation, the SYSPRO Installer Application checks if you have the correct prerequisites installed and displays the Prerequisites window if it discovers anything missing. Simply click on the Microsoft .NET Core 3.1.12 or higher Windows Hosting Bundle link to install the missing prerequisites.
The SYSPRO OData Service is then installed.
-
SYSPRO OData is configured by Internet Information Services (IIS) and uses the SYSPRO 8 e.net Communications Load Balancer to communicate.
We strongly recommend exposing this service over HTTPS to ensure security.
-
-
Define the required setup options:
-
Open the Setup Options program and navigate to the Connectivity System Setup form (Setup Options > System Setup > Connectivity).
-
Indicate the base portion of the SYSPRO OData URL and your particular endpoint at the OData endpoint field.
This lets operators who are configured as OData users to access data within the SYSPRO databases using the SYSPRO OData Service.
For example:
https://localhost/SYSPRO8Odata/SYSPROOData/edu1
-
Ensure that your Email/SMTP settings are configured correctly.
This lets you use the Send email function of the Operator Maintenance program to email an operator their OData credentials.
- SMTP server IP address
- Outgoing email address
- Username
- Password
- Server port
- Use SSL
-
-
Create your SYSPRO OData operators:
-
Open the Operator Maintenance program (SYSPRO Ribbon bar > Setup > Operators).
-
Indicate the operator code you want to use for OData or create a new operator and enter the applicable Operator Details.
Ensure that this user has ADMIN rights.
-
Navigate to the Options tab of the Options pane:
-
Enable the OData user option.
-
Select the Generate credentials function.
The system prompts you to first save your changes before generating an associated OData SQL user and password with READ-ONLY permissions. Once saved:
-
The new credentials are populated automatically within the OData user name and OData SQL login name fields
-
The system prompts you to confirm if you want to email the credentials to the operator. If you consent, the password is then encrypted and emailed to the user (using SMTP).
-
The credentials are stored within the AdmOperatorPwd table.
-
-
Optional
Use the Configure OData email template function to define the template used when emailing OData credentials to the operator.
Once configured, this template is stored in the \Base\Samples folder with the file name EML_ODASVC_EN.TXT.
Select the Send Email function to email these credentials confidentially (using SMTP) to the email address defined against the operator, including a password reset and resend function in SYSPRO.
-
-
Save your changes.
-
The following uses Microsoft Excel as an example of a third party application connecting to the SYSPRO data:
-
Launch the third-party application - in this case Microsoft Excel.
-
Select the Data tab and expand the Get Data menu, followed by From Other Sources.
Select the From OData Feed option.
In other third party applications, you would typically select the Choose OData Feed option from Available data sources (or similar wording).
-
Select Basic on the OData feed window and enter your endpoint (i.e. URL) for the applicable entity (i.e. company id or system) that you want to query.
URL example:
https://localhost/SYSPRO8Odata/SYSPROOData/edu1
-
Select OK.
The OData feed window is displayed.
-
From the Basic tab, enter the user name and password applicable for your configured SYSPRO OData operator.
-
Select the Connect function.
The endpoint is queried and the Navigator window returns all available tables for the SQL database being queried.
-
Select the tables that you want to pull into Microsoft Excel (i.e. your SQL based third party application).
The initial load of OData can take a few minutes in some instances, however subsequent calls are significantly faster as the system uses preloaded libraries, cached metadata, etc.
The SYSPRO OData Service exposes the metadata available that can be used by third party applications to understand the data, and the relationship between the data. Therefore, you can query the metadata for the service by utilizing the $metadata call (e.g. SYSPRO8OData/SYSPROOData/edu1/A/$metadata).
This applies for both standard database tables and custom form field tables as the $metadata call provides all the entity information, including the custom form entities.
This call (as per the OData standard) returns the EDMX (i.e. XML document) that contains a complete description of types, relationships and properties exposed by the service.
For example:
SYSPRO database structure:
-
https://localhost/SYSPRO8OData/SYSPROOData/edu1/$metadata
SYSPRO InvPrice table structure example:
-
https://localhost/SYSPRO8OData/SYSPROOData/edu1/$metadata#InvPrice
Sample output:
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
<edmx:DataServices>
<Schema Namespace="SYSPRODBModel" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityType Name="AbcAnalysis">
<Key>
<PropertyRef Name="Element" />
<PropertyRef Name="EntryNumber" />
<PropertyRef Name="Incident" />
<PropertyRef Name="InvRegJournal" />
<PropertyRef Name="TrnDate" />
</Key>
<Property Name="Element" Type="Edm.String" Nullable="false" />
<Property Name="Incident" Type="Edm.String" Nullable="false" />
<Property Name="TrnDate" Type="Edm.DateTimeOffset" Nullable="false" />
<Property Name="InvRegJournal" Type="Edm.Decimal" Nullable="false" />
<Property Name="EntryNumber" Type="Edm.Decimal" Nullable="false" />
<Property Name="StockCode" Type="Edm.String" />
<Property Name="GlPeriod" Type="Edm.Decimal" Nullable="false" />
...
The SYSPRO OData Service exposes read-only data as entities that have a direct mapping to the SYSPRO database tables.
For example:
-
https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer returns data for the ArCustomer table from SYSPRO company EDU1.
-
https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer/$top=600 returns the top 600 rows of data for the ArCustomer table from SYSPRO company EDU1
Therefore, the SYSPRO OData Service supports the following OData query options on the SYSPRO entities:
-
$filter
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?$filter=Name eq ‘Spider Man ind’
-
$count
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?count=true
-
$orderby
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?orderby=ShortName, Name
-
$skip
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?skip=10
-
$top
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?top=10
-
$expand (only 1st-level expansion is supported)
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?expand=ArCustomer_2
-
$select
e.g. https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?select=Customer,Name
Paging is supported via the $skip / $top clauses.
OData protocol provides powerful features for querying data via URLs, very similar to Microsoft SQL Server. Therefore, the following cheat sheet can assist regular SQL users in understanding how to query data via OData, and how the OData features correspond to the most commonly used SQL features:
Feature |
Microsoft SQL Server |
OData |
---|---|---|
Number of records in a table |
SELECT COUNT(*) FROM Emp |
/Emps/$count |
Querying specific table columns |
SELECT ENAME, JOB, SAL FROM Emp |
/Emps?$select=ENAME,JOB,SAL |
Querying the second 5 records |
SELECT * FROM Emp ORDER BY (SELECT NULL) OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY |
/Emps?$top=5&$skip=5 |
Ordering Data |
SELECT * FROM Emp ORDER BY ENAME DESC, SAL |
/Emps?$orderby=ENAME desc,SAL |
Querying joined tables |
SELECT * FROM Dept LEFT OUTER JOIN Emp ON Dept.DEPTNO = Emp.DEPTNO |
/Depts?$expand=Emps |
Filtering data |
SELECT * FROM EMP WHERE (SAL/2 > 500 AND HIREDATE <= '01/01/1985') OR (COMM IS NOT NULL AND ENAME LIKE 'J%') |
/Emps?$filter=(SAL div 2 gt 500 and HIREDATE le 1985-01-01) or (COMM ne null and startswith(ENAME,'J')) |
Aggregating data |
SELECT SUM(SAL) AS Sum, MAX(SAL) AS Max, Min(SAL) AS Min, AVG(Sal) AS Avg FROM Emp |
/Emps?$apply=aggregate(SAL with sum as Sum,SAL with max as Max,SAL with min as Min,SAL with average as Avg) |
OData protocol supports a number of different mathematical, logical, etc. operators and functions in the $filter expression. Here you can find a brief list of these operators and functions that you can use in your OData requests together with their SQL analogs.
These are case-sensitive in OData requests.
Microsoft SQL Server |
OData |
---|---|
= |
eq |
!= |
ne |
> |
gt |
>= |
ge |
< |
lt |
<= |
le |
AND |
and |
OR |
or |
NOT |
not |
+ |
add |
- |
sub |
* |
mul |
/ |
div |
% |
mod |
( ) |
( ) |
IS NULL |
eq null |
IS NOT NULL |
ne null |
Microsoft SQL Server |
OData |
---|---|
REPLACE(X,'Y','Z') |
replace(X,'Y','Z') |
SUBSTRING(X,2,3) |
substring(X,2,3) |
TRIM(X) |
trim(X) |
CONCAT(X,Y) |
concat(X,Y) |
Microsoft SQL Server |
OData |
---|---|
DATEPART(year,X) |
year(X) |
DATEPART(month,X) |
month(X) |
DATEPART(day,X) |
day(X) |
DATEPART(hour,X) |
hour(X) |
DATEPART(minute,X) |
minute(X) |
DATEPART(second,X) |
second(X) |
Microsoft SQL Server |
OData |
---|---|
ROUND(X) |
round(X) |
FLOOR(X) |
floor(X) |
CEILING(X) |
ceiling(X) |
The following provides some examples of calls that are allowed by the SYSPRO OData Service:
Using the System entity enables you to call into the SYSPRO System database (e.g. Sysprodb).
The format of the call to query the System database is:
ServiceBaseURL /SYSPROOData/ system / SYSPRO Table ? Query parameters
In this scenario:
-
Select top 2 operators
-
Return only their Operator codes, Names and Group codes
Sample call:
https://localhost/SYSPRO8OData/SYSPROOData/system/AdmOperator?$Select=Operator, Name, GroupCode&$Top=2
Sample output:
{
"@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/system/$metadata#AdmOperator(Operator,Name,GroupCode)",
"value": [
{
"Operator": "ADMIN",
"Name": "SYSPRO Administrator",
"GroupCode": "ADMIN"
},
{
"Operator": "ALAN ",
"Name": "ALAN BROWN2",
"GroupCode": "OPS"
}
]
}
In this scenario:
-
Select everything from the SysproAdmin table
Sample call:
https://localhost/SYSPRO8OData/SYSPROOData/system/SysproAdmin
Sample output:
{
"@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/system/$metadata#SysproAdmin",
"value": [
{
"Company": "EDU1",
"DatabaseName": "DS001_CMP_EDU1_800 ",
"CollationName": "Latin1_General_BIN"
},
{
"Company": "R ",
"DatabaseName": "SysproCompanyR",
"CollationName": "Latin1_General_BIN"
}
]
}
The format of the call to query data is:
ServiceBaseURL /SYSPROOData/ SYSPRO Company / SYSPRO Table ? Query parameters
In these examples, we are querying the InvMaster table for the EDU1 company.
Sample call:
https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster
Sample output:
This will return all the rows of the InvMaster table.
Sample call:
https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster?$filter=StockCode eq 'A100'
Sample output:
{
"@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/edu1/$metadata#INVMaster",
"value": [
{
"StockCode": "A100",
"Description": "15 Speed Mountain Bike Boys",
"LongDesc": "Distribution",
"AlternateKey1": " "
"AlternateKey2": " ",
"EccUser": " ",
"StockUom": "EA",
"AlternateUom": "EA",
"OtherUom": "EA",
"ConvFactAltUom": 1.000000,
"ConvMulDiv": "M",
"ConvFactOthUom": 1.000000,
"MulDiv": "M",
"Mass": 10.000000,
"Volume": 0.300000,
...
In this example, we are joining the InvWarehouse to the InvMaster table in our call to get stock levels. To perform any expands (i.e. joins), you must include the key of the primary table in your call. Therefore, to expand InvWarehouse in InvMaster, you have to select StockCode from the InvMaster as part of the call.
In this scenario:
-
Select StockCode and Description from InvMaster
-
Join to InvWarehouse (SYSPRO handles the key joins)
-
Return Warehouse and QtyOnHand from the InvWarehouse record
Sample call:
All Stock Codes
https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster?$Select=StockCode, Description&$Expand=InvWarehouse($Select=Warehouse, QtyOnHand)
Single Stock Code
https://localhost/SYSPRO8OData/SYSPROOData/edu1/InvMaster?$Select=StockCode, Description&$Expand=InvWarehouse($Select=Warehouse, QtyOnHand)&$Filter=StockCode eq 'A100'
Sample output:
{
"@odata.context": "http://localhost/SYSPRO8OData/SYSPROODATA/EDU1/$metadata#INVMaster(StockCode,Description,INVWarehouse(Warehouse,QtyOnHand))",
"value": [
{
"StockCode": "A100",
"Description": "15 Speed Mountain Bike Boys",
"INVWarehouse": [
{
"Warehouse": "E",
"QtyOnHand": 962.000000
},
{
"Warehouse": "FG",
"QtyOnHand": 0.000000
},
{
"Warehouse": "N",
"QtyOnHand": 910.000000
},
{
"Warehouse": "S",
"QtyOnHand": 435.000000
}
]
}
]
}
The GetVersion call returns the version of the SYSPRO OData Service and triggers a “refresh” of the company data structure. This ensures that any database configuration changes made within SYSPRO are refreshed and utilized by the service.
Sample call:
https://localhost/SYSPRO8OData/GetVersion
Sample output:
The SYSPRO OData Service allows for queries on custom form tables (i.e. SQL tables ending with a plus) within the company databases. Therefore, the service checks for these upon startup and performs the required build of the custom form tables to provide for the OData URL queries.
Custom form tables are referenced with the table name followed by an underscore and company id.
For example:
The ArCustomer+ table within the Edu1 company database will be referenced as ArCustomer_edu1 in the OData URL.
Custom form tables that don't follow the standard custom form field conventions are excluded and therefore not provided for.
In this scenario:
-
Query the ArCustomer+ custom form table within the EDU1 company database
Sample call:
https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer_edu1
Sample output:
“value”: [
{
“Customer”: “000000000000001”,
“NrOfEmployee”: 5145,
“Color”: “Red”
},
{
“Customer”: “000000000000002”,
“NrOfEmployee”: 0,
“Color”: “ ”
},
{
“Customer”: “000000000000003”,
“NrOfEmployee”: 10,
“Color”: “Blue”
},
{
“Customer”: “000000000000005”,
“NrOfEmployee”: 200,
“Color”: “Silver”
},
In this scenario:
-
Query the ArCustomer+ custom form table within the EDU1 company database
-
Expand on color and name
Sample call:
https://localhost/SYSPRO8OData/SYSPROOData/edu1/ArCustomer?$expand=ArCustomer_edu1($select=Color)&$select=Name
Sample output:
“value”: [
{
“Name”: “Spider Man Ind”,
“ArCustomer_2”: {
“Color”: “Red”
}
},
{
“Name”: “Bikes & Blades - North”,
“ArCustomer_2”: {
“Color”: “ ”
}
},
{
“Name”: “Bikes & Blades - South”,
“ArCustomer_2”: {
“Color”: “Blue”
}
},
{
“Name”: “Cash Sales”,
“ArCustomer_2”: {
“Color”: “Silver”
}
},
{
“Name”: “Country Gardens - South”,
“ArCustomer_2”: {
“Color”: null
}
},
Referencing
You should only update this file for temporary debugging purposes (i.e. not as a method to update values for the service).
Any temporary changes made to the configuration files are reset to their original installation values when this application is next updated. Because these temporary changes are not carried forward, forgetting that you made them could hamper troubleshooting efforts if unexpected issues arise.
Key | Description |
---|---|
ODataMaxRecords |
5000 This indicates the default maximum records to be returned by the service. |
WCFServiceAddress |
net.tcp://localhost:31001/SYSPROWCFService This indicates the SYSPRO 8 e.net Communications Load Balancer endpoint address as configured during the service installation via the SYSPRO Installer Application. |
ServiceManagerAddress |
net.tcp://localhost:30140/SYSPROServiceManager This is reserved for future use. |
Copyright © 2022 SYSPRO PTY Ltd.