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