SYSPRO OData

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.).

The only difference between on-premise and SYSPRO Cloud ERP sites connecting to SYSPRO OData is the base URL for the OData Website.

The format of the call to query company data is:

https:// ServiceBaseURL /SYSPROOData/ SYSPRO Company / SYSPRO Table ? Query parameters

The format of the call to query system data is:

https:// ServiceBaseURL /SYSPROOData/ system / SYSPRO Table ? Query parameters

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]

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.

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.

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.

1. Open you browser.

2. 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.

SYSPRO OData supports paging as the $top and $skip clauses are built-in as standard.

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.

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 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:

1. Launch the Internet Information Services (IIS) Manager.

2. Select your server name from the Connections menu (i.e. the host to which you want to install the certificate).

3. Navigate to the Security section by selecting the Server Certificates icon under Security.

4. Select the Complete Certificate Request function from the Actions menu.

5. 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:

1. 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.

2. Select the Bindings function of the Actions menu.

3. 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.

4. 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.

5. 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:

1. 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.

2. 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).

In this scenario:

• Select top 2 operators

• Return only their Operator codes, Names and Group codes

{
    "@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

{
    "@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" 
        }
    ]
}

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

{
    "@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 
                }
            ]
        }
    ] 
}

In this scenario:

• Query the ArCustomer+ custom form table within the EDU1 company database

“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

“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
        }
    },

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)