Wednesday, June 16, 2021

OEM Authentication

Catch 22

OEM authentication is explained in the SQL Anywhere Help, but that explanation can only be understood by someone who already understands OEM authentication.

John Yossarian's Explanation

This blog post came in the form of an email from a client who offered to help me set Foxhound up to work for clients who use the OEM Edition of SQL Anywhere.

This explanation from John Yossarian (not his real name) was so clear, concise and helpful that I asked for (and received) permission to publish it here.


from:    John Yossarian <>
to:      Breck Carter <>
date:    Jun 12, 2021, 4:28 PM
subject: Authenticated OEM SQL Anywhere Connections

The three parts of the authentication business are 
  a) licencing the server engine 
  b) authenticating the database and 
  c) authenticating the connection.
When you go into the authentication business SAP give you three keys - the keys are all different but match 
each other and the publishing company & application names (ie they won't work with other keys):

- an install key: you use this with dblic.exe -k to create OEM lic files (just like a normal install key) - but 
  we can then redistribute those engines. (this bit isn't your problem of course)

- a database key: this is used once on a database to make it an Authenticated database which will work on 
  the server created with the install key - the setting is persistent. This is done with a SET OPTION command; e.g.


  This step needs SET ANY SECURITY OPTION rights.

- a connection key: This is used by connections to the database. Each connection has a 30 second (or so) grace 
  period to set a temporary Connection_authentication option; e.g.


- a non-authenticated connection to an authenticated database running on an OEM engine is allowed to connect 
  but after thirty seconds is strictly read-only.
So, if you wanted to support authenticated servers fully, you would need users to supply both the database key 
and the connection key, in the set-up process & then use them as above.  

Monday, February 8, 2021

Embedding Highcharts In SQL Anywhere


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: SQL Anywhere 17 database
Highcharts JavaScript: SQL Anywhere 17 database
Data location: CSV files in static website CSV files in static website SQL Anywhere 17 database
Data retrieval: Static file via XMLHttpRequest() Static file via XMLHttpRequest() Web service via XMLHttpRequest()
CORS headers: 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:

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" ""
"C:\download\wget\wget.exe" ""
"C:\download\wget\wget.exe" ""
"C:\download\wget\wget.exe" ""

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
  • and then uses the Windows START command to launch the hello.html web service in a browser window:

  -dba dba,sql^
  -mpl 3^

  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -xs http(port=54321)^

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

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


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.

   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 )

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 )

CALL dbo.sa_set_http_header ( 
   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 ); 

              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;
   SELECT STRING ( '[', @file_name, ' not found]' );


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:

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

   ( 'highcharts.js', 
     dbo.xp_read_file ( 'C:\\temp\\highcharts.js' ) );

   ( 'data.js',       
     dbo.xp_read_file ( 'C:\\temp\\data.js' ) );

   ( 'hello.html',    
     dbo.xp_read_file ( 'C:\\temp\\setup_hello.html' ) );

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

<b>Hello, Chart!</b>
<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="" 
   target="_blank"><b><u>the code in this blog post</u></b></a> )</i>

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 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 ( "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 ... executing this code:

   AS CALL get_csv_chart_data ( :data_set );

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

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', 
          LIST ( 
             STRING ( DATEFORMAT ( chart_data.chart_timestamp, 'YYYY-MM-DD HH:MM:SS' ),
                      chart_data.chart_percent ),
             ORDER BY chart_data.chart_timestamp ) )
  FROM chart_data
 WHERE chart_data.data_set = COALESCE ( @data_set, 1 );


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


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 Documentation
Highcharts JS API Reference
Working with Highcharts data.js
Highcharts CSV sample
XMLHttpRequest Tutorial
Web Services in SQL Anywhere

Wednesday, January 27, 2021

Comparing Execution Plans

Q: Why is my SELECT so slow?

Answer: Graphical Plan!

Q: Why is my SELECT sometimes slow and the rest of the time it's fast?

Answer: Compare Plans!

The new Compare Plans tool in SQL Anywhere 17 lets you compare two graphical plans to see all the differences side-by-side... not just structural differences between diagrams, but individual differences between performance statistics like the number of disk reads for a single table.

Tip: You don't have to understand every detail of the graphical plans if you can find inspiration in the differences.

Here's a demonstration using the Tutorial: Comparing Plans in Interactive SQL as a starting point:

1. Start the SQL Anywhere 17 demo database.

Tip: You can compare SQL Anywhere 16 plans but you have to launch the Compare Plans tool via ISQL 17.

2. Flush the cache in ISQL.

CALL sa_flush_cache();

3. Copy and paste this SELECT into ISQL but do not execute it (so the cache will still be empty when the next step captures the plan).

SELECT DISTINCT EmployeeID, GivenName, Surname
  FROM GROUPO.SalesOrders WITH (INDEX (SalesOrdersKey)), GROUPO.Employees, GROUPO.SalesOrderItems 
  WHERE SalesRepresentative = EmployeeID and
   SalesOrders.ID = SalesOrderItems.ID and
   SalesOrderItems.ProductID = (SELECT ProductID 
    FROM GROUPO.Products, GROUPO.MarketingInformation
    WHERE Name = 'Tee Shirt' AND
    Color = 'White' AND
    Size = 'Small' AND
    MarketingInformation.Description LIKE '%made of recycled water bottles%');

4. Open the Tools - Plan Viewer and capture the plan as FirstPlan17.saplan.

ISQL - Tools - Plan Viewer

Pick Statistics level:  Detailed and node statistics
          Cursor type:  Asensitive
        Update status:  Read-only

Click on Get Plan
   Wait for the plan to appear

Click Save As...
   Specify FirstPlan17.saplan

Click Save

5. Switch back to ISQL, then repeat Step 4 to save SecondPlan17.saplan.

6. Switch back to ISQL and open Tools - Compare Plans.

ISQL - Tools - Compare Plans

Click on Plan 1: - Browse...
   Pick FirstPlan17.saplan
   Click Open

Click on Plan 2: - Browse...
   Pick SecondPlan17.saplan
   Click Open

Click Compare Plans

Here are the side-by-side plans; the diagrams look the same but the statistics are different:

  • the  red numbers  show zero disk reads for the second execution because all the rows were in the cache, and

  • the  yellow numbers  are disk statistics that were only measured for the first plan:

Tip: You can compare plans for two different SELECT statements, but make sure you're not doing that by accident. The tool doesn't show differences between SELECT statements, just the resulting plans.

Here's a map for describing the Compare Plans display:

Plan 1: [filespec1]    [Browse...]        Plan 2: [filespec2]    [Browse...]     
                           [<-]  [Compare Plans]  [->]                         
Comparison Overview                                                            
                                   Name   Name                                  
                              id1 node1 - id1 node1                               
                              id2 node2 - id2 node2           
1. filename1                            | 2. filename2                          
Subquery: [SubQ dropdown]               | Subquery: [SubQ dropdown]                 
               Diagram                  |               Diagram                
[Details]  [Advanced Details]           | [Details]  [Advanced Details]         
          ------------------------------|           ---------------------------
              Statistics                |              Statistics             
[Match Operators][Unmatch Operators] [Match Queries][Unmatch Queries]          

Plan 1: [filespec1] is for picking the plan to show on the left side, Plan 2: [filespec2] is for the right-hand plan, and [Compare Plans] does the work.

Tip: When you restart the Compare Plans tool after using it before, [filespec1] and [filespec2] will be the same. Be careful what you pick, the tool doesn't care if you compare the same plan with itself.

Tip: When you change [filespec1] or [filespec2], even if you do it via the [Browse...] buttons, don't forget to click [Compare Plans] again.

In other words, don't rely on [filespec1] and [filespec2] for confirmation of exactly what you are looking at. If you forgot to click [Compare Plans], those aren't the plans being displayed.

The [<-] and [->] buttons scroll up and down through Comparison Overview section, highlighting different Name rows and corresponding boxes in the Diagram sections.

Each Name in the Comparison Overview section consists of a generated identifier 1, 2, 3 and a non-unique node title (SELECT, Work, DistH, etc.).

Tip: The Comparison Overview doesn't show the whole query, just the part you selected in the Subquery: [dropdown].

Big complicated SELECT statements can have many entries in the Subquery list. Some of those subqueries can be far more expensive than others, and they can be way down deep in the list.

The two Subquery: [dropdown] lists are synchronized; change one and the other changes, and so do both the Diagram sections.

Each box in the Diagram sections shows one table or other component in the SQL Anywhere query execution plan; for the full list see Execution Plan Components.

Tip: Table names inside rectangles mean table scans, trapezoids mean index scans. Table scans aren't always bad; if SQL Anywhere picks a table scan when an index exists, it's usually because the table scan is faster.

Tip: If a Diagram section shows up empty, click on the second node and then back on the top node.

The Statistics sections, with their Details and Advanced Details tabs, are where all the interesting comparisons are found.

Here's how to find them:

  • Step through all the diagrams in the Subquery: [dropdown] list.

  • Concentrate on the Actual numbers, not the Estimates.

  • Concentrate on the Subtree Statistics sections, not the Node Statistics.

  • Start by looking for large RunTime numbers, not PecentTotalCost.

One Last Thing...

If you don't like surprises, don't click on the [Match] and [Unmatch] buttons at the bottom of the screen.

If you want to learn, see Lesson 3: Manually Match and Unmatch Operators and Queries.

If you don't care, there's still a reason to click on these buttons, it's to answer this question...

Q: How do I see the original SELECT statements for the two plans?


  1. Click on Subquery: Main Query.

  2. Click on the topmost SELECT box in the diagrams.

  3. Click on [Unmatch Operators].

Voila! The original SELECT statements now appear in the Details tabs.

Tip: To reset everything, click on [Compare Plans} again... that gets rid of the effects of clicking on [Match] and [Unmatch].

Thursday, December 10, 2020

A Simple JSON Example

Question: How do I dump one row to a json file?

Answer: Here's an adhoc query that writes all the Foxhound 5 Monitor Options values for one target database to a json text file:

-- Start - Foxhound5 - 2 Adhoc Query via ISQL
SELECT REPLACE ( json_raw_table.json_raw_string, ',"', ',\X0D\X0A"' ) AS monitor_options_json
           FROM alerts_criteria
          WHERE alerts_criteria.criteria_set_type = 'Target Specific'
            AND alerts_criteria.sampling_id       = 13 -- Inventory17
            FOR JSON RAW
       ) AS json_raw_table ( json_raw_string ) 
TO 'C:\\temp\\monitor_options.json'

[{"criteria_set_type":"Target Specific",
"previous_gathered_sample_finished_at":"2020-12-10 08:28:08.609",
"current_sample_finished_at":"2020-12-10 08:28:08.609",
"alert1_in_effect_at":"2020-12-07 11:13:00.567",
"alert1_all_clear_at":"2020-12-08 07:48:11.438",

The docs are light on simple questions like this, perhaps because they lead to more simple questions...

How do I get SELECT FOR JSON output into a string?

Answer: Use a SELECT INTO clause, or a subquery.

How do I actually code SELECT FOR JSON in a subquery?

Answer: Code a derived table with an alias table name and a single alias column name, because the select list for SELECT * FOR JSON consists of one single column.

How do I get line breaks?

Answer: Use REPLACE, and hope your data doesn't contain any comma-doublequote pairs... or write better code than this example :)