Monday, February 8, 2021

Embedding Highcharts In SQL Anywhere


Introduction

Highcharts is a bare-metal browser-based JavaScript API for adding interactive charts to HTML pages.

Highcharts is powerful, well documented and (relatively) easy to use, plus it's been around a long time (2009), it's popular and it's commercially successful.

Here's the Highcharts version of Hello, World!:

Demo, Chart!

[ Click the buttons to see data, then move the mouse to see tooltips ]

( if the buttons don't work, try the Static, Chart! demo instead )

Demo, Chart! is one of three demonstrations, all of which look the same but work differently:

  Demo, Chart! Static, Chart! Hello, Chart!
Where does it run? In this blog post In a website (no CORS) In a SQL Anywhere 17 database
HTML location: blogspot.com bcarter.com SQL Anywhere 17 database
Highcharts JavaScript: code.highcharts.com code.highcharts.com SQL Anywhere 17 database
Data location: CSV files in static website bcarter.com CSV files in static website bcarter.com SQL Anywhere 17 database
Data retrieval: Static file via XMLHttpRequest() Static file via XMLHttpRequest() Web service via XMLHttpRequest()
CORS headers: github.com/Rob--W/cors-anywhere Not required Not required

The code for Demo, Chart! is under your fingertips: View page source, then Find <!-- Start Demo, Chart! code in blog post... -->

The code for Static, Chart! is here:

https://www.bcarter.com/blog/Highcharts/static.html
https://www.bcarter.com/blog/Highcharts/demochart1.csv
https://www.bcarter.com/blog/Highcharts/demochart2.csv

The third demo is shown below.


Hello, Chart! Is Embedded In SQL Anywhere

There are advantages to storing code together with the data in a relational database. For example, application deployment, backup, recovery, portability, security all involve a single *.db file, and the Rules For Relational Databases guarantee consistency among various components.

If you have SQL Anywhere 17 installed, embedding Hello, Chart! is a two-step "Download And Go" process:

Step 1: Download four files into a local folder.

This command file uses the wget utility for Windows to download the files into C:\temp:

CD C:\temp
"C:\download\wget\wget.exe" "https://code.highcharts.com/highcharts.js"
"C:\download\wget\wget.exe" "https://code.highcharts.com/modules/data.js"
"C:\download\wget\wget.exe" "https://www.bcarter.com/blog/highcharts/setup_hello.html"
"C:\download\wget\wget.exe" "https://www.bcarter.com/blog/highcharts/setup_hello.sql.txt"
PAUSE

Step 2: Load the code and launch it in a browser.

The following command file
  • creates a new SQL Anywhere 17 database chart.db via dbinit.exe,
  • starts the database via dbsrv17.exe
  • with the builtin HTTP server running on port 54321,
  • loads the code and data by running setup_hello.sql.txt via dbisql.com
  • and then uses the Windows START command to launch the hello.html web service in a browser window:

"%SQLANY17%\bin64\dbinit.exe"^
  -dba dba,sql^
  -mpl 3^
  "C:\temp\chart.db"

"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -xs http(port=54321)^
  "C:\temp\chart.db"

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=chart; DBN=chart; UID=dba; PWD=sql;"^
  READ ENCODING Cp1252 "C:\temp\setup_hello.sql.txt"

START http://localhost:54321/hello.html

PAUSE

Here's what it looks like in Edge:


How Hello, Chart! Works

Here's how the code works, starting at the point the web browser launches http://localhost:54321/hello.html.

Step 1: The browser passes the request for "hello.html" to the HTTP server embedded inside the SQL Anywhere 17 process dbsrv17.exe, which is listening for HTTP traffic on port 54321 because of the option -xs http(port=54321).

It could use port 80, but this is a demo, and demos are supposed to work, not generate conflicts with existing software :)

Step 2: SQL Anywhere passes the request to the web service named "hello.html" that is defined in this snippet.

CREATE SERVICE "hello.html" TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL get_static_text_file ( 'hello.html' );

Step 3: The web service passes the request on to the procedure shown in this snippet.

The RESULT clause on line 61 specifies that the procedure will return a result set to the caller (the browser) consisting of a single LONG VARCHAR column.

The CALL on line 75 sets the HTTP Content-Type header to 'text/html' because the input file extension is 'html'.

The SELECT on line 91 gets the text file data from a table rather than an actual file; this is the SELECT that defines the RESULT set returned by this procedure.

CREATE PROCEDURE get_static_text_file ( IN @file_name VARCHAR ( 255 ) )
   RESULT ( text_string LONG VARCHAR )
BEGIN

DECLARE @extension_pos INTEGER;
DECLARE @extension     VARCHAR ( 100 );

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

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

CALL dbo.sa_set_http_header ( 
   'Content-Type', 
   CASE @extension
      WHEN 'css'  THEN 'text/css' 
      WHEN 'csv'  THEN 'text/csv' 
      WHEN 'htm'  THEN 'text/html'
      WHEN 'html' THEN 'text/html'
      WHEN 'js'   THEN 'application/javascript' 
      WHEN 'json' THEN 'application/json' 
      WHEN 'xml'  THEN 'application/xml' 
      ELSE             'text/plain'
   END CASE ); 

IF EXISTS ( SELECT *
              FROM static_text_file
             WHERE static_text_file.file_name = @file_name ) THEN
   SELECT static_text_file.file_text
     FROM static_text_file
    WHERE static_text_file.file_name = @file_name;
ELSE
   SELECT STRING ( '[', @file_name, ' not found]' );
END IF;

END;

Step 4: At this point in "how the code works", the browser receives the text for hello.html and starts processing by loading two Highcharts Javascript text files from SQL Anywhere...

<script src="get_static_text_file?f=highcharts.js"></script>
<script src="get_static_text_file?f=data.js"></script>

...using another web service that calls the same stored procedure shown earlier:

CREATE SERVICE get_static_text_file TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL get_static_text_file ( :f );

Here's where all the "static text file" data came from: it was pre-loaded via xp_read_file() calls from actual files into the static_text_file table.

CREATE TABLE static_text_file (
   file_name  VARCHAR ( 255 ) NOT NULL,
   file_text  LONG VARCHAR NOT NULL,
   PRIMARY KEY ( file_name ) );

INSERT static_text_file ON EXISTING UPDATE VALUES 
   ( 'highcharts.js', 
     dbo.xp_read_file ( 'C:\\temp\\highcharts.js' ) );

INSERT static_text_file ON EXISTING UPDATE VALUES 
   ( 'data.js',       
     dbo.xp_read_file ( 'C:\\temp\\data.js' ) );

INSERT static_text_file ON EXISTING UPDATE VALUES 
   ( 'hello.html',    
     dbo.xp_read_file ( 'C:\\temp\\setup_hello.html' ) );
COMMIT;

Step 5: When the browser has finished loading hello.html, it displays this...

...by executing this snippet of HTML:

<b>Hello, Chart!</b>
<p>
<button onClick="showDataSet ( 1 );">Show Data Set 1</button>
<button onClick="showDataSet ( 2 );">Show Data Set 2</button>
<div id="chartDiv" style="margin-top: 0.5em; padding: 0.5em; border-style: solid; border-width: 1px;">
[ <b>Click the buttons</b> to see data, then <b>move the mouse</b> to see tooltips ]
<p style="padding-left: 2em;">
<i>( this is the SQL Anywhere database version of 
<a href="http://sqlanywhere.blogspot.com/2021/02/embedding-highcharts-in-sql-anywhere.html" 
   target="_blank"><b><u>the code in this blog post</u></b></a> )</i>
</div> 

Step 6: When one of the Show Data Set buttons is pressed, the browser executes the following function where

  • the requestUrl string points to a SQL Anywhere web service,

  • the chartXHR.open() sets up a XMLHttpRequest object to perform an asynchronous GET request for the web service and

  • the chartXHR.send() sends the request.

function showDataSet ( dataSet ) { 
   const requestUrl 
      = "get_csv_chart_data?data_set=" + dataSet; // SQL Anywhere service
   chartXHR.open ( "GET", requestUrl, true ); 
   chartXHR.send(); // this will eventually trigger chartXHR.onload
};

Step 7: The web service get_csv_chart_data builds a CSV result set...

Chart Timestamp,Chart Percent
2020-12-01 13:58:54,.1 
2020-12-01 13:59:04,.1 ...

...by executing this code:

CREATE SERVICE get_csv_chart_data TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL get_csv_chart_data ( :data_set );

CREATE PROCEDURE get_csv_chart_data ( IN @data_set INTEGER )
   RESULT ( csv_string LONG VARCHAR )
BEGIN

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

SELECT STRING ( -- convert rows for one data set into CSV data
          'Chart Timestamp,Chart Percent', 
          '\x0d\x0a',
          LIST ( 
             STRING ( DATEFORMAT ( chart_data.chart_timestamp, 'YYYY-MM-DD HH:MM:SS' ),
                      ',',
                      chart_data.chart_percent ),
             '\x0d\x0a'
             ORDER BY chart_data.chart_timestamp ) )
  FROM chart_data
 WHERE chart_data.data_set = COALESCE ( @data_set, 1 );

END;

Here's where the CSV data originally came from; it was pre-loaded into SQL Anywhere using this snippet.

CREATE TABLE chart_data (
   data_set        INTEGER NOT NULL,
   chart_timestamp TIMESTAMP NOT NULL,
   chart_percent   DECIMAL ( 11, 1 ) NOT NULL,
   PRIMARY KEY ( chart_timestamp ) );

INSERT chart_data VALUES ( 1, '2020-12-01 13:58:54', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:04', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:14', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:24', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:34', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:44', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:54', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:04', 1.2 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:18', 10.4 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:28', 23.9 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:38', 29.2 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:43', 14.3 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:53', .2 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:01:03', .7 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:01:13', 1.2 );

INSERT chart_data VALUES ( 2, '2020-12-01 14:01:28', 16.1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:01:38', 24.1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:01:47', 26.0 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:01:53', 7.4 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:02', .9 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:12', .9 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:23', 1.0 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:32', .1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:46', 28.3 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:54', 39.2 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:02', .1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:12', 1.0 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:22', .4 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:32', 1.7 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:45', 18.0 );

COMMIT;

Step 8: When the get_csv_chart_data web service returns the CSV result set, it is received by the XMLHttpRequest.onload event handler.

const chartXHR = new XMLHttpRequest();
chartXHR.onload = function() { // asynchronous callback event handler for chartXHR.send()
   if ( chartXHR.status === 200 ) { // HTTP status OK
      Highcharts.chart ( "chartDiv", { // tell Highcharts where to put the chart
         chart: {
            type: "line"
         },
         data: {
            csv: chartXHR.responseText // tell Highcharts where to get the data
         },
         title: {
            text: "Chart"
         },
         xAxis: {
            title: {
               text: "Chart Timestamp"
            }
         },
         yAxis: {
            title: {
               text: "Chart Percent"
            }
         },
      } );
   } else {
      document.getElementById ( "chartDiv" ).innerHTML = "HTTP Status " + chartXHR.status;
      console.log ( "ERROR: chartXHR.status = ", chartXHR.status );
   }
   return false;
};


See Also...

Highcharts Demos highcharts.com/demo
Highcharts Documentation highcharts.com/docs/index
Highcharts JS API Reference api.highcharts.com
Working with Highcharts data.js highcharts.com/docs/working-with-data/data-module
Highcharts CSV sample jsfiddle.net/gh/get/library/pure/highcharts/highcharts/tree/master/samples/highcharts/data/csv
XMLHttpRequest Tutorial javascript.info/xmlhttprequest
Web Services in SQL Anywhere help.sap.com/viewer/98ad9ec940e2465695685d98e308dff5/17.0/en-US/3bd432396c5f10148782bc977a956885.html


No comments: