http://www.developer.com/

Back to article

Using Google Web Toolkit to Access SQL Anywhere Web Services


December 14, 2006

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".

Deploying the Web Application

Now that SQL Anywhere has been set up as a web server, and it is looking in the directory where GWT is generating the application, the application will be deployed every time the project is compiled. If you have made changes to your application and the changes are not showing up when run in a browser, delete the contents of the www directory and build the application again. GWT is careful not to overwrite files that are already there.

To test this, compile the application and then go to your favorite web browser and enter the URL for the generated web page:

HTTP://localhost/com.iAnywhere.Console/Console.html



Click here for a larger image.



Click here for a larger image.

The application after clicking the button.

You have now created a working application that can be served by SQL Anywhere.

Create the Console Application

Now, create a browser-based application that behaves similarly to the connection viewer portion of the DBConsole application that is included with SQL Anywhere.

DBConsole

The connection viewer in DBConsole allows the user to pick from a list of database connection properties to display, set a refresh rate for updating the data, and then it displays that information to the user. DBConsole is configured through its options dialog.

DBConsole options dialog

To do the same thing, you need to set up a web service and a stored procedure to collect the connection data for display. A web service and stored procedure also are required to get the list of connection properties. You also will need to update the Google Web Toolkit project to display the results from the server.

To get started, edit Console.html Remember to edit the one in the src directory, not the one in the directory you deploy to (by default, the www directory). Otherwise, if you rebuild everything from scratch, you won't get the right HTML file. The user interface for the new application will be based solely on the JavaScript that GWT generates for you. No HTML elements are required. Remove the following from Console.html.

HTML to be deleted from Console.html

GWT generated "Wrapper HTML for Console" as the title. Change the title to "Console". GWT graphical components—for example, buttons and menus—can be customized with style sheets. Many of those components are very plain without style sheet customization. GWT's documentation for each component includes a list of style sheet properties for the component. The style sheet is too long to list here. Look in Console.html to see what I did.

Everyone has a different list of connection properties that are important to them. Start by getting a list of connection properties so that your users can customize what data they view.

Create the service:

CREATE SERVICE "get_connection_properties" TYPE 'XML'
AUTHORIZATION OFF USER "DBA" AS call getConnectionPropeties();

And the stored procedure:

CREATE PROCEDURE "DBA"."getConnectionPropeties"()
RESULT (
   property_name long varchar,
   property_description long varchar,
   property_is_numeric long varchar,
   property_is_cumulative long varchar
)
begin
   select property_name( row_num ) as prop,
      property_description( row_num ),
      property_is_numeric( row_num ),
      property_is_cumulative( row_num )
   from  sa_rowgenerator(0,500)
   where connection_property( prop ) is not null
   order by prop
end

To display the list in the browser, you also need to update the Console class generated by GWT. I added a class called ConnectionOptionsDialog. There is too much code to list here. See the included source files.

Now, you need to be able to get connection data for an arbitrary list of connection properties.

Create a service to get connection data:

CREATE SERVICE "connections" TYPE 'RAW' AUTHORIZATION OFF
USER "DBA" AS call connections(:properties);

And the stored procedure:

CREATE PROCEDURE "DBA"."connections"( IN properties LONG VARCHAR )
BEGIN
   DECLARE pos INT;
   DECLARE start_pos INT;
   DECLARE property LONG VARCHAR;
   DECLARE temp_table LONG VARCHAR;
   DECLARE insert_into LONG VARCHAR;
   DECLARE counter INT;
   DECLARE curr_cid INT;
   DECLARE cid INT;
   DECLARE return_value LONG VARCHAR;
   DECLARE table_heading LONG VARCHAR;
   DECLARE insert_html long varchar;
   DECLARE cols LONG VARCHAR;
   declare local temporary table t_html(
      ord INT,
      str LONG VARCHAR
   ) in SYSTEM not transactional;

   SET temp_table = 'declare local temporary table t_conn_webconsole(';
   SET insert_into = 'insert into t_conn_webconsole values (';
   SET table_heading = '<TR class="header">';
   SET cols = '';
   SET insert_html = 'INSERT INTO t_html SELECT 1, ''<TR>'' ||';

   SET pos = 1;
   SET start_pos = 0;
   SET counter = 0;

   WHILE pos > 0 LOOP
      SET pos = LOCATE( properties, ',', start_pos );
      IF pos = 0 THEN
         SET property = SUBSTR( properties, start_pos,
                        LENGTH( properties ) - start_pos );
      ELSE
         SET property = SUBSTR( properties, start_pos,
                                pos - start_pos );
      END IF;

      IF counter > 0 THEN
         SET temp_table = temp_table || ', ';
         SET insert_into = insert_into || ', ';
         SET cols = cols || ' || ';
         SET insert_html = insert_html || ' || ';
      END IF;

      SET temp_table = temp_table || ' "' || property ||
                       '" varchar(255)';
      SET cols = cols || ' "' || property || '"';
      SET insert_into = insert_into ||
         ' connection_property( ''' || property || ''' , cid )';
      SET table_heading = table_heading || '<TH>'
         || property_description( property ) || '</TH>';
      SET insert_html = insert_html || '''<TD>'' ||"'
         || property || '" || ''</TD>''';

      SET start_pos = pos + 1;
      SET counter = counter + 1;
   END LOOP;

   SET temp_table = temp_table || ' ) in SYSTEM not transactional;';
   SET insert_into = insert_into || ');';
   SET table_heading = table_heading || '</TR>';
   SET insert_html = insert_html || ' || ''</TR>'
      ' FROM t_conn_webconsole';

   EXECUTE IMMEDIATE temp_table;
   set curr_cid = connection_property( 'number' );
   set cid = next_connection( NULL, NULL );
   loop_label:
   loop
      if cid is NULL then
         leave loop_label
      end if;
      if cid <> curr_cid then
         EXECUTE IMMEDIATE insert_into;
      end if;
      set cid = next_connection( cid, NULL );
   end loop loop_label;

   INSERT INTO t_html VALUES (
      0,
      '<HTML><BODY><TABLE BORDER=1 CELLSPACING=1
                       CELLPADDING=0 COLS=' || counter || '>'
                       || table_heading
   );
   EXECUTE IMMEDIATE insert_html;

   INSERT INTO t_html VALUES (
      99999,
      '</TABLE></BODY></HTML>'
   );

   SELECT str FROM t_html ORDER BY ord;
END

This application refreshes its data on a regular interval. A timer is used to regulate the collection of data. The timer needs to start when the application is loaded into the browser's document and stop when the application is unloaded. This block of code demonstrated how I did that:

public void onLoad() {
   _timer = new Timer() {
      public void run() {
         boolean success =
            HTTPRequest.asyncGet( _hostName +
            "connections?properties="+_connectionProperties,
            new ResponseTextHandler() {
            public void onCompletion( String responseText ) {
               _content.setHTML( responseText );
            }
         });
         if( !success ) {
            _timer.cancel();
            // Report problem to user.
            Window.alert( "Error getting connection data." );
         }
      }
   };
   _timer.scheduleRepeating( _refreshRate );
}

public void onDetach() {
   _timer.cancel();
}

Users of the web application don't want to have to set which properties they view every time the application starts up. Settings can be persisted with cookies. GWT has a Cookie class with static methods for setting and getting cookie values. When testing applications that are being served from your own machine, you may have trouble with cookies. Cookies may not work for localhost. If cookies don't work for you with localhost, and your computer is in a domain, try using your computer's fully qualified name instead of localhost.

Here is the completed web application.



Click here for a larger image.

The completed web application.



Click here for a larger image.

The completed web application showing options menu.



Click here for a larger image.

The options dialog for the web application.

If you have ever written JavaScript targeted at multiple browsers, you will know that it can be tricky having everything work as expected in every browser. To get around that problem, GWT generates scripts for several popular browsers. The generated code loads the right script for the browser as it is being loaded. GWT does a nice job of lowering the bar for Java programmers faced with the task of deploying a web application to multiple browsers.

Once you know the steps, getting Google Web Toolkit working with SQL Anywhere is a straightforward process. I don't consider this sample application finished, but I hope it gives you a good start at creating your own applications.

Resources

About the Author

Rob Close is a senior developer on the SQL Anywhere engineering team. He is one of the team's Java gurus working on the administration tools for SQL Anywhere. When Rob is not working on the next version of SQL Anywhere or writing technical articles, he can be found tending to his honey farm in southern Ontario, Canada.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date