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 [email protected].