When you open a web page in a browser, even a simple one, more than one connection to the HTTP server is usually required. Sometimes there will be many dozens of connections: one for the page as a whole, one each image (even the teeny tiny ones), and if the web page is a FRAMESET there will be one connection for each FRAME.
Browsers and traditional HTTP servers do a really good job of handling these multiple connections in an efficient manner. But what about SQL Anywhere's built-in HTTP server? If you use web services inside the database to serve up all your web pages, plus all the images and other bits and pieces, will it be just as efficient?
The answer is yes, and if you use SQL Anywhere 10's HTTP session support, performance and functionality gets even better.
By default, SQL Anywhere starts a new database connection for each web service, and closes that connection when the web service ends. That has two implications: Lots and lots of very short database connections (thousands, millions), and no "memory" or persistent data carried from one web page to the next.
Once upon a time, online transaction systems were classified as conversational versus non-conversational. Today, non-conversational is called "stateless" meaning the server does not store anything (no "state") between one user interaction and the next. Non-conversational or stateless servers are fast and efficient, no storage is required for those thousands of users out there, no code is required to maintain that data. Yesterday CICS, today Apache, dumb as posts unless you work really hard.
The trouble with stateless servers is they're useless... if something even as simple as a password and user id is required, the user would have to provide it with every single interaction. You have to be able to pass
something from one interaction to the next... you have to give your system some memory, you have to make your application conversational or "stateful".
With the SQL Anywhere HTTP server, with or without session support, you can pass data in three ways:
- as parameters in the URL
- in a cookie
- in the database
For example, the
Foxhound database monitor uses a combination of 1 and 3: NEWID() is called to create a GUID that is passed in the URL from one web page to the next, and that GUID is used as the primary key to store and retrieve data that's unique to each continuing user conversation.
Here's a typical URL used by Foxhound, where "z1" is the name of the HTTP parameter carrying the GUID to the next web service:
http://localhost/foxhound?t=rroad_monitor_database1
&i1=N&z1=3ea32c17-1704-48d2-afa7-c0a6cf1d32e9
Here is a snippet from one of several Foxhound tables that has that GUID as primary key:
CREATE TABLE rroad_session_options (
session_id VARCHAR ( 36 ) NOT NULL,
integer_session_id INTEGER NOT NULL
DEFAULT AUTOINCREMENT UNIQUE,
sampling_id UNSIGNED INTEGER NULL,
list_refresh_count BIGINT NOT NULL DEFAULT 0,
display_refresh_count BIGINT NOT NULL DEFAULT 0,
...
PRIMARY KEY ( session_id ) );
Foxhound was originally developed before SQL Anywhere offered session support, and it still doesn't use that feature.
The question is, why bother?
There are several reasons why Foxhound and any other conversational SQL Anywhere web service application should use HTTP session support:
- Each successive web service will use the same database connection as the previous one in the same conversation; it won't have to start and stop a new connection. That's more than just an efficiency consideration, read on...
- Having one continuing connection means you can pass conversational "memory" from one web service to the next in connection-level (GLOBAL TEMPORARY) tables.
- Although you still need to create and store a "session id" and put it in a cookie or the URL, you don't have to use it anywhere else in your code. Your web services don't need to receive it as a parameter, and your SELECT statements don't need to specify it in the WHERE clauses.
- Because there are now fewer database connections used by web services, and they last longer, and it is possible to watch them and monitor them... say, in Foxhound itself.
- Other stuff, like session timeout, comes with it... more code you don't have to write.
Here is the code for two simple web services, s1 and s2, plus a GLOBAL TEMPORARY state table. Each of the web services calls a procedure, p1 and p2 respectively. The code is almost identical for each service/procedure pair, differing only where the digits "1" and "2" are used so you can tell what you're looking at in the browser.
--------------------------------------
-- One row per connection, id = 1 always.
CREATE GLOBAL TEMPORARY TABLE state (
id INTEGER NOT NULL DEFAULT 1
PRIMARY KEY CHECK ( id = 1 ),
value VARCHAR ( 100 ) NOT NULL )
ON COMMIT PRESERVE ROWS;
--------------------------------------
CREATE SERVICE s1
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p1();
--------------------------------------
CREATE PROCEDURE p1()
RESULT (
html_string LONG VARCHAR )
BEGIN
DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );
-- Use the SessionID, or create a new one.
SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );
IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
END IF;
-- Get the input state, if it exists.
SELECT state.value
INTO @input_state
FROM state;
-- Create or update the state.
INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s1 - p1' );
COMMIT; -- don't forget ON COMMIT PRESERVE ROWS
-- Send the HTML back to the browser.
CALL sa_set_http_header (
'Content-Type', 'text/html' );
SELECT STRING (
'<HTML><BODY><TITLE>s1 - p1</TITLE>',
'<PRE>',
'Service s1 - Procedure p1\x0d\x0a\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );
END; -- p1
--------------------------------------
CREATE SERVICE s2
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p2();
--------------------------------------
CREATE PROCEDURE p2()
RESULT (
html_string LONG VARCHAR )
BEGIN
DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );
-- Use the SessionID, or create a new one.
SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );
IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
END IF;
-- Get the input state, if it exists.
SELECT state.value
INTO @input_state
FROM state;
-- Create or update the state.
INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s2 - p2' );
COMMIT; -- don't forget ON COMMIT PRESERVE ROWS
-- Send the HTML back to the browser.
CALL sa_set_http_header (
'Content-Type', 'text/html' );
SELECT STRING (
'<HTML><BODY><TITLE>s2 - p2</TITLE>',
'<PRE>',
'Service s2 - Procedure p2\x0d\x0a\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );
END; -- p2
Figure 1 shows what service s1 displayed when it was launched with http://localhost/s1 in the browser:
- The service s1 has started database connection number 3.
- @session_id shows that procedure p1 has calculated a new GUID to use as session id.
- @input_state was NULL because the "state" table didn't contain any row for connection 3 when service s1 started.
Figure 1: Service s1 at startup
There are exactly two critical pieces of code in each procedure p1 and p2. The first critical piece calls CONNECTION_PROPERTY to see if an HTTP SessionID already exists and has been passed to this service. If it doesn't exist (if @session_id is empty) then the procedure calls NEWID() to generate a new GUID, and sa_set_http_option is called to start a new session.
-- Use the SessionID, or create a new one.
SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );
IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
END IF;
The second critical piece of code passes ?sessionid= to the next service, via the URLs...
http://localhost/s1?sessionid=d27b7503-9051...
http://localhost/s2?sessionid=d27b7503-9051...
...in the links that are part of the HTML constructed by the big SELECT in procedure p1:
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
...
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
Here are four Notes, plus a Tip...
Note: The parameter value can be anything you want (a GUID is used here), but parameter name in the URL must be "sessionid". If you use a different parameter name then the CONNECTION_PROPERTY ( 'SessionID' ) call in the next service won't return the value.
Note: You can use a cookie instead of a parameter in the URL... if you want... it's somewhere in the docs.
Note: It's not enough to just call sa_set_http_option, or to just code ?sessionid= in the URL, you have to do both. At first glance that's a head-scratcher, doing both seems redundant, but it's not: The call to sa_set_http_option tells SQL Anywhere to start a new session, and the ?sessionid= in the URL tells SQL Anywhere *which* of many possible sessions is supposed to be used by the next service.
Note: The ?sessionid= parameter in the URL is for use by SQL Anywhere only. It is possible to name it in your CREATE SERVICE statement and pass it to your procedure...
CREATE SERVICE s1
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p1 ( :sessionid );
CREATE PROCEDURE p1 (
IN @input_sessionid LONG VARCHAR )
...but you probably shouldn't do that. Do not rely on the value passed from the URL because the underlying session might have expired. Call CONNECTION_PROPERTY ( 'SessionID' ) instead; it will return an empty value if the session has expired, and your code will call NEWID() and sa_set_http_option to start a new session.
Tip: If you make a mistake and accidentally create a new session every time you run your web service, perhaps because of a coding error in the URL, you'll end up with a whole mess of long-lasting "orphan" connections to the database (see Figure 2). If you want to find out about this mistake earlier rather than later, run dbeng10.exe instead of dbsrv10.exe so you will get "503 Service Temporarily Unavailable" as soon as the personal server's limit of 10 connections is exhausted.
Figure 2: Orphan Connections
Figure 3 shows what service s2 displayed when "Go to service s2" was clicked in Figure 1:
- A new service has started but database connection number 3 is still the one being used.
- @session_id shows the GUID calculated by the previous service is still being used.
- The @input_state value 'state set in s1 - p1' shows that the GLOBAL TEMPORARY TABLE has successfully carried data from the previous service forward to this one.
Figure 3: After "Go to service s2" was clicked on s1
Figure 4 shows what is displayed after the round trip back to service s1: the session id and the connection number are the same as before, and @input_state shows that data is still being passed from service to service.
Figure 4: After "Go to service s1" was clicked on s2
Figure 5 shows what happens when a fake session id is manually entered:
- A new connection to the database has been started: number 4.
- A new GUID has been calculated, meaning that a new HTTP session has been started... and the fake session id has been ignored.
- The @input_state is NULL because the GLOBAL TEMPORARY TABLE does not carry data across connection changes.
Figure 5: Unknown session id is ignored
Figure 6 shows what happens when you click on one of the links in Figure 5: The new database connection is being used, so is the new session id, and the state table is again carrying data from one service to the next. In other words, the fake session id has had no effect whatsoever, it is as if the ?sessionid= had been left off the URL in Figure 5... except for the session id value, Figures 5 and 6 look exactly like Figures 1 and 3.
Figure 6: "Go to service" link fixes session id
Figure 7 shows the display from a verbose version of the two service/procedure pairs (scroll down for the code).
Figure 7: Verbose service
Here's what's different about the verbose version:
- The new @input_sessionid parameter shows the ?sessionid= value that is passed from the URL to the procedure via the service's new :sessionid parameter. I know that earlier I said "don't do this", but it's OK as long as you do not trust the value. Here it is used just to determine if a new session is starting because of a (likely) timeout; see the @info variable below.
- The new @init_SessionID variable shows what CONNECTION_PROPERTY ( 'SessionID' ) returned when first called.
- The new @info variable (empty here) will show 'New session (new request)' when the service is first started, and 'New session (possible timeout)' when the ?sessionid= value passed from the URL has timed out.
- http_session_timeout shows that the timeout period has been set to 1 minute for testing purposes, via SET TEMPORARY OPTION HTTP_SESSION_TIMEOUT = '1'.
- SessionCreateTime and SessionLastTime are two useful CONNECTION_PROPERTY values; see the Help for more information.
- The @counter variable displays a new column in the GLOBAL TEMPORARY TABLE state: how many times services have been executed in the current session.
Here's the code for the modified state table and the two verbose service/procedure pairs; you can use this code for your own experiments with HTTP sessions...
--------------------------------------
-- One row per connection, id = 1 always.
CREATE GLOBAL TEMPORARY TABLE state (
id INTEGER NOT NULL DEFAULT 1
PRIMARY KEY CHECK ( id = 1 ),
value VARCHAR ( 100 ) NOT NULL,
counter BIGINT NOT NULL DEFAULT 0 )
ON COMMIT PRESERVE ROWS;
--------------------------------------
CREATE SERVICE s1
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p1 ( :sessionid );
--------------------------------------
CREATE PROCEDURE p1 (
IN @input_sessionid LONG VARCHAR )
RESULT (
html_string LONG VARCHAR )
BEGIN
DECLARE @info VARCHAR ( 100 );
DECLARE @init_SessionID VARCHAR ( 36 );
DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );
DECLARE @counter BIGINT;
SET @info = ''; -- nothing interesting to report yet
-- Save the initial SessionID for display.
SET @init_SessionID =
CONNECTION_PROPERTY ( 'SessionID' );
-- Use the SessionID, or create a new one.
SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );
IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
-- Determine likely reason for the new session.
IF COALESCE ( @input_sessionid, '' ) = '' THEN
SET @info = 'New session (new request)';
ELSE
SET @info = 'New session (possible timeout)';
END IF;
END IF;
-- Set a really short timeout for testing purposes.
SET TEMPORARY OPTION HTTP_SESSION_TIMEOUT = '1';
-- Get the input state, if it exists.
SELECT state.value
INTO @input_state
FROM state;
-- Create or update the state.
INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s1 - p1' );
-- Update the service execution counter.
UPDATE state
SET state.counter = state.counter + 1;
COMMIT; -- don't forget ON COMMIT PRESERVE ROWS
-- Get the service execution counter.
SELECT state.counter
INTO @counter
FROM state;
-- Send the HTML back to the browser.
CALL sa_set_http_header (
'Content-Type', 'text/html' );
SELECT STRING (
'<HTML><BODY><TITLE>Verbose s1 - p1</TITLE>',
'<PRE>',
'Verbose Service s1 - Procedure p1\x0d\x0a\x0d\x0a',
'@input_sessionid: ',
COALESCE ( @input_sessionid, '[NULL]' ),
'\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@init_SessionID: ',
COALESCE ( @init_SessionID, '[NULL]' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@info: ',
@info,
'\x0d\x0a',
'http_session_timeout: ',
CONNECTION_PROPERTY ( 'http_session_timeout' ),
'\x0d\x0a',
'SessionCreateTime: ',
CONNECTION_PROPERTY ( 'SessionCreateTime' ),
'\x0d\x0a',
'SessionLastTime: ',
CONNECTION_PROPERTY ( 'SessionLastTime' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a',
'@counter: ',
@counter,
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );
END; -- p1
--------------------------------------
CREATE SERVICE s2
TYPE 'RAW' AUTHORIZATION OFF USER DBA
AS CALL p2 ( :sessionid );
--------------------------------------
CREATE PROCEDURE p2 (
IN @input_sessionid LONG VARCHAR )
RESULT (
html_string LONG VARCHAR )
BEGIN
DECLARE @info VARCHAR ( 100 );
DECLARE @init_SessionID VARCHAR ( 36 );
DECLARE @session_id VARCHAR ( 36 );
DECLARE @input_state VARCHAR ( 100 );
DECLARE @counter BIGINT;
SET @info = ''; -- nothing interesting to report yet
-- Save the initial SessionID for display.
SET @init_SessionID =
CONNECTION_PROPERTY ( 'SessionID' );
-- Use the SessionID, or create a new one.
SET @session_id = TRIM ( COALESCE (
CONNECTION_PROPERTY ( 'SessionID' ), '' ) );
IF @session_id = '' THEN
SET @session_id = NEWID();
CALL sa_set_http_option (
'SessionID', @session_id );
SET TEMPORARY OPTION
HTTP_SESSION_TIMEOUT = '1';
-- Determine likely reason for the new session.
IF COALESCE ( @input_sessionid, '' ) = '' THEN
SET @info = 'New session (new request)';
ELSE
SET @info = 'New session (possible timeout)';
END IF;
END IF;
-- Set a really short timeout for testing purposes.
SET TEMPORARY OPTION HTTP_SESSION_TIMEOUT = '1';
-- Get the input state, if it exists.
SELECT state.value
INTO @input_state
FROM state;
-- Create or update the state.
INSERT state ( value )
ON EXISTING UPDATE
VALUES ( 'state set in s2 - p2' );
-- Update the service execution counter.
UPDATE state
SET state.counter = state.counter + 1;
COMMIT; -- don't forget ON COMMIT PRESERVE ROWS
-- Get the service execution counter.
SELECT state.counter
INTO @counter
FROM state;
-- Send the HTML back to the browser.
CALL sa_set_http_header (
'Content-Type', 'text/html' );
SELECT STRING (
'<HTML><BODY><TITLE>Verbose s2 - p2</TITLE>',
'<PRE>',
'Verbose Service s2 - Procedure p2\x0d\x0a\x0d\x0a',
'@input_sessionid: ',
COALESCE ( @input_sessionid, '[NULL]' ),
'\x0d\x0a',
'Connection: ',
CONNECTION_PROPERTY ( 'Number' ),
'\x0d\x0a',
'@init_SessionID: ',
COALESCE ( @init_SessionID, '[NULL]' ),
'\x0d\x0a',
'@session_id: ',
COALESCE ( @session_id, '[NULL]' ),
'\x0d\x0a',
'@info: ',
@info,
'\x0d\x0a',
'http_session_timeout: ',
CONNECTION_PROPERTY ( 'http_session_timeout' ),
'\x0d\x0a',
'SessionCreateTime: ',
CONNECTION_PROPERTY ( 'SessionCreateTime' ),
'\x0d\x0a',
'SessionLastTime: ',
CONNECTION_PROPERTY ( 'SessionLastTime' ),
'\x0d\x0a',
'@input_state: ',
COALESCE ( @input_state, '[NULL]' ),
'\x0d\x0a',
'@counter: ',
@counter,
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s1',
'?sessionid=', @session_id,
'">Go to service s1 - Procedure p1</A>',
'\x0d\x0a\x0d\x0a',
'<A HREF="http://localhost/s2',
'?sessionid=', @session_id,
'">Go to service s2 - Procedure p2</A>',
'</PRE>',
'</BODY></HTML>' );
END; -- p2
-- [end]
