Using SQL Anywhere Database with a Web Services Server

This article explains how to create a web services server that works with the SQL Anywhere database. The entire process, from creating the database through verifying installation of the web service, will be described.

Introduction

In Part 1, installation of SQL Anywhere Studio 9.0 and using the Sybase Central; Interactive SQL; and details about database objects were presented. In Part 2, creating database projects with SQL Anywhere Studio was described in detail, with examples of Excel, VB, and ADO.NET applications. In this tutorial, the creation of a web services server that works with the SQLAnywhere database will be described. A very simple example to verify the web services server will be shown. 

Creating a SQLAnywhere database

SQL Anywhere 9.0 is installed at the following location on my computer: C:Program FilesSybaseSQL Anywhere 9win32, which has all the executable files. A database can be created by executing the command -dbinit.exe. The dbinit.exe is executed from the command line (Start –>Run–>change to the above directory) as shown below. Make sure your Asademo 9 server is running before you execute the command.  At the command prompt type dbinit mysorian.db. The name of the database created is mysorian.db. The collation default is set to 1252 Latin collation. During the execution, the system tables are created, and the deployment option for Ultralite is also set.

 

This creates a new database, mysorian.db. In the following step, a web services server will be installed which works with the Sybase Central User interface.

{mospagebreak title=Creating a web services server to work with the database}

In this step, using appropriate command line arguments, a web services server will be created to work with the above database. Type in the following command: dbeng9 -xs http(port:8082) mysorian.db at the command prompt as shown. Since port 80 is already being used by the IIS server, port 8082 (or a different one of your choice) was used. The HTTP listener will be at Port 8082.

This will log all the information related to mysorian server as shown in this screen. Keep this screen up and it stays minimized on the desktop with the title icon showing.

Now bring up Sybase Central, and click on Connect, accessible from the menu item Tools. This brings up the following screen, where you choose the option to connect to Adaptive Server Anywhere 9.

 

When you click OK you will get the following screen, where you enter the authentication information as shown. The User ID is dba and the Password (case sensitive) is sql.

 

Now click on the tab Database and click on the button Find…to search for the database name. The program searches all the available servers and then you will be asked to choose from the servers to connect to as shown:

Now click on mysorian and click OK.  You will get the following screen, where you need to find the database file. If you look back and review the log created while installing, you will see the database file is located at C:Program filesSybaseSQL Anywhere 9win32mysorian.db. Now use the browse button to locate this file as shown.

When you click OK, you will see that mysorian is added to Sybase Central‘s list of server nodes as shown. Currently this database is quite empty except for the system tables as discussed earlier.

Now using Interactive SQL, a utility that comes with the installation, you create database objects. You can also use the built-in utility to import into SQLAnywhere the data of your choice, or by using other third party products, database objects can be imported.

{mospagebreak title=Verifying Installation}

The web services server (mysorian) hosts the web service. If the web server responds to HTTP requests, it is a sure test that it is installed properly. The verification consists of the following steps:

1. Create a stored procedure. It does not have to be data based.

2. Create a Web Service which calls this stored procedure.

3. Display the web service in a browser.

Creating a stored Procedure

Right clicking the database invokes the Interactive SQL window where you code the stored procedure.

The following stored procedure was coded. The return from this code is a HTML document. If the content type is not set to ‘text/html’, then your HTML will look screwed up. dbo_sa_set_http_header (‘content-type’, ‘text/html’) is a stored procedure only meaningful while responding to HTTP requests. If you were to test it, as it is in Interactive SQL, it would return an error.

Create Procedure pretty2()
Result (html_doc long varchar)
Begin
Call dbo_sa_set_http_header ('Content-type', 'text/html');
Select '<p><font color="red">Welcome to Database Programming</font></p>n';
End

{mospagebreak title=Creating a Web Service}

The Interactive SQL window can be used to create a Web Service. This service, called RedCarpet, is coded as follows. For the present do not worry about the type parameter. You can see that the authorization is taken off. The Web Service is simply accessed by calling the stored procedure.

In my Sybase Central, I have several services, one of them being RedCarpet. If you follow the list columns you will see a column heading related to URL.

In the above picture the URL Path is set to ‘ON’. You do this by double clicking the Web Service in the Web Services tab, which brings up the next screen. In this screen, you can set the URL path as required. If the URL path is off you cannot access the service via URL.

When you have the URL path set to ‘ON’, you can access the Web Service using the URL as shown. This also verifies that the web Services server is responding to http requests. Note the port used for accessing the service and the syntax.

 

Summary

SQL Anywhere has a very cool interface for creating data related web services. The web server is very easy to install and work with. From USENET postings, I understand that entire websites have been created using the Web Services Server alone. Creating a web service is very easy, especially on the developer’s edition that is used in this tutorial. This tutorial was tested on Windows XP Professional platform with the SQL Anywhere version 9.0.2.2451.

2 thoughts on “Using SQL Anywhere Database with a Web Services Server

  1. Web services, SOA will be buzzing for years to come. Soon there will come a time when interoperability will be laid to rest. Couple of years ago I tried a vb.net client to call a web service created on Sun Studio software. I had a good time arguing about this because they did mot work. I blaned it on DOM and SAX. I still have problems with the interoprability between .Net client and a SQL Anywhere service. One of these days I will find out why.

    Read my articles on ColdFusion/.NET web services. They are interoperable for the simple examples I tried. Will they work for more complex data types?

    Thanks for reading the articles and I will be happy to answer your questions.

  2. Hi,
    It is wonderful post for learning the usenet. This post gives the amazing knowledge of the usenet with my sql and at linux. I would like to say thanks for this nice information….:)

[gp-comments width="770" linklove="off" ]