November 28, 2014
Hot Topics:

Using Google Web Toolkit to Access SQL Anywhere Web Services

  • December 14, 2006
  • By Rob Close
  • Send Email »
  • More Articles »

SQL Anywhere is well known as a database server, but it is more than that. What is less known is that SQL Anywhere Server contains an HTTP server that can serve database query result sets as web pages or through Web Services. Google Web Toolkit (GWT) is a Java software development framework that makes writing AJAX applications easier for developers. It helps eliminate the differences between browsers. It allows Java developers to work in a familiar environment, with tools they already know. GWT includes a compiler that converts your Java classes to browser-compliant JavaScript and HTML.

The Google Web Toolkit is fun and easy to use, and it works well with SQL Anywhere. Over the next few paragraphs, I will show you how to get SQL Anywhere setup with its HTTP server and get started with Google Web Toolkit, including deploying a web application to SQL Anywhere. Then, I will show you what you need to create a web application to monitor connections to your SQL Anywhere server. I will not be covering security or error handling in SQL Anywhere.

You don't need to have any experience with SQL Anywhere or GWT, but you will need to have Java and SQL knowledge to get the most out of this document.

Get the Software

Go to the GWT web site and follow the download link. Build 1.1.10 was the newest version available at the time of writing, and that is the version I worked with.

I used version 10 of SQL Anywhere, but you also can use version 9. You will need at least version 10.0.0 build 2719 or later, or 9.0.2 build 3385 or later. Earlier versions had a bug that prevented GWT from working. Updates can be downloaded from the SQL Anywhere developer site. Follow the EBF's link. If you don't have a copy of SQL Anywhere, you can download a free evaluation copy from the SQL Anywhere web site.

Finally, you need Sun's Java 2 Runtime Environment 1.4.2 or newer. You can download a JRE from Sun's web site. I used the latest version of Java 5. You will also need an editor to edit Java source.

Create an Application with GWT

At the end of this exercise, I want to have a web application that looks and behaves like the connections portion of DBConsole. DBConsole is an application that monitors SQL Anywhere. It is included with SQL Anywhere.

GWT comes with a tool to generate a shell application for you. The tool is called applicationCreator and is in the top-level directory for the version of GWT you installed. You will need to add that directory to your path, or include the path to applicationCreator when you run it. Start by making a directory for your project. I put mine in w:x, and you will see that throughout this document. You will need to replace any references to w:x with your project's directory. When you run applicationCreator, you need to give it the qualified name of your class. Your package name must be followed by ".client". I put my application in a package named com.iAnywhere, and my application is named Console.

Creating an application

Creating the application also creates scripts for building the application and running the application in hosted mode.

Build the Application

The default compile obfuscates the generated Java script. To make the generated JavaScript easier to read, you can edit Console-compile.cmd, adding "-style DETAILED" to the command line before "- out".

To build the application, run Console-compile.cmd at the command line.

Run the Application in Hosted Mode

In hosted mode, the application will be run in the GWT shell, which will launch the GWT browser to run your application. To run the newly generated and built application in hosted mode, run Console-shell.cmd at the command line. The two following dialogs will appear.

The GWT shell



Click here for a larger image.

A newly generated application running in Google's browser

Create a database and start SQL Anywhere Server

A database is needed to store services and stored procedures that will be used by the web application, plus any data you want to store in it. You can use an existing SQL Anywhere database or you can create a new one. To create the database, run dbinit console.db at the command line. That will create a file called console.db that will be initialized as a SQL Anywhere database. The database file will be created in the directory dbinit is run in, unless you qualify the database file name with a directory path. I put my database in the root of my project directory, w:x.

To start the server, enter the following at the command line:

dbsrv10 -xs HTTP(port=80) console.db

This will start the database server. Database applications will be able to connect the database, assuming they have the correct authentication, just like they can to any other database served by SQL Anywhere. The .xs option tells SQL Anywhere to listen for web protocols. In this case, they are HTTP requests on port 80.

SQL Anywhere needs to have a web service created to respond to a particular request. For example, if you created a web service named foo, you would access it with this URL:

HTTP://localhost/foo

That doesn't work very well for the case where pages are being generated. GWT generates many files. Some of those files have generated names that get regenerated every time you rebuild your project with Console-compile.cmd. By default, SQL Anywhere expects you to have a service for every file requested, but there is a workaround to that problem. You create a web service named "root". The root service will handle all requests that do not match a service.

Here is the source for my root service:

CREATE SERVICE "root" TYPE 'RAW'
AUTHORIZATION OFF USER "DBA" URL ON
AS call RootPage(:url);

This service is very simple. It disables authentication and uses DBA authority for all calls it makes. It collects the calling URL and passes it on to a stored procedure named RootPage. RootPage does the work of dispatching the request.

RootPage uses the URL prefixed by the directory where GWT generates its output to read the file and return it to the requestor. It looks at the file extension for each file and sets the appropriate content type. Note that I used 'w:\x\www\' here. The 'www' directory is where Console-compile writes the generated files by default. You will need to change that to the location where you are building your project.

CREATE PROCEDURE "DBA"."RootPage"( url_part varchar(250) )
RESULT( HTML_doc XML )
BEGIN
   declare root_page long varchar;
   set root_page = xp_read_file( 'w:\x\www\'
                 + HTTP_decode( url_part ) );

   IF COMPARE( '.html', LOWER( RIGHT( url_part, 5 ) ) ) = 0 OR
      COMPARE( '.htm', LOWER( RIGHT( url_part, 4 ) ) ) = 0 THEN
      CALL dbo.sa_set_http_header( 'Content-Type', 'text/HTML' );
   ELSEIF COMPARE( '.js', LOWER( RIGHT( url_part, 3 ) ) ) = 0 THEN
      CALL dbo.sa_set_http_header( 'Content-Type',
                                   'text/javascript' );
   ELSEIF COMPARE( '.css', LOWER( RIGHT( url_part, 4 ) ) ) = 0 THEN
      CALL dbo.sa_set_http_header( 'Content-Type', 'text/css' );
   ELSEIF COMPARE( '.xml', LOWER( RIGHT( url_part, 4 ) ) ) = 0 THEN
      CALL dbo.sa_set_http_header( 'Content-Type', 'text/xml' );
   ELSEIF COMPARE( '.gif', LOWER( RIGHT( url_part, 4 ) ) ) = 0 THEN
      CALL dbo.sa_set_http_header( 'Content-Type', 'image/gif' );
   ELSEIF COMPARE( '.jpe', LOWER( RIGHT( url_part, 4 ) ) ) = 0 OR
      COMPARE( '.jpeg', LOWER( RIGHT( url_part, 5 ) ) ) = 0 OR
      COMPARE( '.jpg', LOWER( RIGHT( url_part, 4 ) ) ) = 0 THEN
      CALL dbo.sa_set_http_header( 'Content-Type', 'image/jpeg' );
   END IF;
   select "root_page";
END;

Web services and stored procedures can be created and maintained with DBISQL or Sybase Central. DBISQL and Sybase Central are tools that are included with SQL Anywhere. I found Sybase Central more efficient for this work.

On Windows, DBISQL and Sybase Central can be launched from the Start menu. When they start up, you need to connect to the newly created database. For all new databases, the default user ID is "dba", and the default password is "sql". If there are other SQL Anywhere databases already being served, you also will have to give the name of the database. In this case, use "console".





Page 1 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel