Web enabling data on a SQL Anywhere Server using a native web service

Introduction

Exposing server data to intranet / internet, an attractive and much needed method has become possible due to the native web services, services that can be created on the database itself to which HTTP or Soap requests can be made. Microsoft Access in some of its versions provided this dynamic capability via Data Access Pages and this became possible from Microsoft SQL Server directly, only in its 2005 version. However MS Access 2007 does not support Data Access Pages instead recommends the use of SharePoint server. SQL Anywhere server, even in version 9.0 possessed this built-in capability.

SQL Anywhere Server 10

The graphical management tools, Interactive SQL and Sybase Central, were introduced in a previous article describing the .NET Framework integration. Additionally the Hodentek blog link provides several related articles. Extensive documentation is also available at SQL Anywhere’s home page. You may download an evaluation version of the software and take it for a test drive. The evaluation version includes Sybase Central, a one stop graphical database management interface to the database and its various supporting applications. We will be using Sybase Central for several of the tasks in this tutorial.

Creating a project directory

To keep the tutorial files isolated, we are going to create a directory to store our database files. Open a prompt and create an HTTPWebServerDemo directory using the listing below.

C:\> mkdir C:\HTTPWebServerDemo
C:\> cd C:\HTTPWebServerDemo

Starting the database server

SQL Anywhere 10 includes a sample database called demo.db. Because it already contains tables with data, we will use it to create our web services.

Since we do not want to modify our demo.db file directly (in case we want to use it for other demos), we will make a copy of it into our HTTPWebServerDemo directory. The demo.db file is installed in the SQL Anywhere 10 samples directory. An environment variable, SQLANYSAMP10, points to this directory. This variable as created when you installed SQL Anywhere 10.

C:\HTTPWebServerDemo> copy “%SQLANYSAMP10%”\demo.db
C:\HTTPWebServerDemo> dbsrv10 –n SAWebServer demo.db –n demo

To follow best practices, we will give both the database server and the database a name. We want to start a SQL Anywhere 10 network sever named SAWebServer. Inside of our server we will start a database named demo loaded from the file demo.db that we just copied. The database server can be started by executing the dbsrv10 command as shown in the next listing. Later we will see how it can be started to accept web requests.

C:\HTTPWebServerDemo> dbsrv10 –n SAWebServer demo.db –n demo

Once the network server has started successfully, a small icon of the server (see Figure 22) will be placed in the control tray of the desktop. You can right click and restore the window as shown in the next figure.

We now want to connect to our database server using Sybase Central. Sybase Central can be started at Start --> All Programs -->SQL Anywhere 10 -->Sybase Central. You can now connect to this database by choosing Connections --> Connect with SQL Anywhere 10 from the menu. You must login using the default login for a new database (username: dba; password: sql).

Now click on the tab Database which opens the Database tabbed page as shown in the next figure.

To connect to the server you need a server name. You can type in sawebserver for the server name, or alternatively click on the Find… button which pops-up the Connect dialog. While this dialog is shown, Sybase Central is attempting to locate all the servers in its network. When the search finishes the results will be shown in the Find Servers window shown below.

Select sawebserver and click on OK.

We can also specify our database name (demo) and database file (demo.db). However, because we have only one database running in the server (sawebserver) it will automatically defaults to this server. To test if we can connect to the server, click on the Tools button in the bottom left, and choose Test Connection.

Click on OK in the Test Connection window and the Connect window.

The Connect window will now show the database objects as shown in the next figure.

To view the tables in this database, click Tables in the main pane. The tables in the sample database are shown in the next figure .

Creating a URL accessed Web Service

Most web services will call stored procedures. To do this, you much first create a stored procedure and then you create a web service that calls this procedure. The result from this web service is can be displayed as an HTML page.

Creating a Stored Procedure

For the purposes of this tutorial we will be creating a simple stored procedure that displays the rows in the Customers table. The Interactive SQL tool discussed earlier can be used to create this stored procedure. Alternatively you could use the Create Stored Procedure Wizard which guides you through the process for creating the stored procedure.

For this example we will be creating a stored procedure using the Interactive SQL tool. You can bring up this tool by clicking on the Tools menu item as shown in the next figure.

This opens the Interactive SQL window. You will need to provide the login information (username: dba; password: sql) in the same way as when we connected with Sybase Central. The Tools menu on this window provides access to several useful items like Table Name and Stored Procedure Name lookups; Index Consultant, etc.

The stored procedure TestProc used in this tutorial is typed directly into the SQL Statements window as shown.

This is a simple stored procedure that displays all the columns from the GROUPO.Customers table.

Creating the web service

Again there are two ways you can create a web service using the Sybase Central Management Console. In the window of Figure 7 when you double-click the Web Services list item on the right you open up the window for web services. There are no services listed as yet. You will create a new Web service which calls the TestProc stored procedure you created earlier. Click on Create Web Service in the Web Service Design Tasks on the left.

This opens the wizard and steps you through creating a web service as shown. You will need to provide the name for this service. Herein it is called TestHtmlSvc.

Click on the Next button. This opens the page which allows you to choose the type of web service you want to create. There are several types you can create. Quoting from the program windows the various types are: Raw: The result set of the SQL statement or procedure is sent to the client without any additional formatting.

XML: The result set of the SQL statement or procedure is assumed to be XML; if it is not, then the result set is converted to XML RAW format.

HTML: The result set of the SQL statement or procedure is formatted as an HTML document with a table containing the rows and columns.

SOAP: The request must be a valid SOAP (Simple Object Access Protocol) request, and the result set is formatted as a SOAP response.

DISH: A DISH service acts as a proxy for a group of SOAP services and generates a WSDL (Web Services Description Language) file for each of its SOAP services.

In this example we will be choosing a HTML type that would display the result set in a HTML document.

Click on the Next button after highlighting HTML in the above window. This will display the authorization for accessing this service. For this example, as seen in the figure, no authorization has been chosen. Since no authorization is required, we need to specify with what authorization level this will be run at. We will use DBA in this case.

Click on the Next button. This brings up the security related page. The default option (no security) will be chosen. However for internet users this may not be the proper option.

Click on the Next button. This brings up the wizard window wherein you specify a statement which is usually a call to a stored procedure name as shown in the next figure.

Click on the Next button. This brings up the window shown in the next figure wherein you define how the URI access is processed. Again there are a couple of options. Since this stored procedure has no parameters the default option is chosen.

Click on the Next button. This brings up the final screen where you may insert a comment for this service as shown.

Click on the button Finish. This creates the service TestHtmlSvc with all the options chosen as shown.

After configuring the service you may also interact with this service in Sybase Central. To modify the Authorization, Security, etc. Click on the field you want to modify and choose the required item from the drop-down list.

Although we have created our web service and stored procedure, we did not start the server with the HTTP server started. Before we can start our server enabled for web access, we must disconnect from Sybase Central and shut down our database server.

Before we can stop the server, we should disconnect all applications that are connected to it. We only have one application that is connected (Sybase Central). To disconnect, choose Connections->Disconnect.

To stop the server, right-click on the SQL Anywhere icon in the system tray and from the pop-up menu choose Restore. Once the server window appears (Figure 1), hit the Shutdown button in the bottom right. This will stop our database server.

Enabling the server for web access

The listing below shows the syntax for starting the database server with the HTTP server.

Listing 4

{dbeng10 | dbsrv10} -xs {none | web-protocols}... web-protocols : { http [ ( parm=value;...) ] | https ( [ FIPS={ ON | OFF }; ]parmlist ) } , ...

For this example, start the server to listen to HTTP request using the command in the listing below.

Listing 5

C:\HTTPWebServerDemo> dbsrv10 –n SAWebServer demo.db –n demo -xs http(port=8082)

This will start the database server and enable its HTTP server feature. The HTTP server will be listening on port 8082. If not port was specified, it would use the default value of port 80.

Displaying result set from Web Service

With the access port known you just need to type in the following in the URL address of IE Browser for accessing this service.

The result will be displayed as shown in the next figure.

Creating the web service directly using Interactive SQL

While the wizard is a nice interface to create the web service it is also possible to create the service directly using the Interactive SQL tool. Type the CREATE SERVICE statement directly into the SQL Statement page of the Interactive SQL and execute the statement as shown.

This adds this web service EmployeeListHtml to the list of web services as shown in the next figure. The select list is similar to the one used in the TestHtmlSvc.

This can be accessed from the browser using the same address except for the name of the service. A similar HTML page will be displayed when this URL is browsed.

Summary and Conclusions

This tutorial described how the data in the SQL Anywhere server can be accessed by making a HTTP request to a web service. Creating the web service directly using the Interactive SQL was also described. Both Interactive SQL as well as the wizard driven process makes it very easy to web-enable data on the server. This should be very attractive to companies who want to host data on the intranet as well as reap benefits of a Database server with such excellent support for going ‘mobile’. This should also be useful to those who migrated from MS Access to retain the functionality of Data Access Pages by web enabling their data using the native web services described in this article.