Skip to Content

This is a modified version of a http://groups.google.com/group/sql-anywhere-web-development/browse_thread/thread/62acc8d0f5a8d07b I originally made in the SQL Anywhere Web Development Forum on September 3, 2009

There was recently a question in the SQL Anywhere Web Development Forum regarding how to use TYPE 'HTML' SQL Anywhere web services with dynamically generated queries that use EXECUTE IMMEDIATE code. In answering that question, I created a function called dynamic_sql_to_html that mimicked the output of the built-in TYPE 'HTML' services. The function takes a single query string as input, and returns a fully-formatted HTML page as output. The code for that function can be found here.

For this blog post, I have created a simplified version of the function called dynamic_sql_to_html_table that only returns the HTML code for the table definition, allowing it to be used more flexibly.

create function dynamic_sql_to_html_table(dynamic_sql_query long varchar)
returns long varchar
begin
 declare html_table_header long varchar;
 declare xml_query long varchar;
 declare temp_table_definition long varchar;
 declare temp_table_column_list long varchar;
 declare res long varchar;
 select
 XMLELEMENT(name tr, XMLATTRIBUTES('header' AS class), XMLAGG
(XMLELEMENT(name th, XMLCONCAT(XMLELEMENT(name small,
"base_table_name"), XMLELEMENT(name br), XMLELEMENT(name b,
"name"))))),
 'XMLAGG(XMLELEMENT(name tr, xmlconcat(' || list('XMLELEMENT(name
td, "' || "name" || '")') || ')) order by
sa_maintain_order_of_derived_table)',
 'declare local temporary table sa_foo
(sa_maintain_order_of_derived_table int default autoincrement, ' ||
list('"' || "name" || '" ' || "domain_name_with_size", ',') || ')',
 LIST('"' || "name" || '"', ',')
 into html_table_header, xml_query, temp_table_definition,
temp_table_column_list
 from sa_describe_query(dynamic_sql_query);
 execute immediate temp_table_definition;
 execute immediate 'insert into sa_foo(' || temp_table_column_list ||
') ' || dynamic_sql_query;
 execute immediate
 'select '''
|| html_table_header
|| ''' || '
|| xml_query
|| ' || '''' into res from sa_foo';
 return res;
exception
 when others then
 return 'Error! ' || ERRORMSG(SQLCODE);
end;

This function uses the sa_describe_query() function to parse the input query string and return a description of the result set. This information is used to create 4 strings (lines 9-23):

  • The temporary table definition that will hold the queries result
  • The list of columns in the temporary table that will be used by the INSERT INTO statement
  • The HTML code that will be used as the table header
  • The XML query that will be used to build the rows of the table

The procedure uses these generated strings to create a temporary table (line 24), insert the results of the query into that temporary table (line 25), and generate the HTML out of the temporary table (lines 26-32). It is necessary to use a temporary table in this case to guarantee that any ORDER BY directives in the original query will be preserved in the output HTML.

To show off the use of this function, we can create a simple browser-based iSQL application.

CREATE FUNCTION sp_isql(query long VARCHAR)
RETURNS long VARCHAR
BEGIN
  CALL sa_set_http_header('Content-type', 'text/html');
  RETURN '<html><head><title>Web iSQL</title></head><body><h2>Web iSQL</h2>
<form action="isql" method="get">
Enter a query:
<input size="50" type="text" name="query" value="'
|| query
|| '"/>
<input type="submit" value="Submit" />
</form><hr/>'
|| IF LENGTH (query) > 0 THEN '<code>'
|| query
|| '</code><br/><br/>'
|| dynamic_sql_to_html_table(query) ELSE '' END IF
|| '</body></html>';
END;
CREATE service isql
authorization off
USER dba
TYPE 'raw'
url elements
AS SELECT sp_isql(:query);

This function returns a simple HTML page containing a text field, and query results (if any). The dynamic query is passed as a GET variable called query.

Finally, point your browser to http://localhost:8080/isql (assuming you started your SQL Anywhere HTTP Server on port 8080), and tada…a simple browser-based isql, totally implemented inside SQL Anywhere, in less than 70 lines of code.

http://iablog.sybase.com/efarrar/wp-content/uploads/2009/09/web_isql.png

Of course, there are security implications of actually using this on a production server since it allows anyone to execute arbitrary statements. This demo was intended to show off the use of the dynamic_sql_to_html_table function, and the flexibility of HTML code generation within SQL Anywhere web services

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply