SYSPRO OData
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).
Exploring
Systems Integrators, Independent Software Vendors and SYSPRO Customers can benefit from this solution in various ways:
-
Perform easier and faster integrations between other systems and SYSPRO data using the HTTP protocol stack
-
Access and interact with SYSPRO company and system-wide databases in a consistent manner using RESTful web services
-
Access a simple and uniform way to share data in a discoverable manner
-
Extend SYSPRO's use for third-party data analysis tools (e.g. Excel, PowerBI, Tableau and other external analytical solutions)
-
Simplify the data access mechanism
-
Lower the risk of inappropriate data access
-
Prevent the requirement for direct SQL Access in future
-
Retrieve data from a SYSPRO Business Activity Query
As indicated on the diagram, there are four main components to consider:
OData Client Applications
-
Client applications that are OData-compliant can consume the OData feed exposed by the SYSPRO OData Service and gain access by providing the basic authentication details over HTTPS.
-
The functionality available from the SYSPRO OData Service depends on the client application's capabilities.
-
The client application is then used to process and present data in a meaningful way.
IIS Website and SYSPRO OData Service
-
The SYSPRO OData Service is hosted by Internet Information Services (IIS).
-
A secure HTTPS connection must exist between the SYSPRO OData Service and the OData Client application.
-
SYSPRO OData interacts with SYSPRO and the SYSPRO databases to expose the SYSPRO OData entities (and the data contained within these entities) using OData 4.0 standards.
-
Basic authentication over HTTPS is used to authenticate OData operators of the SYSPRO OData Service.
-
REST calls received from the OData Client applications are serviced by SYSPRO OData according to OData 4.0 standards.
Microsoft SQL Server
-
This is the SQL Server responsible for hosting the SYSPRO databases and equates to the data store for SYSPRO OData.
-
For the SYSPRO OData Service to gain access, SQL users must be configured within Microsoft SQL Server with read-only access to the relevant tables.
Currently, OData operators only require READ access to the SYSPRO databases and relevant tables.
SYSPRO
-
SYSPRO provides all the required functionality for SYSPRO OData to establish safe connections to the SYSPRO databases and perform the required license check for the OData functionality.
-
SYSPRO allows SYSPRO OData access to all the required SYSPRO information for serving correct SYSPRO OData entities, entity relationships and entity properties (i.e. fields).
The SYSPRO OData feature provides SYSPRO administrators with a mechanism for flagging a SYSPRO operator as an OData operator. The system then generates a set of credentials that can be used in the OData environment which can then be sent via email to a third-party user to authenticate to the OData layer and SQL databases.
All controlled access to the SYSPRO database then occurs by means of the configured SYSPRO operator code which has been given read-only access to specific companies and/or tables.
There are two types of Data Connector - OData licensing:
-
Site license
-
Managed Run Time Product license
The simplest OData license mechanism will be at the site level.
A SYSPRO site (on-premise or using SYSPRO Cloud ERP) can request an OData site license. Once the license has been issued and implemented at the site, any applications or solutions (such as analysis tools or subscriber feeds) can use the SYSPRO OData Service to access the SYSPRO databases at that site.
SQL login permissions are used to allow or deny data access, typically at the table level.
Run Time Products are applicable where an ISV has a commercial arrangement with SYSPRO. Once agreed, a Run Time Product is added to the list of available Run Time Products that can be included in the SYSPRO license.
If a customer elects to include the Run Time Product, then the license is issued with the Run Time Product included.
Once imported at the site, the Run Time Product can then pass the product code at the time of logging into e.net Solutions. The login process validates the product code against the SYSPRO license and grants access to the applicable functional areas (i.e. Standard or Managed).
In addition, if it has been agreed that the Run Time Product can access the SYSPRO OData Service, then the Run Time Product passes the product code as part of the call. This causes the SYSPRO OData Service to validate the OData Run Time Product license - providing access when relevant.
With a Managed Run Time Product license for SYSPRO OData, ISV's can connect their applications to SYSPRO for read-only data access, regardless of whether the customer has an OData Site license.
Considerations:
-
Each time the ISV product calls the SYSPRO OData Service, the Run Time Product code is passed (alongside any additional authentication) so that the service can enable the data access to proceed.
-
The OData Run Time Product requires a Managed license type. Run Time Products created prior to SYSPRO 8 2021 R1 that require OData access must be changed to be Managed licenses and require a list of e.net Functional Areas.
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
The following technology prerequisites are applicable:
-
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
-
SYSPRO OData Service
We strongly recommend exposing this service over HTTPS to ensure security.
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
Setup Options > System Setup > Connectivity
You need to configure the following setup options:
OData
This lets you connect to the SYSPRO OData Service (i.e. the OData layer).
-
OData endpoint
For example:
https://localhost/SYSPRO8Odata/SYSPROOData
Email/SMTP settings
This is required if you select to use the Send email function of the Operator Maintenance program.
- SMTP server IP address
- Outgoing email address
- Username
- Password
- Server port
- Use SSL
SYSPRO Ribbon bar > Setup > Operators
The OData user option must be enabled against the SYSPRO operators that require SYSPRO OData access.
To use the SYSPRO OData solution, the required SQL users must also be created within Microsoft SQL Server and configured with READ access to the relevant SQL tables and databases.
Therefore, from SYSPRO 8 2021 R2, the Generate Credentials function of the Operator Maintenance program does this for you automatically. An associated OData SQL user is generated with READ-ONLY permissions and the new credentials are then populated automatically within the OData user name and OData SQL login name fields.
We don't recommend using an ADMIN user or users with WRITE access to the SQL data if you create SQL users manually.
-
OData URL conventions:
Not all OData URL conventions are supported. In particular, complex multi-level URLs (i.e. multiple entities).
Although there are limitations in terms of the URL conventions, you are not limited in terms of data retrieval and required data can still be queried by using the supported URL conventions.
For example:
The $filter clause is required to select a specific record or subset of records.
Refer to the SYSPRO OData Query Options section for information regarding supported URL conventions.
-
Records:
SYSPRO OData is a light data gathering solution. Therefore, we limit to 5000 rows under the Microsoft best practice guidelines for performance and fair use.
Refer to the What if I need to call more than 5000 rows? FAQ for information about how to overcome this limitation.
Solving
This error message is displayed within the Operator Maintenance program, if your Email/SMTP settings are not configured correctly when you select the Generate credentials OData function.
The email address provided for the OData user within the Operator Maintenance program can't send to the Outgoing email address defined within the Setup Options program (Setup Options > System Setup > Connectivity).
Ensure that your Email/SMTP settings are configured correctly. Particularly the Outgoing email address entry:
This is the default email address of the sender of the message.
This entry must contain a valid email structure (e.g. auto.generated.mail@company.com).
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.
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.
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.
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
A single OData instance serves multiple companies. Once an OData site license has been issued and implemented, any OData enabled applications or solutions can use the SYSPRO OData Service to access the SYSPRO SQL databases and data at that site.
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)
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.
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.
A Status 401 Unauthorized error returned on the OData server or third-party application means that the SYSPRO OData user password is incorrect.
You can rectify this by proceeding as follows:
-
Generate a new password by selecting the Generate Credentials function of the Operator Maintenance program (SYSPRO Ribbon bar > Setup > Operators).
A new password will be sent to the relevant email address.
-
Enter the new password in the OData server or on the third-Party application.
If you receive the error message HTTP Error 404.0 - Not Found, the cause could be one of the following:
-
The OData URL is incorrect
or
-
You are using localhost instead of the server name
or
-
Internet Information Services (IIS) is not running
Ensure that Internet Information Services (IIS) is running correctly and that you use the correct server name and OData URL.
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.
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
}
},
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 indicates areas in the product that may be affected by implementing this feature:
SYSPRO Ribbon bar > Setup
From SYSPRO 8 2021 R2, this program enables you to define your OData endpoint (i.e. the address of the SYSPRO OData layer that will be used to connect to the SYSPRO OData Service).
The following setup option is available for configuration within the Connectivity System Setup options (Setup Options > System Setup > Connectivity):
-
OData endpoint
SYSPRO Ribbon bar > Setup > Operators
From SYSPRO 8 2021 R2, this program enables you to define a SYSPRO operator as an OData user and automatically creates the required SQL user within Microsoft SQL Server with READ access to the relevant SQL tables and databases.
The program includes the following OData options within the Options tab of the Options pane:
-
OData user
Once you enable this option for the operator, you can use the Generate credentials function to create the associated OData SQL user with READ-ONLY permissions.
These new credentials are then populated automatically within the OData user name and OData SQL login name fields.
This information is stored within the AdmOperatorPwd table.
-
Send Email
This lets you email the newly created credentials confidentially (using SMTP) to the email address defined against the operator, including a password reset and resend function in SYSPRO.
-
Configure OData email template
This lets you define the template to be used when emailing the OData credentials to the operator.
SYSPRO Ribbon bar > Setup > General Setup
-
SYSPRO Site Licenses
If you have a site license for the Data Connector - OData module, then this program includes the license details within the Modules pane below existing (i.e. licensed) Connected Services.
-
Run Time Product Licenses
If you have a Run Time Product license for the Data Connector - OData module, then the Run Time Products pane includes the following:
-
Run time product code
-
Product description
-
License number
-
License access type (i.e. Standard or Managed)
-
Accessible from the company browse function of the SYSPRO login screen
-
SYSPRO Site Licenses
If you have a site license for the Data Connector - OData module, then this program includes the license details within the Registration tab below existing (i.e. licensed) Connected Services.
-
Run Time Product Licenses
If you have a Run Time Product license for the Data Connector - OData module, then the Licensed Run Time Products screen includes the following:
-
Run time product code
-
Product description
-
License number
-
License access type (i.e. Standard or Managed)
-
Copyright © 2022 SYSPRO PTY Ltd.