Creating Windows Mobile Databases with SQL Anywhere
Creating Windows Mobile Databases with SQL Anywhere
One of the biggest problems I've run into when developing for mobile devices is data storage. Two problems come up. First, I'll often be synchronizing with a server using a SQL database. Second, I'll want to use relational databases in my mobile app itself while most devices today don't natively support SQL. (The Android platform is an exception here. It includes SQLite.)
About a year ago, I started exploring Sybase's SQL Anywhere product for another project that I was working on here at the office, and I was quite impressed. SQL Anywhere is actually a suite of tools, and included in it are several desktop versions, as well as a version that runs on Windows Mobile. Additionally, there's the Ultralite edition, which runs on smaller Windows CE devices and Palm OS, and the Ultralite J edition-the J is for Java-which runs on BlackBerry and J2SE (Java 2 Platform Standard Edition).
What's cool about the Windows Mobile edition is it allows developers to have full relational database access through SQL right on a mobile device running Windows Mobile. Users can store the tables right on the device, and later synchronize them with a server. For many of us, this is exactly what we need.
Sybase recently released version 11 of SQL Anywhere. For this article, I'm exploring the SQL Anywhere on a Windows Mobile 6 device (specifically an HTC/AT&T 8525 phone that has been upgraded to Windows Mobile 6).
About six months ago, I was talking to a friend of mine and I mentioned I was trying a product from Sybase. He was a bit surprised and said, "Sybase? They're still around?" Indeed they are. While they seem to have fallen behind in the database competition against such players as Oracle and Microsoft SQL Server, Sybase is definitely still a major player.
The product I'm looking at, SQL Anywhere, comes from a subsidiary of Sybase called iAnywhere. iAnywhere was formed in May 2000, and the company has produced a huge set of products.
The SQL Anywhere product includes a free Developer version that I'm using for this article.
Windows Version and Windows Mobile Edition
To use the Windows Mobile edition, developers actually install the whole Windows desktop version of SQL Anywhere. With that comes the Windows Mobile edition that is then installed on the Windows Mobile device.
The full Windows version includes a whole set of development tools, including a .NET assembly that runs on Windows Mobile. That way, programmers can develop using C# within the familiar Visual Studio environment.
A Few Minor Problems
I did have a few problems up front. I have Visual Studio 2008 installed on my computer, but the SQL Anywhere installer kept wanting to launch the 2005 version of Visual Studio to do some additional installation. Also, while I normally have the annoying Vista User Account Control turned off, I had momentarily turned it back on about a week ago and forgot to turn it back off. When Visual Studio launched, I kept getting messages that it should run with Administrator privileges. Since the SQL Anywhere installer launched Visual Studio, I had no control over that. (Also, the README file included with SQL Anywhere talked about how Visual Studio 2008 would be out later this year. Apparently this installer was created before 2008 was released. Sybase really should fix that now that 2008 has been out for awhile.)
The other problem I had was finding the documentation. The documentation is available, but it has to be downloaded separately, which requires you log into the company's Web site with a username and password. (Registration is free.) Although this isn't a huge deal, from the standpoint of software usability, I shouldn't be left with the installation program closing and me left wondering where to go next. So for those installing the software and looking for the documentation, look in the README that opens after installation, and scroll down to the part on Documentation. Ignore the mention that the documentation was installed (it wasn't, even though it said it would be), and copy the provided link into the Web browser. There will be a download that installs the documentation; from there, users should head over to the C:\Program Files\SQL Anywhere 11\Documentation\en\HTMLHelp and open the sqlanywhere_en11.chm file to get started. Once a programmer finally jumps through the hoops and gets there, they'll find several "Getting Started" documents.
Also, for the samples, I had to hunt a bit for them, too; they're in the folder C:\Users\Public\Documents\SQL Anywhere 11\Samples.
Getting SQL Anywhere onto my Windows Mobile device was a bit of a problem as well-again, not because of functionality problems, but simply finding out how it's done. I resolved this pretty quickly through the forums on the Sybase site. In the directory C:\Program Files\SQL Anywhere 11\CE\Deployment, there's a program called cedeploy.exe. (Again, I'm not trying to gripe too much here, but I shouldn't have to go searching like this. A Google search of "cedploy.exe" yields only seven pages, and none in the official documentation. Fortunately, this is a one-time thing, and these issues here were the only real problems I encountered.)
The actual installation on the device, however, went perfectly fine. The utility created a CAB file, which I copied over to the device. I then ran the CAB file, which installed the software smoothly without incident.
Building in Visual Studio
Creating an SQL Anywhere for Windows Mobile application in Visual Studio is quite easy if you're familiar with creating database applications. One sample that I started with is in the directory C:\Users\Public\Documents\SQL Anywhere 11\Samples\SQLAnywhere\CE\ado_net_sample.
This sample demonstrates the iAnywhere.Data.SQLAnywhere namespace, which includes several of the usual types of classes for .NET database development. For example, the namespace includes classes such as SAConnection, SADataAdapter, and SADataReader. If a programmer has done database development on .NET, they'll see the obvious analogy between these classes and the usual database classes.
Additionally, they can use data-bound controls such as the DataGrid control. In other words, there should be very little learning curve for develping for SQL Anywhere on a Windows Mobile device.
Trying It Out
Building the sample application went smoothly. As I'm using Visual Studio 2008, Visual Studio needed to convert the sample project since it was created for Visual Studio 2005. But it converted without any errors.
I compiled the project and found no errors at all. I tried running it on the emulator, but the libraries weren't present in the emulator and so I received some runtime errors. To be honest, I'm not sure if this is because of the problems where the original SQL Anywhere installation tried to start up Visual Studio 2005 instead of 2008 or if it was some other problem. But instead of taking the time to track down the problem, I decided to just work directly with the actual device. In the past, I've found that working with the actual device is just as fast as working with the emulator, so I often skip the emulator.
Instead of using the deployment in Visual Studio, I kept it simple and just copied the executable over to the Windows Mobile onto the storage card. On the device, I ran the executable from the file manager program, and it worked. I saw a screen with two text boxes and a grid at the bottom.
I clicked the "connect" button, but received an error that it couldn't connect to the database. That makes sense, since I didn't actually start the server. My mistake. So I closed the application and, still on the device, went to the SQL Anywhere folder and ran the server. The server was pretty straightforward. I clicked a button to browse to the sample database, provided a connection name for the database, and finally launched it. The server displayed a tiny little console with log messages from the database startup, and at the end it said the database was ready and listening. It looked good.
I then went back to my sample application and ran it. I changed the connection string to use the name I supplied when starting the server. I received an error that the username and password were wrong. Oops.
Back to the docs. There's a section in the documentation under the Introduction called Sample Database; under that is a page called About the Sample Database. This page includes information on the sample database, including the username (DBA, all uppercase) and password (sql, all lowercase).
(Incidentally, this page also includes the full designs for the tables in the sample database, which comes in handy when playing with the samples.)
Next I updated the connection string with the username and password. Connection strings are one of those things we just learn from with experience. Usually to specify the username you use "userid;" that's what I used, and it worked. Here's the whole shebang:
That one worked and the program connected to the database, displaying a message "connected!" in the field where I typed the connection string. The SQL field was already filled in with a sample query, so I used that one. And it worked. The grid control filled with all the rows and columns of the Employee table. Excellent!
Since I wanted to really try this baby out, I did some joins and other interesting SQL tasks. First, I just tried joining the Employees table with the Departments table, using this SQL:
select * from employees e join departments d
on e.departmentid = d.departmentid
I received an exception. Unfortunately, the sample program didn't have an exception handler for this particular exception, so the whole program crashed and shut down. Darn. So I went back to the code in Visual Studio and added an additional, generic exception handler. Then I ran it again so I could see the errors without a crash. The message I received was that the data table already has a column named DepartmentID. That's fine; some database systems let you have two columns the same name if it's the result of a join, but allowing such is generally bad practice. I modified my SQL and named the actual columns I wanted in the result set:
select e.givenname, e.surname, d.departmentname
from employees e join departments d
on e.departmentid = d.departmentid
This result worked.
But what about stored procedures? Here's a simple one I typed in (very carefully):
create procedure myproc as select * from employees
I did this off the top of my head, expecting it to work, and it did. No exceptions or errors. (No result set either, of course, so it was hard to tell if it even did anything. But no news is good news.) I wasn't sure how to call a stored procedure, so I tried this:
And it worked! I saw a result set containing all the rows from the Employees table.
That's good enough for me. Looks like this thing really works. And it's surprisingly fast. In other words, I have here a full-featured SQL database right on my mobile phone.
Yes, my phone. As the guy on the Sprint commercial says, "Can you believe we still call these things phones?" (With apologies to AT&T, since that's who my carrier is.) Indeed, while talking to my wife on speakerphone, I can be issuing random queries to update a database. Who would figure?
Here's a recap of what I did: I compiled the sample application; I copied it to my device; I started the server; I ran the application and performed queries against the sample database.
But there's a lot more that this thing can do.
For one, you can do advanced replication between the server running on the device and one running on the desktop. Or-and this one is very cool-the server running on the device can accept connections from other machines, including your desktop. I couldn't possibly cover all the features in a short article, so go to the official Web site and check out what all it can do. It can be found here.
Also, there's a lot of interesting programming that can be found. For that, check out one of our other sites, DevSource.com, for an upcoming story with more information on programming this.
Senior Editor Jeff Cogswell can be reached at jeffrey.cogswell@ZiffDavisEnterprise.com.