Calling SQL Server With A BlackBerry Phone

R Glen Cooper

Overview

This article provides a simple Web page to play multimedia files on a home computer using a BlackBerry phone. It does this by using SQL Server to store selected binary images (documents, music, videos, etc.) bulk copied to it. Such images may be opened on a smart phone, copied to other SQL Servers, or downloaded to remote machines.

Examples

For this article, I used a BlackBerry Bold 9700 phone connected to SQL Server Express 2005 on a Windows XP desktop hosting IIS. To connect, I enter

http://nnn.nnn.nnn.nnn/images/images.asp?helpLevel=0

into my BlackBerry browser (where nnn.nnn.nnn.nnn is the external IP address of my home computer):

At this point I can View selected books (pdf) that I read in airports, a spreadsheet of several thousand addresses (handy in a meeting), sample movies (wmv) and various holiday pics (.jpg) to show friends, and a few Word and text files (doc/txt). But what I enjoy most are the audio books (mp3) that I play when walking the dog.

Note that you'll need to install a PDF reader on your BlackBerry, such as the excellent package RepliGo Reader.

I can use the page to download these files to my phone, but memory is scarce so I generally leave them on the desktop. Fortunately, when I'm near my wireless LAN it takes priority for communication, so no service charges are incurred. More importantly, I can modify (or even automate) the multimedia lineup by writing a simple batch job on the desktop that hosts it.

My partner and I use different networks at home, so this page allows her to get our vacation clips after they've been edited on my machine (eliminating the need for emails or memory sticks). Privacy is not an issue because these files never sit on other computers. As well, only selected IP addresses are allowed to sign in.

To interactively transfer files (eg. zip files) from my desktop to other machines, I increase the help level to expose a form for entering client information:

http://nnn.nnn.nnn.nnn/images/images.asp?helpLevel=1

After entering that information (which represents a local machine, although an external IP address could be used) and pressing the Refresh button, I'm ready to transfer files:

After pressing the Download link for any file, that file disappears from the list when the Refresh button is pressed. That's because the application only lists host files that aren't among the client files (determined by the Id value, which is a non-identity key).

Of course, this operation assumes that the client machine has the same table that the host uses for storing images (nothing more is required).

Later, to transfer files to other machines (on my partner's network, for example), I modify the client parameters and hit the Refresh button again.

Both the host and client machines may be pre-set in the Web link, and by setting the help level to 0 only the row of files and download links are displayed. To keep these browser links really short for the smart phone crowd, all parameters may be hard-wired into the app itself so that only the address of the page is required. However, by increasing the help level instead, more diagnostic information is displayed (useful for debugging).

The All link downloads all files at once.

How It Works

The system uses a single table called Images in a SQL Server database on the host desktop:

Its sole purpose is to store a multimedia lineup in a way that my phone can get at it.

Actually that's not quite true. If I wanted to preserve some confidential documents from a variety of formats (text, voice, video, etc.) but still be able to catalog and query its contents, this would be one way to do that. And if my lawyer wanted some of it while he's in a meeting, he could use my page from his phone.

Anyway, the standard IIS Web site (accompanied by its asp service) is set up on the XP desktop that's hosting the database. Then a simple asp page displays a form to get client server particulars (unless they're hard-wired into the page, or contained in the caller's hyperlink). If I specify View, then the appropriate image is downloaded to a recordset, and displayed by my browser (after using the image name to determine its MIME type). If I specify Download, then the host is sent an INSERT query that uses SQL Server's OPENDATASET function to make the client server part of that query.

So the real work occurs on the asp page.

However, we first need a convenient way to populate and maintain the multimedia lineup. Three batch files LoadImages.bat, SaveImages.bat, DeleteImages.bat are used to load, save and delete multimedia images in SQL Server. For example, LoadImages.bat loads images into SQL Server using a parameter file LoadImagesParameters.bat to specify server parameters. In turn, it calls the stored procedure

spLoadImages.sql to perform the loading. The other batch files work the same way.

After using these batch files to set up the images table, the asp page Images.asp may be called by my phone browser to view or download them to another server. If I decide to view images, then Image.asp is called where the appropriate parameters are passed. Otherwise ImageDownload.asp is called to download them to another server.

The system is portable so you can use your own table for storing images by changing some parameters in the source code (some limitations apply).

The code is in the Resources section below in the ZIP file.

Summary

This web page for connecting to SQL Server from a BlackBerry has proved surprisingly useful, given its simplicity.

Results may vary depending on the speed of your wireless connection (buffering may happen during busy sessions). Also, what media you may open varies with the device and file size. Audio books (mp3) can be quite large (> 20 Meg.). For increased security, use https:// (but you'll need a certificate which won't work on BlackBerries).

PDF files should be

You must enable Ad Hoc Remote Queries and OLE Automation for SQL Server (use Surface Area Configuration For Features to do this).

This page was originally written for a large merge replication project, where employee images needed to be replicated over a slow satellite connection in Canada's Northwest Territories.