Home Contact Us
Pocket Regress
Recent News
SQLink
Retrieving Device Data from the Desktop with SQLink

©2001, Innovative Decision Support Systems, Inc.
Last updated: 10/13/01

Download the Evaluation Version of SQLink Here! (296K)

Introduction

About a year ago we started development on an enterprise application for a customer that had several Pocket PC devices that were used for data collection in the field. The idea was that an administrator could assign certain "jobs" to a user through a PC interface and push those jobs to the user's device. The user would then collect field data and when they were done at the end of the day, the data would be extracted from the devices and put into a central database.

The customer wanted the data access to be driven not from the device, but from the desktop. They wanted menu items like "Send Jobs to Device," "Get Completed Jobs From Device," and "Delete Finished Jobs On Device." While this seemed like a simple task, we couldn't find any tools to do it.

We dismissed ActiveSync as an option because we wanted to be able to scale the application to SQL Server as well as provide direct interfacing in the PC application. We looked at SQL Server for CE, but there was no way to initiate a data transfer from the PC. We also looked at a couple popular commercial applications. These, again, required device-side action plus they had a per-user or per-device pricing and we were trying to provide a fixed-cost solution that the client could scale to their desire and need without having to purchase licenses.

We finally came to the conclusion that we were going to have to write our own data transfer engine and so was born the concept of SQLink. SQLink is not a synchronization engine, and it never was intended to be one. It doesn't compare, say, and Access database and a CDB on the device and determine what is different about the two. Instead SQLink gives the desktop programmer the ability to directly run SQL commands in PC applications against Pocket PC device databases.

Architecture

SQLink consists of two pieces: a device-side executable (SQLink.exe) and a PC side Active-X library (SQLink.dll). The general architecture follows this outline:

The PC applications (Server) creates a SQLink object.
The Server connects to the Pocket PC device (Client).
The client launches the silent SQLink executable, which calls back to the Server.
The Server can now pass SQL statements to the Client and the Client can pass back data.
The returned data is put into a Recordset object on the Server.
A notification event fires to let the PC application know the data is ready.

Using SQLink

Simplicity was one of our requirements for SQLink and the end-product shows it. Still, it is important to know that SQLink operates in a non-blocking, or asynchronous, manner. This means that when you run a SQL statement, the Server application does not wait for the data to return from the Client before it continues processing. Instead, an event fires when the data is ready and at that point the Server can use the data in Recordset format. This requires a little more thought in architecting a Server application, but it allows large datasets to be transferred through slow connections (like a serial port) without causing the Server application to appear "locked" to the user.

The easiest way to understand how SQLink works is through a sample project. Download the SQLink sample project here for this walk through.

The SQLink library has a single object called Link. It is through the Link that all SQLink data transfer occurs, so the first thing the project does is create a Link object:

    Listing 1 - Creating the Link object
    Private Sub Form_Load()
    Set obj = New SQLink.Link
    'init code for command list
    End Sub

Form_Load also contains some initialization code that fills the Command ListBox to make things a bit easier to work with by allowing us to select pre-written commands instead of having to type them in. By double-tapping an item in the list, txtSend get automatically populated.

Connecting to the Device

Once the object has been created, the next step is to Connect to the device. This connection is made via ActiveSync if , so the device must be connected to the PC via ActiveSync, though it doesn't matter whether the connection is serial, USB, or TCP/IP. To connect, simply call the Connect method of the Link object. cmdConnect Click shows how to do this:

    Listing 2 - Connecting to the device
    Private Sub cmdConnect_Click()
    If txtPort <> "" Then
    ' use user-defined port
    obj.Connect txtHost, CInt(txtPort)
    Else
    ' use default port (4377)
    obj.Connect txtHost
    End If
    End Sub

The Host and Port parameters are both optional. In fact, situations where either are necessary are rare. The Host parameter is either the host name or IP address of the machine that is listening for a Link connection. In most cases, this will be the name or IP of the machine on which your SQLink application is running, and if you leave it blank, SQLink will actually fill in your local host name as the value for you. If you had a non-standard requirement, you could launch a Link from one PC and build a custom application that would listen for device connections and run on a completely separate machine. This is fairly esoteric and won't be covered in this article, but be aware that it is an option.

The Port is simply the port number through which the Link will be made. This defaults to 4377, but you may need to modify this if it is being used internally on your network for other purposes.

When a connection is made with the evaluation version, a splash screen will appear on the device (Figure 1). This is the only difference between the evaluation and production versions of SQLink.

Figure 1

Creating a Database

Once you have made a connection, the next step is to start executing SQL statements against the device. First, let's create a new database on the device. Run the following by typing or selecting it into the cmdSend TextBox and clicking Send:

CREATE DATABASE '\MyDB.cdb'

This will create the database MyDB.cdb in the root directory of the Pocket PC device by sending the command through the Execute method of the Link object. You will notice that several events will fire during the execution of the command: Before Execute, TransferProgress (twice) and ExecuteComplete. These will show up in the lower right ListBox on the form of SQLinkSample (Figure 2).

Figure 2

TransferProgress fires at least twice for every command, once for 0% and once for 100%. If the command will bring back large amounts of data, the data will be transferred in packets and TransferProgress will fire with the transfer of each packet, providing you a gauge of how much of the data has been transferred. This is especially useful for updating a progress bar for the user.

Changing the RemoteDatabase

The next step is extremely important, and can (and has) caused a lot of debugging headaches. You must now set the RemoteDatabase property to the path to the database you just created, in this case "\MyDB.cdb". If you don't change the RemoteDatabase, or if there is any error in the RemoteDatabase property (misspelling, database doesn't exist, etc.), ADOCE will go straight to the native data store without informing SQLink. This means

Running SQL Commands Against a Database

Running SQL commands against the remote database is now simple. Using the Execute method of the Link object, you can execute commands that do or do not return data. Throughout the execution process, several events will fire. If the command returns data, when the ExecuteComplete event fires, your data set is ready.

For example, let's look at creating a new table, inserting data into that table then retrieving the data from the table.

You could do this from the sample application's GUI or through code. Through code it would look something like this:

    Listing 3 - Creating a table, adding data, and retrieving data
    Public rs As ADOCE.Recordset
    Public bData As Boolean

    Sub CreateAndPopulate()
    bData = False
    obj.Execute "CREATE TABLE Inspectors (ID int, Name varchar(20))"
    obj.Execute "INSERT INTO Inspectors VALUES (1, 'Bill')"
    obj.Execute "INSERT INTO Inspectors VALUES (2, 'Janine')"

    bData = True
    Set rs = obj.Execute("SELECT * FROM Inspectors")
    End Sub

    Sub obj_ExecuteComplete(ByVal RequestID As Long)
    If Not bData Then Exit Sub

    MsgBox "You have " & rs.Recordcount & " records."
    End Sub

At this point you could use the Recordset as a disconnected recordset for your needs. Modifying the Server Recordset or the data it contains has no effect on the Client database. To modify the client database you must use SQL commands such as UPDATE passed through the Link object's Execute method.

Disconnecting

When you are finished transferring data, disconnect the Link by calling the Disconnect method.

;