How to view data in the Offline OData datastore database

[olav_rask2]

I recently started working on an HCPms / Kapsel project that makes use of the Offline OData plugin. While working on the project we thought it would be useful to have the ability to open the local offline database and examine the data in it. In the following, I will share how to do just that.

The following steps are involved:

1. Installing SAP SQL Anywhere 17 / SQL Central
2. Locating the database files
3. Opening the database files in SQL Central
4. Viewing the content of a table

Installing SAP SQL Anywhere 17 / SQL Central

The first step is to install SAP SQL Anywhere 17 available from here: https://go.sap.com/cmp/syb/crm-xu15-int-sqldevft/index.html. This installation includes the database administration tool SQL Central. SQL Central will connect to UltraLite databases – the format used by the Offline plugin.

Note: For me using Gmail, the e-mail with the download link ended up in “Promotions”

I won’t cover the installation process here in details, since it is more or less next, next, next. However, I will say that during installation I tried to limit what was installed to only what I needed. I select the “Database Client” for both 32 and 64 bit as well as the Database Administration tool. Everything else (including the Database Server components) I opted not to install.

Locating the database files

Once installation is completed, the next step is to obtain the actual database file(s). The Offline plugin splits data into two separate databases. One database for the actual offline entity data cache where you will find tables for each entity set and one database for the request queue where you will find request waiting to be send to the server as well as the table containing the error archive.

The location of the files will obviously differ from platform to platform. I initially tried this on Windows 8.1. I tested the application by opening the project directly in Visual Studio and a least in that case you will find the files here: “C:\Users\<username>\AppData\Local\Packages\<app id + identifier>\LocalState”.

The database files will be named after the name you supplied when opening the offline store (calling store.open() in javascript in our case). The files will be named as follows:

• Offline entity database: <store name>.udb
• Request queue: <store name>.rq.udb

On iOS, as far as I remember, you will find the files in the Documents directory. If you are running on the simulator this directory can be accessed directly via the file system, but if you are using an actual iOS device you will need a file explorer utility (such as https://www.macroplant.com/iexplorer/) to browse and extract the files.

I do not know where the files are located on Android but I would expect it to be similar.

Opening the database files in SQL Central

To open one of the database files in SQL Central, go to “Connections” and select “Connect with UltraLite 17…”

Next select “Browse” and locate the file you want to open (in my case I opened the entity data cache). Next select “Open”.

After selecting the path to the database file, select “Connect” to open the connection to the database. This will take you to a view like this:

You can now inspect the metadata of the database. Double clicking “Tables” will display the tables in the database. If you scroll to the bottom of the list, you will see the tables that contain the actual entity data (names starting with a “random” character sequence in the image below).

Viewing the content of a table

To view the content of a table, right click it and select ”View Data in Interactive SQL”.

This will bring up the Interactive SQL query windows with a prepopulated SELECT statement for the selected table. The query will be executed automatically and the result will be displayed in the bottom half of the window.

So there you have it. The next time you are wondering if a particular piece of data is actually in the database (or if you just want to have a look at how the Offline plugin stores data under the hood), just open the database and have a look! :smile:

Hopefully someone finds this helpfull - feel free to leave comments, suggestion or corrections below!

Best regards,

Olav Rask