Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 


An SQL Anywhere Northwind(lite) Database with OData service

Using the Personal Web IDE with SQL Anywhere

Using the SAP Cloud Platform Web IDE with SQL Anywhere


Goto Example Northwind(lite) Web IDE Projects


If you have heard about OData then I am sure you know about the example Northwind OData service.

The Northwind service is referenced a lot in OData examples here on the SAP Community and elsewhere on the web. I was interested in replicating the OData version 2 of the Northwind service. Another primary objective was to use an actual database with the Web IDE for such a setup. The Mock Server offers great flexibility with JSON files to setup more meaningful test data but I wanted to use an actual database as part of my setup.

I discovered SQL Anywhere here on SCN SAP community a few years ago and found it to be a fantastic database/platform to use. A couple of other items came together which I cover in this blog and it enabled me to setup my Northwind(lite) database/OData service.

There are a number of reference services available at and a link to these is here

A screenshot of the current available reference services at with Northwind in the red box.



Setting Up An SQL Anywhere Northwind(lite) Database with OData service

My thanks to paulhoran who had previously setup a Northwind database with SQL Anywhere and inspired my to try it all out myself.

Paul's blog links are below, as they were extremely helpful to setup my SQL Anywhere Northwind base OData service. I was unable to successfully use Paul's original database for reasons I will cover however the OData information was invaluable for me to actually even attempt a Northwind setup.

I only have SQL Anywhere at version 17 on my computers right now and the database Paul offered for download is at version 16. I did upgrade the downloaded database to version 17 but I had a number of issues and it was not working as I expected, which at the time I thought may have been related to how I upgraded the databse. So I took the decision to create a Northwind based database myself but start with SQL Anywhere version 17. I created a standard database using the gui SQL Central tools and only changed to unicode when given the choice.  I then setup the Northwind data and OData templates. My Northwind(lite) database and log file can be downloaded here and comes with the Northwind(lite) OData services that I use in the rest of this blog. SQL Anywhere has an OData server that is compatible with version 2.0 of the OData protocol.

If you are interested in using this database then you have to have SAP SQL Anywhere installed ;). You can download it from this link.

SQL Anywhere Developer Edition Download Link

Minimum Version Required = -Using an older version may prompt for an upgrade. And Paul's blog has a Northwind Database at version 16, but I only use my example database from now on.

It is best to read on (at least a little bit more) to see how successful I was in creating a Northwind database with an OData service and why I ended up with Northwind(lite) in the title.

Northwind & SQL Anywhere Licenses

In my mind Paul had set a precedence in offering the database download based on Northwind. However I felt I had to check more of the details as I was going to do the same. From my reading of the Northwind license on the CodePlex site (Microsofts Open Source project hosting site) it enables redistribution and derivative works of the data. Also from my understanding of the SQL Anywhere side then as long as it is not used in a productive setting you are able to evaluate, develop and test applications. Exactly my intention. Link to the codeplex license site and an SQL Anywhere link is above for the download - where you can check the license.

I cover how I startup my database in the Appendix in this blog SQL Anywhere HTTPS and Startup Options but do cover a lot of the background to setting up my SQL Anywhere with encryption in my previous blog on the Earth Observation Service (see appendix). I like to setup HTTPS but it does involve a few more steps to achieve this, if you are interested in old skool HTTP then you can remove the HTTPS settings in my startup script or just create your own HTTP service.

As already mentioned there a lots of examples on the web and SAP Community based on the "Northwind" OData services. I put quotes around "Northwind" due to some of the additional steps I ended up creating in my Database to get these examples to work with SQL Anywhere and the Web IDE.
The "Northwind" based examples I will use are

  1. Startup Performance thanks to frederic.berg for this example (goto ⇒)

  2. CRU(D) Example thanks to for this example (goto ⇒)

  3. Walkthrough of Remote OData service thanks to the OpenUI5 team for this example (goto ⇒)

  4. Display images from the Northwind service thanks to for this example (goto ⇒)

Things change quickly in the world of UI5. What is standard / best practice at any particular time is not necessarily relevant today or in the future. For example OData version 4 is getting more relevant all the time but I still think version 2 will be around and relevant for a while yet. I would now highlight the point that the "Northwind" services in my database are not a perfect match to the services. You may find that it does not cover an example you had in mind.  Certain limitations of the SQL Anywhere OData service may prevent it being used for certain examples at all. However as a base service I was able to get it to work with all the examples above but with some limitations, that I will cover, in the CRU(D) example. If you do download then you are free to extend change or improve on it as you see fit.

Using the Personal Web IDE with SQL Anywhere

First I Installed Web IDE on Windows and I have used various versions of the Web IDE. You can download the trial personal edition at this link

Installation is covered in the documentation

I have had some disappointing issues installing the Web IDE on windows and relates the the way it was extracted. Once installed though all was fine.

First objective was to connect the Web IDE to my OData service in SQL Anywhere. There is no gui to set connection destinations with the personal edition so I followed the help.

Connecting remote servers

In the following directory I setup a text file called S17


The contents of the S17 file for my setup - I use HTTPS service on my laptop odata.sqlany17.haw on port 8444. The start command for my setup is in the appendix to this blog.


I found it useful to just create an example UI5 project as a base example to see the connections and control files contents e.g. file neo-app.json . As these details would prove helpful once I imported the other example projects based on the Northwind services. Also it is a good way for me to show how to setup the OData service URL


The Northwind OData service I setup is called via the following URL (using the base S17 destination details).


If you select the show details and diagram options on this screen you can see a graphic of the base Northwind OData service I have setup.


The above view is tiny but if you hit the expand icon you can view and zoom around the OData setup.


If you have ever seen the actual Northwind OData diagram you will notice the above is limited. I basically followed Paul's blog information to only setup links between Products, Services and Categories tables for my Northwind(lite) example OData service. This could easily be expanded to cover more using the OSDL features of SQL Anywhere OData functionality.

The base OData service model in the database is shown in the screenshot below.


My thanks again to Paul for his blog describing the enhanced OData support in SQL Anywhere. That blog link again as it covers the elements in the above screenshot.
Back to the WEB IDE and example app.

I took the following from the example project files to use when I adapted other peoples projects.

From the file neo-app.json  the following destination definition.
"path": "/S17",
"target": {
"type": "destination",
"name": "S17"
"description": "S17"

From the file manifest.json the URL to the OData service
		"dataSources": {
"mainService": {
"uri": "/S17/SQL17/V2/Northwind/Northwind.svc/",
"type": "OData",
"settings": {
"odataVersion": "2.0",
"localUri": "localService/metadata.xml"

As the example code may use UI5 from the cloud I was interested to see how it was defined in my local example project. I could use these settings as an example to use local UI5 resources in imported projects.
		<!-- Bootstrap the UI5 core library -->
<script id="sap-ui-bootstrap"
data-sap-ui-libs="sap.ushell, sap.collaboration"
data-sap-ui-resourceroots='{"test": "../"}'
data-sap-ui-frameOptions='allow'> // NON-SECURE setting for te


Example One

Onto the first Example of a Northwind Service which is based on Frederic's blog on Advanced Startup Performance which uses the classic Northind service. I would adapt this to use my SQL Anywhere service. A link to the performance blog again.

I downloaded the zip file of code from Frederics Github link

To get the project to import I found I have to unzip the code, enter the directory I unzipped to and then zip the project again for import.



The zip file highlighted was the one I imported into my Web IDE. When I imported directly from the downloaded zip file I had issues with the run configuration of the project.

Using the file->import menu options, I imported into a project area on my Web IDE by accepting the default "import to*" project name.

I changed the manifest.json and neo-app.json files to match my SQL Anywhere service with the settings noted earlier.

For the manifest.json change I made sure to match the brackets and had to remove the last comma in my cut and paste of the code. (the syntax red crosses in the screenshot were already shown after first opening the file and I made no changes to those lines)

The performance example loads UI5 from the cloud and at first I did not make any changes to this location. As a result I ran the project and checked in Chrome developer tools for the OData service being served from my SQL Anywhere instance.


Checking Version after version after version

After checking I had successfully changed the location of the OData service, I made a change to load UI5 from my Web IDE installation.

I changed the index.html from


After a period of further testing I then realised the default UI5 libraries were different after checking sap.ui.version in the console and I didn't think of that in the first place. Doh!

There is the possibility to use specific UI5 versions in the cloud by specifying the version in the URL. This way I could match the Personal Web IDE version to the cloud, for example.


It is worth checking the SAPUI5 version overview page.

This page points out 1.40 is out of maintenance. The Personal Web IDE (as of my current installation version of Web IDE: 161201) does support two versions still in maintenance -1.38 and 1.28.


During testing it was interesting for me to note the compression  status of the local Web IDE. It's something I'll check more into later as it is getting more compressed in the cloud.

  • Cloud based UI5 load chrome audit

  • Local UI5 load chrome audit

Example Two

Onto the second example which is based on Denise's blog for a CRU(D) best practice for SAPUI5. I would adapt this to use my SQL Anywhere service. A link to the blog again.

First I downloaded the code from Denise's github page.

I uploaded the project directly from the download for this example

I then had a few issues......

Denise had included some realistic Mock Data and checking the application with this made me realise my Northwind tables did not match. E.g the products table had a ratings column which did not exist in my database table.

<error xmlns="">
<message xml:lang="en-US">
Type 'NorthwindModel.Product' does not have a property named 'Rating'.

The project was setup to use the OData/OData.svc/ OData service from and not the named Northwind service.

So a bit of an issue how to replicate this example with SQL Anywhere. Initial thoughts were to take all the data from the service into database tables. However when I saw the supplier named "Tokyo Traders" that name appealed to me to create some test data myself. I had the idea to setup Tokyo Traders II with a great product line of Minidiscs, Walkmans and Green Tea. To me that sound like a great company  and a get rich scheme in the making 🙂

To make it happen I setup 3 new tables to match the service

I have called my tables CategoriesV2, ProductsV2 and SuppliersV2. In a default setup of the SQL Anywhere 17 OData server this would cause problems with the application as the table names would not match the service. So I took advantage of the OSDL capabilities of SQL Anywhere where I could setup my service with these table names to match the CRU(D) service calls.

Also from my reading of the code there is a call to identify the "Product ID" before the creation of a new product. This call is used to send an updated "Product ID" to the database and avoiding auto-increment data types in the table for "Product ID". One challenge for the other Northwind service in my database is that all the tables, do have auto-increment ID fields set where appropriate. So how to deal with the OData calls with auto-increment fields? This would have to be resolved before attempting to use the Northwind example when creating of new items. I don't have an answer to that! Maybe you could help me out on that one and provide an example 😉 .

The OData service was setup to use complex types in the Address. I chose to bypass/work around it by changing the source code of the project to avoid the complex types in the OData service. I'll explain the changes made below. I chose the workaround due to this statement "Complex types (except as return values from service operations)" in the "not supported" section from the OData protocol limitations page for SQL Anywhere. It was easier 🙂 for me to adapt the Address in the source code for what I wanted to achieve.

My SQL Anywhere OData service for this example looks like this.

In the screenshot I reference the tables with out the V2 suffix therefore enabling my OData service to match the expectation of the source code in the project.

The changes I made to the CRU(D) example to get it to work.

I edited the neo-app.json file as before and altered the destination.

And following Denise's blog I altered component.js to point to my SQL Anywhere service

		config : {
resourceBundle : "i18n/",
serviceConfig : {
name : "Northwind",
serviceUrl : "/destinations/S17/SQL17/V2/OData/OData.svc/"

and updated the referenced to UI5 in index.html

I ran the project to show my great product line up for Tokyo Traders II



I then added (created) a new product for my supplier a Playstation.

And Checked the SQL Anywhere database for the new product in ProductsV2 table.

One final change to adapt the complex type for the supplier address.
					<Label text="{i18n>supplierAddressName}" />
<Text text="{Name}" />
<Label text="{i18n>supplierAddressStreet}" />
<Text text="{Street}" />
<Label text="{i18n>supplierAddressCity}" />
<Text text="{City}" />
<Label text="{i18n>supplierAddressZIPCode}" />
<Text text="{ZipCode}" />
<Label text="{i18n>supplierAddressCountry}" />
<Text text="{Country}" />

I removed Address from the above lines in file SupplierAddressForm.fragment.xml

The address for Tokyo Traders is then displayed as shown below (it was missing in the previous screenshots)


Using the SAP Cloud Platform WebIDE with SQL Anywhere

To use my SQL Anywhere OData service with the SAP Cloud Platform (SCP) I needed to setup the SAP Cloud Connector and related destinations in the SCP.

Example Three

SAP Cloud Connector Setup

I installed SCC following the standard help but I did change the port, I access it on, with the changeport command noted in the documentation.

I followed the setup guides and connected to my trial instance.

For recent versions of the Cloud Connector the location ID is important to use consistently. For me that was the hawwin1.. text as partly shown in the screenshot below.

For the connections I used the same settings for the virtual connections and internal. This meant for me the connections were HTTPS on host odata.sqlan17.haw on port 8444.

Connecting Remote Servers

My Destination for the SCP again keeping the S17 example from my Personal Web IDE destination. The important settings for my setup are the OnPremise setting and my choice of Cloud Connector LocationID as this had to match my Cloud Connector settings.


For the Cloud Web IDE I would use the standard OpenUI5 walkthrough guide to enable remote OData services. The example is based on Northwind, so again I will set it up to use my SQL Anywhere based Northwind service.


First I downloaded the code

I had no issues loading the zip file first time with this example and left the default name for the import.

The project was setup to use the openUI5 at the URL below,

To actually adapt the code to use my SQL Anywhere service was a bit different to the first example.

First I change the manifest.json file
"uri": "/destinations/S17/SQL17/V2/Northwind/Northwind.svc/",

As described in the UI5 remote OData source walkthrough there was a requirement to add the neo-app.json file. The contents of this file.
"routes": [
"path": "/destinations/S17",
"target": {
"type": "destination",
"name": "northwind"
"description": "Northwind OData Service"

These changes pointed to my SQL Anywhere database via the SAP Cloud Connector

I checked the chrome console and the OData trace log file to see everything was working as expected.

The above image shows the imported walk through project and connections being successfully routed to my SQL Anywhere database. The highlighted items at the bottom of the image show connections from my cloud based Web IDE and OData calls in the myodata.log trace file. The myodata.log is to capture the OData messages from the SQL Anywhere OData server. It is setup in my startup options listed below in the Appendix.

Example Four

My final example is based on Nabi's blog which explains how to display the images in the Northwind service.

I would adapt this to use my SQL Anywhere service. A link to Nabi's blog again.

The code provided was not based on a Web IDE project but a one page example and it also used a cors-anywhere service to enable the images to display. To consume remote services with the Web IDE then the CORS issues are dealt with by destinations (as explained in the previous example for the remote OData service for openui5).

To setup the example from Nabi's code I chose the following method.

Add a "onepager" folder to my base Web IDE and then copy and paste Nabi's code into a file called index.html

Selecting the new "onepager" folder I right clicked to add an OData service, I followed the same process to add the Northwind(lite) service from SQL Anywhere as per the first example in this blog.

The Web IDE automatically created the following structure to my "onepager" project

When I came to add the image I had some unexpected results! To cut a slightly long story short I explain my working (workaround) solution. I added an extra column to the Categories table called PictureBase. As shown below with an update SQL statement using a base64_encode function of SQL Anywhere. This followed Nabi's explanation of a base64 encoded string, and keeping the code which cut the return string at the 104th character in his original project.

I needed to do this as the original "Picture" binary column was being corrupted (it appeared to be extra characters) when consumed by the normal OData server of SQL Anywhere. I am not sure why so added the workaround/fix via the extra column in the table. The most likely cause was the SQL Anywhere OData server as the table data in the database is valid in my opinion.

I could easily adapt this in my Northwind(lite) data source, as I had my own SQL Anywhere database and OData server and not relying on the external/read only service via Although the SQL Anywhere OData was causing me some issues with the extra characters. You win some and you lose some ;).

I had to adapt the code the as follows to display an image (based on the new PictureBase column). I could rely on the Web IDE to consume the service and therefore use it instead of the cors-anywhere url of the original.

                    return Controller.extend("MyController", {
onInit : function () {
var sUrl, oImg;

sUrl = "/S17/SQL17/V2/Northwind/Northwind.svc/Categories(8)/PictureBase?$format=json";
oImg = this.byId("myImage");

$.get( sUrl, function( data ) {
var sTrimmedData = data.d.results.PictureBase.substr(104);
oImg.setSrc("data:image/bmp;base64," + sTrimmedData);



The end result was a successfully displayed image from my Northwind(lite) OData service. And in the spirit of Tokyo Traders II, the seafood category with a description of "Seaweed and fish" seems the most appropriate image to end with....






While setting up this Northwind(lite) database I did start to appreciate the power and flexibility of OData more. Previously I was used to setting up SQL Anywhere procedures and functions to achieve similar results but these were always hard coded in some way. I can see the benefit and the power of OData URL calls. However there are some implementation challenges still for me. Apart from any restrictions that any OData server may have, the way an actual database's data types have been setup would also need to be considered. For the Web IDE I did appreciate the automatic diagrams of OData services and this helped me to visualise and have ideas of what needed to be done with my service. I liked the Mock Server feature as well, although I was focused on the actual database I was impressed in how easy the Mock Server could be setup.

Overall I am happy that I have an example Northwind (lite) service to use and test with some well known data. I can point my SQL Anywhere database to any example code locally or on the cloud. I chose the lite name to indicate that it is less or subset of features of the real thing. However having said that there are options to improve and add to the example OData service.

Also a final idea to setup Tokyo Traders II with a product line chosen and ready to make my fortune :).....Thanks for reading and I end with the Appendix sections.





SQL Anywhere HTTPS and Startup Options

My personal preference is to use encryption and setting things up to use TLS/SSL/HTTPS from the start. And SQL Anywhere does come with the ability to create and sign certificates. I found this a great feature for my Node.js setup that I covered in the blog linked below. The blog link is relevant only in the certificate authority and singing certificates as I do not use Node.js at all in this blog but the Web IDE.


For the initial setup and configuration of my Northwind(lite) database I used Windows 10 (running on VMware Fusion on my macbookpro to be more specific). I use alias host names (linked to one IP address) for each service i.e. odata.sqlany17.haw is for any OData calls. My Windows 10 hosts file looks like this. sqlany17 sqlany17.haw odata.sqlany17.haw njs.sqlany17.haw webide.sqlany17.haw scc.sqlany17.haw


SQL Anywhere can setup a CA process with the createcert command and I covered a lot of detail in my blog linked above.


I transferred the certificates and keystore that I had created on my Mac to use with my SQL Anywhere installation on windows 10. For this blog the important part for me was the ODATA service and I use port 8444 for HTTPS connections. Also I found it important to use a log file for the OData server which enabled me to check on any errors/issues with my use of OData. My startup script is below with the OData section in bold text in the table.


dbspawn dbsrv17 -zoc "web.log" -o "messages.log" -xs  "HTTP(port=8081;TO=3600)", "HTTPS(port=8443;FIPS=N;IDENTITY="SQL2server.pem";IDENTITY_PASSWORD=0llie5;TO=3600)", "ODATA(SecureServerPort=8444;SSLKeyStore=keystore;SSLKeyStorePassword=0llie5;ServerPort=8082;LogFile=myodata.log;LogVerbosity=4)" sqlany17north

In the above startup command I create a number of log files for SQL Anywhere.

LogFile=myodata.log;LogVerbosity=4 is for OData logs

web.log is for web service calls

messages.log is for database messages

Check out the help on the startup commands/options for more information

dbspawn  command starts the database in the background

Database startup options link-> 


Setup HTTPS with Personal WEBIDE

I setup the Personal WEBIDE to use HTTPS and my preference is to setup dedicated host names for these types of things. I could use wildcard certificates or other ways to simplify the work but I like the extra steps and effort 🙂 for my setup. This meant for the Web IDE I required a new certificate for my host name webide.sqlany17.haw and subsequently a keystore to start the WEBIDE for HTTPS.

With the createcert command as mentioned in my previous blog I can easily create a certificate signed by my own personal root certificate as below.

Extract below of the createcert command using my root CA certificate entserver.pem to create my certificate for my WEBIDE setup.
Common Name: webide.sqlany17.haw
Enter file path of signer's certificate: entserver.pem
Enter file path to save certificate: WEBIDEpublic.pem
Enter file path to save private key: WEBIDEprivate.key
Enter password to protect private key: xxxxxxxx
Enter file path to save identity: WEBIDEserver.pem


For SQL Anywhere's OData server you also need a keystore and I described the process for this in my other blog.  I did transfer the Mac created keystore for the OData service to my Windows for that setup. However I wanted to create a new keystore for the webide. I already knew how to use openssl to create a pkcs12 file from my certificate and key.

So how would I get openssl installed on Windows though?

Simple ;).

I decided to add a complete Linux based subsystem to Windows 10.



After the installation then openssl is available

openssl pkcs12 -inkey WEBIDEprivate.key -in WEBIDEpublic.pem -export -out WEBIDEjetty.pkcs12


The Java keytool command required to now create the keystore is available with SQL Anywhere itself.


C:\Program Files\SQL Anywhere 17\Bin64\jre180\bin

I issued the following to create the keystore from a standard windows command prompt

keytool -importkeystore -srckeystore WEBIDEjetty.pkcs12 -srcstoretype PKCS12 -destkeystore WEBIDEkeystore

I know had a keystore "WEBIDEkeystore" to use to setup HTTPS  with my personal SAP WEBIDE

Configuring the Orion server


To enable the WebIDE to pick up the WEBIDEkeystore keystore I modified the orion.ini config file


In the referenced orion.conf file

Which means I run my personal WEBIDE on HTTPS on port 8555


SQL Anywhere Default OData Service

SQL Anywhere can create an OData service based on any table with a primary key.

Using the Web IDE Diagram option as mentioned earlier this is a view of the default OData service SQL Anywhere would offer. I do find it an impressive out of the box feature. I wanted more control over the OData services I wanted to setup to match the example code I chose to work with.

Below is a screenshot of the default service. I have chose to show all tables and links so the image is not really visible in this blog. I still think its shows the difference between this default and my OSDL genereated OData for my setup of the Northwind(lite) service. It is a better experience in the actual Web IDE where you can zoom and pan around the image.



SQL Anywhere: Compatibility of Database Features between Operating Systems


I created the Northwind(lite) database on a Windows 10 operating system with SQL Anywhere version 17 but the actual database files can run on many operating systems without further changes.

Database file compatibility

There are some limitations on where the OData server can run directly with SQL Anywhere. For example it is not compatible/activated on the SQL Anywhere Mac Version.

So to prove my setup on Windows could be used on other systems I tested using SQL Anywhere on my Ubuntu Virtualbox setup. I transferred the database and log files over to the Ubuntu desktop and started it successfully. I used my Mac Web IDE to connect to the SQL Anywhere OData server. This worked.

Testing the CRU(D) example below to add another excellent product an NEO GEO Console 🙂




Labels in this area