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!"
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!"
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...Here's the code, plus a test SELECT..."%SQLANY16%\bin64\dbspawn.exe"^ -f "%SQLANY16%\bin64\dbsrv16.exe"^ ddd16A.db "%SQLANY16%\bin64\dbisql.com"^ -c "ENG=ddd16A;DBN=ddd16A;UID=dba;PWD=sql;CON=ddd16A"
CREATE PROCEDURE web_server_procedure (
IN @service_parm1 LONG VARCHAR,
IN @service_parm2 LONG VARCHAR )
RESULT ( html_string LONG VARCHAR )
BEGIN
CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );
SELECT STRING (
'<HTML>
<BODY BGCOLOR=#FFFFFF>
<TABLE>
<TR>
<TD STYLE="padding: 20">', @service_parm1, '</TD>
<TD STYLE="padding: 20">', @service_parm2, '</TD>
</TR>
</TABLE>
</BODY>
</HTML>' );
END;
SELECT * FROM web_server_procedure ( 'Hello', 'World' );
html_string
-------------------------------------------
<HTML>
<BODY BGCOLOR=#FFFFFF>
<TABLE>
<TR>
<TD STYLE="padding: 20">Hello</TD>
<TD STYLE="padding: 20">World</TD>
</TR>
</TABLE>
</BODY>
</HTML>
(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: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."%SQLANY16%\bin64\dbspawn.exe"^ -f "%SQLANY16%\bin64\dbsrv16.exe"^ -xs http(port=12345)^ ddd16A.db "%SQLANY16%\bin64\dbisql.com"^ -c "ENG=ddd16A;DBN=ddd16A;UID=dba;PWD=sql;CON=ddd16A"
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
TYPE 'RAW'
AUTHORIZATION OFF
USER DBA
AS CALL web_server_procedure (
:service_parm1,
:service_parm2 );
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 :)http://localhost:12345/web_service?service_parm1=Hello&service_parm2=World
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:Here's the code for a SQL Anywhere web client function:"%SQLANY16%\bin64\dbspawn.exe"^ -f "%SQLANY16%\bin64\dbsrv16.exe"^ ddd16B.db "%SQLANY16%\bin64\dbisql.com"^ -c "ENG=ddd16B;DBN=ddd16B;UID=dba;PWD=sql;CON=ddd16B"
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.CREATE FUNCTION web_client_function ( IN parm1 LONG VARCHAR, IN parm2 LONG VARCHAR ) RETURNS LONG VARCHAR URL 'HTTP://localhost:12345/web_service?service_parm1=!parm1&service_parm2=!parm2';
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' );
web_client_function('xxx','yyy')
-----------------------------------------
<HTML>
<BODY BGCOLOR=#FFFFFF>
<TABLE>
<TR>
<TD STYLE="padding: 20">xxx</TD>
<TD STYLE="padding: 20">yyy</TD>
</TR>
</TABLE>
</BODY>
</HTML>
(1 rows)
Execution time: 0.044 seconds
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()
RETURNS LONG VARCHAR
URL 'http://www.amazon.com/'
TYPE 'HTTP:GET';
SELECT web_client_function();
web_client_function()
--------------------------------------------------------------
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<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>
<!--btech-iplc-->
<script type="text/javascript">
var btiplv;
new Image().src = "http://g-ecx.images-amazon.com/images/G/01/gno/beacon/BeaconSprite-US-01._V397411194_.png";
</script>
... etcetera ...
</body>
</html>
<!-- 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- it's coded as a web-style CREATE PROCEDURE instead of a function,
- it uses a parameter called ASIN (Amazon Standard Identification Number) that is referenced as !ASIN to customize the URL when the procedure is called, and
- it returns a two-column multi-row result set instead of a single string.
Tip: If you don't get any rows back, it might mean TYPE 'HTTP:GET' is missing from the web-style CREATE PROCEDURE statement.CREATE PROCEDURE web_client_procedure ( IN ASIN LONG VARCHAR ) RESULT ( attribute LONG VARCHAR, value LONG VARCHAR ) URL 'http://www.amazon.com/dp/!ASIN' TYPE 'HTTP:GET'; SELECT * FROM web_client_procedure ( '0345529057' );
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>
<head>
<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=/; domain=.amazon.com; ...
expires -1
Connection close
p3p policyref="http://www.amazon.com/w3c/p3p.xml",CP="CAO DSP LA...
Server Server
Date Fri, 31 May 2013 17:44:15 GMT
(16 rows)
Execution time: 2.445 seconds
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.
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?
And guess what? There's a level below sub-basement, that's where HTTP lives, maybe lower.
In other words, out of scope, sorry :)
2 comments:
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.
@Vest: Send me an email breck dot carter at gmail
Post a Comment