Using Google Web Toolkit to Access SQL Anywhere Web Services
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:
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.
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.
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
Page 2 of 3