SQL Anywhere: Calling Stored Procedures From HTML

Question: How do I call a SQL Anywhere stored procedure from HTML without refreshing the whole web page?

Answer: You can use the XMLHttpRequest JavaScript object to send and receive data to and from a SQL Anywhere web service that calls the stored procedure.

Here is what W3Schools has to say:

The XMLHttpRequest object is a developer's dream, because you can:

Update a web page without reloading the page

Request data from a server after the page has loaded

Receive data from a server after the page has loaded

Send data to a server in the background

If you've read about XMLHttpRequest in the past and been scared off, here's what you don't have to deal with using it:

XML: In spite of the name, XMLHttpRequest can return ordinary string data.
AJAX: Your stored procedure calls can be synchronous (call and return) rather than asynchronous (fire and forget).
Frameworks: The XMLHttpRequest object is useful all by itself, you don't have to commit to a massive library.
Key-value stores, NoSQL, JQuery, Xpath, JSON... the list of stuff you don't need goes on and on.

The Mozilla Developer Network has some of the best docs for XMLHttpRequest.

The code below shows two pairs of like-named web services and stored procedures:

"display" returns a static web page with a search-as-you-type input field that retrieves rows from the Customer table in the SQL Anywhere 16 demo database.
"search" is called via XMLHttpRequest from the display web page; it receives a search string as a parameter and returns a string of HTML.

The following URLs are used to launch the two web services:

http://localhost:12345/display     - from the web browser
search?searchString=xx...          - from JavaScript via XMLHttpRequest

Here's what the display web service and procedure look like:

CREATE SERVICE display
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL display();

CREATE PROCEDURE display()
RESULT ( html_string LONG VARCHAR )
BEGIN
CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
SELECT STRING ( 
   '<HTML> ',
   '<HEAD> ',
   '<STYLE> ',
      'TABLE { padding: 0; border: 1px solid black; border-collapse: collapse; } ', 
      'TD { padding: 0.3em; border: 1px solid black; } ', 
   '</STYLE> ',
   '<SCRIPT> ',
   'function showResults ( searchString ) { ',
      'xmlHttp = new XMLHttpRequest(); ',
      'xmlHttp.open ( "GET", "search?searchString=" + encodeURI ( searchString ), false ); ',
      'xmlHttp.send(); ',
      'document.getElementById ( "searchResults" ).innerHTML = xmlHttp.responseText; ',
   '} ',
   '</SCRIPT> ', 
   '</HEAD> ',
   '<BODY> ',
   '<INPUT TYPE="text" ID="txt1" onkeyup="showResults ( this.value )" /> ',
   '<P> ',
   '<SPAN ID="searchResults"></SPAN> ',
   '</BODY> ',
   '</HTML> ' );
END;

The CREATE SERVICE on lines 1 to 3 sets up a web service wrapper for the display procedure.

The CREATE PROCEDURE on lines 5 to 31 returns a static web page that contains the input field and the XMLHttpRequest logic.

Note: The display web page could be stored in a static HTML text file instead of a web service if it wasn't for the problem of not being not being able to perform cross-domain requests from a file:

XMLHttpRequest cannot load file: ... Cross origin requests are only supported for protocol schemes: http, data, chrome-extension, https, chrome-extension-resource.

To avoid that problem, this web page could also be served up by the general-purpose "web service for website files" described in the earlier article Embedding Fiori In SQL Anywhere.

The HTML INPUT tag on line 26 calls the local JavaScript showResults function whenever a character is typed or deleted.

The JavaScript showResults function on lines 17 through 22 does the following:

A new instance of the JavaScript XMLHttpRequest object is created on line 18.
The XMLHttpRequest.open method is called on line 19 to set up an HTTP GET operation that passes the searchString value to the search service.
The JavaScript encodeURI function works like the SQL HTTP_ENCODE function to ensure that special characters like spaces are not lost when passed in URLs.
The third argument on line 19 turns off the asynchronous behavior of XMLHttpRequest so the send() on line 20 works as a call-and-return rather than fire-and-forget.
The assignment statement on line 21 displays the HTML returned via XMLHttpRequest
- by using the HTML getElementById method to reference the SPAN tag defined on line 28, then
- copying the XMLHttpRequest.responseText returned via the HTTP GET operation
- to the HTML innerHTML property of the SPAN tag.

OK, it's not exactly "Calling A Stored Procedure From HTML", it's calling a stored procedure from JavaScript, but JavaScript is a fact of life when developing HTML web pages... JavaScript is arguably the most important programming language in the world today.

Here's what the search web service and procedure look like:

CREATE SERVICE search 
   TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL search ( :searchString );

CREATE PROCEDURE search ( IN @searchString VARCHAR ( 100 ) )
RESULT ( html_string LONG VARCHAR )
BEGIN
CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
SELECT STRING ( 
          '<TABLE>', 
          LIST ( STRING ( 
             '<TR>',
             '<TD>', ID, '</TD>',
             '<TD>', Surname, '</TD>',
             '<TD>', GivenName, '</TD>',
             '<TD>', Street, '</TD>',
             '<TD>', City, '</TD>',
             '<TD>', State, '</TD>',
             '<TD>', Country, '</TD>',
             '<TD>', PostalCode, '</TD>',
             '<TD>', Phone, '</TD>',
             '<TD>', CompanyName, '</TD>',
             '</TR>\X0D\X0A' ),
             ''
             ORDER BY ID ),
          '</TABLE>' )
  FROM Customers
 WHERE Surname                    LIKE STRING ( @searchString , '%' )
    OR GivenName                  LIKE STRING ( @searchString , '%' )
    OR Street                     LIKE STRING ( '%', @searchString , '%' )
    OR City                       LIKE STRING ( @searchString , '%' )
    OR STRING ( State, '=state' ) = @searchString 
    OR PostalCode                 LIKE STRING ( @searchString , '%' )
    OR Phone                      LIKE STRING ( @searchString , '%' )
    OR CompanyName                LIKE STRING ( '%', @searchString , '%' );
END;

The SELECT on lines 9 through 35 builds an HTML TABLE containing all the columns in the Customer table.

The WHERE clause on lines 28 through 35 applies the search string to eight of those columns, with a few cute twists:

The search string is matched against leading characters in Surname, GivenName, City, PostalCode and Phone.
The search string is matched against any substring in Street and CompanyName.
The special format 'xx=state' lets the user specify exact State values.

Those cute twists aren't important in themselves, they only serve to illustrate that all the power of SQL queries is available when calling stored procedures from JavaScript.

Here's are the Windows command line for starting the SQL Anywhere 16 demo database with the builtin HTTP server enabled on port 12345, and then launching an ISQL session so you can load the code shown above:

"%SQLANY16%\bin64\dbspawn.exe"^
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -o dbsrv16_log_demo.txt^
  -x tcpip^
  -xs http(port=12345;maxsize=0;to=600;kto=600)^
  "C:\Users\Public\Documents\SQL Anywhere 16\Samples\demo.db"

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=demo; DBN=demo; UID=dba; PWD=sql; CON=demo-1"

The following screen capture doesn't do justice to the search-as-you-type action, you really have to try it yourself:

SQL Anywhere

Pages

Tuesday, January 13, 2015

Calling Stored Procedures From HTML

No comments:

Categories