Creating a Mobile AJAX Process Using SQL Anywhere Web Services

• Download demo - 3.32 KB

What is AJAX?

AJAX, Asynchronous JavaScript and XML, is a technique for web development that aims to enable interactive web applications. Popularized through well-known web applications as Google Suggest, Google Maps, Flickr, and Odeo.com, AJAX does not require the entire web page to be reloaded each time the user requests a change. AJAX is not a new programming language, but rather a relatively new technique for using the existing JavaScript, HTML, XML, and CSS standards. AJAX techniques provide the opportunity to create rich, user-friendly websites.

What does 'Mobile' AJAX mean?

In the most broad sense, 'Mobile' AJAX is meant to encapsulate the ability of Mobile browsers to display sites that use AJAX techniques. Although Mobile AJAX has been supported by the Mozilla and Opera mobile browsers for a longer period of time, the release of Windows Mobile 6 offers new prospects in Mobile AJAX. The updated version of Microsoft's popular Internet Explorer Mobile supports the XML DOM and JavaScript needed to run basic AJAX applications. Most notably, Internet Explorer for Windows Mobile 6 supports the 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.

This sample is divided into two parts. In Part 1, "Creating an AJAX Process", the source code for the sample is developed. This segment explains the basic AJAX technique employed, and explains the HTML, JavaScript, and SQL (web service) code. Besides explaining the sample code, this segment also explores alternative methods for formatting the server response. Although this sample will run in non-mobile environments, a non-mobile environment supports other AJAX functionality that is not covered. Part 2, "Setting Up the Process on Your CE Device", explains using this process with a mobile device and SQL Anywhere's HTTP server. This section gives a simple way to set up the sample to run in an offline, mobile environment.

Requirements

The following tools are required:

• SQL Anywhere 10 Developer Edition (please choose to install SQL Anywhere CE components).
• Windows Mobile 6 Device or Visual Studio .NET 2005 and the Windows Mobile 6 Professional and Standard SDK Refresh
• ActiveSync

A basic understanding of HTML and JavaScript is recommended, but not necessary, to understand Part 1. Being able to connect to a mobile device through ActiveSync is necessary for Part 2. After installing SQL Anywhere 10 and connecting to your mobile device or device emulator through ActiveSync, choose Start > Programs > SQL Anywhere 10 > Deploy SQL Anywhere for Windows Mobile.

Part 1: Creating the AJAX Process

Creating a simple AJAX process requires three steps:

1. creating the core HTML which will call the JavaScript function
2. writing the JavaScript which will create the XMLHttpRequest object, send the server request, and receive the server response
3. writing the server-side code (in this case, web services) that will respond to JavaScript calls

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 showUser(this.value).

• this.value refers to the current choice of the select
• showUser() is a JavaScript function that will be written in Step 2

In general, any form style can be used as long as it appropriately calls a JavaScript function that passes in parameters. This call is made typically by either the onchange property or with a Submit button.

Next, a div element is created under the HTML form. This div is given an ID so it can be accessed using the document.getElementById() function through JavaScript. 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:

<html>
    <head>
      <script language="javascript" type="text/javascript"> 
     <!-- JavaScript will go here --> 
      </script>
   </head>
   <body>
      <form name='select'>
      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>
         </select>
      </form> 
      <p>
      <div id="txtHint"><b>User information will be listed here.</b></div>
      </p>
   </body>
</html>

Step 2: Writing the Associated JavaScript Functions

The JavaScript code is put between the <script></script> tags.

The form's onchange property calls the showUser() function. This function first creates an XMLHttpResponse object. This is done by calling the function GetXmlHttpObject():

function GetXmlHttpObject()    
{
    var xmlHttp=null;

    try
    {     
        // Firefox, Opera 8.0+, Safari     

        xmlHttp=new XMLHttpRequest(); 
    }   catch (e)   
    {    //Internet Explorer     

        try   
        {
            xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");     
        }   
        catch (e)
        {
            xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");     
        }  
    } 
    return xmlHttp;
}

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:

function showUser(str)
 {
   xmlHttp = GetXmlHttpObject()
   if (xmlHttp==null)
  {
    alert ("Browser does not support HTTP Request")
     return
   }
   else
  {…     // more code to come   …}

}

If an XMLHttpRequest object can be created, it is stored as the xmlHttp variable.

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:

var url="get_user"
var url=url+"?user_id="+str

Before we call this URL, we must tell the object to wait for a response. To do this, we use:

xmlHttp.onreadystatechange=stateChanged;

which will call the stateChanged() function whenever xmlHttp's readyState changes. For information about readyState, see Wikipedia.

The stateChanged() function is as follows:

function stateChanged()
{
   if (xmlHttp.readyState==4 || xmlHttp.readyState=="complete")
   {
      document.getElementById("txtHint").innerHTML=xmlHttp.responseText
   }
}

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);
xmlHttp.send(null);

The 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.

The JavaScript code is now complete.

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"
          WHERE "service_name"='get_user')
THEN 
    DROP SERVICE "get_user";
END IF
GO

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 get_user service.

CREATE SERVICE get_user
TYPE 'RAW'
AUTHORIZATION OFF
USER DBA
AS SELECT 'Age: ', Age,
          'Gender: ', Gender,
          'Hometown: ',Hometown,
          'Job: ',Job,
          'Score: ',Score
FROM ajax_demo WHERE id = :user_id;
GO

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:

1. 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.
2. Making a web service of type 'XML' and then parsing the XML with JavaScript: Web services can also be given the type 'XML'. This will set the response to an XML formatted table. Then, XML DOM or plain text techniques can be used to parse the responseXML (not responseText) with JavaScript.

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):

• ajax_demo.sql
• example.html
• webservices.sql

Sample Directions

The following procedure assumes you have downloaded your supplemental code to the C:\temp directory.

Setup your Windows CE device and the sample database:

1. Connect the Windows CE device to your computer using ActiveSync.
2. 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
3. Start the database engine. At a Windows command prompt, execute:

   C:\temp> dbeng10 demotable.db

4. 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

5. 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

6. 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:

7. Copy demotable.db, the database you just created into the My Documents folder of your Windows CE device.
8. Copy example.html to the My Documents directory of your Windows CE device.
9. Disconnect your Windows CE device from your computer.

   Start the database server on the Windows CE Device:

10. On your mobile device, navigate to File Explorer: Start > Programs > File Explorer
11. Navigate to dbsrv10.exe: My Device > Program Files > SQLAny10 > dbsrv10.
12. Tap dbsrv10. The server startup options will appear. Choose:
    • database: "My Documents\demotable.db"
    • server name: "CEserver"
      • cache size: "5MB"
      • options: "-gd all -xs http(port=8080)
      • TCP/IP: √

        where –gd all lets all users start and stop the database, and -xs http(port=8080) specifies an HTTP server to listen on port 8080.

    

    Seeing the finished AJAX example:

13. Browse to http://localhost:8080/example.html.
14. 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".
15. 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.

Considerations

• In the examples provided, the XMLHttpReqest object was created in a function call. However, if a browser does not support this object, creating the object in static JavaScript would inform the user of the error before the page loads.
• 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.

Afterword

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.