Page 1
Page 2
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.
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: BeforeExecute, TransferProgress
(twice) and ExecuteComplete. These will show up in
the lower right ListBox on the form of SQLinkSample (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.
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:
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.
Conclusion
SQLink fills the large gap between having
no PC-side connectivity to device data and the very expensive
commercial enterprise suites available. With very little
code, you can pull data from or push data to the Pocket
PC from any VB application, giving you full control over
what any data filtering and manipulation.
Previous Page