Wednesday, June 5, 2013

Client-Server Via Web Services Inside SQL Anywhere

Have you ever found yourself copying code from brief Help topics, terse tutorials and concise examples without really understanding how that code works or even what it's doing?

Then, when it doesn't work, you hack around and Google for fixes and finally it seems to be doing what you want, so you ship it and try forget it?

And you feel a bit guilty for having done that?

If you're an Alpha or Beta, the answer is "Of course not! I created the technology, and those tutorials aren't terse, they're incredibly verbose!" 1998-07-15

If you're a Tech Charlatan, the answer is "Of course not! I understand every new technology the moment it's announced! Look, I watch TED, I made puns out of SOAP and DISH before you were born!" 1995-11-09

If you're one of those, this article is NOT for you

Disclaimer: This is not a tutorial on web services inside SQL Anywhere, it doesn't rise to that level.

It isn't even a "Getting Started" article, it's not up to that high standard either. And it certainly isn't a Help topic.

No, this article comes from the sub-basement level, below the the parking garage, in the University Of SQL Anywhere annex on the other side of town.

This article is for all the folks who took stuff apart when they were kids, to see "how it worked", and couldn't get it back together... except this time, your parents aren't going to yell at you. And it's for the folks who didn't have the courage to take stuff apart, but still wanted to see...

...because this time, you're can read this article by yourself and nobody will know. It won't make you an Alpha or a Beta, but it won't make you a Tech Charlatan either.

OK, my expectations have been lowered, what IS this article?

This article is about the foundations (footings, actually) for client-server communications inside SQL Anywhere, where the server is a website (or web service) and the client is a stored procedure or function, some or all of which reside inside SQL Anywhere databases. It's about passing data across the internet back and/or forth between blocks of SQL code running inside SQL Anywhere, with no application servers involved, no C# or Java or Python or VB or .NET and certainly no ORMs.

( cue muttered curses, sounds of room emptying... is anyone left? OK, fine, I'll talk to the hotel staff... :)

Step Zero: Create a couple of SQL Anywhere databases

Here's the Windows commands for that, or you can use Sybase Central if you want...
"%SQLANY16%\bin64\dbinit.exe" ddd16A.db

"%SQLANY16%\bin64\dbinit.exe" ddd16B.db

Step 1: Write a web server procedure that returns an HTML web page in a string

First, you have to start one of the databases (call it "Server A") and ISQL too...
  -f "%SQLANY16%\bin64\dbsrv16.exe"^

  -c "ENG=ddd16A;DBN=ddd16A;UID=dba;PWD=sql;CON=ddd16A"
Here's the code, plus a test SELECT...
CREATE PROCEDURE web_server_procedure (
   IN @service_parm1   LONG VARCHAR,
   IN @service_parm2   LONG VARCHAR )
RESULT ( html_string LONG VARCHAR )

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

         <TD STYLE="padding: 20">', @service_parm1, '</TD>
         <TD STYLE="padding: 20">', @service_parm2, '</TD>
   </HTML>' );


SELECT * FROM web_server_procedure ( 'Hello', 'World' );

         <TD STYLE="padding: 20">Hello</TD>
         <TD STYLE="padding: 20">World</TD>
(1 rows)
Execution time: 0.024 seconds
At this point there's nothing "web" about this "web server procedure" except for the fact it returns HTML... and other than looking like HTML, it's just ordinary text. In particular, the dbsrv16.exe command does not specify the -xs option, so the built-in SQL Anywhere HTTP server is not running.

The ordinary CREATE PROCEDURE statement starting on line 1 receives two string parameters and returns a single-row, single-column result set. That's what a SQL Anywhere "HTML web procedure" looks like: zero or more parameters that come from the URL, and a big fat HTML string as the result set.

The CALL to sa_set_http_header() on line 7 is required for web procedures that return HTML pages to the browser. Other kinds of web procedures might return images, for example, so 'text/html' will have to be changed to something else.

The SELECT starting on line 9 calls STRING() to build up an HTML string. Remember, at this point it's just text that happens to contain HTML thingies.

The SELECT * on line 23 is a unit test; it shows what web_server_procedure returns.

Step 2: Write a web service that returns an HTML web page to the browser

Web services require the builtin HTTP server to be running, so at this point you have to shut down Server A and start it again like this:
  -f "%SQLANY16%\bin64\dbsrv16.exe"^
  -xs http(port=12345)^

  -c "ENG=ddd16A;DBN=ddd16A;UID=dba;PWD=sql;CON=ddd16A"
The -xs option tells SQL Anywhere to start the builtin HTTP server, and the port=12345 is only necessary if you've already got a local web server running (say, Foxhound) on port 80.

Here's the code for the web service; the CREATE SERVICE statement is really just a wrapper around the CALL to web_server_procedure.

Tip: Don't confuse CREATE SERVICE with remote servers (CREATE SERVER) or Linux/Windows services (dbsvc), they're not related.
CREATE SERVICE web_service 
   AS CALL web_server_procedure ( 
      :service_parm2 );
The names "service_parm1" and "service_parm2" correspond to parameter names that will be coded in the URL in the browser, and the funky ":" syntax is something the SQL Anywhere parser needs.

And here it is, the URL to code in the browser...
Tip: The ? comes in front of the first parameter, & in front of the others... getting that wrong is one of THE most common mistakes ever made :)

Step 3: Write a web client function that can call the web service

So, you can call the web server procedure directly, that was shown in Step 1. But what if you wanted to call that procedure from somewhere else, like a different SQL Anywhere database?

And what if you didn't want to go through some kind of "remote procedure call" mechanism, but wanted to call the web service instead? There's no CALL statement for web services.

And there's no web browser inside a SQL Anywhere database, so you can't code the URL like in Step 2.

Or can you?

At this point, Server A is still running, still processing HTTP requests. A second server (call it Server B) is also required, but it doesn't need HTTP:
  -f "%SQLANY16%\bin64\dbsrv16.exe"^

  -c "ENG=ddd16B;DBN=ddd16B;UID=dba;PWD=sql;CON=ddd16B"
Here's the code for a SQL Anywhere web client function:
CREATE FUNCTION web_client_function (
   IN parm1   LONG VARCHAR,
   IN parm2   LONG VARCHAR )
URL 'HTTP://localhost:12345/web_service?service_parm1=!parm1&service_parm2=!parm2';
The parameter names parm1 and parm2 are chosen by you, just for this web-style CREATE FUNCTION statement; they don't have scope outside it.

The IN parm1 line defines the parameter name, and !parm1 refers to the parameter value at runtime; same thing for IN parm2 and !parm2.

Here's a demo:
  • A function call inside a SELECT on Server B

  • uses the URL to go out across the (local) internet,

  • fires up the web service inside Server A,

  • feeds it 'xxx' and 'yyy'

  • and gets back the web page as a single string...
SELECT web_client_function ( 'xxx', 'yyy' );

         <TD STYLE="padding: 20">xxx</TD>
         <TD STYLE="padding: 20">yyy</TD>
(1 rows)
Execution time: 0.044 seconds
Now you can honestly say you have "consumed a web service"... so much more sophisticated than "I called a function", doncha think?

Tip: Use simple parameter names like "parm1". Don't use "@service_parm1" or "service_parm1" or even "@parm1", they might compile but they will not work.

Step 4: Write a web client function that calls an external web service

Yes, you can go get stuff off real websites; here's how:
CREATE FUNCTION web_client_function()
URL '' 

SELECT web_client_function();

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
<script type="text/javascript">var ue_t0=ue_t0||+new Date();</script>
<script>var BtechCF = {a:1,cf:function(){if(--BtechCF.a == 0){ uet('cf');}},inc:function(){BtechCF.a++;}};</script>
  <script type="text/javascript">
  var btiplv;
     new Image().src = "";

... etcetera ...

<!-- MEOW -->
Yeah, seriously, I know; <!-- MEOW -->

Tip: If you get '405 Method Not Allowed' or '503 Service Temporarily Unavailable', it might mean TYPE 'HTTP:GET' is missing from the web-style CREATE FUNCTION statement.

Step 5: Write a web client procedure that can call an external web service

Here's an example that's looks like Step 4 except
CREATE PROCEDURE web_client_procedure (
   attribute  LONG VARCHAR,
   value      LONG VARCHAR )

SELECT * FROM web_client_procedure ( '0345529057' );
Tip: If you don't get any rows back, it might mean TYPE 'HTTP:GET' is missing from the web-style CREATE PROCEDURE statement.

Here's a SELECT that trims down the output a bit:
SELECT CAST ( attribute AS VARCHAR ( 20 ) ) AS attribute,
       CAST (
          STRING ( 
             LEFT ( TRIM ( value ), 60 ),
             IF LENGTH ( TRIM ( value ) ) > 60
                THEN '...'
                ELSE ''
             ENDIF )  
          AS VARCHAR ( 63 ) ) AS value 
  FROM web_client_procedure ( '0345529057' );

attribute            value                                                           
-------------------- --------------------------------------------------------------- 
Status               HTTP/1.1 200 OK                                                 
Body                 <html>
                     <script type="text/javascript">var ue_t0=ue_t... 
cache-control        no-cache                                                        
x-sap-pg             book_display_on_website                                         
Content-Type         text/html; charset=ISO-8859-1                                   
x-amz-id-2           hTtuCROD6gAIAKBqbfCpSpzjfvatUHoZf+7Chh/eKOrsHb/Fxeq7yhcpokR4... 
x-amz-id-1           0QJ30295GDTVSSP47EDW                                            
pragma               no-cache                                                        
Vary                 Accept-Encoding,User-Agent                                      
x-frame-options      SAMEORIGIN                                                      
Set-cookie           session-id=186-7664488-2512739; path=/;; ... 
expires              -1                                                              
Connection           close                                                           
p3p                  policyref="",CP="CAO DSP LA... 
Server               Server                                                          
Date                 Fri, 31 May 2013 17:44:15 GMT                                   
(16 rows)
Execution time: 2.445 seconds
The "Body" value is all that the web client function in Step 4 returned; here, you get all sorts HTTP stuff, even the cookie.

You can use Interactive SQL to feed the Body string back into a browser to see what you got; here's how:
  • Run a SELECT like this in Interactive SQL:
    SELECT TRIM ( value ) AS Body
      FROM web_client_procedure ( '0345529057' )
     WHERE attribute = 'Body';

  • On the Results pane, doubleclick on the '<html> <head> ...' value.

  • The "Value of column 'Body'" dialog box will open.

  • Select the HTML Preview tab, which will appear empty.
Then click on the Open in Browser button to see the Body string opened as local temporary "file:///" in the browser.

What's missing?

Two big things are missing from this article: First, gathering raw HTML ain't the most effective way to get data from other people's web services.

Second, some details have been left out, like
  • Where does HTTP fit into all of this?

  • What the heck is TYPE 'HTTP:GET' anyway?

  • What's the story on all those attributes like Content-Type and pragma and p3p?
This, however, is the academic sub-basement. More effective ways to get data won't be found here, you have to go at least one level up.

And guess what? There's a level below sub-basement, that's where HTTP lives, maybe lower.

In other words, out of scope, sorry :)


Vest said...

Hi Breck,

Thank you for the interesting article. It sheds light on some topics that I am currently learning. To tell you the truth, I was looking at your application - Foxhound, and I want to ask you a question that is probably a secret one :)

How were you able to generate dynamic (not 10,000% of course, but still) pages with so low-level functions? If I understood correctly, SQL Anywhere functions allow you to send whatever you want back to the browser. However, the page should be somehow generated or built.

The code that you wrote here is based on SELECT and STRING statements. The same approach is used in SA demos.

Do you use a stand-alone web server (e.g. Apache+PHP, or Tomcat+JSP), where you design pages, test them and after they are complete, you cut them in notepad? I assume that you probably have some kind of a helper that uploads static resources to the database, and always loads them via a generic function.

I know that my question is quite... proprietary maybe. Unfortunately I am very curious about this, because I do not want to invent a wheel, a hammer, a bucket filled with invented nails and... wait... I have build the LHC?!

Thank you in advance.

Breck Carter said...

@Vest: Send me an email breck dot carter at gmail