Webservices IQ
One of the really cool features of Sybase IQ is the ease of use of WebServices. Strictly spoken it is not really something specific for Sybase IQ, but more of Sybase Adaptive Server Anywhere, the catalog store for IQ.
This page describes how to install and use Webservices for html documents. IQ also allows you to create XML, SOAP or DISH documents but this will not be covered here.
Contents
Configure IQ
Add an extra line to the cfg file for IQ to start up an http listener.
-xs http{port=8080}
The port number 8080 is just as an example.
Then stop/start IQ to activate the listener.
To activate a webservice, for instance for your browser, you need to define a service in IQ. When IQ is running on, let's say a host called prd-syb-iq1, an URL http://prd-syb-iq1:8080/my_service expects a service in IQ called my_service. The name "root" is the default service so http://prd-syb-iq1:8080 will activate a service called root.
Create services
Create a service with dbisql, isql or another client tool. Sybase Central does not support services.
Log on as DBA or with an account with DBA permission and create a service.
create service root type 'html' as call sp_iqcontext()
You can only define a single statement in a service, so this will usually be a stored procedure and this case we choose sp_iqcontext.
The html type of service allows you create a resultset through a select statement and the output is automatically formatted by IQ. In this example the sp_iqcontext stored procedure selects some data, and IQ will then automatically format it for you. You can already activate this service now by activating the URL http://prd-syb-iq1. You will get a pop-up screen for a username/password, enter a valid IQ login here.
Create an advanced webpage
To create a more advanced webpage you should do your own html formatting. Use a service with type "raw" for this.
create service test_service type 'raw' as call sp_test()
It's up the sp_test stored procedure to create a valid html file and send it back to IQ.
create procedure sp_test()
begin
call dbo.sa_set_http_header('Content-Type', 'text/html');
select '<html><head></head><body>Hello World</body></html>';
end
You need to invoke a call to sa_set_http_header, otherwise your browser will not understand IQ is sending an html file.
This type of service can create complex html documents, but you should create all of that document in a single resultset. You can not issue a couple of select statements each making up a part of the html file, IQ will only look at the first resultset. Here's how you can create more complex stuff.
create procedure sp_test()
begin
declare local temporary table html(
nr integer not null default autoincrement,
html_data varchar(1024) not null);
insert into html(html_data) values('<html><head></head>');
insert into html(html_data) values('<body>Hello Earth</body>');
insert into html(html_data) values('</html>');
call dbo.sa_set_http_header('Content-Type', 'text/html');
select html_data
from html
order by nr;
drop table html;
end
It's now easy to see that the possibilities are endless.
Passing parameters from an html document to IQ
To send values to a stored procedure coming from an html document, here are some other examples. Suppose the html file you generate has the following hyperlink in it:
<a href="service_show_top">Show top 10</a>
This will expect a service in IQ called service_show_top.
A more complex hyperlink:
<a href="service_show_quarter?year=2007&quarter=4>2007 - 4</a>
Now the service service_show_quarter can retrieve these two parameters and pass them on to a stored procedure like this:
create service service_show_quarter
type 'raw'
as
call show_quarter(http_variable('year'),http_variable('quarter'));
The stored procedure show_quarter can then be created.
create procedure show_quarter(in int year, in tinyint quarter) begin /* A lot of code here */ end
