What is AJAX?
What does 'Mobile' AJAX mean?
getElementById method used in most AJAX applications.
What is SQL Anywhere 10?
SQL Anywhere is a powerful enterprise caliber relational database which is available on Linux, Windows, UNIX, MacOSX, and Windows Mobile. SQL Anywhere is attractive for a Windows Mobile platform since it supports enterprise database features such as stored procedures, triggers, and database events. Although SQL Anywhere is larger (about 5.6 MB) than other "embedded" databases currently available for Windows Mobile, it has a greater subset of SQL support and an optimizer to streamline complicated queries running on Windows Mobile. SQL Anywhere is also binary compatible, allowing you to create a SQL Anywhere database on an operating system (such as Windows, in this article) and physically copy the database to another supported platform. In Windows Mobile environments, SQL Anywhere is typically involved in database synchronization. In this demo, it is used as a simple standalone database since this tutorial focuses on an AJAX technique of data access.
How can I use Mobile AJAX with SQL Anywhere 10?
AJAX can be used with a mobile SQL Anywhere database through SQL Anywhere's HTTP server. This sample shows the use of AJAX techniques to query a mobile SQL Anywhere database through Internet Explorer Mobile.
The following tools are required:
Part 1: Creating the AJAX Process
Creating a simple AJAX process requires three steps:
XMLHttpRequest object, send the server request, and receive the server response
The steps below are in respect to creating an AJAX process that acts on a SQL Anywhere database. The second part of this article explains setting up the sample database for a mobile environment and running the demo.
Step 1: Creating the HTML Page
The first step is to create an HTML form. This form will be used to send and receive the data needed for the server request. This example will use a
select form with twelve options, corresponding to the twelve names entered in the sample database. The
onchange property of the select will call
this.value refers to the current choice of the
onchange property or with a Submit button.
div element is created under the HTML form. This
div is given an ID so it can be accessed using the
innerHTML of this
div element will be used to display the result of selecting a name from the list. For information about the
innerHTML property, w3schools provides a brief tutorial.
The complete HTML is as follows:
Select a User:
<select name="users" onchange="showUser(this.value)">
<option value="1">Sally Dawson</option>
<option value="2">AJ Yussin</option>
<option value="3">George Erasmus</option>
<option value="4">Ali Day</option>
<option value="5">Katrine Joy</option>
<option value="6">Mustafa Ifa</option>
<option value="7">Chi Han</option>
<option value="8">Pierre Portal</option>
<option value="9">Jacqui Brean</option>
<option value="10">Tom Hoal</option>
<option value="11">Sarah Masid</option>
<option value="12">John Doe</option>
<div id="txtHint"><b>User information will be listed here.</b></div>
onchange property calls the
showUser() function. This function first creates an
XMLHttpResponse object. This is done by calling the function
} catch (e)
This function creates the appropriate object based on the browser being used. If the browser does not support AJAX, it will return
null. For more information about the
XMLHttpRequest object, see Wikipedia.
So far, the
showUser() function is as follows:
xmlHttp = GetXmlHttpObject()
alert ("Browser does not support HTTP Request")
XMLHttpRequest object can be created, it is stored as the
If the browser does support the HTTP
Request object, we will use the
str parameter passed in to create the URL for the server. The web service which will be written in Step 3 is called
get_user. It expects the
user_id parameter to be passed to it. To create the URL needed, we add the lines:
Before we call this URL, we must tell the object to wait for a response. To do this, we use:
which will call the
stateChanged() function whenever
readyState changes. For information about
readyState, see Wikipedia.
stateChanged() function is as follows:
if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
The logic for this function reads: "if the
readyState is 4 (server is done creating response), then change the
div whose ID is '
txtHint' to have the
innerHTML of the response generated".
Now that the object knows what to do when the server responds, it is time to call the web service
get_user with the supplied parameter. The code to do this is:
xmlHttp.open("GET", url, true);
open command will open the given URL. The
true parameter means the request is being made asynchronously. Since the parameter is in the URL, nothing needs to be sent.
Step 3: Creating the Necessary Web Service
The last step is to write the web service that deals with the server-side processing. To do this in a mobile environment, you must be connected to the database and be able to run SQL commands against the database (using, for example, Interactive SQL). If you don't know how to do this, it will be explained in the second segment of this paper.
Firstly, if the service exists, drop this service.
IF EXISTS(SELECT * FROM "SYS"."SYSWEBSERVICE"
DROP SERVICE "get_user";
SQL will give you an error if you try to create a service that already exists, so a little error handling can never hurt.
Next, create the
CREATE SERVICE get_user
AS SELECT 'Age: ', Age,
'Gender: ', Gender,
FROM ajax_demo WHERE id = :user_id;
This service is of type
RAW, which means it returns exactly what the
select statement dictates. This
select statement includes text in it to format the result; ajax_demo is the name of the table in the sample database. Since inserting HTML tags into a
select statement can only provide simple formatting options, alternative formatting options will soon be addressed.
The web service is now ready for use.
These three steps render a simple, functioning AJAX example that works with a SQL Anywhere database. Part two will explain setting up this process in your mobile device.
Aside: Formatting Options
Because of the limitations of mobile devices, formatting the results of your AJAX request becomes important. Although this issue is not AJAX specific, the
responseText of an
XMLHttpRequest object provides for a few AJAX-oriented options. Two common alternatives to placing HTML tags in your
select statements are:
- Making a web service of type
RAW and then using procedures to format the result. The sample database used a simple
select statement for formatting. More complex formatting can be done by calling procedures that return long varchars representing HTML documents or JSON objects. For documentation of HTML documents, see the SQL Anywhere 10 Documentation.
Part 2: Setting up the Process on Your Mobile Device
This section gives step-by-step instructions for setting up the sample AJAX code to run on a mobile device. The sample provided extends the code developed in part one, but in an understandable way.
Sample Files (in the Zip file):
The following procedure assumes you have downloaded your supplemental code to the C:\temp directory.
Setup your Windows CE device and the sample database:
- Connect the Windows CE device to your computer using ActiveSync.
- Create the sample database. At a Windows command prompt, execute:
C:\temp> dbinit –s –i –z UTF8BIN –zn UTF8BIN demotable.db
where the command line options used are:
- -s: add checksum to the database pages
- -i: do not install jConnect support
- -z UTF8BIN: specify UTF8BIN as the collation sequence for the
CHAR data type
- - zn UTF8BIN: specify UTF8BIN as the collation sequence for the
NCHAR data type
- Start the database engine. At a Windows command prompt, execute:
C:\temp> dbeng10 demotable.db
- Create the schema, and insert data into the sample database. At a Windows command prompt, execute:
C:\temp> dbisql -c "uid=dba;pwd=sql;eng=demotable" ajax_demo.sql
- Create the webservices for the remote database. At a Windows command prompt, execute:
C:\temp> dbisql -c "uid=dba;pwd=sql;eng=demotable" webservices.sql
- Stop the database engine. At a Windows command prompt, execute:
C:\temp> dbstop -c "uid=dba;pwd=sql;eng=demotable"
Copy these files onto your Windows CE device:
- Copy demotable.db, the database you just created into the My Documents folder of your Windows CE device.
- Copy example.html to the My Documents directory of your Windows CE device.
- Disconnect your Windows CE device from your computer.
Start the database server on the Windows CE Device:
- On your mobile device, navigate to File Explorer: Start > Programs > File Explorer
- Navigate to dbsrv10.exe: My Device > Program Files > SQLAny10 > dbsrv10.
- Tap dbsrv10. The server startup options will appear. Choose:
- database: "My Documents\demotable.db"
- server name: "CEserver"
Seeing the finished AJAX example:
- Browse to http://localhost:8080/example.html.
- Select a name from the first drop down menu. A list of personal information will appear in the space that states "User information will be listed here".
- Choose a minimum and maximum score (between 0 and 20). Click "Query Database". A list will appear, in the space that states "Your query result will display here", of all users whose score is between the values indicated.
- In the examples provided, the
- Although an XML response text allows for richer formatting options, it will slow the application since more processing must be done on both server and client sides.
- When running an
XMLHttpRequestObj.open, appending "&sid="+Math.random();" to the URL will ensure the request is run again. Otherwise, a cached page may be opened.
- Since submitting an AJAX request does not refresh the entire page, there is much inconsistency with the effect of using the back button on a browser. The most popular solution to this is the use of an invisible IFrame which stores request history.
Since Internet Explorer Mobile on Windows Mobile 6 devices now contains sufficient AJAX support, AJAX-driven applications and sites are becoming more common. Using this technology with a SQL Anywhere 10 HTTP server on a remote device offers a more seamless interface for browsing data.