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:xwww' 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:xwww'
+ 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
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.
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
- Source code used in the article
- Google Web Toolkit: HTTP://code.google.com/webtoolkit/
- SQL Anywhere: http://www.ianywhere.com/products/sql_anywhere.html
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.
