Creating a Mobile AJAX Process Using SQL Anywhere Web Services

-- if service exists, drop service to create new one
if exists(select * from SYS.SYSWEBSERVICE where service_name='query_db') then
	drop service "query_db";
end if
go

/* query_db service: returns RAW code, meaning html tags musts be inserted
 selects FirstName and LastName with a space between and the HTML tag for a new line after 
 sample url: http://localhost/get-user?minimum=10&maximum=20*/
CREATE SERVICE query_db
TYPE  'RAW'
AUTHORIZATION OFF
USER DBA
AS SELECT FirstName, ' ', LastName, '<br/>' FROM ajax_demo WHERE Score >= :minimum AND Score <= :maximum;

-- if service exists, drop service to create new one
if exists(select * from SYS.SYSWEBSERVICE where service_name='get_user') then
    drop service "get_user";
end if
go

/* creates get_user service which
returns an HTML table of all people
whose user_id match the inputted one
sample url: http://localhost/get_user?user_id=1 */
CREATE SERVICE get_user
TYPE 'RAW'
AUTHORIZATION OFF
USER DBA
AS SELECT 'Age: ', Age, '<br/>Gender: ', Gender, '<br/>Hometown: ',Hometown, '<br/>Job: ',Job, '<br/>Score: ',Score FROM ajax_demo WHERE id = :user_id;
go

/* creates root service which allows user to directly navigate to example.html
sample url: http://localhost/example.html (directs to example.html)
sample url: http://localhost/ (gives message 'Access Denied') */
CALL sa_make_object( 'service', 'root') 
go
ALTER SERVICE "root" TYPE 'RAW' AUTHORIZATION OFF USER "DBA" URL ON AS call sp_get_page(:url);
go

/* procedure called by root web service
opens example.html if example.html provided as argument
otherwise gives an error */
CALL sa_make_object( 'procedure', 'sp_get_page') 
go
ALTER PROCEDURE "DBA"."sp_get_page"( url char(1024) default 'index.html', authin char(1) default '0' )
 RESULT( result long binary)
BEGIN
  declare page long binary;
  
  if url = '' or url is null then 
    set url='index.html';
  end if;
  
  if url not in ('example.html')  and url not like '%.ico' and url not like '%.bmp'
        and url not like '%.gif' and url not like '%.jpg'
        and (authin = 0 ) and varexists('auth_user')!=1 then
--message http_header( '@HttpURI' );
--message auth_user;
    call sa_set_http_header('@HttpStatus',404);
    select 'Access denied';
    return;
  end if; 

--opens example.html if error has not already occurred  
  set page=xp_read_file('\My Documents\' + url);
  call dbo.sa_set_http_header( 'Content-Type', 'text/html' );
  select page;
END