Development | Starting Out

Retrieving Device Data from the Desktop with SQLink
Written by Christopher Tacke  [author's bio]  [read 37419 times]
Edited by Derek

         

Page 1  Page 2 

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 an 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 from http://www.innovativedss.com/sqlink.asp 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:

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 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:

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

Next Page 

Back to Starting Out | [Article Index]