From the June 2000 issue of MSDN Magazine.

Article
10/25/2019

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links. MIND

Info on the Go: Wireless Internet Database Connectivity with ASP, XML, and SQL Server

Srdjan Vujosevic and Robert Laberge

This article assumes you're familiar with Visual Basic, SQL, ASP, XML

Level of Difficulty 1 2 3

Many handheld wireless devices such as cellular phones and PDAs already have the ability to access Web sites. So how do you build Web applications that tap this wireless audience? Although there are a number of limitations to wireless devicesâ€"such as screen size, navigation, and connection speedâ€"you can use familiar Web development technologies to make your existing Web applications available to mobile users.
This article outlines the services and equipment currently available to support wireless Web access. A sample wireless-accessible Web site that dynamically draws data from a SQL Server database back end in real time is created using tools such as ASP and XML.

here's a new trend in wireless telecommunications, Internet, and database integration: Internet connectivity via handheld devices. More people are buying wireless handheld devices such as cellular phones and personal digital assistants (PDAs), and they demand access to online resources. This article discusses the missing link used to connect these portable devices with the vast resources of the Internet, focusing on connecting a cellular phone to your database-driven Web site.
The samples we'll provide are based on the Unwired Planet (Phone.com) UP.Link server platform and Wireless Markup Language (WML). There are other standards proposals, such as Handheld Device Markup Language (HDML), a WML predecessor, but we will not discuss them here. If you want to learn more about standardization visit https://www.wapforum.org, a site operated by the Wireless Application Protocol (WAP) Forum. WAP uses HTTP 1.1-compliant Web servers, which means that CGI, ASP, NSAPI, Java servlets, and other Internet technologies can also be used. WAP created the XML-based WML syntax, which is what most wireless systems use.
Some of the networks that support UP.Link Server Suite and offer wireless Internet access to subscribers are AT&T, Bell Mobility, DDI, France Telecom, GTE, Mannesmann, NEXTEL, Shinsegi, Telestra, US West, and Verizon Wireless,. Supported telephone types include the Alcatel One Touch POCKET, Alcatel One Touch VIEW, Motorola i1000, SAGEM GSM 900, SAGEM DCS 1800, Samsung SGH-800, Siemens S25, as well as the latest Motorola i500plus smart phone. For the complete list of supported telephone types visit https://www.phone.com/solutions/phones.html.

Components of a Wireless Web Service

      There are four basic components that make up a wireless Web service: UP.Browser phones, WML, UP.Link Server, and WML services.
      UP.Browser phones are handheld devices with special software that replace conventional Web browsers.
      The WML language is a programming language consisting of a set of statements that defines what the UP.Browser phone displays in its window and how it interacts with the user. Instead of Web pages, the wireless world uses decks consisting of cards. Here is an example of a static WML deck:

  
<?xml version="1.1"?>
  <wml>
    <card>
      <p>
        Welcome to WML world!
      </p>
    </card>
</wml>

      It's possible to create applications based only on static WML decks, but real-time information can only be provided to users by creating dynamic WML decks. This can be achieved by using CGI, the Java language, ASP, or any scripting language that can query databases dynamically and format the output to conform to WML standards. In effect, dynamic WML programming is the key to database-driven wireless connections.
      UP.Link server is the messaging relay between UP.Browser phones (cellular networks) and Web servers on computer networks.
      WML services are Web applications that return WML and other content in response to HTTP requests. WML services can support both push and pull transactions. Push transactions send information (notifications) to the UP.Browser phone asynchronously, independent of any interaction with the user. C++, Visual BasicÂ®, or Perl can be used to create push transactions. Pull transactions transmit information in response to a user request.
      The design of WML decks requires special considerations due to a few distinct limitations. First, handheld devices have a smaller screen size and resolution. A cell phone may only have a few lines of textual display, with each line containing eight to 12 characters. Models vary in window display size.
      Second, cellular phones have a numeric keypad and several additional function keys. A more sophisticated device may have software-programmable buttons, but there are no keyboards or mice in the wireless world.
      Third, handheld devices and cellular phones have limited computational resources. The low-power CPU and small memory size of these devices are often limited by power constraints.
      Finally, you have to deal with narrow-bandwidth connectivity. Handheld devices have low connection speeds and high latency. Devices with 300bps to 10Kbps network connections and five to 10-second round-trip latency are common.
      Note that PDA devices have a wider range of capabilities. Currently, the most common resolution is 160ï¿½100 pixels. Some PDAs support pointing devices.

How Wireless Communication Works

      Unlike the classic Web scenario where communication is direct to the Web server, wireless Web communication requires an intermediate server to translate messages between the cellular phone or other handheld device and the Web server (see Figure 1). This intermediate server also translates the responses from the database server back to WML display commands (whether you're using ASP, the Java language, or Perl).
      Certain additions to your Web server must be made to accept these new types of requests. You must add a new MIME type of text/vnd.wap.wml, with a file extension of wml. You must also add a 1-bit black and white bitmap image if you plan to use graphics in your service. This requires its own MIME type of image/bmp, with a file extension of bmp. Other useful MIME types include application/x-up-alert and application/x-up-cacheop.
      By the way, if you don't have a cellular phone or another Web-enabled handheld device, don't worryâ€"Phone.com and Unwired Planet (creators of the UP.Browser technology) provide an excellent emulator for cellular phones. Simply register as a developer at https://updev.phone.com and download the SDK. There are separate versions for HDML and WML.
      Here's a possible technical scenario. Imagine you've started a company, just you and your partner. You've invited a potential investor to dinner to present your business plan. If you succeed, you're in business; if not, it's back to the cubicle.
      You're both waiting on some very important data you've been gathering all day on your system in the office across town. No one is in the office because you both must be present to deal with the investors, but you must get that data in a matter of minutes.
      What do you do? Simple. You call up the database server using the wireless Internet capabilities from your cellular phone and query the database. The idea is to call your SQL Server and invoke a stored procedure or issue a SQL statement that will query the database and send the results back to your cell phone. Before you can do this, there are some preparatory steps you must take.

Creating the Sample Database

      The type of back end database you will use depends on the type of application you are going to connect to the wireless network. From the Web server's perspective, this is not an issue as long as there is a standard method of connectivity available for the database, such as ODBC, Data Access Objects (DAO), or ActiveXÂ® Data Objects (ADO).
      In this example, we'll use a MicrosoftÂ® SQL Serverâ„¢ 7.0 database called WAPTest. With minor modifications, this script will work on SQL Server 6.5 and Sybase servers. Use the script in Figure 2 to create a database. Then use the scripts in Figure 3 to create the required tables.
      The following simple WISQL scripts can be used to populate the security and items tables:

  
/** Create two entries in Security table**/
insert into WMLSecurity (UID,PASS,CustomerNO)
    values ('123456','1234','2322780')
insert into WMLSecurity (UID,PASS,CustomerNO)
    values ('654321','4321','2322781')
/** Adjust some data and flag one user as disabled **/
update WMLSecurity set LastVisit = getdate(),
    visitssofar = visitssofar + 1 where UID = '123456'
update WMLSecurity set Enabled = "N" where CustomerNO = '2322781'
/** Create five entries in Items table**/
insert into WMLItems (ItemValue, ItemQuantity, WhoCanSee,
    ItemName, Sold) values (5.25, 50, '2322780', 'Socks', 10)
insert into WMLItems (ItemValue, ItemQuantity, WhoCanSee,
    ItemName, Sold) values (5.00, 40, '2322780', 'Socks', 6)
insert into WMLItems (ItemValue, ItemQuantity, WhoCanSee,
    ItemName, Sold) values (15.75, 4, '2322780', 'Music Box', 2)
insert into WMLItems (ItemValue, ItemQuantity, WhoCanSee,
    ItemName, Sold) values (55.25, 4, '2322781', 'Modem', 1)
insert into WMLItems (ItemValue, ItemQuantity, WhoCanSee,
    ItemName, Sold) values (4.25, 25, '2322780', 'Blank CD', 15)

To avoid any additional costs (since some ISPs charge for DSN entry creation for database connectivity), we'll connect to the database using a DSN-less connection from the ASP pages. The WMLSecurity table will be used for security to log into the SQL Server database, and the WMLItems table will be used as the main reporting table.

Implementing the WML Deck

Our sample code will be a combination of four pages; two WML decks and two dynamic ASP pages will enable our IS professional to log in and query the database. Then the database will send the results, reformatted in WML, to the partner's cell phone without the client ever knowing what happened.
Let's start by creating the WML deck, which will serve as the main gateway. The code (shown in Figure 4) will also include a sample telephone directory, which is important for the technical support aspects of the design. Here are the screen images produced by the code in Figure 4.

If one partner would like to reach some of the people in the telephone list, say technical support or the boss, he would select option 1, which would display the following screen:

The following code represents the login.wml deck:

  
<?xml version="1.0"?>
<!DOCTYPE wml PUBLIC "-//PHONE.COM//DTD WML 1.1//EN"
"https://www.phone.com/dtd/wml11.dtd" >
<!--  Happy Comp Login code for file login.wml -->
<wml>
<head>
<meta http-equiv="Cache-Control" content="max-age=0"/>
</head>
<card>
<do  type="accept" label="Login">
<go  href="https:// YourSiteName /login.asp?
    User=$(User)&amp;Smart=$(Smart)"/>
</do>
<p>
<b>SQL Tool Login</b>
<br/>User ID:
<input name="User" maxlength="10" type="text" emptyok="false"/>
<br/>
<b>SQL Tool Login</b>
<br/>Password:
<input name="Smart" maxlength="4" type="password"
    emptyok="false"/>
<br/>
</p>
</card>
</wml>

This code will be invoked from the following line in the WML program shown in Figure 4.

  
<option onpick="https://YourSiteName/login.wml">
<img  localsrc="wrench" alt="" src=""/>
&nbsp;SQL Tasks</option>

By simply pressing 2 key on the keypad of your cell phone, the deck will allow the user ID and password to be passed to the ASP page, which in turn will log in and establish a SQL Server session.
Here's what the window of the cell phone will look like when the deck is executed:

The login information will be passed to the login.asp routine, which will execute the ADO connection to SQL Server. Note that it is very important to separate passed parameters with the HTML equivalent for an ampersand, &. The login.asp routine is invoked from this line in the login.wml code:

  
<go  href="https://YourSiteName/login.asp?
    User=$(User)&amp;Smart=$(Smart)"/>

      Due to limitations in the size of the information that can be passed to WAP-enabled devices, you should try to use simple but clear messages for users. Also, use drilldown menus as much as possible to avoid the cumbersome data entry on cellular phones.
      In order to test the sample code, replace YourSiteName with your own site name since you'll be copying the source code to your own server.
      The ASP program in Figure 5 will evaluate the passed user ID and password and, depending on the result, execute one of the following options:

If the user ID or password was incorrect, it will present the user with a message and let her try again.
If the user ID and password are correct but the administrator has disabled login, the program will present the user with the company's telephone number to call.
If the login is OK, the user can proceed to the WML page where she'll be allowed to make a selection from the list of tasks to be performed.

The following screen will be presented to the partner after successfully logging in:

Great, we're in! Now she can execute the SQL statement, which will produce the anticipated results on the cellular phone window in real time.
The following SQL statements will be individually executed by pressing keys 1 to 4 on the cellular phone keypad:

  
select count(*) as UserNo from master..sysprocesses where
    substring(db_name(dbid),1,10)='WAPTest'
select ItemName, avg(ItemValue) as AverageP from WMLItems
    group by ItemName
select ItemName, sum(Sold) as ItemsSold from
    WMLItems group by ItemName
select convert(varchar, Sum(ItemValue * Sold)) from WMLItems

Note that in the DDL for the WMLItems table, a field called WhoCanSee also exists. In this example, WhoCanSee will not be used, but its purpose is to limit access to certain records only to authorized personnel.
Figure 6 shows the ASP code to execute the previous SQL statements and the reply that will be returned to the browser.

Getting the Required Data

The following four screens show the results from all of our queries. They are the dynamic representations of the data from the SQL Server table.

      The partner now has the results in real time on his cellular phone. The investors have no idea of the work involved, and yet the results are accurate and up-to-the-minute. In conclusion, the deal is successful, the potential investor is happy with the technology, and the partners are in business!
      All the code presented in this article can be modified to enhance the usability and the flexibility of the process. For example, in the table creation DDL (see Figure 3), you will notice a table called WMLOptions. This table, which we haven't mentioned yet, can be used to dynamically drive menus that are presented to the user. This method allows maintenance and changes to the code to be significantly reduced. Also, this table can be used to assign some of the options to selected users and hide them from others. The task of incorporating this table access into the code presented here is up to you. The code in Figure 7 can be used as a guideline.
      New and emerging technologies are already in place not only to offer the database connectivity shown here, but also to use databases on PDAs and cellular phones. Some industry analysts predict that in three years more people will be accessing the Internet via wireless devices than from desktop PCs. As a result, you should see more features being developed and integrated for these devices in the coming months.

For related articles see:
https://www.phone.com/products/upsdk.html
https://msdn.microsoft.com/downloads/samples/Internet/xml/adoxml/sample.asp
Background information:
https://www.microsoft.com/ISN/telco/default.asp
https://www.microsoft.com/ISN/ind_solutions/wireless_data.asp
Srdjan Vujosevic and Robert Laberge are the creators of WorldJobMart.com, one of the first Internet job sites available on wireless handheld devices. They're also creators of WaveDev.com, a venture that specializes in wireless-enabling Internet sites using WAP, WML, and HDML. The authors can be reached at authors@wavedev.com.