Embedding Fiori In SQL Anywhere

---

Update January 7, 2015: This article has been updated to reflect Mark Culp's comment about using LONG BINARY and the CSCONVERT() function, plus the new website_file.csconvert_required column.

---

Fiori is the name given to a new style of user interface developed using the SAPUI5 client-side HTML5 and JavaScript library. This article shows how to embed the SAPUI5 library in a SQL Anywhere database so you can build Fiori-style applications with the following advantages:

• All the scripts and data are stored in a database where they are protected from loss, attack and accidental modification.
  

• All the scripts and data are available locally when the internet connection is lost.
  

In particular, this article shows how to:

1. Store text and binary files in a SQL Anywhere table so they can be served up as HTTP responses by SQL Anywhere's built-in web server.
   

2. Code a SQL Anywhere "root" web service that returns rows from the table just like an ordinary web server returns files from the server.
   

3. Use a simple HTML page to show that the table and web service work as advertised.
   

4. Download all the SAPUI5 Fiori files into your SQL Anywhere database so they'll work just like the files stored on the internet.
   

5. Show how the "SAPUI5 in 20 Seconds" Fiori demo can be made to work from inside SQL Anywhere by making a one-line change.
   

1. Store Website Files In SQL Anywhere

Here's a Windows batch file that creates and starts a new SQL Anywhere database and then launches an ISQL session for running SQL commands:

"%SQLANY16%\bin64\dbinit.exe" website.db "%SQLANY16%\bin64\dbspawn.exe"^ -f "%SQLANY16%\bin64\dbsrv16.exe"^ -o dbsrv16_log_website.txt^ -x tcpip^ -xs http(port=12345;maxsize=0;to=600;kto=600)^ website.db "%SQLANY16%\bin64\dbisql.com"^ -c "ENG=website; DBN=website; UID=dba; PWD=sql; CON=website-1"

The dbsrv16.exe -xs option specifies port 12345 for HTTP traffic so SQL Anywhere doesn't compete with other processes that might already be using port 80 (such as Foxhound or Skype).

Here's the table that can hold all the HTML, CSS, JavaScript and other files that make up an file-based website:

CREATE TABLE website_file ( file_name VARCHAR ( 260 ) NOT NULL PRIMARY KEY, content_type VARCHAR ( 100 ) NOT NULL, csconvert_required VARCHAR ( 1 ) NOT NULL, contents LONG BINARY NOT NULL );

The file_name column holds the relative URL for each file, and the data is stored in the contents column.

The content_type and csconvert_required columns are filled in automatically when a row is inserted or updated:

CREATE TRIGGER insert_update_website_file
   BEFORE INSERT, UPDATE ON website_file 
   REFERENCING NEW AS new_website_file
   FOR EACH ROW
BEGIN
DECLARE @extension_pos INTEGER;
DECLARE @extension     VARCHAR ( 100 );

SET new_website_file.file_name = TRIM ( REPLACE ( new_website_file.file_name, '\\', '/' ) );

SET @extension_pos = LOCATE ( new_website_file.file_name, '.', -1 );

SET @extension 
   = IF @extension_pos = 0
        THEN ''
        ELSE SUBSTR ( new_website_file.file_name, @extension_pos + 1 )
     ENDIF;

CASE @extension
   WHEN 'bin'    THEN 
      SET new_website_file.content_type       = 'application/octet-stream';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'css'    THEN 
      SET new_website_file.content_type       = 'text/css';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'dll'    THEN 
      SET new_website_file.content_type       = 'application/octet-stream';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'doc'    THEN 
      SET new_website_file.content_type       = 'application/msword';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'exe'    THEN 
      SET new_website_file.content_type       = 'application/octet-stream';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'gif'    THEN 
      SET new_website_file.content_type       = 'image/gif';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'htm'    THEN 
      SET new_website_file.content_type       = 'text/html';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'html'   THEN 
      SET new_website_file.content_type       = 'text/html';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'jpg'    THEN 
      SET new_website_file.content_type       = 'image/jpeg';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'jpeg'   THEN 
      SET new_website_file.content_type       = 'image/jpeg';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'js'     THEN 
      SET new_website_file.content_type       = 'application/javascript';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'json'   THEN 
      SET new_website_file.content_type       = 'application/json';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'pdf'    THEN 
      SET new_website_file.content_type       = 'application/pdf';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'png'    THEN 
      SET new_website_file.content_type       = 'image/png';
      SET new_website_file.csconvert_required = 'N';
   WHEN 'saplan' THEN 
      SET new_website_file.content_type       = 'application/xml';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'txt'    THEN 
      SET new_website_file.content_type       = 'text/plain';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'xml'    THEN 
      SET new_website_file.content_type       = 'application/xml';
      SET new_website_file.csconvert_required = 'Y';
   WHEN 'zip'    THEN 
      SET new_website_file.content_type       = 'application/zip';
      SET new_website_file.csconvert_required = 'N';
   ELSE               
      SET new_website_file.content_type       = 'text/html';
      SET new_website_file.csconvert_required = 'Y';
END CASE;

END;

The SET on line 9 changes all the Windows "\" characters to URL-friendly forward slashes.

The code on lines 11 through 77 extracts the extension (e.g., .html) from the file_name value, then uses it to fill in the content_type and cs_convert_required columns.

2. Create A Web Service For Website Files

This web service is like no other: it has no name, and no parameters (well, it does have the name "root" but that's syntactic sugar meaning "no name").

This is the web service that gets called when no other web service matches the URL, and that's what happens when an URL specifies one of the file_name values from the website_file table; e.g., http://localhost:12345/Hello_World.html

CREATE SERVICE root 
   TYPE 'RAW' AUTHORIZATION OFF URL ON USER DBA
   AS CALL root();

CREATE PROCEDURE root() RESULT ( html_string LONG BINARY )
BEGIN
   
DECLARE @url                LONG VARCHAR;
DECLARE @content_type       LONG VARCHAR;
DECLARE @csconvert_required VARCHAR ( 1 );

SET @url = HTTP_VARIABLE ( 'URL' );

IF EXISTS ( SELECT * 
              FROM website_file 
             WHERE website_file.file_name = @url ) THEN

   SELECT website_file.content_type,  
          website_file.csconvert_required 
     INTO @content_type,
          @csconvert_required
     FROM website_file
    WHERE website_file.file_name = @url;

   CALL dbo.sa_set_http_header( 'Content-Type', @content_type );

   SELECT IF @csconvert_required = 'Y'
             THEN CSCONVERT ( website_file.contents, CONNECTION_PROPERTY ( 'CharSet' ) )
             ELSE website_file.contents  
          END IF
     FROM website_file
    WHERE website_file.file_name = @url;

ELSE

   CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

   SELECT STRING ( 'URL not found: "', @url, '"' );

END IF;

END;

The CREATE SERVICE statement on lines 1 to 3 defines a service-with-no-name that calls the web_server procedure with no parameters.

The SET statement on line 12 copies the URL value from the HTTP header of the same name.

The SELECT on lines 18 through 23 determines what 'Content-Type' value to use in the CALL on line 25, and whether or not the CSCONVERT() function should be called from the IF expression on lines 27 through 30.

The SELECT on lines 27 through 32 returns the website_file.contents to the browser.

3. Run Hello_World.html

Here's what the Hello_World.html file looks like:

<HTML> <TITLE>Hello World!</TITLE> <BODY> Hello World! </BODY> </HTML>

Here's how to load the file into the table; note that the trigger takes care of the content-type column:

INSERT website_file ( file_name, contents ) ON EXISTING UPDATE SELECT 'Hello_World.html', xp_read_file ( 'Hello_World.html' ); COMMIT;

Here's what SQL Anywhere shows in the browser when you specify http://localhost:12345/Hello_World.html:

4. Download SAPUI5 Into SQL Anywhere

The SAPUI5 files can be downloaded from Open UI5.

For the purposes of this article, the SAPUI5 files were downloaded to C:/download/Open UI5 Runtime 1.24.3.

The 4,300 files in the resources subfolder comprise the SAPUI5 library; here's what they look like:

The following code shows how a CLASS 'DIRECTORY' proxy table was used to load the SAPUI5 library:

CREATE OR REPLACE VARIABLE @root LONG VARCHAR = 'C:/download/Open UI5 Runtime 1.24.3/resources/';

CREATE SERVER folder
   CLASS 'DIRECTORY' USING 'ROOT={@root};SUBDIRS=10';

CREATE EXTERNLOGIN DBA TO folder;

CREATE EXISTING TABLE proxy_folder AT 'folder;;;.';

INSERT website_file (
       file_name,
       contents )
SELECT proxy_folder.file_name,
       proxy_folder.contents
  FROM proxy_folder
 WHERE LEFT ( proxy_folder.permissions, 1 ) <> 'd'
   AND proxy_folder.size > 0
 ORDER BY proxy_folder.file_name;
COMMIT;

The code on lines 1 through 8 uses the techniques shown in Proxy {Variables} to create a proxy table that represents the resources folder and all its subfolders, and the INSERT SELECT on lines 10 through 18 loads all the files (just the files, not the 'd' directories) into the website_file table.

Here's what the table looked like after the INSERT SELECT;

SELECT website_file.file_name FROM website_file; file_name 'Hello_World.html' '.theming' 'jquery-sap-dbg.js' 'jquery-sap.js' 'jquery.sap.act-dbg.js' ... 'sap/m/.theming' 'sap/m/ActionListItem-dbg.js' 'sap/m/ActionListItem.js' 'sap/m/ActionListItemRenderer-dbg.js' 'sap/m/ActionListItemRenderer.js' ...

Note that the file_name field contains the relative path subfolder names beneath the resources folder so that different files with the same name located in different subfolders can be differentiated from one another.

5. Run "SAPUI5 in 20 Seconds" In SQL Anywhere

The original "SAPUI5 in 20 Seconds" demo can be found here: How to Create and Run a Simple SAPUI5 Application from Scratch within 20 Seconds.

Here's what it looks like after this single line

src="https://sapui5.hana.ondemand.com/resources/sap-ui-core.js"

was changed to this

src="http://localhost:12345/sap-ui-core.js"

and stored in the file SAPUI5_SQL_Anywhere_in_20_Seconds.html:

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
    <title>SAPUI5 in 20 Seconds</title>

    <!-- 1.) Load SAPUI5 (from a remote server), select theme and control library -->
    <script id="sap-ui-bootstrap"
        src="http://localhost:12345/sap-ui-core.js"
        data-sap-ui-theme="sap_goldreflection"
        data-sap-ui-libs="sap.ui.commons"></script>
    <!-- 2.) Create a UI5 button and place it onto the page -->
    <script>
        // create the button instance
        var myButton = new sap.ui.commons.Button("btn");
        // set properties, e.g. the text (there is also a shorter way of setting several properties)
        myButton.setText("Hello World!");
       // attach an action to the button's "press" event (use jQuery to fade out the button)
        myButton.attachPress(function(){$("#btn").fadeOut()});
        // place the button into the HTML element defined below
        myButton.placeAt("uiArea");
        // an alternative, more jQuery-like notation for the same is:  
        /*
        $(function(){
            $("#uiArea").sapui("Button", "btn", {
                text:"Hello World!",
                press:function(){$("#btn").fadeOut();}
            });
         });
         */
     </script>
</head>
<body class="sapUiBody">

    <!-- This is where you place the UI5 button -->
    <div id="uiArea"></div>
</body>
</html>

Here's how to load the modified file into the SQL Anywhere table:

INSERT website_file ( file_name, contents ) ON EXISTING UPDATE SELECT 'SAPUI5_SQL_Anywhere_in_20_Seconds.html', xp_read_file ( 'SAPUI5_SQL_Anywhere_in_20_Seconds.html' ); COMMIT;

Here's the URL to launch the modified "SAPUI5 in 20 Seconds" demo:

http://localhost:12345/SAPUI5_SQL_Anywhere_in_20_Seconds.html

Note that the button shows the Fiori-style color change when the cursor hovers over it:

---

Tip: When something goes wrong, use the browser's built-in debugger. Chrome is best; the latest version of Firefox is pretty good, but IE's debugger sucks.

For example, if you try to run the "SAPUI5 in 20 Seconds" demo from the file stored on your hard drive instead of the one in the database, like this

file:///C:/Temp/SAPUI5_SQL_Anywhere_in_20_Seconds.html

you will see absolutely nothing in the browser window. Click right mouse - Inspect element to open the debugger to see the dreaded "not allowed access" error:

XMLHttpRequest cannot load
No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'null' is therefore not allowed access.

Here's what it looks like in the Chrome debugger:

To find a solution just copy and paste the error message text into Google; chances are, many others have found and fixed and documented the same problem (in this case, there are workarounds that will let you mix HTML files on the local disk and in the database, but the simplest solution is to load everything into the database so it all has the same "origin".)

Oh, and if you see several messages, just make one fix and test again... chances are that all the messages have the same cause.

And don't feel bad if you don't completely understand everything the debugger is showing you; you're not looking for a deep understanding of the internet, just a fix for your application.

---